12

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.

Improve this question

2 Answers 2

Reset to default
11

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

You too are talking about user1 and user2 both trying to update the same record. Deadlock in that case is acceptable. However, I want to know if anyone has seen a deadlock result from two transactions - one of which is a WRITE and the other is a read (on the same data of course).
have a look I have updated the answer and explained.
@M.Ali Which Isolation Level would solve it?
5

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.

Improve this answer

2 Comments

Well - you are describing two update transactions. In that case, it makes sense to run into a deadlock. My question is - one of the transactions is just a READ - no updates. The other one is writing to the same record that is being read. How can this possibly result in a deadlock?
Off the top of my head, that shouldn't deadlock but if you edit the question with more details of what the transactions are trying to do, we might be able to get to the bottom of it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.