I'm storing a queue of events in Sql Server with concurrent writers and I'm assigning a sequence number using an IDENTITY column.
I'm willing to accept the gaps that might occur, but I would like readers not to read past the minimum identity active in any uncomitted transaction... like what I can do with
min_active_rowversion for ROWVERSION columns.
Is this possible?
EDIT: The actual problem
What I'm trying to do is finding a solution for the problem described here: https://dba.stackexchange.com/a/135116/176861
This is solved for ROWVERSION with
min_active_rowversion like this:
TX A begins and inserts a row with rowversion = 1 SELECT * FROM events WHERE rowversion < min_active_rowversion() => no result TX B begins and inserts a row with rowversion = 2 SELECT * FROM events WHERE rowversion < min_active_rowversion() => no result TX B completes SELECT * FROM events WHERE rowversion < min_active_rowversion() => no result TX A completes SELECT * FROM events WHERE rowversion < min_active_rowversion() => both rows as result
Like this, I won't "read past" the minimum active rowversion and won't miss updates. But rowversion is not immutable like a proper identity is, and I would like to do the same but using an IDENTITY column.
I would like to make a SELECT statement that does not read a row with an id greater than the lowest identity in any active transaction.
EDIT 2: The schema
A simplified version of my table looks like this:
CREATE TABLE events ( sequence int NOT NULL IDENTITY(0,1), name nvarchar (850), data varbinary(max) );
EDIT 3: Proposed solution
I got an idea for a - probably obvious - solution: Have both IDENTITY and ROWVERSION.
If a row have both, I can use an immutable Identity value as the starting point of where I want to read from (which row did I read and successfully process last) and
min_active_rowversion for my cutoff, so I don't read to far ahead.
I'll try it out an report back here.