5

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, deadlock graph for Exmaple2 Example 3: deadlock3.xml, deadlock graph for Example3

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.

  • 2
    @final, are you aware that some of the consequences of NOLOCK include missing or duplicate rows in the query results, not just uncommitted data? – Dan Guzman Jan 8 at 12:49
  • @DanGuzman Thanks for the input, I was not. In that case, NOLOCK is no option for the large select. I could still use NOLOCK on the select that fills the table variable, since this is just used for hinting that a future appointment exists in a mouse over. Missing, duplicate and phantom rows wouldn't be very important there. – final Jan 8 at 12:57
7

When looking at the deadlock graph:

enter image description here

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

| improve this answer | |
  • Thanks a lot Randi, you're the man. Your explanation makes perfect sense. I double checked the execution plan of the query, and the self join is indeed done last. This means that the timing doesn't have to be quite so precise for the update statement, which further supports your theory. I'll rewrite the query to avoid the self-join by putting it into a temp table before the large select query – final Jan 8 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.