Locking

DB2 provides isolation levels to control concurrency. In most cases, you do not need to take direct action to establish locks. In general, locks are acquired implicitly by DB2 according to the semantics defined by the isolation level.

Lock Attributes

The resource being locked is called an object. The only objects you can explicitly lock are tables and table spaces. Implicit locks on other types of objects, such as rows, index keys, and sometimes tables, are acquired by DB2 according to the isolation level and processing situations. The object being locked represents the granularity of the lock.

The length of time a lock is held is called the duration and is affected by the isolation level. The access and rules that pertain to a lock are defined by the lock mode. Some lock modes are only used for locking table objects, while other lock modes are used for row objects. DB2 uses the following hierarchy of lockable database objects:

• Table spaces

• Tables

• Rows

The different modes of table locks are listed here in order of increasing control:

• IN (Intent None) The owner of the lock can read any data, committed or noncommitted, in the table. Other applications can read or update the table.

• IS (Intent Share) The owner of the lock can read any data in the table and obtains an S or NS lock on each row read. Other applications can read or update rows in the table.

• S (Share) The owner of the lock can read any data in the table and will not obtain row locks. Other applications can read the table data.

• IX (Intent Exclusive) The owner of the lock can read any data in the table if a U, S, NS, or X lock can be obtained on rows and also can change any data in the table if an X lock can be obtained on rows. Other applications can both read and update table rows.

• SIX (Share with Intent Exclusive) The owner of the lock can read any data in the table and change rows if it can obtain an X lock on the target row(s). Other applications can only read the table data.

• U (Update) The owner of the lock can read any data in the table and can change data if an X lock on the table can be obtained prior to the update. Other applications can only read the table data.

• X (Exclusive) The owner of the lock can read or update any data in the table. No row locks are obtained. Only other applications using the UR isolation level can read rows of the table.

• Z (Super exclusive) No other application can access the table.

The different modes of row locking are listed below in order of increasing control over resources:

• NS (Next Key Share) The row is being read by one application and can be read by concurrent applications. Held by applications using RS or CS isolation levels. It is compatible with the NX lock.

• S (Share) The row is being read by one application and is available for read only by concurrent applications.

• U (Update) The row is being read by one application, which intends to update the data in this row. It is available for read only by concurrent applications. Only one application can possess a U lock on a row. The lock owner will acquire X locks on the rows prior to update.

• NX (Next Key Exclusive) This lock is acquired on the next row when a row is deleted from an index or inserted into an index in a table. The lock owner can read, but not change, the locked row.

• NW (Next Key Weak Exclusive) This lock is acquired on the next row when a row is inserted into the index of a noncatalog table. The lock owner can read, but not change, the locked row. Only individual rows can be locked in NW mode. This is similar to X and NX locks except that it is compatible with the W and NS locks.

• X (Exclusive) The row is being changed by one application and is not available for concurrent applications, except for those with UR isolation level. The lock owner can read and change data in the locked object.

• W (Weak Exclusive) This lock is acquired on the row when a row is inserted into a noncatalog table. The lock owner can change the locked row. This lock is similar to an X lock except that it is compatible with the NW lock. Only UR applications can access the locked row.

Table 6.1 illustrates how all the lock modes work together (called lock compatibility). The table demonstrates whether one lock is compatible with another; “no” means the requesting application must wait for the lock to be released and “yes” means the lock can be granted.

Lock Conversion

If an application holds a lock on a data object, and the mode of access requires a more restrictive lock, the lock is converted to the more restrictive lock. This process is known as lock conversion. During the lock conversion process, the more restrictive lock may or may not be granted.

Let's look at an example of lock conversion. Assume that an application fetches a row from the PROJECT table with the intent to update this row. The intent to update tells DB2 to acquire an update lock on the currently positioned row during the query processing.

The database manager holds an IX lock on the table and a U lock on the specified row. When the update statement of our example is issued, the database manager holds an IX lock on the PROJECT table and an X lock on the changed row. All the locks are released when your application terminates the unit of work with either a COMMIT or ROLLBACK. The CS isolation level was used in this example.

Lock Escalation

If your application changes many rows in one table, it might be better to have one lock on the entire table rather than many locks on each of the rows. DB2 requires memory for each lock; therefore, if a number of row locks can be replaced with a single table lock, the locking storage area can be used by other applications. When DB2 converts the row locks to a table lock on your behalf, this is called lock escalation. DB2 performs lock escalation to avoid resource problems caused by too many resources being held for the individual locks.

Two database configuration parameters have a direct effect on lock escalation.

• LOCKLIST— Defines the amount of memory allocated for the locks.

• MAXLOCKS— Defines the percentage of the total locklist permitted to be allocated to a single application.

Two different situations require lock escalation.

• One application exceeds the percentage of the locklist as defined by the MAXLOCKS configuration parameter. The database manager attempts to free memory by obtaining a table lock and releasing row locks for this application.

• Many applications connected to the database fill the locklist by acquiring a large number of locks. DB2 attempts to free memory by obtaining a table lock and releasing row locks.

Also note that the isolation level used by the application has an effect on lock escalation.

• CS acquires row level locks initially. If required, table level locks can be obtained. Usually, a very small number of locks are acquired by each CS application because they only have to guarantee the integrity of the data in the current row.

• RS locks all rows in the original result set.

• RR may or may not obtain row locks on all rows read to determine the result set. If it does not, then a table lock is obtained instead.

Lock Wait Behavior

What happens if one application requests to update a row that is already locked with an X lock? The application requesting the update simply waits until the X lock is released by the other application. To ensure that the waiting application can continue without needing to wait indefinitely, the LOCKTIMEOUT configuration parameter can be set to define the length of the timeout period. The value is specified in seconds. By default, the lock timeout is disabled (set to a value of –1). This means the waiting application will not receive a timeout and will wait indefinitely.

Deadlock Behavior

In DB2, contention for locks by processes using the database can result in a deadlock situation. A deadlock can occur in the following manner:

• PM1 locks record 1.

• PM2 locks record 5.

• PM1 attempts to lock record 5, but waits because PM2 already holds a lock on this record.

• PM2 then tries to lock record 1, but waits because PM1 already holds a lock on this record.

In this situation, both PM1 and PM2 will wait indefinitely for each other's locks until an external event causes one or both of them to ROLLBACK. DB2 uses a background process, called the deadlock detector, to check for deadlocks. The process is activated periodically as determined by the DLCHKTIME parameter in the database configuration file. When activated, it checks the lock system for deadlocks.

When the deadlock detector finds a deadlock situation, one of the deadlocked applications receives an error code and the current unit of work for that application is rolled back automatically by DB2. When the rollback is complete, the locks held by this chosen application are released, thereby allowing other applications to continue.

LOCK TABLE Statement

You can use the LOCK TABLE statement to override the rules for acquiring initial lock modes. It locks the specified table until the unit of work is committed or rolled back. A table can be locked either in SHARE MODE or in EXCLUSIVE MODE. When using the LOCK TABLE statement in SHARE MODE, no other application can update, delete, or insert data in the locked table. If you need a snapshot of a table that is frequently changed by concurrent applications, you can use this statement to lock the table for changes without using the RR isolation level for your application.

The EXCLUSIVE MODE is more restrictive than SHARE MODE. It prevents concurrent applications from accessing the table for read, update, delete, and insert. If you want to update a large part of the table, you can use the LOCK TABLE statement in EXCLUSIVE MODE rather than locking each row.

LOCKSIZE Parameter of ALTER TABLE Statement

The default locking method for tables in DB2 is row locking. DB2 now provides you with the ability to override this default for a table by using the ALTER TABLE statement and the LOCKSIZE parameter.

The LOCKSIZE parameter allows you to specify the granularity of locking you want DB2 to do for a particular table, either row- or table-level locking. For example, to change the default locking method for the PROJECT table from row locking to table locking, you would issue the following SQL statement:

ALTER TABLE PROJECT LOCKSIZE TABLE

Whenever an application requires a lock to access data in the table, an appropriate table-level lock will be issued. It is important to realize that because all locks on the table are on the table level and not on the row level, it reduces the concurrency of applications accessing this table.