Error when called StoreProcedure from Pipeline / Snapshot isolation transaction aborted

[GHerz]

Dear all, 

I am calling a stored procedure in my Fabric DWH from a pipeline. 

The Proc is for incremental data load. 

It performs the following operations in order to merge a table with new/modified records into the main table.

- Update modified records

- Insert new records

- creation of surrogate keys (unique key that will allow joining tables in the power BI modell using numeric expressions)

That works well for a lot of tables, but for few tables (mainly the largest in terms of record count) I'm getting the following error message during the update step:

"Snapshot isolation transaction aborted due to update conflict. Using snapshot isolation to access table 'g_l_entry' directly or indirectly in database 'DWH' can cause update conflicts if rows in that table have been deleted or updated by another concurrent transaction. Retry the transaction."

Basically I'm just setting up the system newly, and there is no other processes that would use that table.

Could possibly one of the earlier steps in the Proc (update, insert) cause this problem? But then I don't know how to fix it.. I tried to insert a WAIT for 1 minute, but that did not help.

I have a workaround: If a proc does not succeed then I make another loop and rerun the failed procedures again. 

Disadvantage: I don't like this solution, because i would like to fixed the underlying problem 

And secondly this works often but not for every nightrun.

Can someone help with an idea what this is about and how to solve?

Thanks and best regards!!

[GHerz]

Dear Yilong, thanks for your comments to this problem.

Could you pls help me to understand this a bit better? 

In my Procedure the different steps 

1 Update modified records

2 Insert new records

3 creation of surrogate keys

are performed. 

If I understand this correctly, it can be that step 3 is starting while step 2 still has an "active snapshot".

Why is the snapshot from step 2 not released BEFORE step 3 starts? Are the steps in stored procedure not processed sequentially?

Is there a way to wait with step 3 until step 2 is completely finished and the snapshot is inactive/released/deleted (whatever the right word would be)?

Alternatively shouldn't a WAIT statement help between step 2 and 3 to avoid the overlapping of both snapshots transactions? I tried that with 1 Minute, but maybe that was to short. I could extend that, if that would make any sense.

Regarding the lock hints:

My steps 2 and 3 are both updating data in my table.

Step 2 should be finished before step 3. 

Are you saying that using HOLDLOCK for example could solve the "snapshot isolation transaction aborted" issue as this will work better in my case as letting the query optimizer doing it work?

 Thanks for your efforts and for your time.

I really appreciate it!!

Best regards

[Anonymous]

Hi @GHerz ,

The error “Snapshot Isolation Transaction Aborted Due to Update Conflict” occurs when a transaction is reading a snapshot of data while another transaction is modifying the same data item, and the two transactions overlap in time. This error is caused by the nature of the snapshot isolation level. When a transaction reads a snapshot of data, it acquires a read lock to protect the consistency of the data, and when another transaction modifies the same data item, it acquires a write lock to protect the atomicity of the modification. When the locks of the two transactions overlap, a conflict occurs, causing the snapshot isolation transaction to abort.

So I think you can take this below in addition to trying to retry the transaction:

One solution to the “Snapshot isolation transaction aborted due to conflicting updates” error is to use lock hints. Lock hints are a way of instructing the database engine to use a specific type of lock when executing a query. By using appropriate lock hints, you can avoid update conflicts in snapshot-isolated transactions.

The following are some common types of lock hints:

   1. WITH (HOLDLOCK): Use a HOLDLOCK lock to hold a shared lock until the entire transaction is finished.
   2. WITH (UPDLOCK): Use an UPDLOCK lock to upgrade a shared lock to an exclusive lock to prevent other transactions from modifying the data.
   3. WITH (XLOCK): Use an XLOCK lock to hold an exclusive lock until the end of the entire transaction.
You can use these lock hints with query statements such as SELECT, UPDATE, DELETE, and so on to instruct the database engine to use a specific type of lock when executing a query.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GHerz]

@Anonymous Would you have some comments to above? I'm new in the forum and just learned that it's possible to address something to someone with "@ Username", therefore I'm trying this. I would appreciate any comment very much, that helps me getting forward in my project 😉