时间戳

首页 > 解决方案 > 解决多个存储过程读取和更新 SQL Server 2014 数据库中的同一个表时的死锁

sql - 解决多个存储过程读取和更新 SQL Server 2014 数据库中的同一个表时的死锁

问题描述

我有两个存储过程。当从服务器接收到记录以将其保存在关系表中时，第一个 SP 运行。第二个 SP 在每个固定时间间隔后通过来自 .NET 服务器的调度程序运行。

第一个存储过程代码：

BEGIN TRY
INSERT INTO RELATIONSHIP SELECT DATA FROM ANOTHER TABLE
SELECT INTO TEMPTABLE FROM RELATIONSHIP
UPDATE RELATIONSHIP SET...
END TRY

第二个存储过程代码：

BEGIN TRY
BEGIN TRANSACTION
SELECT INTO TEMPTABLE FROM RELATIONSHIP
UPDATE RELATIONSHIP SET ISPROCESSED = 1 WHERE ...
COMMIT TRANSACTION
END TRY

当第二个存储过程运行时，它会陷入死锁。此外，第一个存储过程不是写在事务中。

考虑到代码逻辑和流程无法更改，解决死锁的最佳做法是什么？我只编写了与关系表相关的代码，并跳过了与其他表相关的代码，因为该表上发生了死锁。如果您需要更多信息，请告诉我，我也可以从运行状况监视器发布死锁日志。

死锁图1 在此处输入图像描述

    <deadlock>
 <victim-list>
  <victimProcess id="process207c2cd3c28" />
 </victim-list>
 <process-list>
  <process id="process207c2cd3c28" taskpriority="0" logused="0" waitresource="PAGE: 5:1:53898 " waittime="774" ownerId="10078305" transactionname="UPDATE" lasttranstarted="2021-07-27T15:43:22.120" XDES="0x20d350857d0" lockMode="U" schedulerid="10" kpid="9288" status="suspended" spid="74" sbid="0" ecid="16" priority="0" trancount="0" lastbatchstarted="2021-07-27T15:43:21.030" lastbatchcompleted="2021-07-27T15:43:21.030" lastattention="1900-01-01T00:00:00.030" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="6900" isolationlevel="read committed (2)" xactid="10078305" currentdb="5" currentdbname="" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
   <executionStack>
    <frame procname="ProcessMessage" line="153" stmtstart="11462" stmtend="11928" sqlhandle="0x03000500abb08931c097be0072ad000001000000000000000000000000000000000000000000000000000000">
UPDATE lar
        SET 
            -- some code
        FROM Relationship lar INNER JOIN --few joins   </frame>
    <frame procname="" line="133" stmtstart="10056" stmtend="10112" sqlhandle="0x03000500caf178474f6b59006ead000001000000000000000000000000000000000000000000000000000000">
    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 5 Object Id = 1199108554]   </inputbuf>
  </process>
  <process id="process20958d27848" taskpriority="0" logused="2060" waitresource="PAGE: 5:1:53794 " waittime="523" ownerId="9400887" transactionname="ProcessDiscrepancies" lasttranstarted="2021-07-27T13:24:45.573" XDES="0x20d2cb80420" lockMode="IU" schedulerid="2" kpid="7576" status="suspended" spid="94" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-07-27T13:24:45.570" lastbatchcompleted="2021-07-27T13:24:45.573" lastattention="1900-01-01T00:00:00.573" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="6900" loginname="" isolationlevel="read committed (2)" xactid="9400887" currentdb="5" currentdbname="" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
   <executionStack>
    <frame procname="ProcessDiscrepancies" line="140" stmtstart="10254" stmtend="11002" sqlhandle="0x030005003968a12f1d68be0072ad000001000000000000000000000000000000000000000000000000000000">
UPDATE lar

            SET 

                IsProcessed = 1,

            FROM Relationship lar   

            INNER JOIN --few joins  </frame>
    <frame procname="adhoc" line="1" stmtend="60" sqlhandle="0x010005007715562a80688d2c0d02000000000000000000000000000000000000000000000000000000000000">
EXEC ProcessDiscrepancie    </frame>
   </executionStack>
   <inputbuf>
EXEC ProcessDiscrepancies   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <pagelock fileid="1" pageid="53898" dbid="5" subresource="FULL" objectname="Relationship" id="lock1ffa075f280" mode="IU" associatedObjectId="72057597189357568">
   <owner-list>
    <owner id="process20958d27848" mode="IU" />
   </owner-list>
   <waiter-list>
    <waiter id="process207c2cd3c28" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="53794" dbid="5" subresource="FULL" objectname="Relationship" id="lock204c8f59680" mode="U" associatedObjectId="72057597189357568">
   <owner-list>
    <owner id="process207c2cd3c28" mode="U" />
   </owner-list>
   <waiter-list>
    <waiter id="process20958d27848" mode="IU" requestType="wait" />
   </waiter-list>
  </pagelock>
 </resource-list>
</deadlock>

标签： sqlsql-serverstored-proceduresdeadlockdatabase-deadlocks

解决方案

很少有修复死锁场景的建议。您必须尝试看看它们是否有助于解决问题。

  1. 始终以类似的方式处理交易。有一个交易模式可以遵循。下面是模板，我在关注。参考 SO 帖子
CREATE PROCEDURE [Name]
AS
SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0
        BEGIN TRANSACTION

       [...Perform work, call nested procedures...]

    IF @starttrancount = 0 
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION;
    THROW;
    --before SQL Server 2012 use 
    --RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
GO

  1. 使交易尽可能小。为什么要在事务中加载 temptable。尝试将其加载到事务之外以使事务更小。

  2. 定义适当的索引以使查询运行得更快，这将使事务更小

  3. 为什么要一一插入和更新关系表。为什么你不能把你的更新作为插入的一部分来处理。它将完全避免 UPDATE 语句。

推荐阅读