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

发布于 2024-12-01 08:34:36 字数 742 浏览 0 评论 0原文

我的 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 主聚集索引。

I've got a deadlock occurring in an SQL Server application. I've run SQL Profiler and pulled up a "deadlock graph", and this seems to be telling me that both processes are holding and waiting for a lock on the same primary key index.

The deadlock is being caused by an update to the table, there are multiple threads running and both processes are running the same update stored procedure (although with different parameters). This is fine as the stored proc only increments counters, so previous data state doesn't matter.

i.e. UPDATE xxx SET yyy = yyy + zzz WHERE aaa = @aaa

  1. Why are both processes requesting a U lock when they both already hold an X lock, surely they could both perform the update with the X lock?
  2. How have both processes got a X lock at the same time?
  3. How do I go about fixing this? :-)

Thanks for the help.

enter image description here

edit: Additional info

The exact procedure is:

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

The index is just an int primary clustered index.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

嘿看小鸭子会跑 2024-12-08 08:34:36

我建议您发布实际的死锁 XML。图形表示并不总是准确的,请参见 死锁图中U锁之谜。由于应用更新的顺序,即使在经过良好调整的查询上看似简单的语句上也可能发生死锁,请参阅 读/写死锁。即使在显然 100% 安全的系统上,由于哈希冲突,就像您最初在帖子中描述的系统(在不同键上聚集索引中的一行更新,没有二级索引更新)一样,死锁也可能发生,请参阅 %%lockres%%碰撞概率魔法标记:16,777,215

现在我不认为你的案例是一个深奥的案例,在你的情况下,它看起来只是缺乏关于实际发生的情况的信息。请发布数据的准确架构定义(所有表、所有索引)、涉及的每个事务中发生的确切操作以及实际的死锁 XML,而不是图形呈现。

I recommend you post the actual deadlock XML. The graphical representation is not always accurate, see The puzzle of U locks in deadlock graphs. Deadlocks can happen even on deceptively simple statements on well tuned queries, due to order of applying the updates, see Read/Write deadlock. And deadocks can happen even on systems that apparently are 100% safe, like the one you originally described in the post (one row update in a clustered index w/o secondary index updates, on different keys) due to hash collisions, see %%lockres%% collision probability magic marker: 16,777,215.

Now I don't expect your case to be an esoteric one, in your situation what it appears to be is simply lack of information about what actually happens. Please post the exact schema definition of your data (all tables, all indexes), the exact operations that occur in each transaction involved, and the actual deadlock XML, not the graphical rendering.

莫多说 2024-12-08 08:34:36

尝试:

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

UPDATE Player  
    SET
        HandsPlayed = updateValue 
    WHERE
        PlayerId = @PlayerId

TRY:

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

UPDATE Player  
    SET
        HandsPlayed = updateValue 
    WHERE
        PlayerId = @PlayerId
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文