3

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.

asgerhallas
  • 225
  • 1
  • 7

0 Answers0