I have a table which tracks 'reservations', defined as follows:
CREATE TABLE [dbo].[Reservation]
(
[OrderId] nvarchar(40) CONSTRAINT [PK_Reservation_OrderId] PRIMARY KEY CLUSTERED,
[GroupId] nvarchar(40) NOT NULL,
[Currency] nvarchar(40) NOT NULL,
[Quantity] decimal(19,10) NOT NULL DEFAULT(0)
)
CREATE NONCLUSTERED INDEX [IX_Reservation_GroupId_Currency]
ON [dbo].[Reservation]
(
[GroupId],
[Currency]
)
INCLUDE (
[Quantity],
[OrderId]
)
As the application receives an order, it first checks if the sum total of reservations belonging to the relevant GroupId/Currency pair is below some fixed amount. If it is, it then inserts a row to represent that this new order has been accepted.
The query for this is as follows (with the params coming from the application):
declare @total decimal(19, 10);
select @total = sum(Quantity)
from [orders].[Reservation] with(updlock)
where [GroupId] = @groupId
and [Currency] = @currency
set @total = coalesce(@total, 0)
if (@total + @quantityToReserve) <= @someDefinedLimit
insert into [orders].[Reservation] ([GroupId], [Currency], [Quantity], [OrderId])
values (@groupId, @currency, @quantityToReserve, @orderId)
The general idea being to prevent 2 concurrent processes both doing the select
part of the query, both deciding they're allowed to insert, and both inserting. My understanding is that the updlock
should block the 2nd process from performing the select for the same GroupId/Currency combination.
This query is being performed in a transaction at an isolation level of read committed
It seems to work correctly (no unwanted extra insertions), but I do experience periodic deadlocks. From reading around, it seems that this is likely due to the nonclustered index keyed on GroupId/Currency. I don't fully understand the internals, but something along the lines of:
- Process 1: Gets U lock on nonclustered index
- Process 1: Gets X lock on clustered index
- Process 1: Releases U lock on nonclustered index
- Process 2: Gets U lock on nonclustered index
- Process 2: Tries to get X lock on clustered index, blocked by Process 1
- Process 3: Tries to get U lock on nonclustered index, blocked by Process 2
(Again, I don't claim to fully understand the internals, or if the above sequence is correct. I got this from reading an answer from a MS staff member here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/28f7bb6e-faf0-4587-bf34-63c3344a23f8/use-of-the-quotupdlockquot-hint-with-update-and-insert-statements?forum=transactsql)
Is there a fundamentally different approach I should be taking to the problem of "do insert only if the results of a previous query in the same transaction say so"?
Are intermittent deadlocks unavoidable in this kind of situation, or are they just a symptom of the way I've designed the indexes and/or the query?
Edit: updated with deadlock graph and query plan
Column names are slightly different to the initial question, but the logic is the same. PortfolioId
= GroupId
, Currency
= Asset
Deadlock XML: https://pastebin.com/YPeA7xbU
Query plan: https://www.brentozar.com/pastetheplan/?id=B1RP6Vd9c
holdlock, updlock
hint might help. Are there other queries accessing the table? Please show us a full deadlock graph XDL, and please share the query plan via pastetheplan.cominsert into [orders].[Reservation] ([GroupId], [Currency], [Quantity], [OrderId]) select @groupId, @currency, @quantityToReserve, @orderId where (select isnull(sum(Quantity), 0) from [orders].[Reservation] with(updlock) where [GroupId] = @groupId and [Currency] = @currency) + @quantityToReserve <= @someDefinedLimit;
although that is unlikely to solve the deadlockholdlock
basically raises the transaction isolation level toserializable
and can still incur deadlocks. But regarding why they're happening in the first place, I also don't understand, given the consistent ordering of the statements. This is why I think it's something to do with the order in which it takes various locks on the nonclustered index, I'm just not sure what exactly.holdlock
might help in instances when there are no rows to read at all in theselect
, it can indeed end up locking too much, but given the indexes it's unlikely to cause more deadlocks. Like I said, we need the deadlock graph, and the query plan, it's impossible to diagnose otherwise.