Cursors and Locking

It is important to understand that using cursors in your stored procedures can affect other applications or people using the database. Whenever you access data, DB2 puts a lock on the row or table where it is stored. Locks in DB2 vary in severity from those that will prevent anyone from reading any data in a table to a row on a row that only lasts for as long as you are reading it. The type of lock that DB2 uses depends on the type of cursor being used and the DB2 isolation level. Both factors are summarized in the following sections.

Lock Modes

DB2 has different lock modes, and each has its own specific attributes. The common locks that can be held on rows are listed in Table 5.1. A complete list of all the lock modes and their behaviors can be found in the DB2 documentation under "Lock Attributes."

Table 5.1. Row Lock Descriptions
Lock Mode	Applicable Object Type	Description
S (Share)	Rows, blocks, tables	The lock owner and all concurrent applications can read, but not update, the locked data.
U (Update)	Rows, blocks, tables	The lock owner can update data. Other UOW can read the data in the locked object, but cannot attempt to update it.
X (Exclusive)	Rows, blocks, tables, bufferpools	The lock owner can both read and update data in the locked object. Only uncommitted read applications can access the locked object.

Isolation Levels

Isolation levels are used by DB2 to control the level of protection you provide to the data you are reading. Increasing the isolation level will reduce the ability to access the data by the other applications. This makes your queries more consistent, but it can also greatly reduce concurrency of your application as more lock contention can occur. The four isolation levels in order from least severe to most severe are Uncommitted Read, Cursor Stability (the default), Read Stability, and Repeatable Read. In addition to the four isolation levels supported by all three platforms, DB2 for iSeries supports a fifth isolation level No Commit which is less severe than Uncommitted Read.

Uncommitted Read (UR)

This isolation level is the lowest level and is often referred to as Dirty Read because it ignores all locks on rows in a table and will return the data regardless. With this isolation level, any row read during a unit of work can be read and changed by any other application. The application can also read any uncommitted changes made by other application. Uncommitted changes are also known as dirty data. The values could be rolled back, and you would never know that you have read incorrect data. This isolation level is normally only used in read-only databases, such as data warehouses, or where data integrity is not an issue.

No Commit (NC)iSeries Only

This isolation level behaves the same as the UR isolation level, with the exception that COMMIT and ROLLBACK operations have no effect on the SQL statements. What this means is that when a COMMIT or ROLLBACK is issued, cursors are not closed and locks held are not released (even those acquired with the LOCK TABLE statement). Additionally, any updates to the database are applied immediately and visible to other applications. With this isolation level, no locks are acquired for READ operations. For UPDATE operations, the duration of the lock is minimized--for example, a row is locked only while it is being updated.

Cursor Stability (CS)

This is the default isolation level and it will keep the row currently being read or updated locked and protected from changes by other applications. You will also not be able to read data altered by other processes until the data has been committed.

A share lock will be placed on any rows being read, while an X lock will be used for deleted rows. An X lock will also be placed for updated rows.

Read Stability (RS)

Read Stability is similar to Cursor Stability but instead of locking the current row that is being processed, it will apply the appropriate locks to all rows that are in the result set. This will ensure that within the same transaction, rows that have been previously read by a cursor cannot be altered by other applications.

Repeatable Read (RR)

Repeatable Read is the strongest isolation level. DB2 will lock all the rows in the result set as well as rows that are used to build the result set. If a row is read by the application using this isolation level, no other application can alter it until the transaction is completed. This will ensure that your result set is consistent. However, it can greatly reduce concurrency because of the large number of locks may be held.

The example in Figure 5.23 uses the data in the department table to demonstrate how the rows would be locked by each ISOLATION level as a cursor runs against it.

Figure 5.23. Partial table for DEPARTMENT.

 DEPTNO LOCATION ------ -------- A00    A B01    B0 C01    B1 D01    D D11    E

Figure 5.24. SELECT statement for the example.

 SELECT deptno, location FROM department WHERE location LIKE 'B%'

The example in Figure 5.24 demonstrates the locks that will he held on the different rows in the department table when the cursor is first opened. Figure 5.25 demonstrates which locks will be held during the second fetch.

Figure 5.25. Lock types on each row at first fetch of row (B01, B0).

 Deptno     Location     UR/NC     CS     RS     RR A00        A                                    S B01        B0                     S      S      S C01        B1                            S      S D01        D                                    S E01        E                                    S

Figure 5.26. Lock types on each row at second fetch of row (C01, B1).

 Deptno     Location     UR/NC     CS     RS     RR A00        A                                    S B01        B0                            S      S C01        B1                     S      S      S D01        D                                    S E01        E                                    S

As you can see in the example, the locking behavior on each row is the same with RR, RS, and UR/NC during the first and second fetch. The row that is locked does vary with CS because when a row is fetched, the lock on the previous row is released. You should use the isolation level that gives you the data integrity you need in your queries but also causes the minimal amount of contention. Cursor Stability and Read Stability are most often used to achieve these goals.

Controlling Locking in DB2 LUW

Database applications can sometimes result in concurrency issues when two different applications both try to access the same row. By default, DB2 will lock all rows that it scans until it determines if the row will be used in the result set. This is normally the desired behavior, but it may cause unnecessary locks if there is lock contention on a row that will end up not even being in the result set. You have the ability to change this behavior for CS and RS. DB2 can evaluate the row first before it tries to lock it.

The DB2 registry variable DB2_EVALUNCOMMITTED must first be turned on before this will take effect. The registry variable can be set by following command by a user with DB2 System Administrator authority. The example in Figure 5.27 demonstrates how the DB2_EVALUNCOMMITTED registry variable can be set.

Figure 5.27. Turning on the DB2_EVALUNCOMMITTED behavior.

 Set: db2set DB2_EVALUNCOMMITTED=YES Unset db2set DB2_EVALUNCOMMITTED=

The following example illustrates the new uncommitted evaluation behavior.

The table in Figure 5.28 illustrates the new uncommitted evaluation behavior on the department table.

Figure 5.28. DEPARTMENT table data.

 DEPTNO DEPTNAME                      MGRNO  ADMRDEPT LOCATION ------ ----------------------------- ------ -------- ---------------- A00    SPIFFY COMPUTER SERVICE DIV.  000010 A00      - B01    PLANNING                      000020 A00      - C01    INFORMATION CENTER            000030 A00      - D01    DEVELOPMENT CENTER            -      A00      - D11    MANUFACTURING SYSTEMS         000060 D01      - D21    ADMINISTRATION SYSTEMS        000070 D01      - E01    SUPPORT SERVICES              000050 A00      - E11    OPERATIONS                    000090 E01      - E21    SOFTWARE SUPPORT              000100 E01      -

The following two transactions in Figure 5.29 are acting against the same data table.

Figure 5.29. department table data.

 Application 1             |         Application 2 ------------------------------------------------------------------------------- CONNECT TO sample         |         CONNECT TO sample (+c) ------------------------------------------------------------------------------- UPDATE                    | department                | SET mgrno = 005000        | WHERE deptno = B01        | -------------------------------------------------------------------------------                           |         SELECT deptname, mgrno                           |         FROM department                           |         WHERE mgrno > 00050 -------------------------------------------------------------------------------

The first application updates a row in the table without committing the change. This is controlled by the command-line option (+c) to turn off autocommit. The update stops Application 2 from returning any rows because it waits to obtain a lock on the second row before the application can continue processing the remainder of the rows. The second application waits even though the row it is waiting on will not be part of its final result set. Unless the first application commits or rolls back the transaction to release the lock, the second application would not be able to complete its update.

This problem could be avoided if the evaluate uncommitted feature was used. With the feature turned on, the second application would have first checked to see if the second row is part of the final result set. Because it would not be, the application will not lock the row and no lock contention would occur. Application 2 could then continue processing the other rows. Using the feature can help you minimize concurrency issues that can occur with data that is updated, and also has reports run against it.

No Wait and Lock Wait

The amount of time an application will wait for a lock before returning an error is set at the database level using the LOCKTIMEOUT parameter. This controls the number of seconds that an application should wait for a lock. However, there are times when you do not want your application to ever wait on a lock. You may want to know immediately if there is any lock contention for certain processes or applications.

You can directly control how long an individual application will wait for a lock using the SET CURRENT LOCK TIMEOUT command. This command will override the default wait time for the particular application. This would be useful in a system where there is a mixed work load of large long running reports and update batch sessions. The reports would be willing to wait indefinitely until the locks were freed, but the update sessions may want to return immediately. You could then determine why there is lock contention occurring and fix the problem.

The syntax of the command is given in Figure 5.30.

Figure 5.30. The SET LOCK MODE syntax.

         .-CURRENT-.                .-=-. >>-SET--+---------+--LOCK TIMEOUT--+---+------------------------> >--+-WAIT-----------------------+------------------------------><    +-NOT WAIT-------------------+    +-NULL-----------------------+    | .-WAIT-. |    +-+------+--integer-constant-+             '---host-variable---'

Figure 5.31 demonstrates how you can use the SET LOCK TIMEOUT command.

Figure 5.31. SET CURRENT LOCK TIMEOUT examples.

 Setting lock waiting to 0 SET CURRENT LOCK TIMEOUT NOT WAIT Setting the application for up to 30 seconds on a lock SET CURRENT LOCK TIMEOUT WAIT 30 Return the lock timeout value to the database LOCKTIMEOUT value SET CURRENT LOCK TIMEOUT NULL

Controlling Locking in DB2 UDB for iSeries

DB2 UDB for iSeries is tightly integrated with the operating system. As such, 'its locking algorithms are the same as the i5/OS object locking algorithms.

For DML statements, the record (row) wait time can be modified using the WAITRCD parameter of the create (CRTPF, CRTLF) or change (CHGPF, CHGLF) file commands or the override database file command (OVRDBF). The default is 60 seconds. Similarly, the file (table) wait time can be modified using the WAITFILE parameter of the create or change file commands, or the override database file command. The default is 0 seconds.

For DDL statements and the LOCK TABLE statements, the lock-wait time is defined by the job default wait time (DFTWAIT), and can be changed with the change job (CHGJOB) or class (CHGCLS) commands.

Controlling Locking in DB2 UDB for zSeries

DB2 for zSeries uses two bind parameters to control lock duration:

ACQUIRE. Determines when the table, table space, or partition locks are taken. If the value of this parameter is set to ALLOCATE, when the first SQL statement is issued, the maximum required lock is taken on all the objects in the plan or package. If the value of this parameter is set to USE, when a SQL statement is issued, the required lock is taken on the involved object at this time.
RELEASE. This parameter determines when the table, table space, or partition locks are released. If RELEASE DEALLOCATE is used, the locks will be released at the end of the program; when RELEASE COMMIT is used, the locks will be released at commit time.

Evaluate Uncommitted and Avoiding Locks

zSeries has a parameter called EVALUATE UNCOMMITTED that is the equivalent to the registry variable DB2_EVALUNCOMMITTED used in LUW. This parameter can be found in installation panel DSNTIP8. In addition, zSeries has mechanisms to avoid locking on rows and pages for committed data. A small instruction is used to test if a row or page contains committed data. If it does, zSeries will avoid using locks on that row or page at all. Lock avoidance is normally the default for most use; however, a combination of settings and events need to occur for zSeries to avoid using locks:

It will only occur when dealing with read-only or ambiguous cursors.
The proper isolation level needs to be set; CS allows for the most lock avoidance.
The proper value for the CURRENTDATA bind parameter needs to be set; a value of NO provides for the most lock avoidance.

Using isolation level UR by itself will also provide for lock avoidance.

Lock Timeout and Deadlock Detection

zSeries does not have controls available at the statement level to manage lock timeouts or deadlock detection. The parameters that are used for these at the subsystem level are

Resource Timeout (IRLMRWT). The number of seconds a transaction will wait for a lock before a timeout is detected. The default value is 60 seconds.
Deadlock Time (DEADLOK). Specifies the time, in seconds or milliseconds, of the local deadlock detection cycle. One second is the default.