死锁 - 锁定并等待相同的索引
我的 SQL Server 应用程序中发生了死锁。我已经运行了 SQL Profiler 并拉出了一个“死锁图”,这似乎告诉我两个进程都在持有并等待同一主键索引上的锁。
死锁是由表更新引起的,有多个线程正在运行,并且两个进程都在运行相同的更新存储过程(尽管参数不同)。这很好,因为存储过程仅递增计数器,因此以前的数据状态并不重要。
即 UPDATE xxx SET yyy = yyy + zzz WHERE aaa = @aaa
- 为什么两个进程都已经持有 X 锁时还请求 U 锁,当然它们都可以使用 X 锁执行更新?
- 两个进程如何同时获得 X 锁?
- 我该如何解决这个问题? :-)
感谢您的帮助。
编辑:附加信息
确切的过程是:
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
- 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?
- How have both processes got a X lock at the same time?
- How do I go about fixing this? :-)
Thanks for the help.
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 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我建议您发布实际的死锁 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.
尝试:
TRY: