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.


 


BobDorr_0-1655140843700.png


 



  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.