Database Deadlock: Select blocking an Update?

[generelz]

Hi all,

I was wondering if anyone here had experience diagnosing some SQL Server deadlock I have recently run into, or if anyone has encountered this problem before on other databases.

The situation I have run into is that of a SELECT query blocking an UPDATE query, and the UPDATE blocking the SELECT. Now, it makes sense that the UPDATE would block the select (since it is a part of a transaction). The transaction isolation level is set to read committed and according to the activity monitor the UPDATE has acquired an exclusive lock (LCK_M_X). However I do not understand why the UPDATE is waiting on a SELECT. The SELECT does not hold any locks and its wait type is ASYNC_NETWORK_IO. Now, I looked this wait type up and it suggests that this is when the database is waiting on the client to read data. However what I do not understand is why or how this would block the UPDATE.

My understanding (which is failing me at this point I can see) is that when this scenario arises the UPDATE should finish (commit its transaction), releasing its exclusive lock on that row in the process, allowing the SELECT to proceed and read the committed data.

Luckily, this scenario seems to be fairly reproducible, which has allowed me to capture a trace of the statements leading up to the deadlock, as well as having the ability to fire up the activity monitor and look at what exactly is going on and what is blocking what when it happens. I have a screenshot of the activity monitor available as well as the trace file if anyone is interested.

Has anyone encountered this sort of thing before? Does anyone have any suggestions on how I could possibly go about debugging/diagnosing this sort of thing? The UPDATE and SELECT are coming from two different threads running in a Java webapp. My current idea is to attempt to diagnose the problem outside of the Java application (essentially distill the problem down to what exactly is happening) and then modify the Java to avoid/remedy this situation.

 

[modi123]

are you using the WITH(NOLOCK) in your select statement?

 

[generelz]

are you using the WITH(NOLOCK) in your select statement?

No. I am not sure of the default behavior here (could someone please enlighten me?) - I know for sure the select is operating inside a connection that has SET IMPLICIT_TRANSACTIONS OFF.

Also according to the Activity monitor, the SELECT that is blocking the UPDATE holds no lock, so I don't think this is the issue?

I am beginning to believe this is actually the result of some bad code that is in the Java application. Here is why I am thinking that:

First, we query the database to get some rows. While we are iterating over these rows (the ResultSet in Java), we are potentially going out and making more queries on the database (read-only though). Now, I believe that it is some of these queries that are waiting on the UPDATE to release its exclusive lock. However the UPDATE cannot complete until the SELECT releases its rows, and the select cannot finish until it gets the result from its sub-queries (which are waiting on the update). Boom, there is your circularity and your deadlock.

 

[mikeblas]

My understanding (which is failing me at this point I can see) is that when this scenario arises the UPDATE should finish (commit its transaction), releasing its exclusive lock on that row in the process, allowing the SELECT to proceed and read the committed data.

It'll depend on what isolation mode you've got. Which isolation mode you've got depends on which version of SQL Server you're using (if I remember right).

SELECT statements will take read locks. UPDATE statements take read locks (to find the rows to update), then try to upgrade those to read-write locks. If the transaction doing the SELECT hasn't ended, then you'll get a timeout or a deadlock.

You can modify the isolation level, or have the SELECT statement do no locks.

 

[mikeblas]

Also note that the SELECT an the UNION might try to access the pages (or rows) in a different order than eachother. That may be what causes a deadlok instead of a timeout.

 

[generelz]

It'll depend on what isolation mode you've got. Which isolation mode you've got depends on which version of SQL Server you're using (if I remember right).

SELECT statements will take read locks. UPDATE statements take read locks (to find the rows to update), then try to upgrade those to read-write locks. If the transaction doing the SELECT hasn't ended, then you'll get a timeout or a deadlock.

You can modify the isolation level, or have the SELECT statement do no locks.

Mike,

Thanks for the info. I believe the isolation level is read committed. However I am not sure this SELECT is inside of a transaction where the isolation level is declared so I would suppose it would be using the default isolation level. Is that correct?

I believe the scenario I described above was exactly the scenario causing the deadlock. I changed the code to close the result set before doing any more queries and that resolved the issues we were seeing.

 

[mikeblas]

I believe the scenario I described above was exactly the scenario causing the deadlock.

Which one?

Thing is, you need to be ready to handle a deadlock even if you've worked around the case that you were seeing most often. It can happen any time more than one writer or more than a single reader and writer are on the database. (Unless you code carefully, or don't care about consistency, and so on. Those are very rare exceptions.)

 

[generelz]

Which one?

There is a situation in the code where we query the database and get the result from that query which is a couple hundred rows. While iterating over these resulting rows we are making additional calls to determine if the data returned should be added to the result list by evaluating some business logic. The problem is, during the evaluation of this business logic the EJB container is making more queries through another connection which include attempting to select data from the row that the update has an exclusive lock on. This leads to the situation where you have the update waiting on the result set to be closed (wait type ASYNCH_NETWORK_IO because the client is still reading the result rows), and the result set waiting on the business logic to query the row that the update holds a lock on. Hence, a circular wait state and deadlock.

Thing is, you need to be ready to handle a deadlock even if you've worked around the case that you were seeing most often. It can happen any time more than one writer or more than a single reader and writer are on the database. (Unless you code carefully, or don't care about consistency, and so on. Those are very rare exceptions.)

That is correct. We have realized this, and we have come to the conclusion that deadlock is mostly inevitable. The good news is, since this is a J2EE application the application server container has robust transaction management which is able to detect the cases when there is database resource deadlock (i.e. when sql server kills a process that is deadlocking) and simply re-runs the transaction. Through those means we are able to ensure consistency of the data.

While it would be nice to eliminate any chance of deadlock, in an enterprise environment there is never enough time to completely audit the code and prove every single access to not be deadlock prone.