How DB2 chooses lock types

Different types of SQL data manipulation statements acquire locks on target tables.

Begin program-specific programming interface information.

The lock acquired because of an SQL statement is not always a constant throughout the time of execution. In certain situations,DB2® can change acquired locks during execution. Many other processes and operations acquire locks.

End program-specific programming interface information.

Locks acquired for SQL statements
When SQL statements access or modify data, locks must be acquired to prevent other applications from accessing data that has been changed but not committed. How and when the locks are acquired for a particular SQL statement depend on the type of processing, the access method, and attributes of the application and target tables.
Lock promotion
Lock promotion is the action of exchanging one lock on a resource for a more restrictive lock on the same resource, held by the same application process.
Lock escalation
Lock escalation is the act of releasing a large number of page, row, LOB, or XML locks, held by an application process on a single table or table space, to acquire a table or table space lock, or a set of partition locks, of mode S or X instead.
Modes of transaction locks for various processes
DB2 uses different lock modes for different types of processes.

Parent topic: Concurrency and locks