none
Cannot figure out how to read this deadlock

    Question

  • In one process the DiabloSTG.Diablo.LongLegal table is being switched out to an empty copy (after that a stage copy will switched into it) and from another session DiabloSTG.Diablo.LongLegal is consumed during and update of [tTaxMat].[db255_4842310_Property] table with a join.

    From what I can visualize this should be a schema lock blocking or being blocked by a select, but instead its generating a deadlock (attached). Doesn't a deadlock require a lock held from at least two sessions and trying to hold another impossible lock?

    Any idea how this concluded as a deadlock

    Thank you

    <deadlock-list>
     <deadlock victim="process43227708">
      <process-list>
       <process id="process43227708" taskpriority="0" logused="0" waitresource="OBJECT: 8:1037465574:28 " waittime="5839" ownerId="45072025" transactionname="user_transaction" lasttranstarted="2012-04-20T02:40:19.930" XDES="0x2d51498520" lockMode="Sch-M" schedulerid="40" kpid="8948" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-04-20T02:39:33.043" lastbatchcompleted="2012-04-20T02:39:33.037" clientapp="LoadLongLegal_20120420_020004_A4B39425" hostname="FACLSNA01VAPP34" hostpid="5248" loginname="FIRSTAMDATA\FACL-SA-SQL11SRVC" isolationlevel="read committed (2)" xactid="45072025" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
         <frame procname="adhoc" line="38" stmtstart="2946" stmtend="3078" sqlhandle="0x020000001a974630c05801f5a83f06faa04ca0a8cf7abf20">
    ALTER TABLE Diablo.LongLegal SWITCH TO Diablo.LongLegal_SWITCH;     </frame>
        </executionStack>
        <inputbuf>
    BEGIN TRY
    
    	IF OBJECT_ID(&apos;Diablo.LongLegal&apos;)IS NOT NULL
    		BEGIN
    
    			IF EXISTS(SELECT *
    					  FROM sys.indexes
    					  WHERE object_id = OBJECT_ID(N&apos;[Diablo].[LongLegalNew]&apos;)
    						AND name = N&apos;LongLegal_UNIQ&apos;)
    				DROP INDEX LongLegal_UNIQ ON Diablo.LongLegal WITH(ONLINE = OFF);
    
    			CREATE UNIQUE CLUSTERED INDEX LongLegal_UNIQ ON Diablo.LongLegalNew(CntyCd ASC, Edition ASC, PclId ASC, PclSeqNbr ASC)
    				WITH(PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
    				ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DEFAULT]; 
    
    			IF OBJECT_ID(&apos;[Diablo].[LongLegal_SWITCH]&apos;)IS NOT NULL
    				DROP TABLE
    					 Diablo.LongLegal_SWITCH;
    			SELECT TOP 0
    				   * INTO
    						  Diablo.LongLegal_SWITCH
    			FROM Diablo.LongLegal;
    
    			CREATE UNIQUE CLUSTERED INDEX LongLegal_SWITCH_UNIQ ON Diablo.LongLegal_SWITCH(CntyCd, Edition, PclId, PclSeqNbr);
    
    			IF EXISTS(SELECT *
    					  FROM sys.indexes
    					  WHERE object_id = OBJECT_ID(N&apos;[Diablo].[LongLegal]&apos;)
    		    </inputbuf>
       </process>
       <process id="process38631dc8" taskpriority="0" logused="0" waitresource="OBJECT: 8:1037465574:26 " waittime="3247" ownerId="45072182" transactionname="Lookup" lasttranstarted="2012-04-20T02:40:22.520" XDES="0x13f5e51c60" lockMode="Sch-S" schedulerid="29" kpid="9944" status="suspended" spid="137" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2012-04-20T02:40:02.873" lastbatchcompleted="2012-04-20T02:40:02.873" clientapp="LoadProperty_db255_4842310_20120420_023521_DD9720A4" hostname="FACLSNA01VAPP34" hostpid="15344" loginname="FIRSTAMDATA\FACL-SA-SQL11SRVC" isolationlevel="read committed (2)" xactid="45055018" currentdb="14" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
         <frame procname="adhoc" line="2" sqlhandle="0x02000000abea0110253b88235ccb2fddd7e9353b0d8bd049">
    UPDATE dp
    SET
        dp.CntyLegalTxt = sl.Legal
    FROM [tTaxMat].[db255_4842310_Property] AS dp INNER JOIN DiabloStg.Diablo.LongLegal AS sl
       ON dp.CntyCd = sl.CntyCd
      AND dp.PclId = sl.PclId
      AND dp.PclSeqNbr = sl.PclSeqNbr
      AND sl.Edition = ( SELECT TOP 1
                                    vlc.Edition
                             FROM [Diablo].[Control].[vLoadControlDetail] AS vlc
                             WHERE vlc.ProcessType = &apos;MAT&apos;
                               AND vlc.FileType = &apos;PCL&apos;
                               AND vlc.LoadControlId = 16105 )
    WHERE dp.CntyLegalTxt != sl.Legal     </frame>
         <frame procname="adhoc" line="102" stmtstart="5504" stmtend="5544" sqlhandle="0x020000001625132dfdb724cd655a1dd7d8cdf637cb3130fa">
    EXEC ( @SQL ) ;     </frame>
         <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
    unknown     </frame>
        </executionStack>
        <inputbuf>
    (@P1 varchar(13),@P2 varchar(6),@P3 varchar(7),@P4 smallint,@P5 smallint,@P6 varchar(5))
    /* PARAMETERS:
     * _parDataFolder = ?	eg. &apos;db010_243&apos;
     * _gDiabloDbName = ?	eg. &apos;Diablo&apos;
     * targetSchema = ?		eg. &apos;tTaxMat&apos;
     * _parLoadControlId = ?	eg. 20
     * _parProcessTypeId = ?	eg. 10 (MAT), 20 (PROP)
     * _parCntyCd = ?		eg. &apos;06037&apos;
     */
    DECLARE
       @parDataFolder VARCHAR ( 20 ) = @P1;
    DECLARE
       @gDiabloDbName VARCHAR ( 20 ) = @P2;
    DECLARE
       @targetSchema VARCHAR ( 20 ) = @P3;
    DECLARE
       @parLoadControlId INT = @P4;
    DECLARE
       @parProcessTypeId TINYINT = @P5;
    DECLARE
       @CntyCd CHAR ( 5 ) = @P6
    DECLARE
       @SQL VARCHAR ( MAX );
    
    /***************
     * Propagation *
     ***************/
    IF @parProcessTypeId = 20
        BEGIN
    
            SET @SQL =
    &apos;IF OBJECT_ID ( &apos;&apos;tempdb..#Editions&apos;&apos; ) IS NOT NULL
        DROP TABLE
             #Editions
    CREATE TABLE #Editions (
                 CntyCd CHAR ( 5 ) NOT NULL
                                   PRIMARY KEY CLUSTERED,
                 Edition TINYINT NOT NULL, )
    
    INSERT INTO #Editions (
                CntyCd,    </inputbuf>
       </process>
      </process-list>
      <resource-list>
       <objectlock lockPartition="28" objid="1037465574" subresource="FULL" dbid="8" objectname="DiabloSTG.Diablo.LongLegal" id="lock319ce0800" mode="Sch-S" associatedObjectId="1037465574">
        <owner-list>
         <owner id="process38631dc8" mode="Sch-S"/>
        </owner-list>
        <waiter-list>
         <waiter id="process43227708" mode="Sch-M" requestType="wait"/>
        </waiter-list>
       </objectlock>
       <objectlock lockPartition="26" objid="1037465574" subresource="FULL" dbid="8" objectname="DiabloSTG.Diablo.LongLegal" id="lock32fc52a00" mode="Sch-M" associatedObjectId="1037465574">
        <owner-list>
         <owner id="process43227708" mode="Sch-M"/>
        </owner-list>
        <waiter-list>
         <waiter id="process38631dc8" mode="Sch-S" requestType="wait"/>
        </waiter-list>
       </objectlock>
      </resource-list>
     </deadlock>
    </deadlock-list>
    


    Gokhan Varol

    Monday, April 23, 2012 3:26 AM

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 Gokhan Varol 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
  • how is this causing a deadlock, how to interpret the deadlock from the deadlock graph, why wouldn't one of the session simply wait for the other session to be released? This remained unanswered .

    Thank you


    Gokhan Varol

    Monday, April 23, 2012 12:05 PM
  • >>>>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
  • still confused. If I have a session 

    BEGIN TRANSACTION
    CREATE TABLE test(id int)

    and a second session after that

    SELECT * from test

    I can monitor that the second session will wait indefinetely till first session commits or rolls back.

    How is this scenario above any different, why is one of the sessions becoming a deadlock victim instead of waiting. I cannot find explanation to that. Where is what you just mention "It does wait till SQL Server decides to kill one session ( a victim) due to incompatibly of locks " documented about a deadlock?

    Thank you


    Gokhan Varol

    Monday, April 23, 2012 1:01 PM
  • DiabloSTG.Diablo.LongLegal is not partitioned but we are switching into it (instead or sp_rename we are using partition switching)


    Gokhan Varol

    Monday, April 23, 2012 3:03 PM
  • here you go


    Gokhan Varol

    Tuesday, April 24, 2012 12:43 PM
  • 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
  • there is a select and at the same time partition switch going on against the table. This one has nothing todo with covered indexes or indexes it's about schema locks and why there is a deadlock caused by them instead of a simple wait forever lock.

    Gokhan Varol

    Tuesday, April 24, 2012 4:06 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
  • The table is not partitioned at all, there is only single partition on this table (again it's not partitioned)

    Gokhan Varol

    Friday, April 27, 2012 7:21 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
  • aaaahhhhhhhhhh :-)

    thank you so much


    Gokhan Varol

    Friday, April 27, 2012 7:40 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
  • We have 8 physical 48 core total

    Gokhan Varol

    Saturday, April 28, 2012 12:25 AM
  • 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
  • what did you just say?

    Gokhan Varol

    Saturday, April 28, 2012 1:43 PM
  • 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