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.

  • 1
    I'm confused, have you tested this? Have you seen another transaction reading past the identity as described? – George.Palacios Apr 10 at 7:46
  • 1
    Schema definition and code examples might help here. A sequence and an identity column in SQL Server are different things, so it's important to clarify what you're using. – HandyD Apr 10 at 7:56
  • @George.Palacios please see edits. Do they clarify what I mean? – asgerhallas Apr 10 at 8:15
  • Am i being dumb, or can you just solve this by using the correct isolation level? – Philᵀᴹ Apr 10 at 8:40
  • @Philᵀᴹ I think the issue here is that the current seed of the identity column is updated outside the context of a transaction, so isolation level won't help. – George.Palacios Apr 10 at 9:01

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.