死锁：插入与从多个表中选择。快照隔离？
在我的数据库中，我有两个密切相关的表。 有一个经常被称为 SP 的地方，它在事务中将一些行插入到两个表中，并且还有其他几个地方从这些连接的表中执行 SELECT。
INSERT 对两个表都采用 X 锁，SELECT 对它们采用 S 或 IS 锁。由于获取共享锁的顺序因查询而异，因此其中一些查询偶尔会因 INSERT 事务而发生死锁。
有没有什么好方法可以避免这些死锁（NOLOCK 可能不符合“好”条件）？
到目前为止，我能想到的唯一通用解决方案是使用 SNAPSHOT 隔离级别。然而，它会增加一些性能开销，而且我还没有找到任何关于这个开销有多大的可靠数据。
In my database I have two closely related tables.
There is a frequently called SP that INSERTs some rows into both tables within a transaction, and several other places that do SELECTs from these tables joined.
INSERTs take X locks on both tables, SELECTs take S or IS locks on them. Since the order in which shared locks are taken varies from query to query, some of them occasionally get deadlocked with the INSERT transaction.
Is there any good way to avoid these deadlocks (NOLOCK probably doesn't qualify as 'good')?
So far the only general solution I can think of is using SNAPSHOT isolation level. However, it would add some performance overhead, and I haven't yet found any sound data on how large this overhead is.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我在我的系统中使用快照。它不是免费的，这是肯定的，但替代方案也不是免费的 - 阻塞也会消耗资源。使用行锁并不总是有帮助。
快照还为您提供数据的一致时间点快照；否则你会遇到一些微妙的错误。
另一件事：即使只有一张表，也可能会出现死锁，示例如下： 重现仅涉及一张表的死锁
I use snapshot in my system. It is not free, that's for sure, but the alternatives are not free either - blocking uses up resources too. Using rowlock does not always help.
Also snapshot gives you a consistent point in time snapshot of your data; otherwise you are exposed to some subtle bugs.
One more thing: you can get deadlocks even if you have only one table, examples here: Reproducing deadlocks involving only one table
您 SP 是否在事务内从这些表中选择或更新任何内容？如果没有，您可以尝试对插入和其他选择使用行锁提示（行锁通常不会升级为页锁或表锁，除非选择结果中有太多行）。如果是，那么您可以尝试在 SP 事务中选择 updlock 提示。
Does you SP select or update anything from these tables inside transaction? If not, you can try to use rowlock hints for your inserts and other selects (rowlocks usually do not escalate into page or table locks, unless you have too many rows in select results). If yes, then you can try updlock hint for your selects inside SP transaction.
我不确定这是否对您有帮助，但我发现不久前这个博客帮助我解决了一些僵局。
I'm not sure if this will help you but I found this blog awhile ago and it helped me cleanup some deadlocks.