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? – Max Vernon Jun 27 '17 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. – Max Vernon Jun 27 '17 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. – Max Vernon Jun 27 '17 at 18:38
  • @MaxVernon Thanks for your comments. Much appreciated. I'm reviewing all that! – plalx Jun 27 '17 at 20:58
  • My pleasure. Let us know if you decide one of those approaches works for you, – Max Vernon Jun 27 '17 at 20:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.