topic Re: Snapshot isolation transaction aborted due to update conflict when ONLY 1 update is running in Data Warehouse

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

stevehogg — Tue, 16 Jul 2024 15:31:21 GMT

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.

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

AndyDDC — Tue, 16 Jul 2024 20:05:47 GMT

Hi stevehogg does this happen everytime the UPDATE runs?

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

Anonymous — Wed, 17 Jul 2024 02:31:24 GMT

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

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

stevehogg — Wed, 17 Jul 2024 17:36:51 GMT

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

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

stevehogg — Wed, 17 Jul 2024 17:42:14 GMT

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}

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

AndyDDC — Wed, 17 Jul 2024 20:09:14 GMT

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

BEGIN TRANSATION

...UPDATE

COMMIT TRANSACTION

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

stevehogg — Wed, 17 Jul 2024 20:35:02 GMT

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.

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

AndyDDC — Thu, 18 Jul 2024 19:19:20 GMT

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?

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

stevehogg — Thu, 18 Jul 2024 19:30:03 GMT

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

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:

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

Anonymous — Mon, 22 Jul 2024 06:49:48 GMT

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 Overflow

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

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

stevehogg — Tue, 23 Jul 2024 17:11:14 GMT

Thanks. The WAITFOR_DELAY was only added as a potential means to stop the occurance of the error. It is outside of the BEGIN/COMMIT TRANSACTION.

Could there be maintenance operations in the Data Warehouse that are system driven? Something I don't control but is occuring and causing the conflict?

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

stevehogg — Tue, 23 Jul 2024 17:13:10 GMT

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.

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

AndyDDC — Tue, 23 Jul 2024 19:48:27 GMT

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

(

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

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

stevehogg — Fri, 02 Aug 2024 17:36:38 GMT

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.

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

Anonymous — Sat, 03 Aug 2024 14:26:22 GMT

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.

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

MaxYyz — Wed, 14 Aug 2024 02:31:21 GMT

Hello,

I am experiencing same intermittent error. Just wondering if there is any update on this?

Thanks!

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

stevehogg — Wed, 14 Aug 2024 13:34:33 GMT

MaxYyz there is no update to this problem. I continue to experience this problem intermittently. I am rewriting the stored procedure to perform Deletes/Inserts versus Updates.

In my case, there is no other process touching the table(s) in question, at least nothing I can find. Nothing shows up in QueryInsights and I can't catch another session in sys.dm_tran_locks.

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

moalnorrk — Wed, 04 Sep 2024 11:57:57 GMT

I have the same problem too!

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

moalnorrk — Wed, 18 Sep 2024 14:02:03 GMT

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}")

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

JoeDSullivanIII — Thu, 03 Oct 2024 11:31:58 GMT

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!