none
Cannot figure out how to read this deadlock

Answers

  • The sequence that leads to the deadlock is as follows:

    1. Transaction T1 acquires a Sch-S lock on the table. Lock partitioning is enabled in your system (because you have at least 16 CPUs), and this lock is taken on partition 28.

    2. Transaction T2 executes DDL and needs a Sch-M lock. This type of lock must be acquired on all partitions (see http://msdn.microsoft.com/en-us/library/ms187504.aspx). In this case the lock is successfully acquired on partitions 0, 1, 2, ..., 26 and 27, and then the transaction blocks on the Sch-S held by T1 on partition 28.

    3. Transaction T1 tries to acquire a Sch-S lock on partition 26 and runs into a deadlock because the Sch-M is already held there.

    Based on the deadlock graph, I can't say why T1 is taking Sch-S locks on two partitions. This can happen for example with parallel execution plans, but I don't know if this is the case here.

    • Proposed as answer by Jon Gurgul Thursday, May 03, 2012 10:56 AM
    • Marked as answer by Peja Tao Friday, May 04, 2012 1:13 AM
    Friday, April 27, 2012 6:47 PM

All replies

  • Sch-M (Schema Modification)

    Those are very restrictive locks

    All queries require and take a Sch-S lock while they're running, to prevent the table from being modified while a query against it is executing.
     

    http://blog.sqlauthority.com/2010/06/21/sql-server-transaction-dml-and%C2%A0schema%C2%A0locks/


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Proposed as answer by JRStern Monday, April 23, 2012 6:26 AM
    • Unproposed as answer by GV1973 Monday, April 23, 2012 12:03 PM
    Monday, April 23, 2012 4:44 AM
    Answerer
  • The resources are waiting for object 

    Database id =8

    Object id=1037465574

    to me the culprit is "ALTER TABLE Diablo.LongLegal SWITCH TO Diablo.LongLegal_SWITCH.The data switch process is blocked by update statement

    

    

    

    


    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

    Monday, April 23, 2012 5:00 AM
  • I guess the question is why it deadlocks instead of just blocking.

    I think there's a simple rule - don't even try to run DDL code and DML code on the same objects at the same time.

    Josh

    Monday, April 23, 2012 6:34 AM
  • >>>>why wouldn't one of the session simply wait for the other session to be released? This remained unanswered .

    It does wait till SQL Server decides to kill one session ( a victim) due to incompatibly of locks 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Monday, April 23, 2012 12:38 PM
    Answerer
  • Hi,

    It looks to me like you are doing a SELECT and an UPDATE on the same table.

    If the SELECT is using a non-covering Non-Clustered index it will then need to connect to the clustered index.

    Meanwhile the UPDATe is changing the Clustered index and as a consequence will also be updating the non-clustered index.

    UPDATE has lock on Clusterered index but SELECT wants it.

    SELECT has lock on Non-clustered index but UPDATE wants it.

    You could make the non-clustered index a covering index. Or you could put a lock on the clustered index as part of the SELECT batch. This will simply block teh Update until complete.

    Or you could use NoLock on the Select.

    Seth


    http://lqqsql.wordpress.com


    • Edited by Seth Lynch Tuesday, April 24, 2012 2:54 PM
    Tuesday, April 24, 2012 2:54 PM
  • The sequence that leads to the deadlock is as follows:

    1. Transaction T1 acquires a Sch-S lock on the table. Lock partitioning is enabled in your system (because you have at least 16 CPUs), and this lock is taken on partition 28.

    2. Transaction T2 executes DDL and needs a Sch-M lock. This type of lock must be acquired on all partitions (see http://msdn.microsoft.com/en-us/library/ms187504.aspx). In this case the lock is successfully acquired on partitions 0, 1, 2, ..., 26 and 27, and then the transaction blocks on the Sch-S held by T1 on partition 28.

    3. Transaction T1 tries to acquire a Sch-S lock on partition 26 and runs into a deadlock because the Sch-M is already held there.

    Based on the deadlock graph, I can't say why T1 is taking Sch-S locks on two partitions. This can happen for example with parallel execution plans, but I don't know if this is the case here.

    • Proposed as answer by Jon Gurgul Thursday, May 03, 2012 10:56 AM
    • Marked as answer by Peja Tao Friday, May 04, 2012 1:13 AM
    Friday, April 27, 2012 6:47 PM
  • I'm talking about lock partitioning, not table partitioning. Please take a look at: http://msdn.microsoft.com/en-us/library/ms187504.aspx

    Friday, April 27, 2012 7:23 PM
  • I'm talking about lock partitioning, not table partitioning. Please take a look at: http://msdn.microsoft.com/en-us/library/ms187504.aspx

    The 16 cpu trigger is logical cpus? e.g a 2 socket quad core with hyper threading = 16?

    Gokhan Varol can I ask how many cpus you have?


    Jon

    Friday, April 27, 2012 8:45 PM
  • 1. Transaction T1 acquires a Sch-S lock on the table. Lock partitioning is enabled in your system (because you have at least 16 CPUs), and this lock is taken on partition 28.

    "Lock partitioning", well that's new to me, too.  Thanks.

    But is that an issue here - in fact, is it ever an issue in that it makes things more restrictive than they would be without partitioning?  Seems to me the answer has to be, not.  That is, if this were executed on a server with just one or two cores, wouldn't the blocking/deadlock issues would be exactly the same?

    Or, does it in some way explain OP's original question, why it produces a deadlock instead of a block - would it block instead of deadlock, if executed on a one or two core server?

    It is in any case an interesting factor that may show up in reading displays of active locks, and a nice piece of knowledge related to SQL Server efficiency.

    Josh

    Saturday, April 28, 2012 2:38 AM
  • You might try acquiring an exclusive table lock in the SWITCH script early in the transaction (e.g. SELECT TOP (0)...WITH (HOLDLOCK, TABLOCKX)).  This will serialize access to the table before other locks are obtained in the transaction, thus avoiding the deadlock with other sessions.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Saturday, April 28, 2012 6:16 PM
    Moderator
  • You might try acquiring an exclusive table lock in the SWITCH script early in the transaction (e.g. SELECT TOP (0)...WITH (HOLDLOCK, TABLOCKX)).  This will serialize access to the table before other locks are obtained in the transaction, thus avoiding the deadlock with other sessions.

    Sounds like an excellent idea!  Why are the simple solutions, the hardest to think of?

    Josh

    ps - or if all else fails, try serializable ...


    • Edited by JRStern Saturday, April 28, 2012 10:13 PM
    Saturday, April 28, 2012 10:13 PM