Is it TimeStamp to update the TimeStamp value in the row after the transaction commit?

Immediately explain why I need it. For fixing the "new" data in my program, the value of TimeStamp for each table, each interval I fix new or modified rows by TimeStamp value. Everything works great, but there is a nuance.

  • Created snapshot transaction 1
  • Created snapshot transaction 2
  • Added row under transaction 1 ( TimeStamp = 0x0000000000000011)
  • Added row under transaction 2 ( TimeStamp = 0x0000000000000012)
  • Commit transaction 2

At this stage, suppose the program captured a new line ( TimeStamp = 0x0000000000000012) and entered the data into its cache, then set the maximum TimeStamp on the changes 0x0000000000000012.

  • Commit transaction 1

And here the program will not catch the new line, because 0x0000000000000011 <0x0000000000000012

But if I manage to update TimeStamp after the commit, then all the lines will be captured as new.

  • And the "program caught" - is this in another separate third snapshot of the transaction? - i-one
  • The program catches the "new lines" by selecting WHERE [TimeStamp]>MaxTimeStamp , where MaxTimeStamp is the maximum of all previous TimeStamp , TimeStamp in the program works without explicit transactions - Dmitry Chistik
  • one
    You can try to be able to use somehow the min_active_rowversion() function, not sure (depends on the application logic). The alternative is to use Change Tracking (an example, if necessary, I will compile). When using Change Tracking, the data version does not increase until commit . That is, in this case, transaction 1, despite the fact that it changed the data before transaction 2, and its commit happened later, would increase the version. - i-one
  • @ i-one yesterday studied this topic, most likely on Change Tracking and stop, issue as an answer. - Dmitry Chistik

1 answer 1

One possible solution would be to check the value of the min_active_rowversion function:

 select ... from [TableName] where [TimeStamp] > @lastTimeStamp and @@dbts < min_active_rowversion();

In this case, in the situation described in the question, after the commit in transaction 2, nothing would be returned (an additional condition would be false , because there is an incomplete rowversion-active transaction 1). Both added rows would be returned after commit in transaction 1. Ie With this modification, "new data" may be delayed.

Fat minus min_active_rowversion is its non-selectivity. This function does not allow to understand if an incomplete transaction works with the table of interest to us, or with some other one, which also has a field of the type timestamp ( rowversion ). Those. There may be situations where the "new data" will not be given on a false basis.

If the application allows for possible data lags (including for false reasons), then an additional check may be the way out.

An alternative would be to use Change Tracking , a built-in functionality for tracking changed data in tables.

When using Change Tracking , unlike rowversion , the increment of the data version occurs not at the moment of execution of the command, but during commit transactions. Information about deleted data rows is also saved, which the rowversion mechanisms do not provide.

To use this functionality, you need to enable the corresponding option on the database:

 alter database [DbName] set change_tracking = on;

And on the table itself:

 alter table [TableName] enable change_tracking;

By default, data about changes is stored for 2 days and deleted automatically. Database options within certain limits can change the retention period, and auto-deletion can be disabled.

The request for getting new and changed data will look approximately like the following ( change_tracking_min_valid_version for simplicity):

 -- это значение передаётся из приложения declare @lastVersion bigint = ... -- это значение будет использоваться как @lastVersion в следующий раз select change_tracking_current_version(); select T.* from [TableName] T join changetable(changes [TableName], @lastVersion) CT on CT.ID = T.ID;

Those. the table is connected by the primary key with the special table function changetable , which is passed the version number obtained in the previous time.