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)
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
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?

  • 2
    Are you trying to create a message queue? Perhaps this helps?
    – Hannah Vernon
    Commented Jun 27, 2017 at 18:32
  • 2
    If you are just looking for a way to generate a sequence of numbers for each new entry in your stack, perhaps this will help.
    – Hannah Vernon
    Commented Jun 27, 2017 at 18:34
  • 2
    This answer may be helpful as well - it talks about how to reliably generate a sequence of numbers while maintaining great concurrency.
    – Hannah Vernon
    Commented Jun 27, 2017 at 18:38
  • @MaxVernon Thanks for your comments. Much appreciated. I'm reviewing all that!
    – plalx
    Commented Jun 27, 2017 at 20:58
  • My pleasure. Let us know if you decide one of those approaches works for you,
    – Hannah Vernon
    Commented Jun 27, 2017 at 20:59


Your Answer

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

Browse other questions tagged or ask your own question.