重新提交的隔离级别是否可能导致死锁（SQL Server）？ [英] Can a readcommitted isolation level ever result in a deadlock (Sql Server)?

问题描述

我对死锁的理解是-两个试图争夺相同资源的进程-通常是两个试图写入同一行数据的进程。如果一个进程正在读取数据，而另一个进程正在更新数据，那么资源争用​​如何？但是，在我们设置为默认事务级别 ReadCommitted的数据库中，我们看到了几个死锁异常。
ReadComitted definitin-无法读取已修改（但尚未提交）的数据。很好-但是如果SQL Server遇到这种脏读事件，是否应该引发死锁异常？
在这种情况下有人有现实世界的经验吗？我发现了一篇博客文章（由stackoverflow开发人员，同样是：）声称这是真的。

My understanding of deadlocks is - two processes trying to contend for same resource - typically two processes trying to 'write' to same row of data. If all one process is doing is reading the data - and the other process is updating the data, how is that a resource contention? Yet, in our database, which is set to the default transaction level 'ReadCommitted', we are seeing several deadlock exceptions. ReadComitted definitin - Data that has been modified (but not yet committed) cannot be read. That is fine – but should SQL Server throw a deadlock exception if it encounters this ‘dirty read’ taking place? Anybody have real world experience with this scenario? I found a blog post (by the stackoverflow developer, no less :) claiming that this might be true.

谢谢

推荐答案

ReadComitted 事务隔离级别最初在资源上（即在读取行时）获得了 Shared Lock 但是当我们尝试更新该行时，它将获得资源上的 Exclusive锁。多个用户可以在同一行上拥有共享锁，但不会起作用，但是一旦一个用户尝试更新一行，它就会在该行上获得排他锁，这可能导致A 死锁最初由于该行上的共享锁而可以看到记录的用户，但是现在该用户尝试对其进行更新时，该用户已经由第一个用户对其进行了排他锁。设想一个场景，其中User1和User2都拥有共享锁，并且当他们尝试更新某些记录时，它们都在其他用户需要提交事务的行上都获得了排他锁。这将导致死锁。

如果没有设置 Priority Level 时发生死锁，则SQL Server将等待一段时间，然后将 RollBack 要回滚的交易便宜 c。

编辑

是，如果User1仅读取数据，而User2尝试更新某些数据，并且该表上存在非聚集索引，是可能的。

ReadComitted Transaction Isolation Level initially obtains a Shared Lock on a resource i.e while reading the row but when we try to UPDATE the row it obtains an Exclusive lock on the resources. Multiple user can have shared locks on same rows and it wont effect but as soon as One user tries to update a row It gets an Exclusive Lock on the row which can result in A dead lock when a user who could initially see the record because of the shared locks on the row but now when the user tries to update it It already has an exclusive lock on it by the 1st user. Imagine a scenario where User1 and User2 Both has shared locks and when they try to update some records they both get Exclusive locks on the rows which other user need to commit the transaction. this will result in a DEAD LOCK.
In case of a DeadLock if the Priority Level is not set SQL Server will wait for sometime and then it will RollBack the transaction which is cheaper to rollback.
Edit
Yes if User1 is only reading data and User2 trys to Update some data and there a non-clustered index on that table, it is possible.

1）User1正在读取Some Data，并在非聚集索引上获取了共享锁，以便执行查找，然后尝试在该对象上获取共享锁。

1) User1 is reading Some Data and obtains a shared lock on the non-clustered index in order to perform a lookup, and then tries to obtain a shared lock on the page contianing the data in order to return the data itself.

2）正在写/更新的User2首先在包含该数据的数据库页面上获得排他锁。尝试获取索引的排他锁以更新索引。

2) User2 who is writing/Updating first obtains an exlusive lock on the database page containing the data, and then attempts to obtain an exclusive lock on the index in order to update the index.

这篇关于重新提交的隔离级别是否可能导致死锁（SQL Server）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！