Sql-server – Why do temporal tables log the begin time of the transaction

sql-server-2016temporal-tablestransaction

When updating a row in a temporal table the old values for the row are stored in the history table with the transaction begin time as the SysEndTime. The new values in the current table will have the transaction begin time as the SysStartTime.

SysStartTime and SysEndTime are datetime2 columns used by temporal tables to record when a row was the current version. Transaction begin time is the time the transaction containing the updates started.

BOL says:

The times recorded in the system datetime2 columns are based on the
begin time of the transaction itself. For example, all rows inserted
within a single transaction will have the same UTC time recorded in
the column corresponding to the start of the SYSTEM_TIME period.

Example: I start updating all the rows in my Orders table at 20160707 11:00:00 and the transaction takes 5 minutes to run. This creates a row in the history table for each row with SysEndTime as 20160707 11:00:00. All the rows in the current table will have a SysStartTime of 20160707 11:00:00.

If someone were to execute a query at 20160707 11:01:00 (while the update is running) they would see the old values (assuming default read committed isolation level).

But if someone was to then use the AS OF syntax to query the temporal table as it was at 20160707 11:01:00 they would see the new values because the their SysStartTime would be 20160707 11:00:00.

To me this means it doesn't show those rows as they were at that time. If it used the transaction end time the problem wouldn't exist.

Questions: Is this by design? Am I missing something?

The only reason I can think it's using the transaction begin time is that it is the only 'known' when the transaction starts. It doesn't know when the transaction will end when it starts and it would take time to apply the end time at the end which would invalidate the end time it was applying. Does this make sense?

This should allow you to recreate the issue.

Best Answer

The idea is to track logical time vs physical time. Logical simply refers to what a user/app expects the time of an insert/update/delete to be. The fact that the DML operation may take a while for whatever reason, isn't meaningful or even easily determined and understood by a user. If you've ever had to explain lock vs latch contention to an accountant (I have), it's a comparable situation.

For instance, when Bob "tells" the app that all employees in Bob's department will start making $42/min at 20160707 11:00:00, Bob (and his employees) expects everyone's pay is now calculate at $42/min from that time. Bob doesn't care that for this to be effected, the app has to make 2 reads and 6 writes across the database per employee and their data + log files sit on a bunch of RAID-5 SATA II drives so it takes about 7 minutes to finish the task for all 256 of Bob's employees. Bob, his accountant and the payroll manager care that all his employees are paid $42/min starting 20160707 11:00:00. Else, the employees that were updated at 20160707 11:00:01 will be slightly annoyed while those whose records were updated at 20160707 11:00:07 will be gathering outside the payroll department.

There are valid use cases to track physical time such as debugging and forensics but to the end user, it's generally meaningless. The Tlog keeps both ordering and timing information for each of the write operations (among other things) so it's there if you know how to look.

Related Question