Issues

Error updating temporal tables

Danilo BragaLeave a comment

Scenario

At this point, you have probably heard about the feature called System Versioned Temporal Tables released on SQL Server 2016. So, let’s imagine that your team has decided to  implement that on a table called “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, suddenly you start seeing the following error message:

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

You can simulate the error message running the following script:

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
BEGIN TRAN
UPDATE dbo.Orders
SET [OrderValue] = [OrderValue] + 1;
COMMIT TRAN

 

But, what’s the problem here?

The system generated start and end times are keyed to the server’s system time at the moment the BEGIN TRANSACTION is executed.

The times recorded in the system datetime2 columns are based on the begin time of the transaction itself. For example, all rows inserted within a single transaction will have the same UTC time recorded in the column corresponding to the start of the SYSTEM_TIME period.

Is that a bug?

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

Workarounds

  • Build in exception handling that can retry the transaction.
  • Use triggers instead of Temporal Tables
  • Wait for Microsoft to build/release the Application-time feature (that is part of the Standard). More info: https://en.wikipedia.org/wiki/SQL:2011

Leave a comment