129 lines
9.4 KiB
HTML
129 lines
9.4 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 lang="en-us" xml:lang="en-us">
|
||
|
|
<head>
|
||
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
|
|
<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="DC.Type" content="reference" />
|
||
|
|
<meta name="DC.Title" content="Concurrency" />
|
||
|
|
<meta name="abstract" content="Concurrency is the ability for multiple users to access and change data in the same table or view at the same time without risk of losing data integrity." />
|
||
|
|
<meta name="description" content="Concurrency is the ability for multiple users to access and change data in the same table or view at the same time without risk of losing data integrity." />
|
||
|
|
<meta name="DC.subject" content="data integrity, concurrency, data, definition, security, Override Database File (OVRDBF) command, command (CL), Override Database File (OVRDBF), OVRDBF (Override Database File), Change Physical File (CHGPF) command, Change Logical File (CHGLF) command, Change Physical File (CHGPF), Change Logical File (CHGLF), CHGLF (Change Logical File), CHGPF (Change Physical File), GRTOBJAUT (Grant Object Authority), Change Job (CHGJOB) command, command (CL), Change Job (CHGJOB), CHGJOB (Change Job), Change Class (CHGCLS) command, Change Class (CHGCLS), CHGCLS (Change Class), deadlock detection, deadlock detection, LOCK TABLE statement, statements, LOCK TABLE" />
|
||
|
|
<meta name="keywords" content="data integrity, concurrency, data, definition, security, Override Database File (OVRDBF) command, command (CL), Override Database File (OVRDBF), OVRDBF (Override Database File), Change Physical File (CHGPF) command, Change Logical File (CHGLF) command, Change Physical File (CHGPF), Change Logical File (CHGLF), CHGLF (Change Logical File), CHGPF (Change Physical File), GRTOBJAUT (Grant Object Authority), Change Job (CHGJOB) command, command (CL), Change Job (CHGJOB), CHGJOB (Change Job), Change Class (CHGCLS) command, Change Class (CHGCLS), CHGCLS (Change Class), deadlock detection, deadlock detection, LOCK TABLE statement, statements, LOCK TABLE" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafydataintex.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="../db2/rbafzmsth2lockt.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="rbafycncr" />
|
||
|
|
<meta name="DC.Language" 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. -->
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
||
|
|
<title>Concurrency</title>
|
||
|
|
</head>
|
||
|
|
<body id="rbafycncr"><a name="rbafycncr"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Concurrency</h1>
|
||
|
|
<div><p><dfn class="term">Concurrency</dfn> is the ability for multiple
|
||
|
|
users to access and change data in the same table or view at the same time
|
||
|
|
without risk of losing data integrity.</p>
|
||
|
|
<div class="section"><p>This ability is automatically supplied by the <span class="keyword">DB2<sup>®</sup> UDB for iSeries™</span> database
|
||
|
|
manager. Locks are implicitly acquired on tables and rows to protect concurrent
|
||
|
|
users from changing the same data at precisely the same time.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>Typically, <span class="keyword">DB2 UDB for iSeries</span> will
|
||
|
|
acquire locks on rows to ensure integrity. However, some situations require <span class="keyword">DB2 UDB for iSeries</span> to acquire a more exclusive
|
||
|
|
table level lock instead of row locks.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>For example, an update (exclusive) lock on a row currently held
|
||
|
|
by one cursor can be acquired by another cursor in the same program (or in
|
||
|
|
a DELETE or UPDATE statement not associated with the cursor). This will prevent
|
||
|
|
a positioned UPDATE or positioned DELETE statement that references the first
|
||
|
|
cursor until another FETCH is performed. A read (shared no-update) lock on
|
||
|
|
a row currently held by one cursor will not prevent another cursor in the
|
||
|
|
same program (or DELETE or UPDATE statement) from acquiring a lock on the
|
||
|
|
same row.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>Default and user-specifiable lock-wait time-out values are supported. <span class="keyword">DB2 UDB for iSeries</span> creates tables, views, and
|
||
|
|
indexes with the default record wait time (60 seconds) and the default file
|
||
|
|
wait time (*IMMED). This lock wait time is used for DML statements. You can
|
||
|
|
change these values by using the CL commands Change Physical File (CHGPF),
|
||
|
|
Change Logical File (CHGLF), and Override Database File (OVRDBF). </p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>The lock wait time used for all DDL statements and the LOCK TABLE
|
||
|
|
statement, is the job default wait time (DFTWAIT). You can change this value
|
||
|
|
by using the CL commands Change Job (CHGJOB) or Change Class (CHGCLS). </p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>In the event that a large record wait time is specified, deadlock
|
||
|
|
detection is provided. For example, assume one job has an exclusive lock on
|
||
|
|
row 1 and another job has an exclusive lock on row 2. If the first job attempts
|
||
|
|
to lock row 2, it will wait because the second job is holding the lock. If
|
||
|
|
the second job then attempts to lock row 1, <span class="keyword">DB2 UDB for iSeries</span> will
|
||
|
|
detect that the two jobs are in a deadlock and an error will be returned to
|
||
|
|
the second job.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>You can explicitly prevent other users from using a table at the
|
||
|
|
same time by using the SQL LOCK TABLE statement. Using COMMIT(*RR) will also
|
||
|
|
prevent other users from using a table during a unit of work.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>In order to improve performance, <span class="keyword">DB2 UDB for iSeries</span> will
|
||
|
|
frequently leave the open data path (ODP) open. This performance feature also
|
||
|
|
leaves a lock on tables referenced by the ODP, but does not leave any locks
|
||
|
|
on rows. A lock left on a table may prevent another job from performing an
|
||
|
|
operation on that table. In most cases, however, <span class="keyword">DB2 UDB for iSeries</span> will
|
||
|
|
detect that other jobs are holding locks and events will be signalled to those
|
||
|
|
jobs. The event causes <span class="keyword">DB2 UDB for iSeries</span> to
|
||
|
|
close any ODPs (and release the table locks) that are associated with that
|
||
|
|
table and are currently only open for performance reasons. Note that the lock
|
||
|
|
wait time out must be large enough for the events to be signalled and the
|
||
|
|
other jobs to close the ODPs or an error will be returned.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>Unless the LOCK TABLE statement is used to acquire table locks,
|
||
|
|
or either COMMIT(*ALL) or COMMIT(*RR) is used, data which has been read by
|
||
|
|
one job can be immediately changed by another job. Typically, the data that
|
||
|
|
is read at the time the SQL statement is executed and therefore it is very
|
||
|
|
current (for example, during FETCH). In the following cases, however, data
|
||
|
|
is read before the execution of the SQL statement and therefore the data may
|
||
|
|
not be current (for example, during OPEN).</p>
|
||
|
|
<ul><li>ALWCPYDTA(*OPTIMIZE) was specified and the optimizer determined that making
|
||
|
|
a copy of the data performs better than not making a copy.</li>
|
||
|
|
<li>Some queries require the database manager to create a temporary result
|
||
|
|
table. The data in the temporary result table will not reflect changes made
|
||
|
|
after the cursor was opened. A temporary result table is required when: <ul><li>The total length in bytes of storage for the columns specified in an ORDER
|
||
|
|
BY clause exceeds 2000 bytes.</li>
|
||
|
|
<li>ORDER BY and GROUP BY clauses specify different columns or columns in
|
||
|
|
a different order.</li>
|
||
|
|
<li>UNION or DISTINCT clauses are specified.</li>
|
||
|
|
<li>ORDER BY or GROUP BY clauses specify columns which are not all from the
|
||
|
|
same table.</li>
|
||
|
|
<li>Joining a logical file defined by the JOINDFT data definition specifications
|
||
|
|
(DDS) keyword with another file.</li>
|
||
|
|
<li>Joining or specifying GROUP BY on a logical file which is based on multiple
|
||
|
|
database file members.</li>
|
||
|
|
<li>The query contains a join in which at least one of the files is a view
|
||
|
|
which contains a GROUP BY clause.</li>
|
||
|
|
<li>The query contains a GROUP BY clause which references a view that contains
|
||
|
|
a GROUP BY clause.</li>
|
||
|
|
</ul>
|
||
|
|
</li>
|
||
|
|
<li>A basic subquery is evaluated when the query is opened.</li>
|
||
|
|
</ul>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rbafydataintex.htm" title="Data integrity protects data from being destroyed or changed by unauthorized persons, system operation or hardware failures (such as physical damage to a disk), programming errors, interruptions before a job is completed (such as a power failure), or interference from running applications at the same time (such as serialization problems).">Data integrity</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relinfo"><strong>Related information</strong><br />
|
||
|
|
<div><a href="../db2/rbafzmsth2lockt.htm">LOCK TABLE statement</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|