I am trying to implement an event source in SQL Server, and have been experiencing deadlocking issues.
In my design, events are grouped by DatasetId and each event is written with a SequenceId, with the requirement that, for a given DatasetId, SequenceIds are serial, beginning at 1 and increasing one at a time with each event, never missing a value and never repeating one either.
My Events table looks something like:
CREATE TABLE [Events]
(
[Id] [BIGINT] IDENTITY(1,1) NOT NULL,
[DatasetId] [BIGINT] NOT NULL,
[SequenceId] [BIGINT] NOT NULL,
[Value] [NVARCHAR](MAX) NOT NULL
)
I also have a non-unique, non-clustered index on the DatasetId column.
In order to insert into this table with the above restrictions on SequenceId, I have been inserting rows under a transaction using Serializable isolation level, and calculating the required SequenceId manually within this transaction as the max of all existing SequenceIds plus one:
DECLARE @DatasetId BIGINT = 1, @Value NVARCHAR(MAX) = N'I am an event.';
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
BEGIN TRY
DECLARE @SequenceId BIGINT;
SELECT @SequenceId = ISNULL(MAX([SequenceId]), 0) + 1
FROM [Events]
WHERE [DatasetId] = @DatasetId;
INSERT INTO [Events] ([DatasetId], [SequenceId], [Value])
VALUES (@DatasetId, @SequenceId, @Value);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH
This has worked fine in terms of the guarantees that I require on the SequenceId column. However, I have been experiencing deadlocking when trying to insert multiple rows in parallel, even when such rows are for different DatasetIds.
The behaviour seems to be that the query to generate a SequenceId in a first connection blocks the same query to generate a SequenceId in the second connection, and this second attempt blocks the first connections ability to insert the row, meaning neither transaction is able to complete, hence the deadlock.
Is there a means of avoiding this whilst still gaining the benefits of a Serializable transaction isolation level?
Another technique I have been considering is reducing the isolation level and instead using sp_getapplock to manually acquire a lock on the table for a given DatasetId, meaning I can then ensure I can generate a consistent SequenceId. Once the transaction has been committed/rolled back, the lock would automatically be released. Is this approach reasonable, or is manually managing locks like this considered an anti-pattern?
