3

When column of rowversion type is calculated on SQL Server? Is it on transaction commit or before (along with row modification operation)? I'm asking, because it is very important to me whether committed transaction can actually result with lower version than already committed version.

It seems to be very basic issue, but MSDN documentation does not provide such information. I couldn't find it anywhere else either.

Here's visualization of my question:

Rowversion issue diagram

Damian Kamiński
  • 78
  • 1
  • 9
  • 1
    The function `min_active_rowversion` can be used for not looking past the uncommitted versions like described in this question: https://stackoverflow.com/questions/28444599/implementing-incremental-client-updates-with-rowversions-in-postgres (and yes, it is for Sql Server) – asgerhallas Jul 12 '18 at 18:16

1 Answers1

5

Easily seen with a couple of scripts.

Script 1:

create table T1 (
    ID int not null,
    rv rowversion not null
)
go
begin transaction

insert into T1 (ID) values (1)

WAITFOR DELAY '00:03:00'

commit

Script 2:

begin transaction
insert into T1 (ID) values (2)
commit
select * from T1 with (nolock)

Open both scripts and connect to the same database. Run script 1, and whilst it's still running, switch to script 2 and run it.

You'll get this result (or similar):

ID          rv
----------- ------------------
1           0x00000000000007D1
2           0x00000000000007D2

As you can see, the row with ID 1, which has not yet committed, has been assigned the low rowversion value.

On reflection, of course, it has to be this way. Transactions can span multiple statements (as the first script shows) and you're free to re-query a table within the same transaction, including querying for the specific rowversion value that your own transaction just set.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Looks logical, yes. Very annoying for synchronizing, though, because it means you can't simply select rowversion > newestPreviousRowversion to know what changed since last time you checked – yannick1976 Jan 18 '21 at 12:28