代码之家 › 专栏 › 技术社区 › Oyen

同一表中2个INSERT之间的死锁

azure-sql-database entity-framework

Oyen · 技术社区 · 4 年前

我们刚刚开始解决系统中发生的死锁。我们已经追踪了其中的大部分,除了最不常见的情况:2个INSERT事务之间同一表中的死锁。

是什么导致了僵局?我们拥有的信息:

这种情况很少发生:过去一个月发生了3次;我们不能强迫死锁发生在我们的开发环境中。
M_DocumentsOrder表有6万行
xml显示的是“pagelock”,而不是“keylock”(这在我们的其他死锁案例中很常见)

客户端使用EntityFramework连接到数据库,并检查其代码似乎直截了当。然后添加()。SaveChanges()类似于:

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

下面是死锁_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>

以下是表格定义:

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

0 回复 | 直到 4 年前