Introduction

Locking in SQL Server is a very important aspect but misunderstood by many folks and there are many myths that go around this topic. SQL Server provides few knobs to control the behavior of locking to some degree but for the end user perspective it is completely opaque, how SQL Server manages locks when queries are executed.

In this series, I will take some facts around locking and interrogate them to shed more light and quash some misconceptions. For this post, we will look at the some Lock Escalation thresholds. To get a primer on this topic, a good place to start is looking at the below links. Basically, Lock Escalation is triggered automatically by SQL Server when the cost of maintaining many fine-grain locks increases system overhead and changes them to coarse-grain locks.

Lock Escalation in SQL Server 2005

Lock Escalation (Database Engine)

http://blogs.msdn.com/b/sqlcat/archive/tags/concurrency/

Background

SQL Server uses locks to synchronize the data modifications when many users are trying to access the data concurrently. But locks come with a cost around 100 bytes per each lock, while many fine grain locks improve the concurrency only at the additional cost of system overhead maintaining those locks. There is potential of exhausting system resources essentially bringing the server down if correct locking granularity is NOT applied. To avoid situations like this, SQL Server automatically escalates the locks from many fine grain locks to fewer coarser locks thus improving the overall system performance.

Picture below (taken from BOL) lists some details when Lock Escalation is triggered automatically by SQL Server. For this post, we will look at the first statement below which specifies lock escalation is triggered when at-least 5000 locks are required by a single SQL statement on a single non-partitioned table. The misconception I heard around this statement is SQL Server does lock escalation as soon as 5000 locks are reached.

Lock Escalation thresholds from BOL

Ref: msdn.microsoft.com/en-us/library/ms184286(v=SQL.105).aspx

Test + sys.dm_tran_locks

We will go over a simple example and look at the lock escalation details in different ways. Lets create a table and load 50000 rows into the table.

--Create Table
IF OBJECT_ID('dbo.TestTable', 'U') IS NOT NULL
	DROP TABLE dbo.TestTable
GO
CREATE TABLE dbo.TestTable (
	c1 BIGINT IDENTITY(1,1)
	, c2 CHAR(200) NOT NULL
	, CONSTRAINT PK_TestTable PRIMARY KEY (c1)
)
GO
--Load data
;WITH
   X1 AS ( SELECT name AS C FROM sys.objects)
  , X2 AS ( SELECT DISTINCT name AS C FROM sys.columns)
  , X3 AS ( SELECT REVERSE(name) AS C FROM sys.objects)
INSERT dbo.TestTable (c2)
SELECT TOP 50000
   X1.C + ' ' + X2.C + ' ' + X3.C AS C1
FROM X1 CROSS JOIN X2 CROSS JOIN X3
GO

Nothing else is happening on the server and lets try to update 4999 records as the first step and peek into the number of locks acquired and the type of locks using the sys.dm_tran_locks DMV. Note that querying this DMV in a production box may take lot of resources and NOT a good choice unless troubleshooting some issues.

--Try 4999
BEGIN TRAN

UPDATE dbo.TestTable WITH (ROWLOCK)
	SET c2 = c1
	WHERE c1 <= 4999

SELECT resource_type, request_mode, COUNT(1) AS [This Many Locks]
FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
GROUP BY resource_type, request_mode

ROLLBACK TRAN

Picture below shows the type, lock mode and the number of locks taken for updating 4999 records.

Now lets try to update 5000 records, the minimum threshold for triggering lock escalation and check if the lock escalation happens.

--Try 5000
BEGIN TRAN

UPDATE dbo.TestTable WITH (ROWLOCK)
	SET c2 = c1
	WHERE c1 <= 5000

SELECT resource_type, request_mode, COUNT(1) AS [This Many Locks]
FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
GROUP BY resource_type, request_mode

ROLLBACK TRAN

As you see below, lock escalation didn’t happened at the minimum threshold of 5000 locks and still using row locks.

Locks held when updating 5000 records

After some trail and error, it seems 6084 is the magic number for triggering lock escalation on this table.

--Try 6084, this is the magic number for this table
BEGIN TRAN

UPDATE dbo.TestTable WITH (ROWLOCK)
	SET c2 = c1
	WHERE c1 <= 6084

SELECT resource_type, request_mode, COUNT(1) AS [This Many Locks]
FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
GROUP BY resource_type, request_mode

ROLLBACK TRAN
GO

Lock Escalation happened for updating 6084 rows

From the above tests, it is clear that lock escalation doesn’t happen at the minimum threshold of 5000 locks always and might happen after crossing over the threshold. Another important point to note is that lock escalation doesn’t happen from rows to page and then page to table. Its usually happens from either rows to table or page to table. One key information missing from the above is how do we know if the lock escalation happened because the threshold of at-least 5000 locks and NOT some memory threshold?

Using Lock Escalation Trace event

Now, lets look at the data from the same above tests especially for the update when lock escalation is triggered but from a different method using the Lock Escalation Trace Event.

Lock Escalation using Trace Event

Using Extended events

That’s good information but isn’t using Trace Events to capture this information expensive? Yes, let’s try another method to look at this using XEvents. Extended Events are the future of monitoring information in SQL Server going forward as they are lite weight, asynchronous and scales well compared to the Trace Events.

--Now, create a session and captures relevant information
--Use asynchronous file target
CREATE EVENT SESSION TrackLockEscalation ON SERVER
ADD EVENT sqlserver.lock_escalation(
   ACTION (sqlserver.sql_text, sqlserver.database_name, sqlserver.client_hostname, sqlserver.username
			, sqlserver.tsql_stack, sqlserver.server_instance_name, sqlserver.session_id)
   )
ADD TARGET Package0.asynchronous_file_target
( SET filename = 'C:\SQLServer\XEvents\TrackLockEscalation.xel'
		, metadatafile = 'C:\SQLServer\XEvents\TrackLockEscalation.mta')
WITH (MAX_MEMORY=4096 KB
		, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS
		, MAX_DISPATCH_LATENCY=30 SECONDS
		, MAX_EVENT_SIZE=0 KB
		, MEMORY_PARTITION_MODE=NONE
		, TRACK_CAUSALITY=OFF
		, STARTUP_STATE=OFF)
GO

--Now, that the event session for tracking connection pooling
ALTER EVENT SESSION TrackLockEscalation
ON SERVER
STATE=START
GO

--Try 6084, this is the magic number for this table
BEGIN TRAN

UPDATE dbo.TestTable WITH (ROWLOCK)
	SET c2 = c1
	WHERE c1 <= 6084

SELECT resource_type, request_mode, COUNT(1) AS [This Many Locks]
FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
GROUP BY resource_type, request_mode

ROLLBACK TRAN
GO

--Wait for few minutes to capture some activity and stop the session
ALTER EVENT SESSION TrackLockEscalation
ON SERVER
STATE=STOP
GO

--Go, clean up
DROP EVENT SESSION TrackLockEscalation ON SERVER
GO

--Lets parse the xml data in a easy to read format
SELECT
	FinalData.R.value ('@name', 'nvarchar(50)') AS EventName
	,  FinalData.R.value ('(action[@name="sql_text"]/value)[1]', 'nvarchar(2000)') AS [SQL Text]
	, FinalData.R.value ('(data[@name="escalation_cause"]/value)[1]', 'varchar(50)')
		+ ' - ' +
		FinalData.R.value ('(data[@name="escalation_cause"]/text)[1]', 'varchar(50)')
			AS [Lock Escalation cause]
	, FinalData.R.value ('(data[@name="hobt_lock_count"]/value)[1]', 'varchar(50)')
			AS [IntegerData]
	, FinalData.R.value ('(data[@name="escalated_lock_count"]/value)[1]', 'varchar(50)')
			AS [IntegerData2]
	, FinalData.R.value ('(data[@name="mode"]/value)[1]', 'varchar(50)')
		+ ' - ' +
		FinalData.R.value ('(data[@name="mode"]/text)[1]', 'varchar(50)')
			AS [Resource Mode]
	, FinalData.R.value ('(data[@name="resource_type"]/value)[1]', 'varchar(50)')
		+ ' - ' +
		FinalData.R.value ('(data[@name="resource_type"]/text)[1]', 'varchar(50)')
			AS [Resource Type]
FROM (
SELECT
    CAST(event_data AS XML) AS xmldata
 FROM sys.fn_xe_file_target_read_file
 (
	'C:\SQLserver\XEvents\TrackLockEscalation*.xel'
	, 'C:\SQLserver\XEvents\TrackLockEscalation*mta'
	, null
	, null
)) AsyncFileData
CROSS APPLY xmldata.nodes ('//event') AS FinalData (R)

We essentially got the same data as we got from the Lock Escalation trace event.

Lock Escalation using Extended Events

Key points

• Lock Escalation doesn’t happen at the minimum threshold of 5000 locks always.
• Escalation happens from rows to table or page to table only.
• ROWLOCK hints are NOT always honoured.