Avoiding Locks

Team-Fly    

 
DB2 Universal Database for OS/390 v7.1 Application Certification Guide
By Susan Lawson
Table of Contents
Chapter 16.  Locking and Concurrency

Avoiding Locks

Page and row locking can be avoided at execution time by letting the system use lock avoidance mechanisms. DB2 can test to see if a row or page has committed data on it. If it does, then no lock is required on the data at all. Lock avoidance is only valid for read-only or ambiguous cursors and requires a combination of events and settings to occur. First, the statement must be a read-only or an ambiguous cursor along with the proper isolation level and the appropriate CURRENTDATA setting. The best setting for maximum lock avoidance is using an isolation level of CS, either as a bind parameter or on the individual SQL statement, and CURRENTDATA set to NO as a bind option.

Lock avoidance is normally the default for most use, as it removes a lot of locking overhead. DB2 executes a small instruction set to determine if an IRLM lock is truly needed for read-only cursors or ambiguous cursors.

Page and row locking may also be overridden at bind time or execution time. Locking can be overridden for read-only cursors (not ambiguous cursors) by using the UR isolation level at bind time or on the individual statement level, as shown below:

 EXEC SQL SELECT ... WITH ISOLATION UR 

An SQL SELECT statement is considered to be read-only when it uses any of the following:

  • JOIN

  • SELECT DISTINCT

  • GROUP BY

  • ORDER BY

  • UNION

  • UNION ALL

  • SELECT of a column function

  • FOR FETCH ONLY (FOR READ ONLY)

  • SELECT FROM nonupdateable catalog table

If the SELECT statement does not incorporate any of the above, it is said to be ambiguous, meaning that DB2 does not know for sure if modifying DML will be issued against the cursor. The CURRENTDATA setting of NO states to DB2 that the ambiguous cursor is read-only. If the statement is targeted by updating SQL, then you will get an error. Table 16-4 shows when locking is avoided or overridden.

Table 16-4. Lock Avoidance Determination

Isolation Level

CURRENTDATA

Cursor Type

Avoid Locks on Returned Data

Avoid Locks on Rejected Data

UR

n/a

read-only

n/a

n/a

CS

yes

read-only

no

yes

   

updateable

   
   

ambiguous

   
 

no

read-only

yes

 
   

updateable

no

 
   

ambiguous

yes

 

RS

n/a

read-only

no

yes

   

updateable

   
   

ambiguous

   

RR

n/a

read-only

no

no

   

updateable

   
   

ambiguous

   

NOTE

graphics/note_icon.jpg

If your program is bound with ISOLATION(UR), mass deleters cannot run concurrently with your program.



Team-Fly    
Top


DB2 Universal Database for OS. 390 v7. 1 Application Certification Guide
DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)
ISBN: 0131007718
EAN: 2147483647
Year: 2002
Pages: 163
Authors: Susan Lawson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net