2

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 Deadlock graph

Query plan: https://www.brentozar.com/pastetheplan/?id=B1RP6Vd9c

Query plan

11
  • Given the above indexes, and the whole batch running inside a transaction, I don't see how a deadlock could happen, as the locks are always happening in the same order. A 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.com Commented Jun 27, 2022 at 22:33
  • Side note: you could combine the whole batch into one statement: insert 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 deadlock Commented Jun 27, 2022 at 22:35
  • @Charlieface I read that adding holdlock basically raises the transaction isolation level to serializable 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.
    – Twicetimes
    Commented Jun 27, 2022 at 23:04
  • holdlock might help in instances when there are no rows to read at all in the select, 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. Commented Jun 27, 2022 at 23:07
  • Ok, I'll try to get the deadlock graph. Azure sql does make them available via its event logging, but annoyingly it seems to also truncate the XML after a certain length. I'll have to work on getting the full one.
    – Twicetimes
    Commented Jun 27, 2022 at 23:08

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.