341 lines
19 KiB
HTML
341 lines
19 KiB
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 xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-us">
|
||
|
<head>
|
||
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
|
<meta name="dc.language" scheme="rfc1766" content="en-us" />
|
||
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
||
|
<!-- US Government Users Restricted Rights -->
|
||
|
<!-- Use, duplication or disclosure restricted by -->
|
||
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
||
|
<meta name="dc.date" scheme="iso8601" content="2005-09-19" />
|
||
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
<meta name="security" content="public" />
|
||
|
<meta name="Robots" content="index,follow"/>
|
||
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
||
|
<meta name="keywords" content="description, isolation level, share, locks,
|
||
|
share locks, exclusive, exclusive locks, repeatable read, RR,
|
||
|
*RR (repeatable read) precompiler option, RR (repeatable read), phantom rows,
|
||
|
read stability, RS, *RS (read stability) precompiler option,
|
||
|
*ALL (read stability) precompiler option, RS (read stability), cursor stability,
|
||
|
CS, *CS (cursor stability) precompiler option, CS (cursor stability),
|
||
|
uncommitted read (UR), uncommitted read,
|
||
|
*UR (uncommitted read) precompiler option,
|
||
|
*CHG (uncommitted read) precompiler option, UR (uncommitted read), no commit, NC,
|
||
|
*NC (no commit) precompiler option, *NONE (no commit) precompiler option,
|
||
|
NC (no commit), comparison, dirty read, nonrepeatable read, phantom row" />
|
||
|
<title>Isolation level</title>
|
||
|
<link rel="stylesheet" type="text/css" href="ibmidwb.css" />
|
||
|
<link rel="stylesheet" type="text/css" href="ic.css" />
|
||
|
</head>
|
||
|
<body>
|
||
|
<a id="Top_Of_Page" name="Top_Of_Page"></a><!-- Java sync-link -->
|
||
|
<script language = "Javascript" src = "../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
||
|
|
||
|
<a name="isol"></a>
|
||
|
<h2 id="isol"><a href="rbafzmst02.htm#ToC_52">Isolation level</a></h2><a id="idx132" name="idx132"></a>
|
||
|
<p>The <span class="italic">isolation level</span> used during the execution of
|
||
|
SQL statements determines the degree to which the activation group is isolated
|
||
|
from concurrently executing activation groups. Thus, when activation group
|
||
|
P executes an SQL statement, the isolation level determines: </p>
|
||
|
<ul>
|
||
|
<li>The degree to which rows retrieved by P and database changes made by P
|
||
|
are available to other concurrently executing activation groups.</li>
|
||
|
<li>The degree to which database changes made by concurrently executing activation
|
||
|
groups can affect P.</li></ul>
|
||
|
<p>The isolation level can be explicitly specified on a DELETE, INSERT, SELECT
|
||
|
INTO, UPDATE, or select-statement. If the isolation level is not explicitly
|
||
|
specified, the isolation level used when the SQL statement is executed is
|
||
|
the <span class="italic">default isolation level</span>.</p>
|
||
|
<p>DB2 UDB for iSeries provides several ways to specify the <span class="italic">default isolation
|
||
|
level</span>:</p>
|
||
|
<ul>
|
||
|
<li>Use the COMMIT parameter on the CRTSQLxxx, STRSQL, and RUNSQLSTM commands
|
||
|
to specify the default isolation level.</li>
|
||
|
<li>Use the SET OPTION statement to specify the default isolation level within
|
||
|
the source of a module or program that contains embedded SQL.</li>
|
||
|
<li>Use the SET TRANSACTION statement to override the default isolation level
|
||
|
within a unit of work. When the unit of work ends, the isolation level returns
|
||
|
to the value it had at the beginning of the unit of work.</li>
|
||
|
<li>Use the isolation-clause on the SELECT, SELECT INTO, INSERT, UPDATE, DELETE,
|
||
|
and DECLARE CURSOR statements to override the default isolation level for
|
||
|
a specific statement or cursor. The isolation level is in effect only for
|
||
|
the execution of the statement containing the isolation-clause and has no
|
||
|
effect on any pending changes in the current unit of work.</li></ul>
|
||
|
<p>These isolation levels are supported by automatically locking the appropriate
|
||
|
data. Depending on the type of lock, this limits or prevents access to the
|
||
|
data by concurrent activation groups that use different commitment definitions.
|
||
|
Each database manager supports at least two types of locks: </p>
|
||
|
<dl>
|
||
|
<dt class="bold"><span class="bold">Share</span></dt><a id="idx133" name="idx133"></a><a id="idx134" name="idx134"></a>
|
||
|
<dd>Limits concurrent activation groups that use different commitment definitions
|
||
|
to read-only operations on the data.
|
||
|
</dd>
|
||
|
<dt class="bold"><span class="bold">Exclusive</span></dt><a id="idx135" name="idx135"></a><a id="idx136" name="idx136"></a>
|
||
|
<dd>Prevents concurrent activation groups using different commitment definitions
|
||
|
from updating or deleting the data. Prevents concurrent activation groups
|
||
|
using different commitment definitions that are running COMMIT(*RS), COMMIT(*CS),
|
||
|
or COMMIT(*RR) from reading the data. Concurrent activation groups using different
|
||
|
commitment definitions that are running COMMIT(*UR) or COMMIT(*NC) are allowed
|
||
|
to read the data.
|
||
|
</dd>
|
||
|
</dl>
|
||
|
<p>The following descriptions of isolation levels refer to locking data in
|
||
|
row units. Individual implementations can lock data in larger physical units
|
||
|
than base table rows. However, logically, locking occurs at the base-table
|
||
|
row level across all products. Similarly, a database manager can escalate
|
||
|
a lock to a higher level. An activation group is guaranteed at least the minimum
|
||
|
requested lock level.</p>
|
||
|
<p>For a detailed description of record lock durations, see the discussion
|
||
|
and table in the <a href="../sqlp/rbafydicomm.htm">commitment control</a> topic of the <a href="../sqlp/rbafykickoff.htm">SQL Programming</a> book.</p>
|
||
|
<p>DB2 UDB for iSeries supports five isolation levels. For all isolation levels except No
|
||
|
Commit, the database manager places exclusive locks on every row that is inserted,
|
||
|
updated, or deleted. This ensures that any row changed during a unit of work
|
||
|
is not changed by any other activation group that uses a different commitment
|
||
|
definition until the unit of work is complete. The isolation levels are:</p>
|
||
|
<a name="wq24"></a>
|
||
|
<h3 id="wq24"><a href="rbafzmst02.htm#ToC_53">Repeatable read</a></h3><a id="idx137" name="idx137"></a><a id="idx138" name="idx138"></a><a id="idx139" name="idx139"></a><a id="idx140" name="idx140"></a><a id="idx141" name="idx141"></a>
|
||
|
<p>The Repeatable Read (RR) isolation level ensures: </p>
|
||
|
<ul>
|
||
|
<li>Any row read during a unit of work is not changed by other activation
|
||
|
groups that use different commitment definitions until the unit of work is
|
||
|
complete.<sup class="fn"><a href="rbafzmstisol.htm#readhld">5</a></sup></li>
|
||
|
<li>Any row changed (or a row that is currently locked with an UPDATE row
|
||
|
lock) by another activation group using a different commitment definition
|
||
|
cannot be read until it is committed.</li></ul>
|
||
|
<p>In addition to any exclusive locks, an activation group running at level
|
||
|
RR acquires at least share locks on all the rows it reads. Furthermore, the
|
||
|
locking is performed so that the activation group is completely isolated from
|
||
|
the effects of concurrent activation groups that use different commitment
|
||
|
definitions.</p>
|
||
|
<p>In the SQL 2003 Core standard, Repeatable Read is called Serializable.</p>
|
||
|
<p>DB2 UDB for iSeries supports repeatable-read through COMMIT(*RR). Repeatable-read isolation
|
||
|
level is supported by locking the tables containing any rows that are read
|
||
|
or updated.</p>
|
||
|
<a name="wq25"></a>
|
||
|
<h3 id="wq25"><a href="rbafzmst02.htm#ToC_54">Read stability</a></h3><a id="idx142" name="idx142"></a><a id="idx143" name="idx143"></a><a id="idx144" name="idx144"></a><a id="idx145" name="idx145"></a><a id="idx146" name="idx146"></a><a id="idx147" name="idx147"></a>
|
||
|
<p>Like level RR, level Read Stability (RS) ensures that: </p>
|
||
|
<ul>
|
||
|
<li>Any row read during a unit of work is not changed by other activation
|
||
|
groups that use different commitment definitions until the unit of work is
|
||
|
complete. <sup class="fn"><a href="rbafzmstisol.htm#readhld">5</a></sup></li>
|
||
|
<li>Any row changed (or a row that is currently locked with an UPDATE row
|
||
|
lock) by another activation group using a different commitment definition
|
||
|
cannot be read until it is committed.</li></ul>
|
||
|
<p>Unlike RR, RS does not completely isolate the activation group from the
|
||
|
effects of concurrent activation groups that use a different commitment definition.
|
||
|
At level RS, activation groups that issue the same query more than once might
|
||
|
see additional rows. These additional rows are called <span class="italic">phantom rows</span>.</p>
|
||
|
<p>For example, a phantom row can occur in the following situation: </p>
|
||
|
<ol type="1">
|
||
|
<li>Activation group P1 reads the set of rows <span class="italic">n</span> that
|
||
|
satisfy some search condition.</li>
|
||
|
<li>Activation group P2 then INSERTs one or more rows that satisfy the search
|
||
|
condition and COMMITs those INSERTs.</li>
|
||
|
<li>P1 reads the set of rows again with the same search condition and obtains
|
||
|
both the original rows and the rows inserted by P2.</li></ol>
|
||
|
<p>In addition to any exclusive locks, an activation group running at level
|
||
|
RS acquires at least share locks on all the rows it reads.</p>
|
||
|
<p>In the SQL 2003 Core standard, Read Stability is called Repeatable Read.</p>
|
||
|
<p>DB2 UDB for iSeries supports read stability through COMMIT(*ALL) or COMMIT(*RS).</p>
|
||
|
<a name="wq27"></a>
|
||
|
<h3 id="wq27"><a href="rbafzmst02.htm#ToC_55">Cursor stability</a></h3><a id="idx148" name="idx148"></a><a id="idx149" name="idx149"></a><a id="idx150" name="idx150"></a><a id="idx151" name="idx151"></a><a id="idx152" name="idx152"></a>
|
||
|
<p>Like levels RR and RS, level Cursor Stability (CS) ensures that any row
|
||
|
that was changed (or a row that is currently locked with an UPDATE row lock)
|
||
|
by another activation group using a different commitment definition cannot
|
||
|
be read until it is committed. Unlike RR and RS, level CS only ensures that
|
||
|
the current row of every updatable cursor is not changed by other activation
|
||
|
groups using different commitment definitions. Thus, the rows that were read
|
||
|
during a unit of work can be changed by other activation groups that use a
|
||
|
different commitment definition. In addition to any exclusive locks, an activation
|
||
|
group running at level CS may acquire a share lock for the current row of
|
||
|
every cursor.</p>
|
||
|
<p>In the SQL 2003 Core standard, Cursor Stability is called Read Committed.</p>
|
||
|
<p>DB2 UDB for iSeries supports cursor stability through COMMIT(*CS).</p>
|
||
|
<a name="ur"></a>
|
||
|
<h3 id="ur"><a href="rbafzmst02.htm#ToC_56">Uncommitted read</a></h3><a id="idx153" name="idx153"></a><a id="idx154" name="idx154"></a><a id="idx155" name="idx155"></a><a id="idx156" name="idx156"></a><a id="idx157" name="idx157"></a>
|
||
|
<p>For a SELECT INTO, a FETCH with a read-only cursor, subquery,
|
||
|
or fullselect used in an INSERT statement, level Uncommitted Read (UR) allows: </p>
|
||
|
<ul>
|
||
|
<li>Any row read during the unit of work to be changed by other activation
|
||
|
groups that run under a different commitment definition.</li>
|
||
|
<li>Any row changed (or a row that is currently locked with an UPDATE row
|
||
|
lock) by another activation group running under a different commitment definition
|
||
|
to be read even if the change has not been committed.</li></ul>
|
||
|
<p>For other operations, the rules of level CS apply.</p>
|
||
|
<p>In the SQL 2003 Core standard, Uncommitted Read is called Read Uncommitted.</p>
|
||
|
<p>DB2 UDB for iSeries supports uncommitted read through COMMIT(*CHG) or COMMIT(*UR).</p>
|
||
|
<a name="wq28"></a>
|
||
|
<h3 id="wq28"><a href="rbafzmst02.htm#ToC_57">No commit</a></h3><a id="idx158" name="idx158"></a><a id="idx159" name="idx159"></a><a id="idx160" name="idx160"></a><a id="idx161" name="idx161"></a><a id="idx162" name="idx162"></a><a id="idx163" name="idx163"></a>
|
||
|
<p>For all operations, the rules of level UR apply to No Commit (NC) except: </p>
|
||
|
<ul>
|
||
|
<li>Commit and rollback operations have no effect on SQL statements. Cursors
|
||
|
are not closed, and LOCK TABLE locks are not released. However, connections
|
||
|
in the release-pending state are ended.</li>
|
||
|
<li>Any changes are effectively committed at the end of each successful change
|
||
|
operation and can be immediately accessed or changed by other application
|
||
|
groups using different commitment definitions.</li></ul>
|
||
|
<p>DB2 UDB for iSeries supports No Commit through COMMIT(*NONE) or COMMIT(*NC).</p>
|
||
|
<a name="wq29"></a>
|
||
|
<div class="notetitle" id="wq29">Note:</div>
|
||
|
<div class="notebody"><span class="italic">(For distributed applications.)</span> When
|
||
|
a requested isolation level is not supported by an application server, the isolation
|
||
|
level is escalated to the next highest supported isolation level. For example,
|
||
|
if RS is not supported by an application server, the RR isolation level is used.</div>
|
||
|
<a name="isollevel"></a>
|
||
|
<h3 id="isollevel"><a href="rbafzmst02.htm#ToC_58">Comparison of isolation levels</a></h3><a id="idx164" name="idx164"></a>
|
||
|
<p>The following table summarizes information about isolation levels.</p>
|
||
|
<a name="wq30"></a>
|
||
|
<table id="wq30" width="100%" summary="" border="1" frame="hsides" rules="rows">
|
||
|
<thead valign="bottom">
|
||
|
<tr valign="bottom">
|
||
|
<th id="wq31" width="50%" align="left"> </th>
|
||
|
<th id="wq32" width="10%" align="left">NC</th>
|
||
|
<th id="wq33" width="10%" align="left">UR</th>
|
||
|
<th id="wq34" width="10%" align="left">CS</th>
|
||
|
<th id="wq35" width="10%" align="left">RS</th>
|
||
|
<th id="wq36" width="10%" align="left">RR</th>
|
||
|
</tr>
|
||
|
</thead>
|
||
|
<tbody valign="top">
|
||
|
<tr>
|
||
|
<td headers="wq31">Can the application see uncommitted changes made by
|
||
|
other application processes?</td>
|
||
|
<td headers="wq32">Yes</td>
|
||
|
<td headers="wq33">Yes</td>
|
||
|
<td headers="wq34">No</td>
|
||
|
<td headers="wq35">No</td>
|
||
|
<td headers="wq36">No</td>
|
||
|
</tr>
|
||
|
<tr>
|
||
|
<td headers="wq31">Can the application update uncommitted changes made
|
||
|
by other application processes?</td>
|
||
|
<td headers="wq32">No</td>
|
||
|
<td headers="wq33">No</td>
|
||
|
<td headers="wq34">No</td>
|
||
|
<td headers="wq35">No</td>
|
||
|
<td headers="wq36">No</td>
|
||
|
</tr>
|
||
|
<tr>
|
||
|
<td headers="wq31">Can the re-execution of a statement be affected by other
|
||
|
application processes? <span class="italic">See phenomenon P3 (phantom) below</span>.</td>
|
||
|
<td headers="wq32">Yes</td>
|
||
|
<td headers="wq33">Yes</td>
|
||
|
<td headers="wq34">Yes</td>
|
||
|
<td headers="wq35">Yes</td>
|
||
|
<td headers="wq36">No</td>
|
||
|
</tr>
|
||
|
<tr>
|
||
|
<td headers="wq31">Can "updated" rows be updated by other application
|
||
|
processes?</td>
|
||
|
<td headers="wq32">Yes</td>
|
||
|
<td headers="wq33">No</td>
|
||
|
<td headers="wq34">No</td>
|
||
|
<td headers="wq35">No</td>
|
||
|
<td headers="wq36">No</td>
|
||
|
</tr>
|
||
|
<tr>
|
||
|
<td headers="wq31">Can "updated" rows be read by other application
|
||
|
processes that are running at an isolation level other than UR and NC?</td>
|
||
|
<td headers="wq32">Yes</td>
|
||
|
<td headers="wq33">No</td>
|
||
|
<td headers="wq34">No</td>
|
||
|
<td headers="wq35">No</td>
|
||
|
<td headers="wq36">No</td>
|
||
|
</tr>
|
||
|
<tr>
|
||
|
<td headers="wq31">Can "updated" rows be read by other application
|
||
|
processes that are running at the UR or NC isolation level?</td>
|
||
|
<td headers="wq32">Yes</td>
|
||
|
<td headers="wq33">Yes</td>
|
||
|
<td headers="wq34">Yes</td>
|
||
|
<td headers="wq35">Yes</td>
|
||
|
<td headers="wq36">Yes</td>
|
||
|
</tr>
|
||
|
<tr>
|
||
|
<td headers="wq31">Can "accessed" rows be updated by other application
|
||
|
processes?
|
||
|
<p>For RS, "accessed rows" typically means rows selected.
|
||
|
For RR, see the product-specific documentation. <span class="italic">See phenomenon
|
||
|
P2 (nonrepeatable read) below</span>.</p></td>
|
||
|
<td headers="wq32">Yes</td>
|
||
|
<td headers="wq33">Yes</td>
|
||
|
<td headers="wq34">Yes</td>
|
||
|
<td headers="wq35">No</td>
|
||
|
<td headers="wq36">No</td>
|
||
|
</tr>
|
||
|
<tr>
|
||
|
<td headers="wq31">Can "accessed" rows be read by other application
|
||
|
processes?</td>
|
||
|
<td headers="wq32">Yes</td>
|
||
|
<td headers="wq33">Yes</td>
|
||
|
<td headers="wq34">Yes</td>
|
||
|
<td headers="wq35">Yes</td>
|
||
|
<td headers="wq36">Yes</td>
|
||
|
</tr>
|
||
|
<tr>
|
||
|
<td headers="wq31">Can "current" row be updated or deleted by other
|
||
|
application processes? <span class="italic">See phenomenon P1 (dirty-read) below</span>.</td>
|
||
|
<td headers="wq32">See Note below</td>
|
||
|
<td headers="wq33">See Note below</td>
|
||
|
<td headers="wq34">See Note below</td>
|
||
|
<td headers="wq35">No</td>
|
||
|
<td headers="wq36">No</td>
|
||
|
</tr>
|
||
|
<tr>
|
||
|
<td colspan="6" headers="wq31 wq32 wq33 wq34 wq35 wq36">
|
||
|
<a name="wq37"></a>
|
||
|
<div class="notetitle" id="wq37">Note:</div>
|
||
|
<div class="notebody">This
|
||
|
depends on whether the cursor that is positioned on the "current"
|
||
|
row is updatable:
|
||
|
<ul>
|
||
|
<li>If the cursor is updatable, the current row cannot be updated or deleted
|
||
|
by other application processes</li>
|
||
|
<li>If the cursor is not updatable,
|
||
|
<ul>
|
||
|
<li>For UR or NC, the current row can be updated or deleted by other application
|
||
|
processes.</li>
|
||
|
<li>For CS, the current row may be updatable in some circumstances.</li></ul></li></ul></div></td>
|
||
|
</tr>
|
||
|
<tr>
|
||
|
<td colspan="6" headers="wq31 wq32 wq33 wq34 wq35 wq36">
|
||
|
<a name="wq38"></a>
|
||
|
<div class="notetitle" id="wq38">Examples of Phenomena:</div>
|
||
|
<div class="notebody"><a id="idx165" name="idx165"></a><a id="idx166" name="idx166"></a><a id="idx167" name="idx167"></a>
|
||
|
<dl>
|
||
|
<dt class="bold">P1</dt>
|
||
|
<dd><span class="italic">Dirty Read.</span> Unit of work UW1 modifies a row.
|
||
|
Unit of work UW2 reads that row before UW1 performs a COMMIT. UW1 then performs
|
||
|
a ROLLBACK. UW2 has read a nonexistent row.
|
||
|
</dd>
|
||
|
<dt class="bold">P2</dt>
|
||
|
<dd><span class="italic">Nonrepeatable Read.</span> Unit of work UW1 reads a
|
||
|
row. Unit of work UW2 modifies that row and performs a COMMIT. UW1 then re-reads
|
||
|
the row and obtains the modified data value.
|
||
|
</dd>
|
||
|
<dt class="bold">P3</dt>
|
||
|
<dd><span class="italic">Phantom.</span> Unit of work UW1 reads the set of <span class="italic">n</span> rows that satisfies some search condition. Unit of work
|
||
|
UW2 then INSERTs one or more rows that satisfies the search condition. UW1
|
||
|
then repeats the initial read with the same search condition and obtains the
|
||
|
original rows plus the inserted rows.
|
||
|
</dd>
|
||
|
</dl></div></td>
|
||
|
</tr>
|
||
|
</tbody>
|
||
|
</table>
|
||
|
<hr /><div class="fnnum"><a id="readhld" name="readhld">5</a>.</div>
|
||
|
<div class="fntext">For <span class="bold">WITH HOLD</span> cursors, these
|
||
|
rules apply to when the rows were actually read. For read-only <span class="bold">WITH HOLD</span> cursors, the rows may have actually been read in a prior unit
|
||
|
of work.</div>
|
||
|
<br />
|
||
|
<hr /><br />
|
||
|
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstthreads.htm">Previous Page</a> | <a href="rbafzmststoragestruc.htm">Next Page</a> | <a href="rbafzmst02.htm#wq1">Contents</a> |
|
||
|
<a href="rbafzmstindex.htm#index">Index</a> ]
|
||
|
|
||
|
<a id="Bot_Of_Page" name="Bot_Of_Page"></a>
|
||
|
</body>
|
||
|
</html>
|