UPDATE上的SQL Server死锁对于同一个表

I have 3 stored procedures (simplified, please try to ignore why I'm updating the table twice and why the SP is called twice):

我有3个存储过程(简化,请尝试忽略为什么我要更新表两次以及为什么SP被调用两次):

CREATE SP1 AS
BEGIN TRANSACTION

   -- Updated twice
   UPDATE Customers SET Name = 'something' Where Id = 1 OUTPUT INSERTED.*
   UPDATE Customers SET Name = 'something'

   COMMIT TRANSACTION;
END

CREATE SP2 AS
BEGIN TRANSACTION
   UPDATE Customers SET Name = 'anothername'
   COMMIT TRANSACTION;
END

CREATE SP3 AS
BEGIN TRANSACTION

    -- Called twice 
    EXEC SP2
    EXEC SP2

    COMMIT TRANSACTION;
END 

The problem is that I got a deadlock from sql server. It says that SP1 and SP3 are both waiting for the Customers table resource. Does it make sense? Could it be because of the inner transaction in SP2? or maybe the use of OUTPUT statement...?

问题是我从sql server遇到了死锁。它说SP1和SP3都在等待Customers表资源。是否有意义?可能是因为SP2中的内部事务?或者可能使用OUTPUT语句......?

The lock is a Key lock on the PK of Customers. The requested lock mode of each waiting SP is U and the owner is X (The other object i guess).

锁是客户PK的钥匙锁。每个等待SP的请求锁定模式是U,所有者是X(我猜的另一个对象)。

A few more details: 1. These are called from the same user multiple times on different processes. 2. The statements are called twice only for the sake of the example. 3. In my actual code, Customer is actualy called 'Pending Instructions'. The instructions table is sampled every minute by each listener (computer, actualy). 4. The first update query first gets all the pending instructions and the second one updates the status of the entire table to completed, just to make sure that none are left in pending mode. 5. SP3 is calling SP2 twice because it updates 2 proprietory instructions row, this happens once a day.

更多细节:1。这些在不同进程中多次从同一用户调用。 2.仅为了示例,语句被调用两次。 3.在我的实际代码中,客户实际上被称为“待处理指令”。每个听众(计算机,实际)每分钟对指令表进行采样。 4.第一个更新查询首先获取所有待处理指令,第二个更新查询将整个表的状态更新为完成,以确保没有任何处于待处理模式。 5. SP3两次调用SP2,因为它更新了2个专有指令行,这种情况每天发生一次。

Thanks a lot!!

非常感谢!!

1 个解决方案

#1

Why are you surprised by this? You have written the book case for a deadlock and hit it.

你为什么对此感到惊讶?你已经把书籍案例写成了僵局并且打了它。

The first update query first gets all the pending instructions and the second one updates the status of the entire table to completed.

第一个更新查询首先获取所有待处理指令,第二个更新查询将整个表的状态更新为已完成。

Yes, this will deadlock. Two concurrent calls will find different 'pending' instructions (as new 'pending' instructions can be inserted in between). Then they will proceed to attempt to update the entire table and block on each other, deadlock. Here is the timeline:

是的,这将陷入僵局。两个并发调用将找到不同的“待处理”指令(因为可以在其间插入新的“待处理”指令)。然后他们将继续尝试更新整个表并阻塞彼此,死锁。这是时间表:

1. Table contains customer:1, pending

表包含客户:1,待定

2. T1 (running first update of SP1) updates table and modifies customer:1

T1(运行SP1的第一次更新)更新表并修改客户:1

3. T2 inserts a new record, customer:2, pending

T2插入一条新记录,客户:2,待定

4. T3 (running first update of SP1) updates table and modifies customer:2

T3(运行SP1的第一次更新)更新表并修改客户:2

5. T1 (running second update of SP1) tries to update all table, is blocked by T3

T1(运行SP1的第二次更新)尝试更新所有表,被T3阻止

6. T3 (running second update of SP1) tries to update all table, is blocked by T1. Deadlock.

T3(运行SP1的第二次更新)尝试更新所有表,被T1阻止。僵局。

I have good news though: the deadlock is the best outcome you can get. A far worse outcome is when your logic missed 'pending' customers (which will happen more often). simply stated, your SP1 will erroneously mark any new 'pending' customer inserted after the first update as 'processed', when it was actually just skipped. Here is the timeline:

我有个好消息:僵局是你能得到的最好结果。更糟糕的结果是,当您的逻辑错过了“待定”客户时​​(这种情况会更频繁地发生)。简单地说,你的SP1会错误地将第一次更新后插入的任何新“待定”客户标记为“已处理”,而实际上只是跳过它。这是时间表:

1. Table contains customer:1, pending

表包含客户:1,待定

2. T1 (running first update of SP1) updates table and modifies customer:1

T1(运行SP1的第一次更新)更新表并修改客户:1

3. T2 inserts a new record, customer:2, pending

T2插入一条新记录,客户:2,待定

4. T1 (running second update of SP1) tries updates the whole table. customer:2 was pending and is reset w/o actually had been processed (is not i SP1's result set).

T1(运行SP1的第二次更新)尝试更新整个表。客户:2正在等待,并且已经处理了重置(实际上不是我的SP1的结果集)。

5. Your business lost an update.

您的企业丢失了更新。

so I suggest to go back to the drawing board and design SP1 properly. I suggest SP1 should only update on the second statement what it had updated on on the first one, for instance. Posting real code, with proper DDL, would go along way toward getting a useful solution.

所以我建议你回到绘图板并正确设计SP1。我建议SP1应该只在第二个语句上更新它在第一个语句上更新的内容,例如。使用适当的DDL发布真实代码将会获得有用的解决方案。