Lock escalation
DB2 replacing a large number of row or page locks with a single lock on the whole table space, which can block other programs.
Also written LOCKMAX, NUMLKTS, table space lock
DB2 usually locks small pieces of data: individual rows or pages. Every lock uses some memory and processing. When one program holds a very large number of locks on the same table space, DB2 may replace them all with one lock on the whole table space. That is lock escalation.
The limit is set by LOCKMAX, defined for each table space, with a system default. When a program passes it, DB2 escalates. The program then holds a lock that may block every other program that needs the same table space, even for rows it never touched.
Escalation usually happens in batch: a job updates hundreds of thousands of rows without committing. Online transactions that use the same table then start waiting or timing out, and a message in the DB2 log records the escalation.
The main fix is to commit more often, so locks are released before they build up. Sometimes the table space settings are changed. When online response suddenly gets worse while a batch job is running, lock escalation is one of the first things DBAs check.
Where can I learn this hands-on?
Right here, at ZedBank. You take a junior job at a simulated bank and learn each part of the work by doing it, with Zed beside you. There's nothing to install.
Start at ZedBankRelated
- DeadlockWhen two programs each wait for data the other has locked, so neither can continue; DB2 cancels one of them.
- Isolation levelThe DB2 setting that decides how much a program's reads are protected from other programs' changes, and so how much locking it does.
- Commit and rollbackMaking a set of changes permanent together, or undoing them together, so a transaction never half-happens.
- Table spaceThe storage structure in DB2 for z/OS that holds the data of one or more tables, kept in VSAM datasets that DB2 manages.
- DB2 for z/OSIBM's relational database on the mainframe, where a great deal of the world's banking and insurance data is stored.