This article is contributed. See the original author and article here.
When 2 or more workers are modifying the same row, it is possible to encounter 13535. The time of the begin transaction and the modification are the defining properties. When the transaction begin time is before the latest row modification, error 13535 is encountered.
- T1 starts a transaction
T1 is context switched or client has not submitted the modification request
- T2 starts a transaction, just after T1
T2 updates the row in the table
- T1 attempts to update row and is blocked by T2 or T2 commits transaction before T1 issues the modification
- T2 commits the transaction
- T1 is assigned the lock
T1 detects the temporal update for the row was at 12:00:01 but T1 started its transaction before 12:00:01 and the error is raised.
For such an issue the application needs to determine the desired behavior. If T1 must complete before T2 then the application must sequence and complete the T1 query before the T2 query. If serialization is not required, the application can perform a retry.
set nocount on
drop database temporalTest
create database temporalTest
create schema Test
CREATE TABLE [Temporal](
[TemporalId] [bigint] IDENTITY(1,1) NOT NULL,
[DateModified] [datetime2](7) NOT NULL,
[SysStartTime] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
[SysEndTime] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
CONSTRAINT [TemporalId] PRIMARY KEY CLUSTERED ([TemporalId] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [Test].[TemporalHistory] )
create PROCEDURE mysp
DateModified = GETUTCDATE()
TemporalId = @Id
INSERT INTO Temporal
(DateModified) VALUES (GETUTCDATE())
select * from Temporal
select * from Test.TemporalHistory
— ostress -E -S.sql19 -Q”temporalTest..mysp 1″ -r999999999 -n4 -q
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.