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.
DEMO
set nocount on
go
use master
go
drop database temporalTest
go
create database temporalTest
go
use temporalTest
go
create schema Test
go
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])
)WITH(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [Test].[TemporalHistory] )
)
go
create PROCEDURE mysp
@ID bigint
AS
UPDATE
Temporal
SET
DateModified = GETUTCDATE()
WHERE
TemporalId = @Id
go
INSERT INTO Temporal
(DateModified) VALUES (GETUTCDATE())
go
select * from Temporal
go
select * from Test.TemporalHistory
go
-- ostress -E -S.\sql19 -Q"temporalTest..mysp 1" -r999999999 -n4 -q
Microsoft
