Deadlock caused by SELECT JOIN statement with SQL Server

Question

When executing a SELECT statement with a JOIN of two tables SQL Server seems to lock both tables of the statement individually. For example by a query like this:

SELECT ...
FROM
    table1
    LEFT JOIN table2
        ON table1.id = table2.id
    WHERE ...

I found out that the order of the locks depends on the WHERE condition. The query optimizer tries to produce an execution plan that only reads as much rows as necessary. So if the WHERE condition contains a column of table1 it will first get the result rows from table1 and then get the corresponding rows from table2. If the column is from table2 it will do it the other way round. More complex conditions or the use of indexes may have an effect on the decision of the query optimizer too.

When the data read by a statement should be updated later in the transaction with UPDATE statements it is not guaranteed that the order of the UPDATE statements matches the order that was used to read the data from the 2 tables. If another transaction tries to read data while a transaction is updating the tables it can cause a deadlock when the SELECT statement is executed in between the UPDATE statements because neither the SELECT can get the lock on the first table nor can the UPDATE get the lock on the second table. For example:

T1: SELECT ... FROM ... JOIN ...
T1: UPDATE table1 SET ... WHERE id = ?
T2: SELECT ... FROM ... JOIN ... (locks table2, then blocked by lock on table1)
T1: UPDATE table2 SET ... WHERE id = ?

Both tables represent a type hierarchy and are always loaded together. So it makes sense to load an object using a SELECT with a JOIN. Loading both tables individually would not give the query optimizer a chance to find the best execution plan. But since UPDATE statements can only update one table at a time this can causes deadlocks when an object is loaded while the object is updated by another transaction. Updates of objects often cause UPDATEs on both tables when properties of the object that belong to different types of the type hierarchy are updated.

I have tried to add locking hints to the SELECT statement, but that does not change the problem. It just causes the deadlock in the SELECT statements when both statements try to lock the tables and one SELECT statement gets the lock in the opposite order of the other statement. Maybe it would be possible to load data for updates always with the same statement forcing the locks to be in the same order. That would prevent a deadlock between two transactions that want to update the data, but would not prevent a transaction that only reads data to deadlock which needs to have different WHERE conditions.

The only work-a-round so this so far seems to be that reads may not get locks at all. With SQL Server 2005 this can be done using SNAPSHOT ISOLATION. The only way for SQL Server 2000 would be to use the READ UNCOMMITED isolation level.

I would like to know if there is another possibilty to prevent the SQL Server from causing these deadlocks?

Answer 1

This will never happen under snapshot isolation, when readers do not block writers. Other than that, there is no way to prevent such things. I wrote a lot of repro scripts here: Reproducing deadlocks involving only one table

Edit:

I don't have access to SQL 2000, but I would try to serialize access to the object by using sp_getapplock, so that reading and modifications never run concurrently. If you cannot use sp_getapplock, roll out your own mutex.