Blocked persistence nuance with GET*DATE()

Noticed an interesting (and desired) behavior when dealing with persisting timestamps that is blocked by a competing transaction, which might be useful for others as well. For unitemporal and bitemporal tables, we frequently update the knowledge_date as either GETDATE() or GETUTCDATE(), but it is not guaranteeing that the wall-clock time of when record got persisted to the db is the same as the value noted in the knowledge_date column.

To illustrate this, let's say we have a table as

CREATE TABLE special_table (
special_key INT PRIMARY KEY,
special_value INT,
knowledge_date DATETIME)

An we insert a few values into it:

INSERT INTO special_table VALUES(1, 100, GETUTCDATE())
INSERT INTO special_table VALUES(2, 100, GETUTCDATE())
INSERT INTO special_table VALUES(100, 10000, GETUTCDATE())

Now, let's say, we've got two competing transactions, one a read and another a write with the read preceding the write and the read transaction taking a lot more time to finish (simulated with a WAITFOR DELAY).

Read Transaction (at isolation level repeatable read):

BEGIN TRANSACTION
SELECT GETUTCDATE() --ts1
SELECT * FROM special_table WHERE special_key = 2
WAITFOR DELAY '00:00:10'
SELECT * FROM special_table WHERE special_key = 2
SELECT GETUTCDATE() --ts2
COMMIT

Write Transaction (at isolation level read committed):

BEGIN TRANSACTION
SELECT GETUTCDATE() --ts3
UPDATE special_table
   SET special_value = special_value + 1, knowledge_date=GETUTCDATE()
 WHERE special_key = 2
SELECT GETUTCDATE() --ts4
SELECT * FROM special_table WHERE special_key = 2
COMMIT

Execute these two batches in two windows of SSMS with the read preceding the write. Since the read started before the write, ts1+10 ~= ts2 because the read transaction will experience no blocking. The write operation was kicked off a little after read was kicked off (say with interval d). Hence ts1 + d = ts3.

Question: will the knowledge_date updated be closer to ts3 or ts4?

One might think that the knowledge_date value is closer to ts4 when the write transaction actually gets unblocked, however, this is not the case. For Sql Server itself to figure out whether or not the transaction needs to get blocked (because of the default page level locking scheme followed), the query needs to be evaluated and hence the value to be assigned to knowledge_date has to be evaluated at a time closer to ts3 itself. Hence the knowledge_date timestamp will be persisted to the DB at a wall-clock time closer to ts4even if the DB claims the timestamp as closer to ts3.

This can be verified with the output from the read and writes where there is a marked delay between the knowledge_date updated and the ts4. This becomes even more interesting when you have multiple updates in the same write transaction - some of which can proceed - till the point that it gets blocked because of the read and one can notice varying knowledge_date across records even though they were all kicked off in the same transaction.

BEGIN TRANSACTION
SELECT GETUTCDATE() --ts1
UPDATE special_table
   SET special_value = special_value + 1, knowledge_date=GETUTCDATE() --ts
 WHERE special_key = 100
SELECT GETUTCDATE() --ts2
UPDATE special_table
   SET special_value = special_value + 1, knowledge_date=GETUTCDATE() --tss
 WHERE special_key = 2
SELECT GETUTCDATE() --ts3
SELECT * FROM special_table WHERE special_key = 2
COMMIT

Here, knowledge_date for key 100 would be closer to ts1 due to it not getting blocked by the read and the knowledge_date for key 2 would be closer to ts3 and away from ts2 since it was blocked by the read.

BTW, this should not haunt the trigger based td_bl temporal tables as the trigger gets only fired after the base table is updated and effectively captures the timestamp of when the base table was changed (but may not be the exact time when the temporal record got persisted to the db due to blocking concerns).

Hope this helps!

References:

• http://en.wikipedia.org/wiki/Isolation_(database_systems)#Repeatable_reads
• http://en.wikipedia.org/wiki/Isolation_(database_systems)#Read_committed