CODEWITHSUNDEEP
.netsql-serversql-server-2012
np-hard
np-hard

Reputation: 5815

SQL Server rowversion and order

I am using the rowversion property from several tables for change tracking. A periodic process queries many tables with a union all on rowversion converted to BIGINT datatype (as computed column). It then records the highest number read, and next time queries from that higher number.

I am observing that in high concurrency situations, where several threads are writing and one thread is querying for change, sometimes it ends up reading a higher rowversion value and then a lower number gets written.

The value with lower version never gets picked up, causing synchronization issues.

rowversion documentation on msdn states that

The rowversion data type is just an incrementing number

Wondering if I can rely on it being incremental at the time of completing transaction or starting transactions ?

Using default transaction isolation of SQL Server 2012

Upvotes: 3

Views: 1484

Answers (1)

Shannon Severance
Shannon Severance

Reputation: 18410

The time of the increment is at the time of the change of the row, irrespective of when the transaction commits:

Example. (Note @@dbts is the current database rowversion value.)

select @@version
create table t (c int, rv rowversion)
go
begin transaction
print @@dbts
insert into t (c) values (1)
print @@dbts
insert into t (c) values (2)
print @@dbts
insert into t (c) values (3)
print @@dbts
commit
select * from t

Results, note that @@dbts increments with each insert and that the insert gets the higher incremented value. All within one transaction.

Microsoft SQL Server 2014 - 12.0.2254.0 (X64)
Jul 25 2014 18:52:51
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64>
(Build 7601: Service Pack 1) (Hypervisor) 

0x00000000000007D0
0x00000000000007D1
0x00000000000007D2
0x00000000000007D3

C   RV
1   0x00000000000007D1
2   0x00000000000007D2
3   0x00000000000007D3

Upvotes: 3

Related Questions