Edit C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\odbc\7.0.1\help\reference\lock.09.3.html
<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xml:lang="en" lang="en" xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html;charset=utf-8" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <!-- MOTW-DISABLED saved from url=(0014)about:internet --> <title>Isolation Levels</title> <link rel="StyleSheet" href="css/lock.css" type="text/css" media="all" /> <link rel="StyleSheet" href="css/webworks.css" type="text/css" media="all" /> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/context.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/towwhdir.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/wwhpagef.js"></script> <script type="text/javascript" language="JavaScript1.2"> <!-- var WebWorksRootPath = ""; // --> </script> <script type="text/javascript" language="JavaScript1.2"> <!-- // Set reference to top level help frame // var WWHFrame = WWHGetWWHFrame("", true); // --> </script> <script type="text/javascript" language="JavaScript1.2" src="scripts/expand.js"></script> </head> <body class="" style="background-color: #FFFFEE;" onload="WWHUpdate();" onunload="WWHUnload();" onkeydown="WWHHandleKeyDown((document.all||document.getElementById||document.layers)?event:null);" onkeypress="WWHHandleKeyPress((document.all||document.getElementById||document.layers)?event:null);" onkeyup="WWHHandleKeyUp((document.all||document.getElementById||document.layers)?event:null);"> <br /> <div class="WebWorks_Breadcrumbs" style="text-align: left;"> <a class="WebWorks_Breadcrumb_Link" href="lock.09.1.html#106172">7 Locking and Isolation Levels</a> : Isolation Levels</div> <hr align="left" /> <blockquote> <div class="head_1"><a name="106172">Isolation Levels</a></div> <div class="Body"><a name="106173">An isolation level represents a particular locking strategy employed in the database system </a>to improve data consistency. The higher the isolation level, the more complex the locking strategy behind it. The isolation level provided by the database determines whether a transaction will encounter the following behaviors in data consistency:</div> <table class="Format_B" cellspacing="0" summary=""> <caption></caption> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><span style="color: #000000; font-size: 10.0pt; font-style: normal; font-variant: normal; font-weight: normal; text-transform: none; vertical-align: baseline;"><a name="106177">Dirty reads </a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106179">User 1 modifies a row. User 2 reads the same row before User 1 </a>commits. User 1 performs a rollback. User 2 has read a row that has never really existed in the database. User 2 may base decisions on false data.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106182">Non-repeatable reads</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106184">User 1 reads a row, but does not commit. User 2 modifies or </a>deletes the same row and then commits. User 1 rereads the row and finds it has changed (or has been deleted).</div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106187">Phantom reads </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106189">User 1 uses a search condition to read a set of rows, but does </a>not commit. User 2 inserts one or more rows that satisfy this search condition, then commits. User 1 rereads the rows using the search condition and discovers rows that were not present before.</div> </td> </tr> </table> <div class="Body"><a name="106190">Isolation levels represent the database systemâ??s ability to prevent these behaviors. The </a>American National Standards Institute (ANSI) defines four isolation levels: </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="106191">Read uncommitted (0)</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="106192">Read committed (1)</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="106193">Repeatable read (2)</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="106194">Serializable (3) </a></div> </td> </tr> </table> </div> <div class="Body"><a name="106195">In ascending order (0â??3), these isolation levels provide an increasing amount of data </a>consistency to the transaction. At the lowest level, all three behaviors can occur. At the highest level, none can occur. The success of each level in preventing these behaviors is due to the locking strategies they use, which are as follows:</div> <table class="Format_B" cellspacing="0" summary=""> <caption></caption> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106199">Read uncommitted (0) </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106201">Locks are obtained on modifications to the database and held </a>until end of transaction (EOT). Reading from the database does not involve any locking.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106204">Read committed (1) </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106206">Locks are acquired for reading and modifying the database. </a>Locks are released after reading but locks on modified objects are held until EOT.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106209">Repeatable read (2) </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106211">Locks are obtained for reading and modifying the database. </a>Locks on all modified objects are held until EOT. Locks obtained for reading data are held until EOT. Locks on non-modified access structures (such as indexes and hashing structures) are released after reading.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106215">Serializable (3) </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106217">All data read or modified is locked until EOT. All access </a>structures that are modified are locked until EOT. Access structures used by the query are locked until EOT.</div> </td> </tr> </table> <div class="Body"><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'lock.09.3.html#106230', '');" name="106222">Table 7-1</a></span> shows what data consistency behaviors can occur at each isolation level.</div> <table class="Format_A" cellspacing="0" summary=""> <caption> <div class="table_title">Table 7-1. <a name="106230">Isolation Levels and Data Consistency</a> </div> </caption> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106238">Level</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106240">Dirty Read</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106242">Nonrepeatable Read</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106244">Phantom Read</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106246">0, Read uncommitted</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106248">Yes</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106250">Yes</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106252">Yes</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106254">1, Read committed</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106256">No</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106258">Yes</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106260">Yes</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106262">2, Repeatable read</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106264">No</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106266">No</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106268">Yes</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106270">3, Serializable</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106272">No</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106274">No</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106276">No</a></div> </td> </tr> </table> <div class="Body"><a name="106939">Although higher isolation levels provide better data consistency, this consistency can be </a>costly in terms of the concurrency provided to individual users. Concurrency is the ability of multiple users to access and modify data simultaneously. As isolation levels increase, so does the chance that the locking strategy used will create problems in concurrency. </div> <div class="Body"><a name="106280">The higher the isolation level, the more locking involved, and the more time users may </a>spend waiting for data to be freed by another user. Because of this inverse relationship between isolation levels and concurrency, you must consider how people use the database before choosing an isolation level. You must weigh the trade-offs between data consistency and concurrency, and decide which is more important.</div> <script type="text/javascript" language="JavaScript1.2"> <!-- // Clear related topics // WWHClearRelatedTopics(); document.writeln(WWHRelatedTopicsInlineHTML()); // --> </script> </blockquote> <script type="text/javascript" language="JavaScript1.2"> <!-- document.write(WWHRelatedTopicsDivTag() + WWHPopupDivTag() + WWHALinksDivTag()); // --> </script> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de