Lock escalation in DB2.

Lock escalation as it's name suggest is nothing but escalating the lock on the object from lower level to the higher.Lock escalation is controlled by the parameter LOCKMAX of CREATE TABLESPACE and ALTER TABLESPACE.

The purpose of lock escalation is to reduce the impact of low level locks, both in terms of CPU and storage, at the possible expense of concurrency. When the number of low level locks exceeds a specified threshold, DB2 attempts to promote the high level intent locks, either IS or IX, to S or X, respectively. If this succeeds, then the low level locks are all released and no more low level locks are requested. The lock promotion request may itself fail if other processes hold incompatible locks, either low or high level, for longer than the timeout period. If the higher level lock cannot be acquired before the timeout value has been reached, then the process times out, all updates are rolled back, and all locks are released.

LOCKMAX :

The parameter LOCKMAX of CREATE TABLESPACE and ALTER TABLESPACE controls the maximum number of page or row locks that an application process can hold simultaneously in a single table space. In segmented table spaces, a single table can be locked. Segmented table space organization allows some tables to perform page or row locking and other tables in the same table space to perform table locking. If the number of locked pages or rows for a table exceeds the limit, a table lock is acquired for that table.

Lock escalation is controlled by the LOCKMAX option. If a process requests more than the number specified in LOCKMAX, the intent lock on the table space, partition, or table is promoted to S or X and the page or row child locks are released.


Lock escalation occurs when the number of locks held by a single process exceeds the LOCKMAX value on a table space. Lock escalation involves obtaining a table or table space lock, then releasing all of the page or row locks. Lock escalation is a safety valve that DB2 provides in case an application overuses system resources. LOCKMAX 0 means that you are disabling this useful feature. Loss of concurrency is less likely to be incurred with lock escalation if the majority of the rows or pages in a table space are already locked, as long as RELEASE(COMMIT) is used. Lock escalation in such a situation reduces storage use, but more importantly, reduces the CPU time needed to traverse a lock hash synonym chain. Lock escalation also reduces the Internal Resource Lock Manager (IRLM) latch suspension time, which also reduces CPU time.

Lock escalation for a partitioned table space will cause DB2 to promote the intent locks for every partition where child locks are currently held. Any subsequent access to a new partition will cause DB2 to request gross locks on that partition from the outset. The LOCKMAX figure applies to all locks held at the table space level when it is a multi-table table space.

The column LOCKMAX on the catalog table SYSIBM.SYSTABLESPACE stores the maximum number of locks per user on the table or table space before escalating to the next locking level. A value of -1 indicates LOCKMAX=SYSTEM. Any escalation process is suspended during the execution of SQL statements for ALTER, CREATE, DROP, GRANT, and REVOKE.

Lock escalation is an expensive process, both in terms of lock management and concurrency. In today’s high availability environment, you should design the application to allow no reason for lock escalations to occur. The benefit of lock escalation is in protecting DB2 from an excessive number of locks held in the IRLM on behalf of poorly designed applications that are not releasing their locks.

Lock escalation also prevents poorly designed applications from reaching the maximum number of locks per user specified by the NUMLKUS DSNZPARM. From an individual application perspective, this may seem like a good thing, but from an overall availability and performance standpoint, it is merely rewarding bad design. The focus should be on identifying and correcting those poorly designed applications that take an excessive number of locks.

For critical large table spaces that are accessed by many application processes concurrently, choose the LOCKMAX value so that there will not be any lock escalation within a unit of work, if NUMLKTS DSNZPARM is not large enough.