SQL 2008 SP - 死锁原因还是转移注意力？
我的一位同事有一个存储过程，可以执行以下操作
开始翻译
1）动态生成select语句。
2）插入表x
3）执行select语句
End tran
如果此存储过程由两个独立线程同时运行，则会出现以下错误： System.Data.SqlClient.SqlException：事务（进程 ID 57）在锁上死锁 |与另一个进程通信缓冲区资源并已被选为死锁受害者。重新运行事务
这个存储过程真的是问题所在吗？从我天真的想法来看，这看起来是更糟糕的竞争条件，而不是僵局。
One of my co-workers has a stored procedure that does the following
Begin tran
1) Dynamically generate a select statement.
2) Insert into table x
3) Execute the select statement
End tran
If this stored procedure is ran by two septate threads simultaneously, he gets the following error:
System.Data.SqlClient.SqlException: Transaction (Process ID 57) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction
Is this stored procedure really the issue? From my naive mind, this looks to be at worse a race condition, not a deadlock.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
两个“先写后读”序列肯定会造成死锁。您在帖子中省略了一些“详细信息”，例如发生死锁的实际资源以及涉及的请求。我们将凭直觉阅读字里行间的内容，从这样一篇记录不佳的帖子中弥补大部分案例：
还有更多的死锁场景是可能的，但我们会进入深奥的领域，或者开始对OP中缺失的信息进行相当远的推断。
如果我大胆猜测一下，最可能的情况是 2)。情况 1) 可能很容易成为标识符。情况 2）在简单的代码分析中有点难以检测，因为它取决于物理模式设计（索引结构）。
Two 'write then read' sequences can definetely deadlock. You omitted some 'details' in your post, like what is the actual resource the deadlock occurs on and that requests are involved. We'll fly by the sit of our pants and read between the lines, making up much of the case from such a poorly documented post:
Many many more deadlock scenarios are possible, but we'd enter esoterics or start to extrapolate quite far for the missing info in the OP.
If I'd venture a guess, the most likely case is 2). The case 1) would probably easily be identifier. Case 2) is a bit harder to detect in simple code analysis because it depnds on the physical schema design (index structure).
在探查器中运行跟踪（选择空白模板），选择死锁图事件，然后在出现的新选项卡（事件提取设置）上，将每个事件保存在其自己的文件中（选中单独保存死锁 XML 事件）。在 xml 查看器中打开此文件，很容易看出发生了什么。每个进程都包含有一堆过程调用等，并且所有锁也都在其中，因此您可以确定导致死锁的原因。
让这个跟踪运行直到死锁再次发生，只有当死锁发生时才会记录信息，所以不会有太多开销。
run a trace in the profiler (pick the blank template), select the deadlock graph event, and on the new tab that appears (Events Extraction Settings), save each (check save deadlock XML events separately) in its own file. Open this file in an xml viewer and it will be easy to tell what is happening. Each process is contained, with a stack of procedure calls, etc. and all locks are in there too, so you can be sure what is causing the deadlock.
Let this trace run until the deadlock happens again, info is only recorded when a deadlock happens, so not much overhead.