Please read my previous post on SQL Server Locks and concurrency control before this….
Lock Escalations are there for a reason. For every lock taking place on SQL Server it takes up around 100 bytes approximately in memory in RAM. So, if we are holding 500,000 locks then it would be 47.68372 MB of memory used. This includes all types of locks.
Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of concurrency contention.
As the SQL Server Database Engine acquires low-level locks, it also places intent locks on the objects that contain the lower-level objects:
•When locking rows or index key ranges, the Database Engine places an intent lock on the pages that contain the rows or keys.
•When locking pages, the Database Engine places an intent lock on the higher level objects that contain the pages. In addition to intent lock on the object, intent page locks are requested on the following objects:
•Leaf-level pages of nonclustered indexes
•Data pages of clustered indexes
•Heap data pages
Lock escalation is triggered when when either of the following conditions occurs:
•A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.
•A single Transact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table and the ALTER TABLE SET LOCK_ESCALATION option is set to AUTO.
•The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.
If locks cannot be escalated because of lock conflicts, the Database Engine periodically triggers lock escalation at every 1,250 new locks acquired.
Quick Demo Setup: select any table and do the similar setup like below:
SET ROWCOUNT 4900
BEGIN TRAN
UPDATE dbo.Apps1
SET [Apps_Column1]= 0+ [Apps_Column2]
--commit transaction
Now make a note of SPID of above transaction and put it into query below.
SELECT spid,
COUNT(*),
request_mode,
[resource_associated_entity_id],
sys.dm_tran_locks.resource_type AS object_type,
Db_name(sysprocesses.dbid) AS dbname
FROM sys.dm_tran_locks,
sys.sysprocesses
WHERE spid = 65
and sys.dm_tran_locks.resource_type IN ( 'page', 'key', 'object','RID' )
GROUP BY spid,
[resource_associated_entity_id],
request_mode,
sys.dm_tran_locks.resource_type, Db_name(sysprocesses.dbid)
Now to see lock escalation increase row count to more than 5000
SET ROWCOUNT 5005
BEGIN TRAN
UPDATE dbo.Apps1
SET [Apps_Column1]= 0+ [Apps_Column2]
--commit transaction
And now run the above query again to see lock escalation.
Hope this post was helpful. 🙂