DB2 Locking, Part 2: Table Space and Table Locks

Today's post is the second in our DB2 locking series and it covers the topic of table space and table locks.

Table Space Locks

A table space lock is acquired when a DB2 table or index is accessed. Note that I said accessed, not updated. The table space is locked even when simple read-only access is ­occurring.

Refer to to Table 1 below for a listing of the types of table space locks that can be acquired during the execution of an SQL statement. Every table space lock implies two types of access: the access acquired by the lock requester and the access allowed to other subsequent, concurrent processes.

Table 1. Table Space Locks

(click on image for larger view)


When an SQL statement is issued and first accesses data, it takes an intent lock on the table space. Later in the process, actual S-, U-, or X-locks are taken. The intent locks (IS, IX, and SIX) enable programs to wait for the required S-, U-, or X-lock that needs to be taken until other processes have released competing locks.

The type of table space lock used by DB2 during processing is contingent on several factors, including the table space LOCKSIZE specified in the DDL, the bind parameters chosen for the plan being run, and the type of processing requested. Table 2 provides a synopsis of the initial table space locks acquired under certain conditions.

Table 2. How Table Space Locks Are Acquired

(click on image for larger view)

A table space U-lock indicates intent to update, but an update has not occurred. This is caused by using a cursor with the FOR UPDATE OF clause. A U-lock is non-exclusive because it can be taken while tasks have S-locks on the same table space. More information on table space lock compatibility follows in Table 3.

An additional consideration is that table space locks are usually taken in combination with table and page locks, but they can be used on their own. When you specify the ­LOCKSIZE TABLESPACE DDL parameter, table space locks alone are used as the locking mechanism for the data in that table space. This way, concurrent access is limited and concurrent update processing is eliminated.

Similar in function to the LOCKSIZE DDL parameter is the LOCK TABLE statement. The LOCK TABLE statement requests an immediate lock on the specified table. The LOCK TABLE statement has two forms—one to request a share lock and one to request an exclusive lock.

     LOCK TABLE table_name IN SHARE MODE; 
     LOCK TABLE table_name IN EXCLUSIVE MODE;

You also can issue LOCK TABLE against a specific partition, for example:

     LOCK TABLE table-name 
          PARTITION integer IN SHARE MODE;

A locking scheme is not effective unless multiple processes can secure different types of locks on the same resource concurrently. With DB2 locking, some types of table space locks can be acquired concurrently by discrete processes. Two locks that can be acquired concurrently on the same resource are said to be compatible with one another.

Refer to Table 3 for a breakdown of DB2 table space lock compatibility. A Yes in the matrix indicates that the two locks are compatible and can be acquired by distinct processes on the same table space concurrently. A No indicates that the two locks are incompatible. In general, two locks cannot be taken concurrently if they allow concurrent processes to negatively affect the integrity of data in the table space.


Table 3. Table Space Lock Compatability

(click on image for larger view)

Table Locks

When segmented table spaces are involved, DB2 can use table locks . Table locks are always associated with a corresponding table space lock.

The same types of locks are used for table locks as are used for table space locks. S, U, X, IS, IX, and SIX table locks can be acquired by DB2 processes when data in segmented table spaces is accessed. Table 1 describes the options available to DB2 for table locking. The compatibility chart in Table 3 applies to table locks as well as table space locks.

For a table lock to be acquired, an IS-lock must first be acquired on the segmented table space in which the table exists. The type of table lock to be taken depends on the LOCKSIZE specified in the DDL, the bind parameters chosen for the plan being run, and the type of processing requested. Table 4 (below) is a modified version of Table 2 (shown earlier), depicting the initial types of table spaces and table locks acquired given a certain set of conditions. Table locks are not acquired when the LOCKSIZE TABLESPACE parameter is used.


Table 4. How Table Locks Are Acquired

(click on image for larger view)

Summary

And so concludes Part 2 of our multi-part series on DB2 locking. Be sure to keep watching this blog for additional postings in this series!