使用 c 更新时 MSSQL 死锁(updlock)

2022-01-17 00:00:00 sql-update database-deadlocks tsql sql-server

我在更新时遇到了死锁.事务级别设置为Read Committed.在这种情况下如何避免死锁?

I got deadlock while updating. The transaction level is set to Read Committed. How to avoid deadlock in such situation?

在其他情况下，WITH (NOLOCK) 和 WITH (UPDLOCK) 有帮助.
我得到了以下 T-SQL 查询:

In other cases WITH (NOLOCK) and WITH (UPDLOCK) helped.
I got the following T-SQL query: 

IF EXISTS (SELECT 1 FROM DEBTORS_CUSTOMERS WITH (NOLOCK) WHERE DebtorId = @DebtorId AND ClientFCCustomerNumber = @CustomerNumber)
        UPDATE DEBTORS_CUSTOMERS WITH (UPDLOCK) SET StatusId = @StatusId WHERE DebtorId = @DebtorId AND ClientFCCustomerNumber = @CustomerNumber
    ELSE
        INSERT INTO DEBTORS_CUSTOMERS (DebtorId, ClientFCCustomerNumber, StatusId, DocId) SELECT @DebtorId, @CustomerNumber, @StatusId, @DocId

这是我遇到的僵局:

   <resource-list>
   <keylock hobtid="72057594105692160" dbid="63" objectname="EOTestDataGenerator.dbo.DEBTORS_CUSTOMERS" indexname="PK_DEBTORS_CUSTOMERS" id="lockdf8abb00" mode="X" associatedObjectId="72057594105692160">
    <owner-list>
     <owner id="process3f59048" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="processbdbfa088" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594105692160" dbid="63" objectname="EOTestDataGenerator.dbo.DEBTORS_CUSTOMERS" indexname="PK_DEBTORS_CUSTOMERS" id="lockdf5ab200" mode="X" associatedObjectId="72057594105692160">
    <owner-list>
     <owner id="processbdbfa088" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process3f59048" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>

推荐答案

每个事务处理多行，对吗?这不应该为一行死锁

You are processing multiple rows per transaction, right? This should not deadlock for one row

不过，您可能会得到双插入，这是一个错误.两个会话可能会得出没有行的结论，然后两个会话都会插入.

You might get double-inserts, though, which is a bug. Two sessions might conclude that there is no row and then both will insert.

有两种方法可以确保安全:

There are two ways to make this safe:

  1. 发出 select WITH (ROWLOCK, UPDLOCK, HOLDLOCK) 这是一个众所周知的锁定提示序列.它需要一个锁来稳定您正在操作的数据.运行此语句后，您将拥有自己的数据.然后您可以插入或更新.您也可以将所有三个语句合并到一个 MERGE 中，但您仍然需要锁定提示.此外，您必须有某种全局顺序来发出写入.现在，无论您如何锁定，如果一个会话写入 A、B，而另一个会话按 B、A 顺序写入，则总是会出现死锁.获得全局顺序的一种简单方法是在单个 MERGE 中发出所有写入 语句.查询处理器通常会选择一个强制执行顺序的计划.
  2. 使用 SERIALIZABLE 隔离并重试死锁.
  1. Issue the select WITH (ROWLOCK, UPDLOCK, HOLDLOCK) which is a well-known lock hint sequence. It takes a lock that stabilized the data you are operating on. After this statement has run you have the data for yourself. You can then insert or update. You can also collapse all three statements into one MERGE but you still need the lock hints. Also, you must have some kind of global order in which you issue the writes. Right now no matter how you lock there can always be a deadlock if one session writes A, B and the other writes in order B, A. An easy way to get a global order is to issue all writes in a single MERGE statement. The query processor usually picks a plan that enforces order.
  2. Use SERIALIZABLE isolation with retry on deadlock.

相关文章