Snapshot isolation transaction aborted due to update conflict when ONLY 1 update is running

[stevehogg]

I have a table with 40 million rows. When performing an update on the table it often fails due to the Snapshot isolation transaction aborted due to update conflict. 

Msg 24556, Level 16, State 2, Line 1165
Snapshot isolation transaction aborted due to update conflict. Using snapshot isolation to access table 'tbl' directly or indirectly in database 'fabric_warehouse' can cause update conflicts if rows in that table have been deleted or updated by another concurrent transaction. Retry the transaction.

The issue here is there is only 1 update running. No other processes are touching this data warehouse at the time. This table is not even used anywhere else. 

[v-vepott]

Hello, Do we have any solution for this error now. because this is giving big trouble in our existing warehouse migration to Fabric warehouse

[Sureshot]

Here we are over 8 months later and microsoft still have not corrected this in Fabric Warehouse.

Replacing with Delete/Insert/Upsert is not always practical.

For me this eror only occurs when I execute the same stored procedure over and over like below. 

When I retry it works and it never has failed just executing it once.

EXEC uspStoredProcedureName '2025-01-01', '2025-04-01'

or
EXEC uspStoredProcedureName '2025-01-01', '2025-01-01'

EXEC uspStoredProcedureName '2025-02-01', '2025-02-01'

EXEC uspStoredProcedureName '2025-03-01', '2025-03-01'

....etc.

[frederikdeclerc]

We have the same issue here.... Update statements that refuse to execute intermittently.

[JoeDSullivanIII]

In case anyone is still running into this issue, here is what we did to resolve it.

First, as stated by @MaxYyz, we avoid updates wherever we can and lean towards deleting/inserting. If an update is necessary, then we add a try/catch and duplicate the update call(s) in the catch as a re-try.

It isn't very pretty, but it does appear to work... 

BEGIN TRY
     {update sql}
END TRY

BEGIN CATCH
IF ERROR_NUMBER() = 24556
BEGIN
    {update sql}
END
ELSE
    THROW
END CATCH

Credit for this soution goes to Lantern Studios (https://lanternstudios.com/), who fixed this for us with the help of Microsoft support. Hope this helps someone else struggling through this!

[moalnorrk]

You can solve it by adding retry mechanism in your code. For example: 

import time
import pyodbc

def update_record_status(record_id: str, new_status: str, connection) -> None:
    """Update the status of a record in the database with retry logic for snapshot isolation conflicts."""
    max_attempts = 10  # Maximum number of retry attempts
    attempt = 0
    sleep_time = 2  # Initial wait time between attempts

    while attempt < max_attempts:
        try:
            with connection:  # Use the provided database connection
                cursor = connection.cursor()
                print(f"Attempting to update record ID: {record_id} with status: {new_status}")
                cursor.execute(f"""
                    UPDATE your_table_name
                    SET Status = ?
                    WHERE RecordID = ?
                """, (new_status, record_id))  # Use parameterized queries for security
                print(f"Successfully updated record ID: {record_id} on attempt {attempt + 1}")
                return  # Exit the function after a successful update

        except pyodbc.ProgrammingError as e:
            # Check if the error is due to a snapshot isolation conflict
            if "snapshot isolation transaction aborted due to update conflict" in str(e).lower():
                attempt += 1
                print(f"Snapshot isolation conflict on record ID {record_id}, attempt {attempt}. Retrying in {sleep_time} seconds.")
                time.sleep(sleep_time)  # Wait before retrying
                sleep_time *= 2  # Exponential backoff to reduce contention

            else:
                # If it's a different type of error, re-raise the exception
                raise e

    print(f"Failed to update record ID: {record_id} after {max_attempts} attempts.")
    raise Exception(f"Max attempts reached for updating record ID: {record_id}")

[Anonymous]

I suggest you use query insights to verify that no other session was touching your table at the time your stored proc was running. 

There is a current limitation with Fabric DW that prevents two UPDATE transactions from executing at the same time on the same table, even when they are affecting different rows. This is inconsistent with how you would expect a relational database (like SQL Server) to work. 

[Anonymous]

Hi @stevehogg,

How did you ingest the data to the warehouse? Is there any operations or additional object that integrated or operated with to the source and target tables?

>> Using snapshot isolation to access table 'tbl' directly or indirectly in database 'fabric_warehouse' can cause update conflicts if rows in that table have been deleted or updated by another concurrent transaction. 

BTW, the error message seems mention the table 'tbl', can you please share some more detailed information about these?

Regards,

Xiaoxin Sheng

[stevehogg]

Hi @Anonymous,

I have one ingestion point which populates a bronze table, a staging table. The stored procedure takes the bronze data and inserts/updates the table in question. In the stored procedure, the table is insert to and updated several times. 

I replaced the actual table name and warehouse name with generic words 'tbl' and 'fabric_warehouse'. 

Here is a full message from today:

Operation on target execute_sql_stored_prodedure failed: Snapshot isolation transaction aborted due to update conflict. Using snapshot isolation to access table 'dim_pay_results' directly or indirectly in database 'ebs_integration_wh' can cause update conflicts if rows in that table have been deleted or updated by another concurrent transaction. Retry the transaction.
Statement ID: {0606B5C7-0608-4DE6-8E0E-D6F3DFF82FB9} | Query hash: 0x99B6F93464A0308E | Distributed request ID: {FACA1296-B404-467C-B8A3-691DC57F3900}
Statement ID: {2F97B2BD-7627-4FEA-AEC6-4AF42EB5AC2C} | Query hash: 0x100B057354AF0686 | Distributed request ID: {E8003A41-469D-46B9-B4DE-444ABCF66E94}
Statement ID: {F3E8B938-F47E-43BF-8950-8DC9B41D2EE5} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {48A2D0AD-0A0F-40FA-99E6-B959FD0B8804}
Statement ID: {25139BC4-0157-43BE-82DB-0067F3818B72} | Query hash: 0x60B57FD9E80233CB | Distributed request ID: {099DE90B-557E-4085-88B1-6116011235AF}
Statement ID: {82DC6FFA-DA63-43E4-A902-43678105FC8E} | Query hash: 0x355A4135BB2EE94E | Distributed request ID: {4E3C3078-90B8-489A-B574-4CAB268A9504}
Statement ID: {E3088DF2-79DD-497F-B580-982540BFCDC2} | Query hash: 0x71AA64E202ACAB43 | Distributed request ID: {6C85A211-5E07-4403-8BC4-35465024D488}
Statement ID: {9C6C4CFE-1260-465A-AC00-00C17C2993BB} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {CA050FDA-A5C2-45B9-B920-11F0D7AB17D4}
Statement ID: {FCEE72CC-4260-4869-BE2D-81A418C96A49} | Query hash: 0x64872CD345ECC375 | Distributed request ID: {E40A73B1-F8C3-4362-BF18-B9A14DD9997B}
Statement ID: {55C015C6-D9B5-4AFD-B639-C5BFFF877182} | Query hash: 0x60E5DBFAD7C17979 | Distributed request ID: {BC90EE44-3499-410A-A0B7-39939CBF19AE}
Statement ID: {785CE4E4-C73F-4D03-9818-E661AC7C2899} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {E25E320E-034D-4EA4-BA75-8F7175D1BCF7}
Statement ID: {0DE374F8-7663-428D-9C2E-52F95053D443} | Query hash: 0x581A05BAF38145FC | Distributed request ID: {52FD4692-21B0-40A3-8120-26ED8FAB36CA}
Statement ID: {5DE46C21-D144-4AA0-9713-4269EC30729C} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {18551AAE-0264-4CF4-B219-5B850EE153CF}
Statement ID: {C0A1964D-FA5C-4C29-A9E5-F208D2FD3149} | Query hash: 0x91AA4C57E1471238 | Distributed request ID: {FFACDF76-D05C-4C10-BC97-FB8433B48F4F}
Statement ID: {6FD65608-A83C-4FC2-81EF-A63E149BA314} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {5A483025-BD2B-4FB4-96FC-A29EA6E15BBD}
Statement ID: {4122828E-1F3A-431A-90FD-84F41FF689B9} | Query hash: 0x14D8FBBA4E31C4DD | Distributed request ID: {3AEDD8D0-79A0-4773-A7AD-A0114267BCF0}
Statement ID: {3904133D-6945-4D2C-A3D7-00FCE2BD85D3} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {D5AAB652-B6B6-4485-AE3D-D6BBD89F0E92}
Statement ID: {6C027973-53D3-4591-B628-3995F2E28895} | Query hash: 0x5E2CD86ABB30C6C0 | Distributed request ID: {3F6C6A21-D286-4262-9307-3C9E2D4B175C}
Statement ID: {F1AE2266-E03A-4DA9-B1CC-B321547ADFDE} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {2CD81E76-9560-49F3-8092-3CA7D809E88C}
Statement ID: {D92D4C7D-DC90-4BB3-8650-B10F3BBA47F5} | Query hash: 0x15FBB5C3199EBADF | Distributed request ID: {A6A0B571-E1B9-4C63-9718-802186C98E05}
Statement ID: {FC266B00-967A-4D4B-856F-164B367462A4} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {9C4015A9-DF81-4CAB-9729-F62D632A0933}
Statement ID: {213BEA5C-0779-4FD4-A84D-A3349FE20159} | Query hash: 0x75F900A9EC277B99 | Distributed request ID: {98F6E228-7409-46EA-858A-2B49730704F7}
Statement ID: {6BDE9ACA-59CA-4053-8DAA-C0F17EB247DA} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {22AB09E0-A965-49DA-909A-376B2CBD8CD6}
Statement ID: {D7A44144-B30E-4579-A1AE-CCB050B59683} | Query hash: 0xED0BB54A1BCD257 | Distributed request ID: {BCC754BA-582D-4F64-A40C-EFA1E78761DD}
Statement ID: {F58C391C-6575-4207-8C7B-100A075F5CEC} | Query hash: 0x9DFCC228AB5839EC | Distributed request ID: {3580E2CA-D0FD-448E-B7F5-1704BC151EBB}
Statement ID: {E76C047B-C6D0-4162-9AD3-02D1A41EF7CB} | Query hash: 0xCC8288B6750EC0F5 | Distributed request ID: {F3794FDA-589B-4789-A699-E755C7C839E2}
Statement ID: {706D31EE-A444-45DD-B4FA-B44F6E828936} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {063A87A0-2552-4BE4-AACB-983F5437BB5E}
Statement ID: {D1C72876-473F-4A61-A92B-8BEBA03BD4B9} | Query hash: 0x4BCBDB95C3CD6503 | Distributed request ID: {9CCA0739-3B32-4B76-82D7-5D42B5CA4CCD}
Statement ID: {A44E1C81-4109-4D1B-9A94-2510C2E08250} | Query hash: 0xB4F7F41377E26E9F | Distributed request ID: {F9BDE03E-9451-4E88-88AA-C8B6C6A9CA34}
Statement ID: {F83B92A5-3E5C-4EDE-9982-6B48A2CC86E8} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {9F8D0EDE-83F2-421F-A5B9-DD688031ABB9}
Statement ID: {3940A8AC-F80F-4DBA-A446-7A2D77515A8C} | Query hash: 0xDC640D2A69CB8358 | Distributed request ID: {CD5D7CCE-8EB6-4E3A-9615-F2D0ABC0D7C1}
Statement ID: {34DC75A8-2765-4EE4-9C65-037173F94195} | Query hash: 0x92B11B7D6204E446 | Distributed request ID: {CCE64BF9-AC1C-41A8-9F4F-24DEF4118726}
Statement ID: {0F0B794F-3EB3-4CE3-84E9-E1881267D73A} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {80F73375-9FB5-4AE4-83EE-394BF9936953}
Statement ID: {755108C9-C7A5-46EE-9127-380B14EED689} | Query hash: 0x46B3F8F7392C3CB | Distributed request ID: {EDC654E9-7CC9-48B5-9A51-5BB6B2EC31E6}
Statement ID: {A742DC53-A270-40EB-AB81-FDBCEE27F703} | Query hash: 0xB139BD9D04B3EC1D | Distributed request ID: {080A6CF5-52E7-486A-897D-11F112A4A350}
Statement ID: {B5CC6757-3533-4330-A0D7-91028C3546F0} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {5846E598-BF61-4BE4-B2C7-097E3A2C7563}
Statement ID: {5C9D5C1A-7CD1-4161-9D0C-901FF7F3285C} | Query hash: 0xC4E468DC77E50E0C | Distributed request ID: {DB48CA1F-0989-42CF-A60C-B61ABDC236A8}
Statement ID: {19929F7B-77E5-4759-8CA0-AF1F83C3F571} | Query hash: 0xCB2D9DC187AEEAF2 | Distributed request ID: {F279CE64-5D6F-4B49-9E2B-E9FD52A5C751}
Statement ID: {49B7C46A-6A53-4C59-9DBD-057922C106B6} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {D7182E35-D1EF-47E3-91E3-A422DF09D3EE}
Statement ID: {B432826B-C290-4623-9522-D65E5E7BCD6A} | Query hash: 0x7D6CC8A86D8A1416 | Distributed request ID: {51F5ADC5-CB2C-48AD-A498-D6BFCA8B508C}
Statement ID: {A26EE2F1-FBB4-46CC-A625-FA17CC8A5D1E} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {7583D3F7-B51F-4435-BC4B-D798CCD993F3}
Statement ID: {EB8575DA-694A-4401-B6C1-022EDF75A76B} | Query hash: 0xCAFA998F694D6DBA | Distributed request ID: {8109E040-D0FD-4B2B-A6A1-D53C774FFACA}
Statement ID: {0F604DCD-F5E5-497A-A529-D48DFF07F7C9} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {743D1E9D-47E3-48E7-A3ED-4A44CFD6F5F7}
Statement ID: {B1DDDDE5-7F29-4DE3-8BA2-0669AB306220} | Query hash: 0xE8E43576E4AA73 | Distributed request ID: {8E0D869F-6A4C-4E0E-A5EE-B74A7FE90846}
Statement ID: {7BA96F76-A288-4EB6-B803-4DFFD58D3087} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {3A1C8A61-DF57-4C6D-8C4C-FC16BA20B910}
Statement ID: {C9A76CFA-204B-4F6E-8FB8-D6BA9D8F5B53} | Query hash: 0xE8E43576E4AA73 | Distributed request ID: {249ED723-DA68-4FC7-86C3-9317A61F64A1}
Statement ID: {8FA31BB5-10FA-4C50-A16F-13BBBC82FD2C} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {F3FD6D51-8B59-4F93-A247-F12539B36C08}
Statement ID: {302DD573-6CF6-4C78-A630-4E64CB950D3B} | Query hash: 0x2DC5F206C068BF8 | Distributed request ID: {BF31AA4A-DE40-4D91-A673-F171F9826D2B}
Statement ID: {262EECF8-CE41-4FE8-8EE2-5879400850B3} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {71E0C7FB-F068-4035-9A51-AEBEFCC283E5}
Statement ID: {EF1E6BF9-2C70-4C90-A9F4-FB038FF35733} | Query hash: 0x32899D99C06A3E68 | Distributed request ID: {D0AB2717-5F59-426E-A977-BA2B0009CC18}
Statement ID: {B86DF847-DDEB-45A2-A160-29C95388EB72} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {0FC5740F-C96C-4D23-AE6B-63835A3D334F}
Statement ID: {73681E69-F680-4F1A-8C62-42927553EF87} | Query hash: 0x122C2DF6B23B0FD8 | Distributed request ID: {E1884E78-3DC1-4696-B62A-00AAD7EB918E}
Statement ID: {9EAA954E-F075-4320-976C-2A82611F8B1C} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {5F1712D0-1F8C-485B-9E29-2612C4F25DD8}
Statement ID: {0DCEEF4D-2FFD-4273-A5A3-34185931DFC6} | Query hash: 0x8B845E193417B7D8 | Distributed request ID: {A89E7288-C2AB-425E-A342-835779FB2BCB}
Statement ID: {303D4D52-01F7-4828-AA3B-E58EC97B52A3} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {29D88227-CD10-410F-8D29-F0A2CAF26796}
Statement ID: {450C8465-8A62-44B4-B103-1E709B569342} | Query hash: 0x4EB2B64E4B9855C1 | Distributed request ID: {52EDE76C-BEF8-434D-BF01-708FB5B7BE3F}
Statement ID: {DA497F89-C466-4566-94D4-1805804EAB49} | Query hash: 0x6B8BA9845E1C64DD | Distributed request ID: {7DF2F3B5-9C32-4B67-B534-F4E6944581E4}
Statement ID: {2F7B3802-134E-4A34-952C-65494464CD7D} | Query hash: 0x408C041BB40B4555 | Distributed request ID: {C400D69A-445C-4F5F-8553-44C0CEB70A75}

[AndyDDC]

Hi @stevehogg does this happen everytime the UPDATE runs?

[AnmolGan81]

Was there any fix for this, we are also facing the same issue!

[stevehogg]

Hi @AndyDDC . This happens regularly but not always in the same update. The stored procedure is updating the table several times, several distinct updates.

[AndyDDC]

Ok. Can you try wrapping each UPDATE statement in an explicit transaction?

BEGIN TRANSATION

...UPDATE

COMMIT TRANSACTION

[stevehogg]

That is a great idea and I tried it, but it did not help. Still getting the same error. Sometimes the error occurs in the second UPDATE and sometimes it occurs in the 3rd, 4th or 5th. 

I also tried adding a 10 second wait between each UPDATE.

[moalnorrk]

I have the same problem too!

[AndyDDC]

I've tried to replicate the issue but cannot get it to fail if I INSERT, UPDATE several times into a table from another table (using 1B rows).  Any chance of sharing pseudo code so I can try and replicate?

[stevehogg]

Here is my actual code, pulled out of the sproc and run standalone. It throws the error at different times and then sometimes, completes all updates successfully. 

IF OBJECT_ID('dbo.dim_pay_results', 'U') IS NOT NULL DROP TABLE dbo.dim_pay_results; 

CREATE TABLE dbo.dim_pay_results

AS 

SELECT * from dbo.vw_dim_pay_results;

PRINT 'UPDATE 1'

BEGIN TRANSACTION

UPDATE pr 

SET pr.is_valid_cost_center_refid = 1

FROM dbo.dim_pay_results pr 

INNER JOIN dbo.dim_wd_cost_center cc 

 ON pr.pay_cost_center_refid = cc.cost_center_refid

COMMIT TRANSACTION

WAITFOR DELAY '00:00:50'

PRINT 'UPDATE 2'

BEGIN TRANSACTION

UPDATE pr 

SET pr.cost_center_sk_src=1,

pr.cost_center_sk = cc.cost_center_sk,

pr.cost_center_cd = cc.cost_center_cd,

pr.division = cc.division,

pr.business_unit = cc.business_unit,

pr.operating_unit = cc.operating_unit,

pr.department = cc.department

FROM dbo.dim_pay_results pr 

INNER JOIN dbo.tmp_employee_cost_center ecc 

 ON pr.employee_id = ecc.employee_id

AND pr.pay_cost_center_refid = ecc.cost_center_refid

AND ecc.cc_priority = 1

INNER JOIN dbo.dim_wd_cost_center cc 

 ON pr.pay_cost_center_refid = cc.cost_center_refid

AND ecc.cost_center_cd = cc.cost_center_cd

AND pr.pay_period_end_date >= cc.cost_center_effdt

WHERE pr.cost_center_sk IS NULL 

COMMIT TRANSACTION

WAITFOR DELAY '00:00:50'

PRINT 'UPDATE 3'

BEGIN TRANSACTION

UPDATE pr 

SET pr.cost_center_sk_src=2,

pr.cost_center_sk = cc.cost_center_sk,

pr.cost_center_cd = cc.cost_center_cd,

pr.division = cc.division,

pr.business_unit = cc.business_unit,

pr.operating_unit = cc.operating_unit,

pr.department = cc.department

FROM dbo.dim_pay_results pr 

INNER JOIN dbo.tmp_employee_cost_center ecc 

 ON pr.employee_id = ecc.employee_id

AND pr.pay_cost_center_refid = ecc.cost_center_refid

AND ecc.cc_priority = 1

INNER JOIN dbo.dim_wd_cost_center cc 

 ON pr.pay_cost_center_refid = cc.cost_center_refid

AND ecc.cost_center_cd = cc.cost_center_cd

WHERE pr.cost_center_sk IS NULL 

COMMIT TRANSACTION

WAITFOR DELAY '00:00:50'

PRINT 'UPDATE 4'

BEGIN TRANSACTION

UPDATE pr 

SET pr.cost_center_sk_src=3,

pr.cost_center_sk = cc.cost_center_sk,

pr.cost_center_cd = cc.cost_center_cd,

pr.division = cc.division,

pr.business_unit = cc.business_unit,

pr.operating_unit = cc.operating_unit,

pr.department = cc.department

FROM dbo.dim_pay_results pr 

INNER JOIN dbo.tmp_employee_cost_center ecc 

 ON pr.employee_id = ecc.employee_id

AND pr.pay_cost_center_refid = ecc.cost_center_refid

AND ecc.cc_priority = 2

INNER JOIN dbo.dim_wd_cost_center cc 

 ON pr.pay_cost_center_refid = cc.cost_center_refid

AND ecc.cost_center_cd = cc.cost_center_cd

WHERE pr.cost_center_sk IS NULL 

COMMIT TRANSACTION

WAITFOR DELAY '00:00:50'

PRINT 'UPDATE 5'

BEGIN TRANSACTION

UPDATE pr 

SET pr.cost_center_sk_src=4,

pr.cost_center_sk = cc.cost_center_sk,

pr.cost_center_cd = cc.cost_center_cd,

pr.division = cc.division,

pr.business_unit = cc.business_unit,

pr.operating_unit = cc.operating_unit,

pr.department = cc.department

FROM dbo.dim_pay_results pr 

INNER JOIN dbo.tmp_employee_cost_center ecc 

 ON pr.employee_id = ecc.employee_id

AND pr.is_valid_cost_center_refid = 0

AND ecc.cc_priority = 1

INNER JOIN dbo.dim_wd_cost_center cc 

 ON ecc.cost_center_cd = cc.cost_center_cd

AND pr.pay_period_end_date >= cc.cost_center_effdt

WHERE pr.cost_center_sk IS NULL 

COMMIT TRANSACTION

WAITFOR DELAY '00:00:50'

PRINT 'UPDATE 6'

BEGIN TRANSACTION

UPDATE pr 

SET pr.cost_center_sk_src=5,

pr.cost_center_sk = cc.cost_center_sk,

pr.cost_center_cd = cc.cost_center_cd,

pr.division = cc.division,

pr.business_unit = cc.business_unit,

pr.operating_unit = cc.operating_unit,

pr.department = cc.department

FROM dbo.dim_pay_results pr 

INNER JOIN dbo.dim_wd_cost_center cc 

 ON pr.pay_cost_center_refid = cc.cost_center_refid

AND pr.pay_period_end_date >= cc.cost_center_effdt

WHERE pr.cost_center_sk IS NULL 

COMMIT TRANSACTION

WAITFOR DELAY '00:00:50'

PRINT 'UPDATE 7'

BEGIN TRANSACTION

UPDATE pr 

SET pr.employee_account = e.employee_account,

pr.employee_hours_account = '988' + RIGHT(e.employee_account,3)

FROM dbo.dim_pay_results pr 

INNER JOIN dbo.dim_employee e 

 ON pr.employee_id = e.employee_id

WHERE pr.employee_account IS NULL 

  AND e.employee_account IS NOT NULL 

COMMIT TRANSACTION

WAITFOR DELAY '00:00:50'

PRINT 'UPDATE 8'

BEGIN TRANSACTION

UPDATE pr 

SET pr.employee_account = w.account,

pr.employee_hours_account = '988' + RIGHT(w.account,3)

FROM dbo.dim_pay_results pr 

INNER JOIN dbo.dim_worker w 

 ON pr.employee_id = w.employee_id

AND w.seq = 1

WHERE pr.employee_account IS NULL 

  AND w.account IS NOT NULL 

COMMIT TRANSACTION

Also attaching the last run message:

[stevehogg]

I'm stuck at this point, now just looking for an alternate way to accomplish what I need without multiple updates. No resolution to the problem is known at this time.

[AndyDDC]

Just thinking outloud here, maybe instead of joining to the tables you can use CTEs? Not sure if the engine would just compile it down to joining the 2 tables the same, but worth a try

E.G instead of:

UPDATE pr 

SET pr.is_valid_cost_center_refid = 1

FROM dbo.dim_pay_results pr 

INNER JOIN dbo.dim_wd_cost_center cc 

 ON pr.pay_cost_center_refid = cc.cost_center_refid

use

;WITH cte_dim_wd_cost_center

AS

(

    SELECT cost_center_refid

    FROM dbo.dim_pay_results

)

UPDATE pr 

SET pr.is_valid_cost_center_refid = 1

FROM dbo.dim_pay_results pr 

INNER JOIN cte_dim_wd_cost_center cc 

 ON pr.pay_cost_center_refid = cc.cost_center_refid

[stevehogg]

@AndyDDC thanks for the tip, unfortuantely that did not help either. 

Could this be caused by the Update running past a time threshold? I have not found any correlation to support this but just trying to think. 

[Anonymous]

Hi @stevehogg,

I think error should relate to multiple transactions. If the ' WAITFOR DELAY ' duration less than real processing duration, it will cause the issue for multiple transactions operation at the same time and get the conflict error.

You can take a look at the following link to know how to handle this scenario:

sql - How can I fix "Snapshot isolation transaction aborted due to update conflict"? - Stack Overflo...

For modifications MSSQL Server always tries to acquire locks, and since there are locks (by using a transaction) on the first table which escalate to locks on the second table because of the (foreign key) the operation fails. So every modification causes in fact a mini transaction.

The default transaction level on MSSQL is READ COMMITTED, but if you turn on the option READ_COMMITTED_SNAPSHOT it will convert READ COMMITTED to a SNAPSHOT like transaction every time you use READ COMMITTED. Which then leads to the error message you get.

Regards,

Xiaoxin Sheng