topic concurrency lock condition error when writing multiple records to an audit log table simultaneously. in Data Engineering

concurrency lock condition error when writing multiple records to an audit log table simultaneously.

Anonymous — Thu, 05 Sep 2024 08:34:22 GMT

Issue: Facing lock condition errors when writing multiple records to an audit log table simultaneously.
Setup:
- One master pipeline invoking multiple child pipelines.
- Audit log notebook placed in both master and child pipelines.
- During a child pipeline failure, the notebook activity updates the audit log table in parallel for both child and master pipeline rows.
Problem: Despite updates being made as separate rows, lock conditions occur, causing pipeline errors.
Attempts to Resolve:
- Applied partition by.
- Used update statements.
- These methods did not resolve the issue in Fabric.

Framed Question:

What are some effective strategies or best practices to avoid lock condition errors when writing multiple records to an audit log table simultaneously in a pipeline setup, especially in a Fabric environment?

Re: concurrency lock condition error when writing multiple records to an audit log table simultaneously.

AndyDDC — Thu, 05 Sep 2024 09:10:44 GMT

Hi Anonymous are you using the Warehouse service? It uses snapshot isolation and has only table level locking. Concurrent UPDATE statements will likely cause issues. I explain further here:

https://www.serverlesssql.com/transaction-isolation-in-fabric-warehouses/

Re: concurrency lock condition error when writing multiple records to an audit log table simultaneously.

Anonymous — Fri, 20 Sep 2024 06:59:45 GMT

Hi Anonymous

Have you resolved this issue? If any of the answers provided were helpful, please consider accepting them as a solution. If you have found other solutions, we would greatly appreciate it if you could share them with us.

Best Regards,
Jing