Lock escalation is the process of increasing the levels of database locks. Lock escalation is supported by popular databases like SQL Server, SyBase and DB2. In DB2, the parameters like ALTER TABLESPACE and LOCKMAX of CREATE TABLESPACE are used to control lock escalation.
- Read: SQL Database Deadlock
What is the purpose of lock escalation?
The main purpose of lock escalation is to reduce the impact of low level data locks. The impact of low level data locks are reduced at the expense of data concurrency. Whenever the specified threshold of low level data block is exceeded in DB2 database, the database locks are promoted to higher levels. After successful application of lock escalation, all lower level data locks will be released. The presence of incompatible locks in lower level will result in the failure of lock escalation. Lock escalation must be done before the timeout period set for lower level locks. Updates done in the database will be rolled back if locks are not escalated or released before time out.
The LOCKMAX parameter of CREATE and ALTER TABLESPACE controls page and row locks of an application present in a segment table space. Database locking of single table is allowed in segment table space. If the number row or column lock permitted for a table is exceeded then it will be escalated into table lock. All lock escalation processes are controlled by the LOCKMAX option. Lock escalation in a table involves in obtaining the table space for lock and releasing row and column locks. Lock escalation is like a safety valve which can be used to prevent an application from overusing the resources.
Database locking in DB2 can be disabled using LOCKMAX 0. The loss of concurrent data is common during the lock escalation. During the lock escalation process both table storage use and CPU time are reduced considerably. Lock escalation used in the partition table will force the DB2 database to promote the locks present in every partition. Access to the new table partition will force DB2 to opt for the gross locks. All the table space including the multi table spaces are controlled by the LOCKMAX figure.
In the catalog table SYSIBM.SYSTABLESPACE, LOCKMAX stores the maximum number locks used in one table. Lock escalation process can be suspended by using the following SQL statements REVOKE, GRANT, CHANGE, ALTER and DROP.
- Check: Database locking in SQL
In terms of data concurrency and lock management, lock escalation is widely considered as a complicated and expensive process. Lock escalation in DB2 can prevent the database from IRLM’s excessive number of locks. The databases which are poorly designed can prevented from reaching maximum number of locks using the lock escalation. The reach of maximum number of locks can be specified using NUMLKUS DSNZPARM. This may hold good for single application but overall design of the database can be amounted as one of the worst database design.
How does Lock Escalation work in RDBMS?
The process of lock escalation is in-built into RDBMS. Lock escalation are determined during its performance. Some DBMS products allows flags inside a table which in turn disable the lock escalation process. The following SQL can be used to disable the lock escalation process in SQL Server
ALTER TABLE student_table SET (LOCK_ESCALATION = DISABLE)
The following SQL can be used to escalate the lock from lower to higher level.
ALTER TABLE student_table SET (LOCK_ESCALATION = TABLE)
Adaptive Lock escalation can alleviate the burden of the database which in turn improves the performance.