First published on MSDN on Aug 31, 2012
Since it took me several days to track down this bug, and I did learn a couple of new things along the way, I thought I would share some of my work.
16 or More CPUS
When a system presents SQL Server with 16 or more CPUs, and you are using a high end SQL Server SKU, SQL Server will enable lock partitioning. (Lock partitioning can be disabled using startup parameter, trace flag -T1229.)
Lock Partitioning
Lock partitioning optimizes locking structures by adding additional, per scheduler structures and actions. This design has similarities to Sub/Super Latching (
http://blogs.msdn.com/b/psssql/archive/2009/01/28/hot-it-works-sql-server-superlatch-ing-sub-lat...
)
As a quick overview, if the query needs to obtain a Shared lock it only needs to acquire the shared lock on the local partition. For an exclusive lock the query acquires the lock on each partition, always progressing from partition 0 to n to avoid deadlocks. This allows the SQL Server to utilize the local partition when appropriate and improves scalability on larger systems.
Deadlock from Shared Lock on a Different Partition - What?
The problem I was presented with was the following deadlock output. (This was from trace flag 1222 and 3605 to add deadlock information to the error log. You could get similar information using the trace events.)
|
objectlock
lockPartition=8
objid=1765581328 subresource=FULL dbid=8 objectname=Test id=lock47b821a00 mode=Sch-M associatedObjectId=1765581328
|
Notice the partition is 8 and the mode held is Sch-M.
|
|
owner-list
owner id=process46c276188 mode=Sch-M
|
The process is the task address that can be mapped to sys.dm_os_tasks, who owns the lock.
|
|
waiter-list
waiter id=process47b07dc38 mode=Sch-S requestType=wait
|
This is the close of the deadlock cycle by the second process.
Note:
The waiter list is usually printed in ascending order based on how the victims will be selected; usually work investment based.
|
|
objectlock
lockPartition=13
objid=1765581328 subresource=FULL dbid=8 objectname=Test id=lock47b821f80 mode=Sch-S associatedObjectId=1765581328
|
Partition 13 is showing the process that already holds the same Sch-S and is attempting a new acquire on partition 8.
|
|
owner-list
owner id=process47b07dc38 mode=Sch-S
|
Owner of the Sch-S lock.
|
|
waiter-list
waiter id=process46c276188 mode=Sch-M requestType=wait
|
Blocked process attempting to acquire the Sch-M lock. This is expected as the Sch-M is attempting to acquire the lock on all partitions.
|
Under a rare condition SQL Server may not associate the proper lock partition with the lock request, leading to additional locking overhead or possible deadlocks. This bug does not expose any locking problems that would lead to data integrity issues. This is a very small window during compile, before a user transaction is started.
The problem is that when using lock partitioning the Sch-S lock should be acquired on the transaction associated, local partition. However, the same process is attempting to acquire the Sch-S lock on 2 different partitions leading to the deadlock. Why?
-
The lock partition hint is stored with the connection object (sys.dm_exec_sessions - physical connection internal object to be more precise.)
-
SQL Server assigns new batches to one of the active schedulers on the same NUMA node based on active task load for the schedulers.
In this case the login took place on scheduler 8 and the lock partition, hint is cached. When the batch is processed it is assigned to scheduler 13 and the second partition becomes involved; triggering the unexpected behavior.
Bob Dorr - Principal SQL Server Escalation Engineer
