在表中插入行时 SQL Server 死锁
sql server 死锁这个话题已经被讨论过很多次了，但是，我不确定即使在一个表上同时进行两次插入也会导致死锁情况。
设想： 在测试我们的应用程序（SQL Server 2005 作为后端，ASP.net 3.5）时，我们同时将记录插入表中（简化概述），这导致超过 70% 的用户陷入死锁。 我无法理解插入是如何陷入死锁的，因为这不是多个资源的情况。经过详细分析（由两个用户重现错误），我发现这两个进程都在表的主键索引上持有 RangeS-S 锁，并试图将其转换为 RangeI-N 锁，这导致了死锁并且一笔交易被终止。
问题： 由于这不是资源访问顺序发生变化的情况，我们是否可以避免或减少此类死锁？难道我们不能强制事务最初获得排他锁，以便它阻止其他进程并避免死锁吗？可能产生什么（不利）影响？ 也有人可以解释一下有关 RangeI-N 锁的更多信息。
其隔离级别是“可串行化”。
任何建议都会有帮助。
谢谢， 高拉夫
The topic of sql server deadlock has been discussed many times, however, I was unsure that even two simultaneous inserts on a table can end up in a deadlock situation.
Scenario:
While testing our application (SQL Server 2005 as backend, ASP.net 3.5) we inserted records into a table simultaneously (simplified overview) and that resulted into a deadlock for more than 70% of users.
I could not get a hang of this as how an insert is being deadlock as this is not a case of multiple resources. After a detailed analysis (of reproducing the bug by two users) I found that both the processes were holding a RangeS-S lock on the primary key index of the table and were trying to convert this into RangeI-N lock, that resulted into a deadlock and one transaction being killed.
Question:
Can we avoid or reduce these kind of deadlocks as this is not a case of change in order of access of resources? Cant' we force the transaction to get exclusive lock initially so that it blocks the other process and avoid deadlock? What (adverse) effects that may have?
Also could some one explain more about RangeI-N lock.
Isolation Level for this was "Serializable".
Any suggestion will be helpful.
Thanks,
Gaurav
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
更改 ADO 隔离级别。除非您对 Serialized 有明确的要求，否则不应该使用它。如果你确实使用它，那么你必须清楚地了解后果，而由于范围锁而导致的频繁死锁就是这些后果之一。
System.Transactions 的隔离级别由
IsolationLevel属性。Change your ADO isolation level. Unless you have clear requirements for Serializable, you shouldn't use it. If you do use it, then you must clearly understand the consequences, and frequent deadlocks due to range locks are one of these consequences.
The isolation level of
System.Transactionsis controlled by theIsolationLevelproperty.使用 sp_getapplock 获取自定义排他锁
Use sp_getapplock to acquire a custom exclusive lock