The duration of a lock

The duration of a lock is the length of time the lock is held. It varies according to when the lock is acquired and when it is released.

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
Start of changeThese locks are acquired when the application first accesses the object.End of change The RELEASE bind option controls when the locks are released. Locks can be released at the next commit point or be held until the program terminates.

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 rules of RELEASE(COMMIT). However, if a statement from another package that uses the RELEASE(DEALLOCATE) bind option accesses a partition in the same tables space, all partition locks are then promoted to follow the rules of RELEASE(DEALLOCATE).

The locks can also be held past commit points for cursors that are defined with the WITH HOLD 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
  • CURRENTDATA