I'm designing an internal event store library based off SQL Server. One challenge I had to solve is to allow concurrent writes while still being able to generate a reliable Log Sequence Number (LSN) that could be used to consume the stream of events in order as they appear.
The way I'm solving this is by having a loose_position bigint IDENTITY(1, 1) column and a strict_position bigint NULL column which will get updated by a single writer and used as a consuming checkpoint. The sequencer does something like the following in a READ COMMITTED transaction:
DECLARE @maxPosition bigint;
SELECT @maxPosition = MAX(strict_position)
FROM RecordedEvent WITH(READPAST)
WHERE strict_position IS NOT NULL;
WITH seq AS (
SELECT id, @maxPosition + ROW_NUMBER() OVER(ORDER BY loose_position) AS strict_position
FROM RecordedEvent WITH (READPAST)
WHERE strict_position IS NULL
)
UPDATE r WITH (READPAST)
SET strict_position = @maxPosition + seq.strict_position
FROM RecordedEvent r
INNER JOIN seq ON seq.id = r.id;
I wanted to prevent the sequencer from being blocked by newly inserted records and vice-versa, so I've used READPAST which seems to work, but I'm not sure that's the best approach.
Please note that I also have a UNIQUE CLUSTERED INDEX ON loose_position and a UNIQUE NONCLUSTERED INDEX ON strict_position WHERE strict_position IS NOT NULL, but I'm not entirely sure how these affects locking.
Could someone confirm that my current solution is appropriate or if there are better ways to achieve what I want?
