Lock duration and release behavior in Db2 for z/OS

The duration of a lock is the length of time the lock is held.

For maximum concurrency, locks on a small amount of data held for a short duration are better than locks on large amounts of data that are held for long durations. However, acquiring locks requires processor time, and holding locks requires storage. Therefore, acquiring and holding one table space lock is more efficient than acquiring and holding many page locks. You must consider that trade-off to meet your performance and concurrency objectives.

Partition, table, and table space locks

Partition, table, and table space locks are acquired when the application first accesses the object. The RELEASE bind option controls when the locks are released. Locks can be released at the next commit point, or they can be held until the program terminates.

If an application issues statements from multiple packages with different RELEASE bind options, the RELEASE option in effect for the application process can change. That is, locks might be first acquired with RELEASE(COMMIT) duration but later promoted to RELEASE(DEALLOCATE) when the application runs a subsequent statement from a different package with the RELEASE(DEALLOCATE) option.

When statements from more than one package acquire partition locks for the same table space, all partition locks are held for the same duration. If the first statement to access the table space is from a package that uses the RELEASE(COMMIT) bind option, all partition locks follow the RELEASE(COMMIT) rules. However, if a subsequent statement from another package that uses the RELEASE(DEALLOCATE) bind option accesses a partition in the same table space, all partition locks are then promoted to follow the RELEASE(DEALLOCATE) rules.

The locks can also be held past commit points for cursors that are defined with the WITH HOLD option.

For more information, see RELEASE bind option.

Page and row locks

If a page or row is locked, Db2 acquires the lock only when it is needed. When the lock is released depends on many factors, but the lock is rarely held beyond the next commit point.

You can use the following bind options to take some control over the duration of locks: ISOLATION, RELEASE, and CURRENTDATA.