用c更新时MSSQL死锁(updlock) [英] MSSQL Deadlock when update withc (updlock)

问题描述

我在更新时陷入僵局.事务级别设置为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.

这篇关于用c更新时MSSQL死锁(updlock)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！