none
Cannot figure out how to read this deadlock

解答

  • 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.

    • 已提議為解答 Jon Gurgul 2012年5月3日 上午 10:56
    • 已標示為解答 Peja Tao 2012年5月4日 上午 01:13
    2012年4月27日 下午 06:47

所有回覆

  • 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/

    • 已提議為解答 JRStern 2012年4月23日 上午 06:26
    • 已取消提議為解答 GV1973 2012年4月23日 下午 12:03
    2012年4月23日 上午 04:44
    解答者
  • 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.

    2012年4月23日 上午 05:00
  • 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

    2012年4月23日 上午 06:34
  • >>>>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/

    2012年4月23日 下午 12:38
    解答者
  • 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


    • 已編輯 Seth Lynch 2012年4月24日 下午 02:54
    2012年4月24日 下午 02:54
  • 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.

    • 已提議為解答 Jon Gurgul 2012年5月3日 上午 10:56
    • 已標示為解答 Peja Tao 2012年5月4日 上午 01:13
    2012年4月27日 下午 06:47
  • I'm talking about lock partitioning, not table partitioning. Please take a look at: http://msdn.microsoft.com/en-us/library/ms187504.aspx

    2012年4月27日 下午 07:23
  • 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

    2012年4月27日 下午 08:45
  • 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

    2012年4月28日 上午 02:38
  • 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/

    2012年4月28日 下午 06:16
    版主
  • 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 ...


    • 已編輯 JRStern 2012年4月28日 下午 10:13
    2012年4月28日 下午 10:13