Sample Header Ad - 728x90

Temporal table return UPDATED.ValidFrom duplicates

2 votes
1 answer
53 views
Im trying to understand Temporal Tables in SQL Server better, and how I can recreate an issue we have. Sometimes the time returned from UPDATE is not the same as what is really stored in the table, and the value returned are duplicate with another thread. I want to at least be able to recreate and understand these issues better. I have a Azure SQL db, with a temporal table:
CREATE TABLE TT (
    Id UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED
  , Amount MONEY NOT NULL
  , [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TTH));
GO

CREATE NONCLUSTERED INDEX IX_TTH_Id ON TT([Id]) WITH(ONLINE = ON)
GO
When my application is called, it runs the following queries: 1. begin tran 2. select * from TT with (updlock, serializable) where Id = '...' 3. some selects, inserts and updates in other tables... 4. update TT set Amount = 123 output INSERTED.ValidFrom where id = '...' 5. commit tran Often there will be two instances running that both want to update the same row in TT (same Id). What is confusing is that Sometimes the ValidFrom returned will not be the same as what is actually written to table: 1. Thread A: begin tran 2. Thread A: select * from TT with (updlock, serializable) where Id = @x 3. Thread B: begin tran 4. Thread B: select * from TT with (updlock, serializable) where Id = @x (this will wait) 5. Thread A: some selects, inserts and updates in other tables... 6. Thread A: update TT set Amount = 123 output INSERTED.ValidFrom where id = @x 7. Thread A. commit tran 8. Thread B: some selects, inserts and updates in other tables... 9. Thread B: update TT set Amount = 123 output INSERTED.ValidFrom where id = @x 10. Thread B. commit tran What now happens is that the time returned in step 6 and step 9 are the same. But when I later inspect the history table they are very different (for example a diff of 2 seconds). How does this happen? The total execution time is less than 100ms. (I have tried to recreate it locally and against a testing db we have, but with no luck. Its easy to make cases when the ValidFrom returned are the same, but then it will also be the same in the history table)
Asked by viblo (511 rep)
Jul 4, 2025, 11:48 AM
Last activity: Jul 5, 2025, 12:30 PM