none
Temporal Table :: Concurrent Update Issue

    Question

  • Hi,

    I am facing issues with temporal tables when there is concurrency. I will explain the issue with a simple example. Lets say I have table dbo.Employee and dbo.EmployeeHistory is the system versioned (temporal implementation) table for it. I have two individual Transactions T1 and T2 where T1's execution time is 1 minute and T2 executes in 10 seconds. Consider T1 started and there is an UPDATE statement at the end of the batch that updates the C1 column of the dbo.Employee table. In parallel T2 started right after 5 seconds of T1 and updates C2 column of dbo.Employee table, commits and exits. When transaction T1 is about to complete, and executes the UPDATE statement to update C1 column, I'm getting an error "Data modification failed on system-versioned table 'TestDB.dbo.Employee' because transaction time was earlier than period start time for affected records.". This is because the transaction start time is recorded in the version table rather than the time of execution of the UPDATE statement alone. Thus the transaction T1 fails. I'm interested in using the new feature or SQL 2016 and I'm looking for a help to resolve this issue.

    Thanks,

    Pradeep


    Thursday, March 2, 2017 10:32 AM

All replies

  • http://stackoverflow.com/questions/40672573/ms-sql-temporal-table-update-failure

    Read this article, it may help you.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, March 2, 2017 10:42 AM
    Answerer
  • Hi Uri,

    I did had a look upon the above given post when I was looking for a solution for the issue. I understand the temporal architecture and what I am looking for, from this forum, is a work around if available. 

    Pradeep

    Thursday, March 2, 2017 11:09 AM
  • Is that possible to wrap the transactions into BEGIN TRAN.... to 'lock' the table while data modification is performed??? 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, March 2, 2017 11:37 AM
    Answerer
  • Hi Uri,

    I had a thought like what you have said. The table which we have in the business cannot be locked.

    Thanks,

    Pradeep

    Thursday, March 2, 2017 11:57 AM
  • I mean to lock only modified data , not a whole table

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, March 2, 2017 12:01 PM
    Answerer
  • Hallo Pradeep,

    do you mean that T1 and T2 are modifying the same record?

    If T1 is updating dbo.employess at the end of the transaction and T2 is doing a modification in between the run of T1 than it should work like a charm.

    • How did you create your history table?
    • Did you had an existing table or has it been created within the DDL for the dbo.Employee-Table?
    ALTER TABLE dbo.Employees SET
    (
    	SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.Employees)
    );
    GO

    If the table history.Employees don't exist it will be created with a PK on [EndTime], [StartTime] only.

    Can you post the DDL of the history table with the indexes?


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Thursday, March 2, 2017 12:09 PM
  • Hi,

    I can explain the business. The transaction T2 that updates column C2 of dbo.Employee table (defined in the example) is a web user session. While transaction T1 is an update feed from the customer that updates the column C1. The transaction will do several pre-processing before the problematic UPDATE happens. The pre-processing and update happens in a single gateway call and the entire gateway call is a single transaction. The update feed can update the entire table if the customer needs so. The table row size is more than 3 to 4 millions.

    Thanks,

    Pradeep

    Thursday, March 2, 2017 12:12 PM
  • What indexes does the table have?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, March 2, 2017 12:15 PM
    Answerer
  • What you are describing is basically the same as this post:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7191de2a-6964-453d-9401-d544341ef37c/error-updating-temporal-table?forum=sqldatabaseengine

    The simplest answer is to stage your updates to a temp table during the process.  Then at the end do a simple UPDATE from the temp table to the target table. 

    Thursday, March 2, 2017 12:40 PM
    Moderator
  • ------------------------------------------------------------------------------------------
    -- USE THE SCRIPT TO REPLICATE THE SECENARIO dbo.Employee AND IT IS SYSTEM VERSIONED WITH 
    -- CUSTOM NAME dbo.EmployeeHistory.
    ------------------------------------------------------------------------------------------
    -- STEPS TO REPLICATE THE ISSUE
    -- STEP 01 :: CREATE THE TABLE dbo.Employee and dbo.EmployeeHistory
    -- STEP 02 :: INSERT A ROW INTO THE TABLE
    -- STEP 03 :: OPEN TWO SSMS SESSIONS THAT CONNECTS TO THE DATABASE WHERE THE NEW TABLE IS CREATED.
    -- STEP 04 :: COPY and PASTE THE TRANSACTION T1 AND T2 INTO SESSION 1 AND 2 RESPECTIVELY
    -- STEP 05 :: EXECUTE SESSION 1 AND THEN SESSION 2 ONE AFTER ANOTHER. WILL GET THE ISSUE AT SESSION 1
    ------------------------------------------------------------------------------------------

    -- STEP 01
    USE [TESTDB]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[Employee]
    (
    [EmployeeID] [INT] IDENTITY(1, 1) NOT NULL,
    [Name] [NVARCHAR](100) NOT NULL,
    [Position] [VARCHAR](100) NOT NULL,
    [Department] [VARCHAR](100) NOT NULL,
    [Address] [NVARCHAR](1024) NOT NULL,
    [AnnualSalary] [DECIMAL](10, 2) NOT NULL,
    [ValidFrom] [DATETIME2](2) GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] [DATETIME2](2) GENERATED ALWAYS AS ROW END NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
    [EmployeeID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
    ) ON [PRIMARY]
    WITH
    (
    SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[EmployeeHistory] )
    )
    GO
    ------------------------------------------------------------------------------------------

    -- STEP 02
    INSERT INTO dbo.Employee
    (
    [Name],
    [Position],
    [Department],
    [Address]
    )
    VALUES
    (
    N'John Smith',
    'COO',
    'Sales',
    N'Department of State, 2430 Nouakchott Place, Washington, DC 20521-2430'
    )
    ------------------------------------------------------------------------------------------

    -- STEP 04 -- COPY TO SSMS SESSION 1
    BEGIN TRANSACTION T1
    -- Here I'm using wait to simulate as certain pre-processing in our business scenario
    WAITFOR DELAY '00:00:30'

    UPDATE dbo.Employee
    SET [Position] = 'CTO'
    WHERE EmployeeID = 1
    COMMIT TRAN T1

    -- STEP 04 -- COPY TO SSMS SESSION 2
    BEGIN TRANSACTION T2
    UPDATE dbo.Employee
    SET [Department] = 'Development'
    WHERE EmployeeID = 1
    COMMIT TRAN T2
    ------------------------------------------------------------------------------------------

    Try this please

    Friday, March 3, 2017 6:24 AM
  • Hi Uwe,

    Here is the script which you have asked.

    ------------------------------------------------------------------------------------------
    -- USE THE SCRIPT TO REPLICATE THE SECENARIO dbo.Employee AND IT IS SYSTEM VERSIONED WITH 
    -- CUSTOM NAME dbo.EmployeeHistory.
    ------------------------------------------------------------------------------------------
    -- STEPS TO REPLICATE THE ISSUE
    -- STEP 01 :: CREATE THE TABLE dbo.Employee and dbo.EmployeeHistory
    -- STEP 02 :: INSERT A ROW INTO THE TABLE
    -- STEP 03 :: OPEN TWO SSMS SESSIONS THAT CONNECTS TO THE DATABASE WHERE THE NEW TABLE IS CREATED.
    -- STEP 04 :: COPY and PASTE THE TRANSACTION T1 AND T2 INTO SESSION 1 AND 2 RESPECTIVELY
    -- STEP 05 :: EXECUTE SESSION 1 AND THEN SESSION 2 ONE AFTER ANOTHER. WILL GET THE ISSUE AT SESSION 1
    ------------------------------------------------------------------------------------------

    -- STEP 01
    USE [TESTDB]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[Employee]
    (
    [EmployeeID] [INT] IDENTITY(1, 1) NOT NULL,
    [Name] [NVARCHAR](100) NOT NULL,
    [Position] [VARCHAR](100) NOT NULL,
    [Department] [VARCHAR](100) NOT NULL,
    [Address] [NVARCHAR](1024) NOT NULL,
    [AnnualSalary] [DECIMAL](10, 2) NOT NULL,
    [ValidFrom] [DATETIME2](2) GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] [DATETIME2](2) GENERATED ALWAYS AS ROW END NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
    [EmployeeID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
    ) ON [PRIMARY]
    WITH
    (
    SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[EmployeeHistory] )
    )
    GO
    ------------------------------------------------------------------------------------------

    -- STEP 02
    INSERT INTO dbo.Employee
    (
    [Name],
    [Position],
    [Department],
    [Address]
    )
    VALUES
    (
    N'John Smith',
    'COO',
    'Sales',
    N'Department of State, 2430 Nouakchott Place, Washington, DC 20521-2430'
    )
    ------------------------------------------------------------------------------------------

    -- STEP 04 -- COPY TO SSMS SESSION 1
    BEGIN TRANSACTION T1
    -- Here I'm using wait to simulate as certain pre-processing in our business scenario
    WAITFOR DELAY '00:00:30'

    UPDATE dbo.Employee
    SET [Position] = 'CTO'
    WHERE EmployeeID = 1
    COMMIT TRAN T1

    -- STEP 04 -- COPY TO SSMS SESSION 2
    BEGIN TRANSACTION T2
    UPDATE dbo.Employee
    SET [Department] = 'Development'
    WHERE EmployeeID = 1
    COMMIT TRAN T2
    ------------------------------------------------------------------------------------------

    There is only one row. Please let me know if this is sufficient.

    Thanks,

    Pradeep


    Friday, March 3, 2017 6:30 AM
  • Hi Pradeep Rajasekharan,

    As an alternative, you may use TRY...CATCH (Transact-SQL) inside a WHILE (Transact-SQL) loop, and use @@ROWCOUNT (Transact-SQL) to detect if any rows were changed.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 7, 2017 10:43 AM
    Moderator
  • Hi Sam, 

    Sorry for the late reply. In the business we are dealing with, I think we cannot allow the code to break and handle it with the help of a try catch. I believe, the temporal table cannot be a right candidate for the business where we have concurrency which I've defined above. 

    Thanks,

    Pradeep

    Tuesday, March 14, 2017 7:59 AM
  • Hi Pradeep Rajasekharan,

    Based on my research, without a retry count with an optional delay, temporal table may not be the solution.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 15, 2017 10:27 AM
    Moderator