My understanding of deadlocks is - two processes trying to contend for the same resource - typically two processes trying to 'write' to the 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 see several deadlock exceptions. ReadComitted definition - 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? Does 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.
2条答案
按热度按时间k4emjkb11#
ReadCommitted 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 anExclusive 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 Adead 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 willRollBack
the transaction which ischeaper
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.
r1zhe5dt2#
Yes, it can happen. Imagine you have two processes each with its own transaction. The first updates TableA then tries to update TableB. The second updates TableB then tries to update TableA. If you're unlucky, both processes manage to complete their first step and then wait indefinitely to the other in order to complete the second step.
Incidentally, that's one of the most common ways to avoid deadlocks: be consistent in order in which you update your table. If both processes updated TableA first then TableB, the deadlock wouldn't occur.