SQL Server Deadlock between two selects and one update on one table

Question

Our application encounters a deadlock every now and again (about once a week).

The main culprit seems to be a query with two selects. One of them is to fill a temp table for performance reasons, the other is a relatively complex select with many joins to return the list of all Appointments with many details. The only potentially special thing I see about the second select is that it includes a self-join. The two-select-query is always part of the deadlock event report by SQL Server.

The other query is a simple DML query (insert or update) on the same table, though this is not always the same DML query. Both queries run with standard READ COMMITTED isolation and not within a explicit transaction.

The two queries are roughly as follows (I've shortened them for clarification)

DECLARE @futureAppointments TABLE(clientId int, StartDate date)
INSERT INTO @futureAppointments SELECT clientId, StartDate FROM Appointments where StartDate >= @startDate

SELECT *, (SELECT COUNT(*) FROM @futureAppointments fa WHERE fa.clientId = a.clientId AND fa.StartDate > a.StartDate)
FROM Appointments a
join b on a.fk_b = b.id
join c on a.fk_c = c.id
join Appointments d on c.somefield = d.anotherfield
WHERE a.StartDate >= @startDate AND a.StartDate <= @endDate

UPDATE Appointments SET someField = @value WHERE id = @id

Example 2: deadlock2.xml, Example 3: deadlock3.xml,

How would I try to prevent deadlocks from happening in this scenario? Also, does anyone know why the first statement with two selects would acquire a U lock on the selected table's PK as in Example 3? I don't think that it matters, but it seems strange.

Answer 1

When looking at the deadlock graph:

Update query - SPID 75

The update query locking (spid 75) is pretty straightforward, an X (exclusive) lock is requested on the key/row value, while that row is currently locked by the select query.

The update query also holds an IX (Intent exclusive) lock on a page, as is expected when taking an exclusive lock on the row that belongs to the page. The IX lock is compatible with the IS (Intent shared) lock issued by the select query (as a result of the S lock on the row).

See: lock compatibility matrix

---

Select query - SPID 103

The unusual part of your deadlock is that the select query (spid 103) wants a S (shared) lock on both the row & the page that has the row data (and possibly other row data). Especially since lock escalation from row to page is not possible (row to table & page to table). A previous transaction holding the lock is also ruled out.

The explanation seems to be in the double access to the dbo.Appointments table. Locks are taken twice and one of these table accesses will want the page lock, while the other one already aquired the rowlock.

The update fires in between these shared locks being aquired.

An example of locks requested / acquired in order

1. A S Lock on the key/row is taken by the first read access on the dbo.Appointments table as part of the select query
2. An IX Lock is taken on the page that has the row data by the update query
3. X lock is requested on the row by the update query
4. The S lock on the page that also has the row data is requested by the second access to dbo.Appointments as part of the select query

All this means that this part of the select query is your deadlock problem:

SELECT *, (SELECT COUNT(*) FROM @futureAppointments fa WHERE fa.clientId = a.clientId AND fa.StartDate > a.StartDate)
FROM Appointments a
join b on a.fk_b = b.id
join c on a.fk_c = c.id
join Appointments d on c.somefield = d.anotherfield
WHERE a.StartDate >= @startDate AND a.StartDate <= @endDate;

How would I try to prevent deadlocks from happening in this scenario?

Reduce

To reduce the probability of the deadlock happening you could look into optimizing the select query by rewriting or adding indexes.

Remove

To remove the possibility of deadlocks between these two queries entirely you could split the self join in two parts by using a temp table, or take a shared page or table lock on the Appointments table by using WTIH(PAGLOCK) / WITH(TABLOCK). Remember that this impacts concurrency.

Also, does anyone know why the first statement with two selects would acquire a U lock on the selected table's PK as in Example 3? I don't think that it matters, but it seems strange.

The owner (select query) holds the shared lock on the key.

An U (update) lock (coming from the update query) is compatible with a shared lock see: Lock Compatibility Matrix.

The update query tries to convert the U lock to an exclusive lock:

<waiter id="process24b7826bc28" mode="X" requestType="convert" />.

The info is correct when opening the XML with SentryOne Plan Explorer (free tool).