4

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://docs.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
2

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.

  • 2
    Application time (as opposed to system time) would allow you to define whatever time you want (write time, commit time, or some marker), and even allow you to go back and change existing data. It's part of the standard but we don't know yet when Microsoft will implement it.] – Aaron Bertrand Jul 5 '18 at 20:02
  • Awesome!!! Thanks for that guys. That's what I was guessing. I think the way to go for now will be adding an exception handling to fix it. The performance of the queries is not actually the problem.. The 15 seconds was just to make it easier to simulate the error. @AaronBertrand, thanks for sharing about the Application time. Hopefully Microsoft will soon implement this feature. It will be pretty handy! – Danilo Braga Jul 6 '18 at 15:51
  • If the transaction is long enough to trigger this error, handling error and retry doesn't solve the problem at all. Isn't it just keeps triggering this error? – liang Aug 27 at 10:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.