Temporal: Encountering Error 13535 Data modification failed on system-versioned table…

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.

 

 

1. T1 starts a transaction
   T1 is context switched or client has not submitted the modification request
   
   


2. T2 starts a transaction, just after T1
   T2 updates the row in the table
   
   


3. T1 attempts to update row and is blocked by T2 or T2 commits transaction before T1 issues the modification
   
   


4. T2 commits the transaction
   
   


5. 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

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.