none
Deadlock on delete

    Question

  • Two theads execute the following SQL statement (id is different) through a DataContext (datacontext.ExecuteCommand). All the SQL-Server settings are default values. The second delete statement result in a deadlock... Why can this happen?

     

    Code Snippet

    DECLARE @Id UNIQUEIDENTIFIER

    SET @Id = '{0}'

    DELETE
     Table1
    WHERE
     Id = @Id

    DELETE
     Table2
    WHERE
     Id = @Id

    DELETE
     Table3
    WHERE
     Id = @Id

     

    Code Snippet

    EventClass     ESbCl SPID Mode Time         TextData

     

    StmtStarting   NULL   61 NULL  10:02:00.757 DELETE Table2 WHERE Id = @Id
    SQLTransaction Begin  61 NULL  10:02:00.757 NULL
    SQLTransaction Begin  61 NULL  10:02:00.757 NULL
    SQLTransaction Commit 61 NULL  10:02:00.757 NULL
    StmtStarting   NULL   63 NULL  10:02:00.770 DELETE Table2 WHERE Id = @Id
    SQLTransaction Begin  63 NULL  10:02:00.770 NULL
    SQLTransaction Begin  21 NULL  10:02:00.833 NULL
    SQLTransaction Commit 21 NULL  10:02:00.833 NULL
    DeadLock Chain 101    4  3 - S 10:02:01.037 Deadlock Chain SPID = 63 (3e00d60a6c34)                                                                         
    DeadLock Chain 101    4  3 - S 10:02:01.037 Deadlock Chain SPID = 61 (df00bd587bcf)                                                                         
    DeadLock       NULL   61 3 - S 10:02:00.833 (df00bd587bcf)

     

     

    Why can both threads grab the lock?

    Thursday, August 07, 2008 11:06 AM

All replies

  • Dunken,

     

    There are numerous factors which are not apparent from your code snippet which may cause your issue.

     

    Firstly, it is unclear which type of locking the statements are carrying out. If there is no index on the Id column then it will effectively scan the entire table looking for records to delete and thus take out a table lock. Should there be an index but the Id column is not particularly selective then it may take out a lock with precludes the second statement from completing. Even if the Id column is primary key on a clustered index, if the table is small it may choose to lock the whole table rather than use a something more suitable.

     

    Secondly, I assume there isn't but you do not say whether there is any maintained referential integrity which deletes dependant records or any triggers. There may also make demands on resources to create a deadlock.

     

    Lastly,  could there be anything else holding a lock on the effected records (such as another process or orphaned transaction).

     

    Rick

    Thursday, August 07, 2008 11:46 AM
  • Rick, thank you for your help.

     

    I wanna try to clarify my setup:

     

    1.        There is an unclustered index on the Id column (not the primary key). The column is quite selective.

    2.        There’s no referential integrity.

    3.        There should’t be any other factors.

     

    I traced the problem again (with Deadlock Graph etc.) and it seems to be as follows:

     

    1.     Transaction 1 takes an exclusive lock on primary key from Table 2 (as mentioned primary key isn’t Id column)

    2.     Transaction 2 takes an exclusive lock on primary key from Table 2 too

    3.     Transaction 2 tries to take a shared lock on pk from Table 2 to which isn’t possible because of 1.

    4.     Transaction 1 tries to take a shared lock on pk from Table 2 to which isn’t possible because of 2.

    5.     Result is deadlock

     

    I’m not sure whether this is correct; however, I don’t understand why Transaction 2 can take an E-lock on Table 2 too….

     

    Thursday, August 07, 2008 1:41 PM
  • In profiler, extract the deadlock events to files by doing:

     

    Profiler – File – Export – Extract SQL Server Events – Extract Deadlock events and save to some files

     

    Then post the deadlock graph on this post.

     

    Also post the execution plan of the delete statement using SET SHOWPLAN_TEXT ON to this post.  The problem is likely the Non-clustered Index causing a lookup, but we would need to see the above two items to be certain.

     

    Thursday, August 07, 2008 4:20 PM
  • Deadlock graph:

    Code Snippet

    <deadlock-list>
     <deadlock victim="processeb8f28">
      <process-list>
       <process id="processeaad48" taskpriority="0" logused="9464" waitresource="KEY: 6:72057594061717504 (670088229c61)" waittime="2433" ownerId="1436530" transactionname="DELETE" lasttranstarted="2008-08-07T14:15:30.200" XDES="0x24a36640" lockMode="S" schedulerid="1" kpid="4744" status="suspended" spid="69" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2008-08-07T14:15:29.730" lastbatchcompleted="2008-08-07T14:15:29.730" lastattention="2008-08-07T14:15:09.187" clientapp=".Net SqlClient Data Provider" hostname="PC-01" hostpid="1556" loginname="test" isolationlevel="read committed (2)" xactid="1436530" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
         <frame procname="adhoc" line="11" stmtstart="430" stmtend="606" sqlhandle="0x02000000e6ba1d07542fc656fa6888f1957f8a57ef056096">
    DELETE
     Table2
    WHERE
     TempId = @TempId     </frame>
        </executionStack>
        <inputbuf>

    DECLARE @TempId UNIQUEIDENTIFIER

    SET @TempId = 'da9329a6-dbca-4482-8170-394bb35f52ff'

    DELETE
     Table1
    WHERE
     TempId = @TempId

    DELETE
     Table2
    WHERE
     TempId = @TempId

    DELETE
     Table3
    WHERE
     TempId = @TempId
        </inputbuf>
       </process>
       <process id="processeb8f28" taskpriority="0" logused="3836" waitresource="KEY: 6:72057594061717504 (5d003d6d9175)" waittime="2355" ownerId="1436605" transactionname="DELETE" lasttranstarted="2008-08-07T14:15:31.870" XDES="0x4bb10f0" lockMode="S" schedulerid="2" kpid="5540" status="suspended" spid="70" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2008-08-07T14:15:30.683" lastbatchcompleted="2008-08-07T14:15:30.683" clientapp=".Net SqlClient Data Provider" hostname="PC-01" hostpid="1556" loginname="test" isolationlevel="read committed (2)" xactid="1436605" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
         <frame procname="adhoc" line="11" stmtstart="430" stmtend="606" sqlhandle="0x0200000043d81630c202b6af7875307bbb7b0de4ec2bd1ad">
    DELETE
     Table2
    WHERE
     TempId = @TempId     </frame>
        </executionStack>
        <inputbuf>

    DECLARE @TempId UNIQUEIDENTIFIER

    SET @TempId = '52a5a874-21d7-4414-a52d-4c517b253c36'

    DELETE
     Table1
    WHERE
     TempId = @TempId

    DELETE
     Table2
    WHERE
     TempId = @TempId

    DELETE
     Table3
    WHERE
     TempId = @TempId
        </inputbuf>
       </process>
      </process-list>
      <resource-list>
       <keylock hobtid="72057594061717504" dbid="6" objectname="adbcore.dbo.Table2" indexname="PK_Table2" id="lock1cdee400" mode="X" associatedObjectId="72057594061717504">
        <owner-list>
         <owner id="processeb8f28" mode="X"/>
        </owner-list>
        <waiter-list>
         <waiter id="processeaad48" mode="S" requestType="wait"/>
        </waiter-list>
       </keylock>
       <keylock hobtid="72057594061717504" dbid="6" objectname="adbcore.dbo.Table2" indexname="PK_Table2" id="lock1cdeec80" mode="X" associatedObjectId="72057594061717504">
        <owner-list>
         <owner id="processeaad48" mode="X"/>
        </owner-list>
        <waiter-list>
         <waiter id="processeb8f28" mode="S" requestType="wait"/>
        </waiter-list>
       </keylock>
      </resource-list>
     </deadlock>
     <deadlock victim="processeb8f28">
      <process-list>
       <process id="processeaad48" taskpriority="0" logused="9464" waitresource="KEY: 6:72057594061979648 (68000cfdccf5)" waittime="5600" ownerId="1436530" transactionname="DELETE" lasttranstarted="2008-08-07T14:15:30.200" XDES="0x24a36640" lockMode="S" schedulerid="1" kpid="4744" status="suspended" spid="69" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2008-08-07T14:15:29.730" lastbatchcompleted="2008-08-07T14:15:29.730" lastattention="2008-08-07T14:15:09.187" clientapp=".Net SqlClient Data Provider" hostname="PC-01" hostpid="1556" loginname="test" isolationlevel="read committed (2)" xactid="1436530" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
         <frame procname="adhoc" line="11" stmtstart="430" stmtend="606" sqlhandle="0x02000000e6ba1d07542fc656fa6888f1957f8a57ef056096">
    DELETE
     Table2
    WHERE
     TempId = @TempId     </frame>
        </executionStack>
        <inputbuf>

    DECLARE @TempId UNIQUEIDENTIFIER

    SET @TempId = 'da9329a6-dbca-4482-8170-394bb35f52ff'

    DELETE
     Table1
    WHERE
     TempId = @TempId

    DELETE
     Table2
    WHERE
     TempId = @TempId

    DELETE
     Table3
    WHERE
     TempId = @TempId
        </inputbuf>
       </process>
       <process id="processeb8f28" taskpriority="0" logused="3836" waitresource="KEY: 6:72057594061717504 (5d003d6d9175)" waittime="3978" ownerId="1436627" transactionname="DELETE" lasttranstarted="2008-08-07T14:15:37.627" XDES="0x4bb10f0" lockMode="S" schedulerid="2" kpid="5540" status="suspended" spid="70" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2008-08-07T14:15:37.627" lastbatchcompleted="2008-08-07T14:15:37.627" clientapp=".Net SqlClient Data Provider" hostname="PC-01" hostpid="1556" loginname="test" isolationlevel="read committed (2)" xactid="1436627" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
         <frame procname="adhoc" line="11" stmtstart="430" stmtend="606" sqlhandle="0x0200000043d81630c202b6af7875307bbb7b0de4ec2bd1ad">
    DELETE
     Table2
    WHERE
     TempId = @TempId     </frame>
        </executionStack>
        <inputbuf>

    DECLARE @TempId UNIQUEIDENTIFIER

    SET @TempId = '52a5a874-21d7-4414-a52d-4c517b253c36'

    DELETE
     Table1
    WHERE
     TempId = @TempId

    DELETE
     Table2
    WHERE
     TempId = @TempId

    DELETE
     Table3
    WHERE
     TempId = @TempId
        </inputbuf>
       </process>
      </process-list>
      <resource-list>
       <keylock hobtid="72057594061979648" dbid="6" objectname="adbcore.dbo.Table2" indexname="IX_Table2_HatWiederholungInAusstrahlungParentId" id="lock1c841340" mode="X" associatedObjectId="72057594061979648">
        <owner-list>
         <owner id="processeb8f28" mode="X"/>
        </owner-list>
        <waiter-list>
         <waiter id="processeaad48" mode="S" requestType="wait"/>
        </waiter-list>
       </keylock>
       <keylock hobtid="72057594061717504" dbid="6" objectname="adbcore.dbo.Table2" indexname="PK_Table2" id="lock1cdeec80" mode="X" associatedObjectId="72057594061717504">
        <owner-list>
         <owner id="processeaad48" mode="X"/>
        </owner-list>
        <waiter-list>
         <waiter id="processeb8f28" mode="S" requestType="wait"/>
        </waiter-list>
       </keylock>
      </resource-list>
     </deadlock>
    </deadlock-list>

     

    execution plan (just 2nd delete statement which causes the deadlock):

    Code Snippet

     DELETE   Table2   WHERE   TempId = @TempId

      |--Sequence
           |--Table Spool
           |    |--Clustered Index Delete(OBJECT:([dbmain].[dbo].[Table2].[PK_Table2]), OBJECT:([dbmain].[dbo].[Table2].[IX_Table2_ParentId]), OBJECT:([dbmain].[dbo].[Table2].

    [IX_Table2_InstanceType]), OBJECT:([dbmain].[dbo].[Table2].[IX_Table2_SPId]), OBJECT:([dbmain].[dbo].[Table2].[IX_Table2_HWIAPId]), OBJECT:([dbmain].[dbo].[Table2].

    [IX_Table2_IWIAPId]), OBJECT:([dbmain].[dbo].[Table2].[IX_Table2_LTLId]), OBJECT:([dbmain].[dbo].[Table2].[IX_Table2_FZHILId]), OBJECT:([dbmain].[dbo].[Table2].

    [IX_Table2_LILId]), OBJECT:([dbmain].[dbo].[Table2].[IX_Table2_OTLId]), OBJECT:([dbmain].[dbo].[Table2].[IX_Table2_TTLId]), OBJECT:([dbmain].[dbo].[Table2].[IX_Table2_PLId]),

    OBJECT:([dbmain].[dbo].[Table2].[IX_Table2_TempId]) WITH ORDERED PREFETCH)
           |         |--Top(ROWCOUNT est 0)
           |              |--Index Seek(OBJECT:([dbmain].[dbo].[Table2].[IX_Table2_TempId]), SEEK:([dbmain].[dbo].[Table2].[TempId]=[@TempId]) ORDERED FORWARD)
           |--Assert(WHERE:(CASE WHEN NOT [Expr1022] IS NULL THEN (0) ELSE CASE WHEN NOT [Expr1023] IS NULL THEN (1) ELSE CASE WHEN NOT [Expr1024] IS NULL THEN (2) ELSE CASE WHEN

    NOT [Expr1025] IS NULL THEN (3) ELSE NULL END END END END))
                |--Merge Join(Left Semi Join, MERGE:([dbmain].[dbo].[Table2].[Id])=([dbmain].[dbo].[Table2].[ParentId]), RESIDUAL:([dbmain].[dbo].[Table2].[ParentId]=[dbmain].

    [dbo].[Table2].[Id]))
                     |--Merge Join(Left Semi Join, MERGE:([dbmain].[dbo].[Table2].[Id])=([dbmain].[dbo].[Table2].[OAId]), RESIDUAL:([dbmain].[dbo].[Table2].[OAId]=[dbmain].[dbo].

    [Table2].[Id]))
                     |    |--Merge Join(Left Semi Join, MERGE:([dbmain].[dbo].[Table2].[Id])=([dbmain].[dbo].[Table2].[IWIAPId]), RESIDUAL:([dbmain].[dbo].[Table2].[IWIAPId]=

    [dbmain].[dbo].[Table2].[Id]))
                     |    |    |--Merge Join(Left Semi Join, MERGE:([dbmain].[dbo].[Table2].[Id])=([dbmain].[dbo].[Table2].[HWIAPId]), RESIDUAL:([dbmain].[dbo].[Table2].[HWIAPId]=

    [dbmain].[dbo].[Table2].[Id]))
                     |    |    |    |--Table Spool
                     |    |    |    |--Index Scan(OBJECT:([dbmain].[dbo].[Table2].[IX_Table2_HWIAPId]), ORDERED FORWARD)
                     |    |    |--Index Scan(OBJECT:([dbmain].[dbo].[Table2].[IX_Table2_IWIAPId]), ORDERED FORWARD)
                     |    |--Sort(ORDER BY:([dbmain].[dbo].[Table2].[OAId] ASC))
                     |         |--Clustered Index Scan(OBJECT:([dbmain].[dbo].[Table2].[PK_Table2]))
                     |--Index Scan(OBJECT:([dbmain].[dbo].[Table2].[IX_Table2_ParentId]), ORDERED FORWARD)

     

     


    Friday, August 08, 2008 9:01 AM
  • I'm still looking for help. Please let me know if you need additional information.

     

    Thank you in advance

     

    Monday, August 11, 2008 1:40 PM
  • Hi all

     

    I still have this problem. I appreciate any help. Please let me know if you need more information.

    Thank you!

    Tuesday, August 26, 2008 8:34 AM
  • What is the table and all associated index DDL for each of these tables? Also what is the row count for each of the tables?

     

    Tuesday, August 26, 2008 11:03 AM
  • Code Snippet

    CREATE TABLE [dbo].[Table2](
     [Id] [bigint] NOT NULL,
     [ParentId] [bigint] NOT NULL,
     [InstanceType] [nvarchar](50) NOT NULL,
     [SPId] [bigint] NULL,
     [OId] [bigint] NULL,
     [HWIAPId] [bigint] NULL,
     [IWIAPId] [bigint] NULL,
     [LTLId] [bigint] NULL,
     [FZHILId] [bigint] NULL,
     [LILId] [bigint] NULL,
     [OTLId] [bigint] NULL,
     [TTLId] [bigint] NULL,
     [PLId] [bigint] NULL,
     [TempId] [uniqueidentifier] NULL,
     CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
    (
     [Id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

     

    GO
    ALTER TABLE [dbo].[Table2]  WITH CHECK ADD  CONSTRAINT [FK_Table2_Table2_HWIAPId] FOREIGN KEY([HWIAPId])
    REFERENCES [dbo].[Table2] ([Id])
    GO
    ALTER TABLE [dbo].[Table2] CHECK CONSTRAINT [FK_Table2_Table2_HWIAPId]
    GO
    ALTER TABLE [dbo].[Table2]  WITH CHECK ADD  CONSTRAINT [FK_Table2_Table2_IWIAPId] FOREIGN KEY([IWIAPId])
    REFERENCES [dbo].[Table2] ([Id])
    GO
    ALTER TABLE [dbo].[Table2] CHECK CONSTRAINT [FK_Table2_Table2_IWIAPId]
    GO
    ALTER TABLE [dbo].[Table2]  WITH CHECK ADD  CONSTRAINT [FK_Table2_Table2_OId] FOREIGN KEY([OId])
    REFERENCES [dbo].[Table2] ([Id])
    GO
    ALTER TABLE [dbo].[Table2] CHECK CONSTRAINT [FK_Table2_Table2_OId]
    GO
    ALTER TABLE [dbo].[Table2]  WITH CHECK ADD  CONSTRAINT [FK_Table2_Table2_ParentId] FOREIGN KEY([ParentId])
    REFERENCES [dbo].[Table2] ([Id])
    GO
    ALTER TABLE [dbo].[Table2] CHECK CONSTRAINT [FK_Table2_Table2_ParentId]
    GO

     


    CREATE NONCLUSTERED INDEX [IX_Table2_FZHILId] ON [dbo].[Table2]
    (
     [FZHILId] 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 [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [IX_Table2_HWIAPId] ON [dbo].[Table2]
    (
     [HWIAPId] 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 [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [IX_Table2_InstanceType] ON [dbo].[Table2]
    (
     [InstanceType] 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 [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [IX_Table2_IWIAPId] ON [dbo].[Table2]
    (
     [IWIAPId] 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 [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [IX_Table2_LTLId] ON [dbo].[Table2]
    (
     [LTLId] 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 [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [IX_Table2_LILId] ON [dbo].[Table2]
    (
     [LILId] 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 [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [IX_Table2_OTLId] ON [dbo].[Table2]
    (
     [OTLId] 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 [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [IX_Table2_ParentId] ON [dbo].[Table2]
    (
     [ParentId] 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 [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [IX_Table2_PLId] ON [dbo].[Table2]
    (
     [PLId] 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 [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [IX_Table2_SPId] ON [dbo].[Table2]
    (
     [SPId] 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 [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [IX_Table2_TempId] ON [dbo].[Table2]
    (
     [TempId] 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 [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [IX_Table2_TTLId] ON [dbo].[Table2]
    (
     [TTLId] 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 [PRIMARY]
    GO

     

     

    I'm testing in a test-database which contains only a couple of testrecords. The real database is growing very fast (about one million records per day).
    Tuesday, August 26, 2008 11:53 AM
  • If I am correct, each table is self relating on multiple levels across multiple columns?  I am almost 95% sure that your design is one of the contributing factors to your deadlocking.  You might try setting Allow_Page_Locks to OFF for all of the indexes or using the With TabLock hint on the Delete statements. 

     

    What is the execution plan for the delete statement?  SET SHOWPLAN_TEXT on and run the delete statement from SSMS to get the output in text format.  Is it always Table2 that deadlocks?

    Tuesday, August 26, 2008 12:51 PM
  • Hi Jonathan

     

    Thank you for your help. I wanna try Allow_Page_Locks OFF or With TabLock.

     

    You can see the execution plan in the post above. As far as I've seen it's always Table2 that deadlocks. Table2 is the only table which is self relating!

     

    Tuesday, August 26, 2008 1:17 PM
  • The other thing I might recommend is to build an intermediate step:

     

    Code Snippet

    DECLARE @RowstoDelete TABLE

    (RID bigint)

     

    INSERT INTO @RowsToDelete

    SELECT ID FROM Table2 WHERE TempID = @TempID

     

    DELETE Table2

    JOIN @RowsToDelete ON ID = RID

     

     

    This allows you to target the data by Primary Key, and you should avoid criss crossing the indexes on this table since you are grabbing the PK only from the NCI which won't force a Key Lookup. 

     

    The other thing you can try is to disable the constraints on the table, then delete the data, and enable the constraints afterwards, or leave the constraints in no-check mode all the time.

    • Unmarked as answer by Dunken Wednesday, February 04, 2009 3:44 PM
    Tuesday, August 26, 2008 1:26 PM
  • >> This allows you to target the data by Primary Key, and you should avoid criss crossing the indexes on this table since you are grabbing the PK only from the NCI which won't force a Key Lookup. 

    Since I still have huge problems with my delete statement, I tried this (insert in table variable and delete afterwards). Sadly I still have a lot of deadlocks (on PK). Do I have any other possibility?
    Wednesday, February 04, 2009 3:43 PM
  • I see this with two threads executing unique deletes in a simple way, and the deadlock graph tells me it's fighting over the clustered index (for no apparent reason).

    Unclustering that index 'solved' the problem, but that wasn't an option.

    The only thing that came to mind is that the WHERE statement will grab a different type of lock to the DELETE, and the two locks are in a deadly embrace between threads.

    I solved my particular problem by using the DELETE <table> WITH (UPDLOCK) WHERE Id=<id> syntax.

    This might help you. I believe i have snapshot read committed on though so it may act differently.

     

    I have yet to understand WHY this is not something that is automatic in SQL Server and has to be my (or your) problem.

    Surely there would be a test harness that ran multiple threads using the basic delete from where pkid= that would never cause problems.

     

    Wednesday, July 06, 2011 12:09 PM