sql-servert-sqltransactionsrowversion

When rowversion column is calculated on SQL Server

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

Solution

  • 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.