ROWVERSION/TIMESTAMP skipping rows problem

Do you use ROWVERSION/TIMESTAMP to pull changed data?
If you do, you might experience a problem – not all changed rows are pulled, some are skipped. How can that be, if we pulled entire range of rowverion changes with no gaps?

Let me illustrate it in this video with demo inside:

Here is a demo script, and a way how to properly pull data without skipping rows:

---------------------------------
-- DEMO: Trap in pulling changes with ROWVERSION - and how to do it safely!
---------------------------------
USE tempdb;
IF OBJECT_ID('dbo.MyTable') IS NOT NULL DROP TABLE dbo.MyTable;
CREATE TABLE dbo.MyTable
(	MyTableId INT IDENTITY PRIMARY KEY CLUSTERED, 
	ChangeDetection ROWVERSION UNIQUE NONCLUSTERED, 
	SomeData CHAR(100) DEFAULT 'a'
);
GO

--------------------
-- SESSION A - Pulling rows wrong way (using @@DBTS)
--------------------
DECLARE @PullPoint ROWVERSION=0, @NewPullPoint ROWVERSION;
DECLARE @rows INT, @changes INT=0;
WHILE 1=1 BEGIN
	SET @NewPullPoint = @@DBTS;

	-- pull changes
	SELECT @rows = COUNT(*)
	FROM dbo.MyTable t
	WHERE t.ChangeDetection > @PullPoint AND t.ChangeDetection <= @NewPullPoint;

	SET @PullPoint = @NewPullPoint

	IF @rows>0 BEGIN
		SET @changes+=@rows;
		RAISERROR('Pulled %d, total changes: %d', 0,0, @rows, @changes) WITH NOWAIT;
	END
	WAITFOR DELAY '00:00:00.01'
END;
GO

--------------------
-- SESSION B - INSERT (1 000 000 rows)
--------------------
-- These are the rows we want to pull later, in a pulling session!
USE tempdb;
SET NOCOUNT ON
GO
TRUNCATE TABLE dbo.MyTable;
GO
INSERT INTO dbo.MyTable(SomeData)
SELECT TOP 1000 '1'
FROM sys.all_columns c CROSS JOIN sys.all_columns c2
GO 1000 -- 1000 times we insert 1000 rows
SELECT total_inserted=COUNT(*) FROM dbo.MyTable -- total 1 000 000 rows

--------------------------------
-- Watch session A (pulling) Messages tab.
-- We lost rows!!!!
-- We pulled less rows than inserted, around 5% of rows are lost!
--------------------------------



------------------------------
-- THE RIGHT WAY
------------------------------



--------------------
-- SESSION A - pull the rows - CORRECT (using MIN_ACTIVE_ROWVERSION)
-- No rows are skipped using this method!
--------------------
DECLARE @PullPoint ROWVERSION=0, @NewPullPoint ROWVERSION;
DECLARE @rows INT, @changes INT=0;
WHILE 1=1 BEGIN
	SET @NewPullPoint = MIN_ACTIVE_ROWVERSION(); -- insted of @@DBTS!

	-- pull changes
	SELECT @rows = COUNT(*)
	FROM dbo.MyTable t
	WHERE t.ChangeDetection >= @PullPoint AND t.ChangeDetection < @NewPullPoint; -- Spot where is equality now!

	SET @PullPoint = @NewPullPoint

	IF @rows>0 BEGIN
		SET @changes+=@rows;
		RAISERROR('Pulled %d, total changes: %d', 0,0, @rows, @changes) WITH NOWAIT;
	END
	WAITFOR DELAY '00:00:00.01'
END;
GO
-- This pulled EXACT number of rows, no data loss!



---------------------
--- EXTRAS:
--
-- Drawback: long transaction on ANY table with rowversion can pause advancing of change point, thus pause pulling.
-- Solution: Avoid long transactions on rowversion tables. If you can't, then Change Tracking is a good alternative, or other methods (Change Data Capture).
---------------------



-- Why @@DBTS does not work?
-- In a single command we can insert or update multiple rows.
-- @@DBTS is increased immediately as rows are changed/inserted, before commit.
-- Pulling session pulls all rowversion numbers with no gaps - how that can skip rows???
-- Well, we insert rows in one session and that increases @@DBTS even before rows are visible, before the are committed.
-- So pulling session does not see those uncommitted rows, but @@DBTS is increased for them and they will be skipped on next pull.
-- MIN_ACTIVE_ROWVERSION will not be increased until commit, so rows are visible for pulling.

100

101

102

103

104

105

106

107

---------------------------------

-- DEMO: Trap in pulling changes with ROWVERSION - and how to do it safely!

---------------------------------

USE tempdb;

IF OBJECT_ID('dbo.MyTable') IS NOT NULL DROP TABLE dbo.MyTable;

CREATE TABLE dbo.MyTable

( MyTableId INT IDENTITY PRIMARY KEY CLUSTERED,

ChangeDetection ROWVERSION UNIQUE NONCLUSTERED,

SomeData CHAR(100) DEFAULT 'a'

);

--------------------

-- SESSION A - Pulling rows wrong way (using @@DBTS)

--------------------

DECLARE @PullPoint ROWVERSION=0, @NewPullPoint ROWVERSION;

DECLARE @rows INT, @changes INT=0;

WHILE 1=1 BEGIN

SET @NewPullPoint = @@DBTS;

-- pull changes

SELECT @rows = COUNT(*)

FROM dbo.MyTable t

WHERE t.ChangeDetection > @PullPoint AND t.ChangeDetection <= @NewPullPoint;

SET @PullPoint = @NewPullPoint

IF @rows>0 BEGIN

SET @changes+=@rows;

RAISERROR('Pulled %d, total changes: %d', 0,0, @rows, @changes) WITH NOWAIT;

END

WAITFOR DELAY '00:00:00.01'

END;

--------------------

-- SESSION B - INSERT (1 000 000 rows)

--------------------

-- These are the rows we want to pull later, in a pulling session!

USE tempdb;

SET NOCOUNT ON

TRUNCATE TABLE dbo.MyTable;

INSERT INTO dbo.MyTable(SomeData)

SELECT TOP 1000 '1'

FROM sys.all_columns c CROSS JOIN sys.all_columns c2

GO 1000 -- 1000 times we insert 1000 rows

SELECT total_inserted=COUNT(*) FROM dbo.MyTable -- total 1 000 000 rows

--------------------------------

-- Watch session A (pulling) Messages tab.

-- We lost rows!!!!

-- We pulled less rows than inserted, around 5% of rows are lost!

--------------------------------

------------------------------

-- THE RIGHT WAY

------------------------------

--------------------

-- SESSION A - pull the rows - CORRECT (using MIN_ACTIVE_ROWVERSION)

-- No rows are skipped using this method!

--------------------

DECLARE @PullPoint ROWVERSION=0, @NewPullPoint ROWVERSION;

DECLARE @rows INT, @changes INT=0;

WHILE 1=1 BEGIN

SET @NewPullPoint = MIN_ACTIVE_ROWVERSION(); -- insted of @@DBTS!

-- pull changes

SELECT @rows = COUNT(*)

FROM dbo.MyTable t

WHERE t.ChangeDetection >= @PullPoint AND t.ChangeDetection < @NewPullPoint; -- Spot where is equality now!

SET @PullPoint = @NewPullPoint

IF @rows>0 BEGIN

SET @changes+=@rows;

RAISERROR('Pulled %d, total changes: %d', 0,0, @rows, @changes) WITH NOWAIT;

END

WAITFOR DELAY '00:00:00.01'

END;

-- This pulled EXACT number of rows, no data loss!

---------------------

--- EXTRAS:

-- Drawback: long transaction on ANY table with rowversion can pause advancing of change point, thus pause pulling.

-- Solution: Avoid long transactions on rowversion tables. If you can't, then Change Tracking is a good alternative, or other methods (Change Data Capture).

---------------------

-- Why @@DBTS does not work?

-- In a single command we can insert or update multiple rows.

-- @@DBTS is increased immediately as rows are changed/inserted, before commit.

-- Pulling session pulls all rowversion numbers with no gaps - how that can skip rows???

-- Well, we insert rows in one session and that increases @@DBTS even before rows are visible, before the are committed.

-- So pulling session does not see those uncommitted rows, but @@DBTS is increased for them and they will be skipped on next pull.

-- MIN_ACTIVE_ROWVERSION will not be increased until commit, so rows are visible for pulling.

Conclusion
Do not use @@DBTS to pull rows! Use MIN_ACTIVE_ROWVERSION() instead. I hope this helped someone.

6 Comments on “ROWVERSION/TIMESTAMP skipping rows problem”

Adriaan says:

2020-08-26 at 01:19

Thanks Verdan

We’ve been using MIN_ACTIVE_ROWVERSION() quite successfully too. Except, today we noticed an unfortunate behavour when using Read-only replicas (we’re using SQL 2017).

Quite surprisingly the value of MIN_ACTIVE_ROWVERSION() is much LARGER on read-only replicas than on the primary database – this doesn’t make sense and obviously causes problems as you can end up missing a while lot of records.

Any ideas about getting a “true” active row version from read-only replicas?

- Vedran says:
  
  2020-08-26 at 08:31
  
  Are both primary and secondary giving the same @@DBTS value? If you stop activity (changes) on primary, is MIN_ACTIVE_ROWVERSION() on both giving you the same value? Due to transaction activity MIN_ACTIVE_ROWVERSION() gives lower value than @@DBTS. Even on secondary replica if you use MIN_ACTIVE_ROWVERSION that should not skip any rows, despite being ahead of primary, if you pull always from the same (secondary) replica.
  
Gary says:

2021-10-28 at 19:58

Well presented. I didn’t even know about @@DBTC but makes sense it wouldn’t be reliable. I just save last pulled value per table and save it and use it for next pull for starting point. Seems like use of default read committed isolation level seems like you can’t leak changes correct?

- Vedran says:
  
  2021-10-28 at 20:25
  
  If you use MIN_ACTIVE_ROWVERSION, it will be ok in both default isolation (read committed) or when RCSI is enabled. If you use @@DBTS, it will be incorrect, skiping rows.
  
Bohdan says:

2021-11-09 at 16:59

Hi
Perhaps you can help me with my similar question. I have a database in which a table with a field data type is timestamp, I noticed one feature that I still cannot understand. When I make a backup of this database and restore it on another server, I observe the following behavior, after inserting a new row into the table, the value of the timestamp field is less than in the last data row present

Marcus Minervino Jr says:

2024-10-30 at 00:34

Excellent! Thanks!

ROWVERSION/TIMESTAMP skipping rows problem

6 Comments on “ROWVERSION/TIMESTAMP skipping rows problem”

Leave a Reply Cancel reply