Error updating temporal tables

Question

I have implemented the System Versioned Temporal Tables on the table "Orders". The application is using different access patterns to modify the data in this table. There are direct statements coming from the application or the application is running long batches in explicit transactions where it makes several changes to multiple tables. The update on the "Orders" table is not the first statement in those long batches! So, sometimes we are facing the following error.

Data modification failed on system-versioned table "Orders" because transaction time was earlier than period start time for affected records.

Apparently this is a standard behavior of System Versioned Temporal Tables. https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-2017#how-does-temporal-work

Is this something that will always need to be handled in an exception routine? Or is Microsoft considering to change that behavior?

--Script to simulate the error message:

CREATE TABLE dbo.Orders 
    (    
      [OrderId] INT NOT NULL PRIMARY KEY CLUSTERED  
      , [OrderValue] DECIMAL(19,4)
      , [ValidFrom] DATETIME2 (2) GENERATED ALWAYS AS ROW START  
      , [ValidTo] DATETIME2 (2) GENERATED ALWAYS AS ROW END  
      , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)  
     )    
     WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory)); 
     GO

     INSERT dbo.Orders ([OrderId], [OrderValue])
     VALUES (1, 9.99), (2, 9.99);
     GO

     SELECT * FROM dbo.Orders;
     GO

       --Run first query
   BEGIN TRAN
      WAITFOR DELAY '00:00:15';
      UPDATE dbo.Orders 
      SET [OrderValue] = [OrderValue] + 1;
    COMMIT TRAN


       --Run Query 2 in another session sql server
    BEGIN TRAN
       UPDATE dbo.Orders 
       SET [OrderValue] = [OrderValue] + 1;
    COMMIT TRAN

Answer 1

Is this something that will always need to be handled in an exception routine?

The system generated start and end times are keyed to the server's system time at the moment the BEGIN TRANSACTION is executed. If you have long-running transactions, you should:

1. Build in exception handling that can retry the transaction.

2. Rework the offending code so it executes more quickly. This may sound like a lot of work, but it will be necessary to do some level of rework to handle temporal-based exceptions correctly. If you're going to do rework, you may as well determine why the transaction is taking so long, and mitigate that behavior.

Or is Microsoft considering to change that behavior?

There is no way to know what Microsoft will do in future versions of their products unless they have made a public announcement that is not covered by a nondisclosure agreement. They might modify the temporal table functionality at some point in the future such that the start and end dates are reflective of the current system time at the point of insert/update, however that is not currently how it works.

Answer 2

Well -- it's still a problem in SQL 2019. But I did find one workaround: If you don't need explicit transactions, avoid BEGIN/COMMIT TRAN, and wrap the update logic using TRY/CATCH.

--Run first query

BEGIN TRY
      WAITFOR DELAY '00:00:5';
          UPDATE dbo.Orders 
          SET [OrderValue] = [OrderValue] + 1;
END TRY
BEGIN CATCH
      WAITFOR DELAY '00:00:5';
          UPDATE dbo.Orders 
          SET [OrderValue] = [OrderValue] + 1;
END CATCH


--Run second query in separate session

       UPDATE dbo.Orders 
       SET [OrderValue] =  [OrderValue] + 1;



--See all history of changes

    SELECT * FROM Orders
    FOR SYSTEM_TIME ALL
    ORDER BY ValidFrom