ibm-information-center/dist/eclipse/plugins/i5OS.ic.db2_5.4.0.1/rbafzmstisol.htm

341 lines
19 KiB
HTML
Raw Permalink Normal View History

2024-04-02 14:02:31 +00:00
<?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">&nbsp;</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 &quot;updated&quot; 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 &quot;updated&quot; 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 &quot;updated&quot; 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 &quot;accessed&quot; rows be updated by other application
processes?
<p>For RS, &quot;accessed rows&quot; 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 &quot;accessed&quot; 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 &quot;current&quot; 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 &quot;current&quot;
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>