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