Deadlock between 2 INSERT's in the same table

Question

We just started resolving deadlocks happening in our system. We've traced most of it except for the least frequent case: a deadlock in the same table between 2 INSERT transactions.

What could be causing the deadlocks? Info we have:

It happened infrequently: 3 instances in the past month; we cannot force the deadlock to happen on our dev environment.
M_DocumentsOrder table has 60k rows
Instead of "keylock" (which was common in our other deadlock cases) the xml shows "pagelock" instead

Client uses EntityFramework to connect to the database, and checking the code it seems straightforward .Add() then .SaveChanges() something like:

using (MyTransaction context = new MyTransaction())
{
        ...set data
        context.DocumentsOfOrder.Add(document);
        context.SaveChanges();
}

Here is the deadlock_xml:

<deadlock>
  <victim-list>
    <victimProcess id="process27221b688c8" />
  </victim-list>
  <process-list>
    <process id="process27221b688c8" taskpriority="0" logused="2880" waitresource="PAGE: 11:1:6488424 " waittime="1935" ownerId="909010590" transactionname="user_transaction" lasttranstarted="2020-02-27T04:29:40.627" XDES="0x272e9308428" lockMode="U" schedulerid="5"
      kpid="12772" status="suspended" spid="226" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-02-27T04:29:40.633" lastbatchcompleted="2020-02-27T04:29:40.633" lastattention="1900-01-01T00:00:00.633" clientapp="EntityFramework" hostname="HOSTNAME"
      hostpid="17832" loginname="myuser" isolationlevel="read committed (2)" xactid="909010590" currentdb="11" currentdbname="MyDatabase" lockTimeout="4294967295" clientoption1="671219744" clientoption2="128056">
      <executionStack>
        <frame procname="d4aaa79f-2ccf-4ea7-a07b-944a4c5cf03d.dbo.tr_M_DocumentsOfOrder_U" queryhash="0xc7b676ceb02e8dc7" queryplanhash="0xc7b676ceb02e8dc7" line="8" stmtstart="394" stmtend="712" sqlhandle="0x03000b00bb81a769bb1a3500e6a9000000000000000000000000000000000000000000000000000000000000">
        UPDATE [M_DocumentsOfOrder] SET [MODIFIED]=GETUTCDATE() FROM Inserted WHERE [M_DocumentsOfOrder].ID = M_DocumentsOfOrder.I </frame>
        <frame procname="d4aaa79f-2ccf-4ea7-a07b-944a4c5cf03d.dbo.tr_M_DocumentsOfOrder_I" queryhash="0x7de7ba0aa490af07" queryplanhash="0x85bac9a23b352e1e" line="7" stmtstart="392" stmtend="720" sqlhandle="0x03000b00825db368ba1a3500e6a9000000000000000000000000000000000000000000000000000000000000">
        UPDATE [M_DocumentsOfOrder] SET CREATED=GETUTCDATE() ,MODIFIED=GETUTCDATE() FROM Inserted Where [M_DocumentsOfOrder].ID = Inserted.I </frame>
        <frame procname="unknown" queryhash="0x69216ae0d9369f18" queryplanhash="0x25afc3efc8bd6515" line="1" stmtstart="42" stmtend="342" sqlhandle="0x02000000e89c2d220cda0bca1e051913383a04bc14b1959e0000000000000000000000000000000000000000"> unknown </frame>
        <frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"> unknown </frame>
      </executionStack>
      <inputbuf> (@0 bigint,@1 bigint)INSERT [dbo].[M_DocumentsOfOrder]([Document_ID], [Order_ID], [CREATED], [MODIFIED]) VALUES (@0, @1, NULL, NULL) SELECT [ID] FROM [dbo].[M_DocumentsOfOrder] WHERE @@ROWCOUNT &gt; 0 AND [ID] = scope_identity() </inputbuf>
    </process>
    <process id="process277b78904e8" taskpriority="0" logused="8305172" waitresource="PAGE: 11:1:8384387 " waittime="1121" ownerId="909010544" transactionname="user_transaction" lasttranstarted="2020-02-27T04:29:40.527" XDES="0x274dbf6c428" lockMode="U" schedulerid="2"
      kpid="20852" status="suspended" spid="133" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-02-27T04:29:40.533" lastbatchcompleted="2020-02-27T04:29:40.533" lastattention="1900-01-01T00:00:00.533" clientapp="EntityFramework" hostname="HOSTNAME2"
      hostpid="16164" loginname="myuser" isolationlevel="read committed (2)" xactid="909010544" currentdb="11" currentdbname="MyDatabase" lockTimeout="4294967295" clientoption1="671219744" clientoption2="128056">
      <executionStack>
        <frame procname="d4aaa79f-2ccf-4ea7-a07b-944a4c5cf03d.dbo.tr_M_DocumentsOfOrder_U" queryhash="0xc7b676ceb02e8dc7" queryplanhash="0xc7b676ceb02e8dc7" line="8" stmtstart="394" stmtend="712" sqlhandle="0x03000b00bb81a769bb1a3500e6a9000000000000000000000000000000000000000000000000000000000000">
        UPDATE [M_DocumentsOfOrder] SET [MODIFIED]=GETUTCDATE() FROM Inserted WHERE [M_DocumentsOfOrder].ID = M_DocumentsOfOrder.I </frame>
        <frame procname="d4aaa79f-2ccf-4ea7-a07b-944a4c5cf03d.dbo.tr_M_DocumentsOfOrder_I" queryhash="0x7de7ba0aa490af07" queryplanhash="0x85bac9a23b352e1e" line="7" stmtstart="392" stmtend="720" sqlhandle="0x03000b00825db368ba1a3500e6a9000000000000000000000000000000000000000000000000000000000000">
        UPDATE [M_DocumentsOfOrder] SET CREATED=GETUTCDATE() ,MODIFIED=GETUTCDATE() FROM Inserted Where [M_DocumentsOfOrder].ID = Inserted.I </frame>
        <frame procname="unknown" queryhash="0x69216ae0d9369f18" queryplanhash="0x25afc3efc8bd6515" line="1" stmtstart="42" stmtend="342" sqlhandle="0x02000000e89c2d220cda0bca1e051913383a04bc14b1959e0000000000000000000000000000000000000000"> unknown </frame>
        <frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"> unknown </frame>
      </executionStack>
      <inputbuf> (@0 bigint,@1 bigint)INSERT [dbo].[M_DocumentsOfOrder]([Document_ID], [Order_ID], [CREATED], [MODIFIED]) VALUES (@0, @1, NULL, NULL) SELECT [ID] FROM [dbo].[M_DocumentsOfOrder] WHERE @@ROWCOUNT &gt; 0 AND [ID] = scope_identity() </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <pagelock fileid="1" pageid="6488424" dbid="11" subresource="FULL" objectname="d4aaa79f-2ccf-4ea7-a07b-944a4c5cf03d.dbo.M_DocumentsOfOrder" id="lock270620df700" mode="U" associatedObjectId="72059477776334848">
      <owner-list>
        <owner id="process277b78904e8" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process27221b688c8" mode="U" requestType="wait" />
      </waiter-list>
    </pagelock>
    <pagelock fileid="1" pageid="8384387" dbid="11" subresource="FULL" objectname="d4aaa79f-2ccf-4ea7-a07b-944a4c5cf03d.dbo.M_DocumentsOfOrder" id="lock274fde20700" mode="IX" associatedObjectId="72059477776334848">
      <owner-list>
        <owner id="process27221b688c8" mode="IX" />
      </owner-list>
      <waiter-list>
        <waiter id="process277b78904e8" mode="U" requestType="convert" />
      </waiter-list>
    </pagelock>
  </resource-list>
</deadlock>

Here's the table definition:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[M_DocumentsOfOrder](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Document_ID] [bigint] NOT NULL,
    [Order_ID] [bigint] NOT NULL,
    [CREATED] [datetime] NULL,
    [MODIFIED] [datetime] NULL,
 CONSTRAINT [PK_M_DocumentsOfOrder] 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].[M_DocumentsOfOrder]  WITH CHECK ADD  CONSTRAINT [FK_M_DocumentsOfOrder_Order] FOREIGN KEY([Order_ID])
REFERENCES [dbo].[Order] ([ID])
GO

ALTER TABLE [dbo].[M_DocumentsOfOrder] CHECK CONSTRAINT [FK_M_DocumentsOfOrder_Order]
GO

ALTER TABLE [dbo].[M_DocumentsOfOrder]  WITH CHECK ADD  CONSTRAINT [FK_M_DocumentsOfOrder_Document] FOREIGN KEY([Document_ID])
REFERENCES [dbo].[Documents] ([ID])
GO

ALTER TABLE [dbo].[M_DocumentsOfOrder] CHECK CONSTRAINT [FK_M_DocumentsOfOrder_Document]
GO

CREATE NONCLUSTERED INDEX [nci_wi_M_DocumentsOfOrder_2D7DBB9CD2F5855142A5836868D3A952] ON [dbo].[M_DocumentsOfOrder]
(
    [Order_ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO


CREATE TRIGGER [dbo].[tr_M_DocumentsOfOrder_I] ON [dbo].[M_DocumentsOfOrder] FOR INSERT AS 
BEGIN 
    IF CONTEXT_INFO() =  cast('disabled' as varbinary(128))
        RETURN;

    UPDATE [M_DocumentsOfOrder] 
        SET CREATED=GETUTCDATE()
        ,MODIFIED=GETUTCDATE()
         FROM Inserted Where [M_DocumentsOfOrder].ID = Inserted.ID
END
GO

ALTER TABLE [dbo].[M_DocumentsOfOrder] ENABLE TRIGGER [tr_M_DocumentsOfOrder_I]
GO

CREATE TRIGGER [dbo].[tr_M_DocumentsOfOrder_U] ON [dbo].[M_DocumentsOfOrder] AFTER UPDATE 
AS
BEGIN
    IF CONTEXT_INFO() =  cast('disabled' as varbinary(128))
    RETURN;

    UPDATE [M_DocumentsOfOrder] SET [MODIFIED]=GETUTCDATE()
    FROM Inserted WHERE [M_DocumentsOfOrder].ID = M_DocumentsOfOrder.ID
END
GO

ALTER TABLE [dbo].[M_DocumentsOfOrder] ENABLE TRIGGER [tr_M_DocumentsOfOrder_U]
GO

@FrankYuchengGu what could be causing this infrequent deadlock? The code that saves the entries seems normal. editing it to make the question explicit. i'm sorry about that. — Oyen, Mar 07 '20 at 04:59
The database being used has not been mentioned - nor - has any code been provided. — Casey Harrils, Mar 07 '20 at 05:22
You have a trigger on that table involved in the deadlock. Please post the full table DDL including indexes and the trigger definition. — David Browne - Microsoft, Mar 07 '20 at 14:07
Yes. Deadlocks do NOT happen between 2 insert statements per definition - 2 inserts do not deadlock. Triggers on them may cause a deadlock, but that is information for you to provide. — TomTom, Mar 07 '20 at 15:37
I've updated the question to add the CREATE statements for table, index, and triggers as requested. We have those Insert and Update triggers on all of our tables to set CREATED and MODIFIED datetime. Is there a problem with having those (in general)? — Oyen, Mar 09 '20 at 00:51

Deadlock between 2 INSERT's in the same table

0 Answers0