死锁 - 锁定并等待相同索引

Question

我在一个SQL Server应用程序中遇到了死锁。我运行了SQL Profiler并打开了“死锁图”，它告诉我两个进程都持有并等待对同一主键索引上的锁。

死锁是由对表的更新引起的，有多个线程在运行，而且两个进程都在运行相同的更新存储过程（尽管使用不同的参数）。这很好，因为存储过程只会增加计数器，所以以前的数据状态并不重要。

即：UPDATE xxx SET yyy = yyy + zzz WHERE aaa = @aaa

以下是需要回答的问题：

1.为什么两个进程都已经持有X锁，还要请求U锁？他们不可能都使用X锁执行更新吗？

2.两个进程如何同时获取X锁？

3.我该如何解决这个问题？ :-)

感谢您的帮助。

附加信息：

确切的存储过程是：

CREATE PROC spuPlayerStats
    @PlayerId int,
    @HandsPlayed int
AS
BEGIN
        UPDATE Player  
        SET
            HandsPlayed = HandsPlayed + @HandsPlayed
        WHERE
            PlayerId = @PlayerId
END
GO

索引只是一个int主键聚集索引。

- Will Calderwood

---

存储过程中还有哪些语句？死锁通常不是由单个语句冲突引起的（至少在语句很简单且原子性时），而通常是因为每个事务中有其他语句，并且其中一些资源可能会受到这些语句（和/或顺序）的影响。您能否提供有关该过程的其余部分、xxx的索引和结构以及导致死锁的@ aaa的值的更多详细信息？该图形很漂亮，但对我们来说确实没有任何有用的信息，因为我们无法追踪对象ID等。 - Aaron Bertrand

@JNK 对，这正是我问的 @PlayerId 的值在发生这种情况时提供了什么。但如果只有这些，那么过程中的一个调用应该会成功，而另一个则等待。因此我认为还有更多的东西 - 我不记得曾经能够使用这么简单的语句重现死锁。 - Aaron Bertrand

@Aaron - 我以前见过这种情况，我认为它会在 PlayerID 过滤器的范围扫描中出现重叠范围。不过这似乎仍然是一个极端情况。 - JNK

除了死锁事件，您还需要使用过滤器（TextData LIKE '%spuPlayerStats%'）跟踪实际的过程调用（例如SP:Completed）。TextData将包含实际语句（例如“'EXEC spuPlayerStats 1,1'”）。 - Aaron Bertrand

@JNK，还有其他存储过程，但根据死锁图中的 XML，这些是涉及到死锁的存储过程。有时候会牵扯到另一个存储过程——插入另一张表格中与问题索引的 PK 有外键关系的记录，但这种情况出现得较少。 - Will Calderwood

Answer 1

请尝试：

SELECT @updateValue = HandsPlayed + @HandsPLayed FROM Player WHERE PlayerId = @PlayerId

UPDATE Player  
    SET
        HandsPlayed = updateValue 
    WHERE
        PlayerId = @PlayerId

- Trevor

Answer 2

我建议您发布实际的死锁 XML。图形表示并不总是准确的，请参见死锁图中U锁之谜。即使在调整良好的查询上，也可能发生死锁，因为更新应用的顺序不同，请参见读/写死锁。而且，即使在表面上看起来100％安全的系统上（例如您在帖子中最初描述的那个：在聚集索引上更新一行而没有进行二级索引更新，使用不同的键），也可能发生死锁，因为哈希冲突，请参见%%lockres%% 碰撞概率魔术标记：16,777,215。

现在我不认为你的情况是一个神秘的案例，在你的情况下，看起来只是缺乏关于实际发生情况的信息。请发布您数据的确切模式定义（所有表，所有索引），每个涉及事务中发生的确切操作以及实际死锁 XML，而不是图形渲染。

- Remus Rusanu

---

很抱歉，我无法发布完整的DB模式，尽管我同意，但我认为有一些事情正在发生，而我不认为是正在发生的（如果你明白我的意思的话）。您提供的“读/写死锁”链接看起来很有趣。我想知道是否可以利用那些信息找到一些线索。我曾尝试将XML粘贴进网站，但该网站不喜欢这种格式。 - Will Calderwood

一些用户通过像pastebin.com或类似的服务将附件发布到SO。 - Remus Rusanu