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