DB2 - Locks

How DB2 locks works?

DB2 Database Manager implicitly acquires locks as they are need, except for occasions where the Uncommitted Read isolation level is used.

It is possible for the DB2 Database Manager to acquire row-level locks or table-level locks on a specific table resource by executing a special form of the ALTER TABLE SQL statement. By default, the DB2 Database Manager always attempts to acquire row-level locks.

  

 The syntax is ALTER TABLE [Table Name] LOCKSIZE [ROW | TABLE] 

 E.g. ALTER TABLE school LOCKSIZE TABLE

The DB2 Database Manager will attempt to acquire table-level locks for every transaction that accesses the SCHOOL table.

The syntax for the LOCK TABLE statement is:

LOCK TABLE [Table Name] IN [SHARE | EXCLUSIVE] MODE

The LOCK TABLE statement allows a transaction to acquire a table-level lock on a particular table in one of two modes: SHARE mode and EXCLUSIVE mode.

If a table is locked using the SHARE mode, a table-level Share (S) lock is acquired on behalf of the requesting transaction, and other concurrent transactions are allowed to read, but not change, data stored in the locked table.

On the other hand, if a table is locked using the EXCLUSIVE mode, a table-level Exclusive (X) lock is acquired, and other concurrent transactions can neither access nor modify data stored in the locked table. You can find the corresponding jobs/JCLs here.

Selecting required lock

The DB2 Database Manager implicitly makes decision of which lock to be used by analyzing the transaction to determine what type of processing it has been designed to perform.

To decide on which type of lock is needed for particular DB2 query, the DB2 Database Manager places all transactions into one of the following categories:

•   Read-Only
•   Intent-to-Change
•   Change
•   Cursor-Controlled