Partilhar via

Strange Sch-S / Sch-M Deadlock on Machines with 16 or More Schedulers

  • Artigo

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 (https://blogs.msdn.com/b/psssql/archive/2009/01/28/hot-it-works-sql-server-superlatch-ing-sub-latches.aspx)

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=13objid=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

Comments

  • Anonymous
    September 10, 2012
    Hi Bob, Do we have fix for it, I think I am seeing same issue on SQLServer 2008 on windows 2003. Thanks.

  • Anonymous
    September 11, 2012
    Investigating the fix.   Since it is limited to the core compile phase reducing compiles will reduce your pain.

  • Anonymous
    September 25, 2012
    Hi Bob, I believe we are experiencing this deadlock bug as well on SQL Server Standard 64bit SP2 / Windows Server 2008 R2 Entreprise 64bits SP1. It occurs on very rare instances when web front (LINQ) access a source table that is being refreshed in the same time. Global Isolation level is RCSI and online Scheduler Count is 16. Your blog entry is the only description of this issue that I have found around. Really not sure how to proceed next. Do you recommend to use Trace Flag 1229 ? Can we expect a fix for SQL Server 2008 or is it possible to keep track on the progress ? -- Error log (TraceOn 1222/1204): objectlock lockPartition=2 objid=2091870519 subresource=FULL dbid=27 objectname=Test id=lock26ed41100 mode=Sch-S associatedObjectId=2091870519 owner-list owner id=process3a4d4c8 mode=Sch-S waiter-list waiter id=process4aac748 mode=Sch-M requestType=wait objectlock lockPartition=1 objid=2091870519 subresource=FULL dbid=27 objectname=Test id=lockb1a2f2500 mode=Sch-M associatedObjectId=2091870519 owner-list owner id=process4aac748 mode=Sch-M waiter-list waiter id=process3a4d4c8 mode=Sch-S requestType=wait   Thanks, Emeric

  • Anonymous
    October 18, 2012
    The comment has been removed

  • Anonymous
    December 03, 2012
    That sounds reasonable for deadlock  Sch-M and Sch-S involved in on object, SP or batch. Any suggestion to resolve it? How about setting the Maximun Degree of Parallell to 1?

  • Anonymous
    February 19, 2014
    Hi Bob, do we have any fix for this bug

  • Anonymous
    May 08, 2014
    The comment has been removed