Lock Escalation and Lock Promotion in DB2

Lock escalation:
 Locks are data structures that indicate which part of the system (row, page, table, tablespace, database, ...) is protected in some way.  DB2 remembers this information in memory. Blocking the whole database all the time when an application accesses a small part of it doesn't make much sense. So if you have an application that deals with a lot of data, it potentially has to have many locks. Those locks accumulate and need space in memory.

So when the list is almost full DB2 does a "lock escalation". That means, many row-level locks held by one transaction on a table are escalated to a single table-level lock, for example. Instead of having 1000s or millions of locks (per row), it is better to have lock on that table , there by relieving the pressure on the lock list.

When lock Escalation Happens:
As the number of locks held by any one application reaches near to MAXLOCKS percentage on the create tablespace statement.(If we remeber we use this paramter on table space DDL statement). Database manager will trigger lock escalation and will continue until the percentage of the lock list held is below the value of MAXLOCKS. Lock escalation also occurs if the LOCKLIST runs out of space.  

How DB2 Handles this scenario:
The database manager determines which locks to escalate by looking through the lock list for the application and finding the table with the most row locks.
Row locks will be replacing with a single table lock. It will keep repeating till MAXLOCKS value is no longer exceeded.

Summary  :
Lock escalation can occur for objects defined with LOCKSIZE ANY, PAGE, or ROW. Value ANY just gives DB2 the choice of what lock to take initially.
If we use LOCKSIZE ANY, LOCKMAX SYSTEM , it means means it uses the number set by the NUMLKTS DSNZPARM.We can turn off lock escalation by setting LOCKMAX to zero.If we choose this approach, be sure the applications that access the objects are committing frequently, and adjust DSNZPARM NUMLKTS to permit more locks to be taken. Otherwise, we risk hitting negative SQL codes when you reach the maximum number of locks.
Lock escalation is thus a way to protect from taking excessive system resources, so if we turn it off, we need to control it.
If we remember the locking modes and the lock sizes, this is how it works:
Page lock and Row lock escalate to Table level and then to Tables space level for Segmented tables space.
But for partioned tables spaces, the page/row lock escalates straight to the Tables space level.
A page and Row lock occurs at the same level.They can not be escalated to each other, ie ROW<-->PAGE

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. We can say, it is the process of changing the lock mode or lock size to a more restrictive level. See the below example .


Example:An application reads data, which requires an IS lock on a table space. Based on further calculation, the application updates the same data, which requires an IX lock on the table space. The application is said to promote the table space lock from mode IS to mode IX.(ie Intent share to intent Exclusive)
Note:  LOCKSIZE ANY will cause DB2 to do page level locking
Want to refresh about the lock modes and intent lock? check out Locking  again.

Inportant information about DB2 LOCK
1. DSNZPARM starts up the parameters for DB2.
2.The LOCKMAX parameter of the create  or ALTER TABLESPACE can be seen in the
MAXROWS column of the SYSIBM.SYSTABLESPACE.
3. If a single user accumulates more page locks than allowed by DSNZPARM, we get a SQLCODE of -904.(resource unavailable)
4.All the page locks held for data in segmented table space are escalated to table locks.
 All the page locks held for data in partitioned table space are escalated to table space locks.

5. Why do we get SQLCODE -904 ?
NUMLKUS parameter in DSNZPARM defines the threshold for the total number of page locks across all table space that can be held concurrently by a single DB2 application.When any other application tries to attempt lock that cause the application to exceed the NUMLKUS threshold,the application receives SQLCODE -904.