Db2 concurrency and locking controls

You can use various application bind options, subsystem parameter settings, and SQL clauses to control concurrency and locking for Db2 for z/OS® database applications.

ISOLATION bind option and SQL isolation-clause

The isolation level of an application or SQL statement defines the degree to which a given application is isolated from other applications. Applications and SQL statements can use the following isolation levels:

Uncommitted read (UR): This isolation option provides the greatest concurrency (and the least locking) and the least stability. The application can read uncommitted data, and does so without getting any locks. An application the uses UR might read rows of data that another transaction has inserted or updated, but eventually rolls back.
Cursor stability (CS): The application sees only committed data. A lock is usually acquired to ensure that each row of data has been committed, but when the next row is fetched, the previous row or page is unlocked, and that process continues for each qualifying row. In that way, the stability of a data row is stable only as long as a cursor is on it. When the application moves off the row or page, another transaction can update it.
Read stability (RS): The application keeps locks on all qualified rows until commit. However, locks are not kept for unqualified rows, and another application might later come along and update any unqualified row such that it now qualifies.
Repeatable read (RR): This isolation option provides the least concurrency (and the most locking) and the greatest stability. Every data row that is read by the application is locked and held until commit, regardless of whether it qualified. No other application can update a non-qualifying row to make it subsequently qualify.

For more information, see Choosing an ISOLATION option.

RESOURCE TIMEOUT field (IRLMRWT subsystem parameter)

The IRLM lock timeout interval, which is the duration after which IRLM returns a request back to Db2, indicating that the request cannot be satisfied. The default is 30 seconds. If an application waits more than this time for a lock, IRLM returns the request back to Db2 with a timeout message, Db2 returns it to the application, and the application must then determine what to do next.

For more information, see Specifying the amount of inactive time before a timeout.

START irlmproc command z/OS IRLM)

Specifies the local deadlock-detection interval in seconds. This is also the interval for IRLM to check for threads that have waited longer than the specified timeout interval.

LOCKSIZE clause of CREATE TABLESPACE statement and ALTER TABLESPACE statement

The LOCKSIZE clause specifies the size for locks held on a table or table space by application processes. You can specify whether you want row locks, page locks, or just a gross lock on the entire table space. The default is ANY, which specifies that you start off getting page locks. If you alter the table space from any to row the table space must be stopped to complete the ALTER. For partitioned table spaces, locks are acquired on individual partitions rather than a single lock on the entire table space.

For more information, see Specifying the size of locks for a table space.

X LOCK FOR SEARCHED U/D field (XLKUPDLT subsystem parameter)

This subsystem parameter specifies that any searched update or delete starts with an X lock on a page or row, instead of starting with a U lock that might later be upgraded to an X lock.

For more information, see Disabling update locks for searched UPDATE and DELETE.

U LOCK FOR RR/RS field (RRULOCK subsystem parameter)

This subsystem parameter specifies that RR or RS transactions acquire U locks for child locks, instead of S locks.

For more information, see Improving concurrency for update and delete operations

EVALUATE UNCOMMITTED field (EVALUNC subsystem parameter)

This subsystem parameter specifies whether Db2 acquires locks to ensure that the data is committed before it evaluates whether a row meets selection criteria.

For more information, see Avoiding locks during predicate evaluation.

SKIP UNCOMM INSERTS field (SKIPUNCI subsystem parameter)

This subsystem parameter specifies whether Db2 waits for or ignores uncommitted inserts for isolation CS or RS applications. For more information, see Accessing currently committed data to avoid lock contention.

USE AND KEEP lock-type LOCKS clause

This SQL clause specifies the lock type—SHARE, UPDATE, or EXCLUSIVE—that the SQL statement uses. For example if the statement includes USE AND KEEP EXCLUSIVE LOCKS, it acquires X locks, even on fetches, and it keeps them until COMMIT.

For more information, see Specifying isolation levels in SQL statements.

SKIP LOCKED DATA

This SQL clause specifies that the statement skips rows when incompatible locks that would block the progress of the statement are held on the rows by other transactions. These rows can belong to any accessed table that is specified in the statement. SKIP LOCKED DATA can be used only with isolation CS or RS and applies only to row level or page level locks. With this option, a fetch doesn't have to wait for data that is locked for update, the locked data is skipped and not returned.

For more information, see Improving concurrency for applications that tolerate incomplete results

CONCURRENTACCESSRESOLUTION bind option

The CONCURRENTACCESSRESOLUTION bind option specifies whether the application sees committed data only, but avoids the need to wait for uncommitted inserts and deletes. That is, if a row has been inserted and not committed the row is not returned. The row is skipped. Similarly if a row has been deleted, but the delete is not yet committed, the deleted row is returned. The statement only returns committed data.

For more information, see Accessing currently committed data to avoid lock contention

LOCKS PER USER field (NUMLKUS subsystem parameter)

FL 507 This subsystem parameter specifies the default value for the maximum number of page, row, or LOB locks that a single application can concurrently hold for all table spaces. After a thread hits this limit, it will stop or rollback depending on its activity. Unlike the NUMLKTS parameter, lock escalation will not occur. End of change

LOCKS PER TABLE(SPACE) field (NUMLKTS subsystem parameter) and LOCKMAX clause of CREATE TABLESPACE statement and ALTER TABLESPACE statement

The NUMLKTS subsystem parameter specifies the number of locks that can be acquired for a single table space before lock escalation occurs. When a thread hits this limit it does not fail. Instead, the thread stops acquiring individual row locks, and lock escalation converts the existing child locks into a gross lock on the entire partition or table space.

The LOCKMAX clause enables or disables lock escalation for a specific table space. If you specify SYSTEM, the NUMLKTS subsystem parameter controls lock escalation for the table space. If you specify 0, lock escalation is disabled. You can also specify a non-zero integer value to override the NUMLKTS setting for that table space.

For more information, see Specifying the default maximum number of locks per table space and Specifying the maximum number of locks that a process can hold on a table space.