Explain the concept of LOCK PROMOTION with the help of an example

A DB2 LOCK PROMOTION is defined as the process of acquiring more restrictive locks on a particular resource. DB2 uses LOCK PROMOTION for the concurrent processes which are trying to access the same DB2 resource. Basically, there are three types of locks.

Shared lock(S)

The concurrent processes can place a shared lock on a resource (DB2 table, row, page, etc) but cannot update the data. In order to update the data, concurrent processes have to promote their lock to UPDATE.

Update lock(U)

The concurrent process can read the data but cannot update it. Update lock indicates that the process is ready to update the resource but in order to make an actual update, any one of the processes can promote its lock to exclusive.

Exclusive lock(X)

Only one process can hold exclusive lock on a resource at a time. The lock owner can read and update the resource. Other concurrent processes cannot acquire any lock in the resource neither can it access the resource.

We shall understand this with the following example.

If there are two COBOL-DB2 programs — PROG A and PROG B which are reading the same row of ORDERS DB2 table. Both the programs will acquire SHARED LOCK on the page in which that DB2 table row resides. Now PROG A wants to update this row then it will acquire an UPDATE LOCK on the page, this is known as LOCK PROMOTION as the lock is promoted from SHARED LOCK to UPDATE LOCK.

Further, if PROG A wants to update the ORDERS table, it will wait until PROG B releases the SHARED lock from the ORDERS table. As soon as PROG B releases its SHARED lock, PROG A will promote its UPDATE lock to EXCLUSIVE lock to update the table. Till the time PROG A holds the EXCLUSIVE lock in ORDERS DB2 table no other process or program can acquire any lock (shared, update or exclusive) on this table.