Systems Engineering and RDBMS

Lock Conversion and Lock Escalation

Posted by decipherinfosys on February 9, 2007

Lock Conversion and Lock Esclation are different though people do get confused with the terminology and mix them up.  Lock conversion means that a particular lock mode type gets converted to a higher lock mode type during the process of execution, for example: in an update statement’s lifecycle, the engine first has to read the rows that it plans to update and thus takes a shared lock on the resource(s) and then does a lock conversion to an exclusive lock prior to doing an update.  This is a common process across RDBMS.
A lock escalation is a process wherein many fine-grain locks are escalated into fewer coarser grained locks i.e. many row level or page level locks can be escalated to table level locks.

Does lock escalation occur in every RDBMS?  No, it doesn’t.   Oracle does not need to do this because of it’s wonderful locking architecture.  There is no concept of a lock manager in the case of Oracle – it is controlled at the block level using ITLs (Interested Transaction Lists).  The lock escalation issues arise in those RDBMS where locking is controlled via a lock manager like in the case of SQL Server and DB2 LUW.

Every lock is a memory structure and too many locks would mean, more memory being occupied by locks.  When a transaction requests rows from a table, SQL Server/DB2 LUW automatically acquire locks on those rows affected and place higher-level intent locks on the pages and table, or index, which contain those rows. When the number of locks held by the transaction exceeds its threshold, the engine then attempts to change the intent lock on the table to a stronger lock (for example, an intent exclusive (IX) would change to an exclusive (X) lock). After acquiring the stronger lock, all page and row level locks held by the transaction on the table are released, reducing lock overhead.  The database engine may choose to do both row and page locking for the same query, for example, placing page locks on the index (if enough contiguous keys in a nonclustered index node are selected to satisfy the query) and row locks on the data.

In one of the future posts, we will look at how lock escalation can be prevented in SQL Server and DB2 LUW.

One Response to “Lock Conversion and Lock Escalation”

  1. […] 2008 We have talked about lock escalation in some of our previous posts – you can read more on it here and here.  In SQL Server 2008, there is an enhancement to the ALTER TABLE statement that allows a […]

Sorry, the comment form is closed at this time.

%d bloggers like this: