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.
Thanks
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 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 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.
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.
