Resolving Write Conflicts in Microsoft Fabric Data Warehouse

[twcyril]

Fabric Data Warehouse (DW) supports ACID-compliant transactions using standard T-SQL (BEGIN TRANSACTION, COMMIT, ROLLBACK) and uses Snapshot Isolation (SI) as its exclusive concurrency control model. All operations within a transaction are treated atomically—either all succeed or all fail. This ensures that each transaction operates on a consistent snapshot of the data as it existed at the start of the transaction, which means.

• Read Consistency: All reads within a transaction reflect the state of all attached databases as of the first read or write operation following the BEGIN TRANSACTION statement, aligning with SQL Server’s snapshot isolation behavior.
• Optimistic Concurrency: Conflicts are detected at commit time, not during execution. This avoids locking overhead but introduces rollback risks.

While this model enables high concurrency for reads, it introduces the possibility of write-write conflicts which are common under SI when multiple transactions attempt to modify the same data concurrently.

Write-write conflicts in Fabric Data Warehouse are fundamentally different from lock-based conflicts. For more information on lock conflicts, refer to Understanding Locking and DDL Blocking in Microsoft Fabric Data Warehouse blog post.

Write-Write Conflict

A write-write conflict (aka update conflict) occurs when two or more concurrent transactions attempt to perform conflicting operations such as UPDATE, DELETE, MERGE or TRUNCATE on the same table. Under SI, only the first transaction to commit will succeed; others will be aborted with a conflict error.

Common Error Messages surfaced:

• Error 24556: Snapshot isolation transaction aborted due to update conflict. Using snapshot isolation to access table '%.*ls' directly or indirectly in database '%.*ls' can cause update conflicts if rows in that table have been deleted or updated by another concurrent transaction. Retry the transaction.
• Error 24706: Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table '%.*ls' directly or indirectly in database '%.*ls' to update, delete, or insert the row that has been modified or deleted by another transaction. Please retry the transaction.
  
  Users should treat the two error messages the same - retry the operation.

Operations Participating in Write-Write Conflict

• UPDATE
• DELETE
• MERGE
• TRUNCATE

MERGE Behavior: Even when MERGE transactions only result in append-only changes, they are still subject to write-write conflict detection.  When MERGE transaction affects different rows than other concurrent DML transactions, it may encounter this error if MERGE is not the first transaction to commit: 'Snapshot isolation transaction aborted due to update conflict.'

This granularity means that even if two transactions touch different rows, they will conflict if they target the same table. Only the first transaction to commit will succeed. Others will be rolled back with errors such as: 'Snapshot isolation transaction aborted due to update conflict...'

This behavior is by design and ensures consistency, but it can be disruptive in high-throughput pipelines.

What causes Write – Write conflicts

Fabric DW currently tracks write-write conflicts at the table level, meaning even if two transactions modify different rows in the same table, they can still conflict. Conflicts arise from two primary sources:

1. User-Induced Workload Conflicts
   • Multiple users or processes concurrently modifying the same table.
   • Common in ETL pipelines, batch updates, or overlapping transactions.
2. System-Induced Conflicts (Compaction)
   • Background system tasks like automatic data compaction rewrites files with poor quality.
   • These can conflict with user transactions such as UPDATE, DELETE, MERGE even if the user is modifying different rows.

Example: A user updates a table while compaction rewrites it. If compaction commits first, the user’s transaction fails due to a write-write conflict.

Refer to Data Compaction documentation to learn more about compaction.

Conflicts can impact performance, reliability, and user experience, especially in high-concurrency environments. Understanding how they work and how to mitigate them is essential for building resilient data pipelines and applications in Fabric DW.

Best Practices to Avoid Conflicts

1. Avoid concurrent UPDATE, DELETE, MERGE on the same table.
2. Use Retry Logic for DML Statements for UPDATE, DELETE, MERGE
   • Fabric DW uses Snapshot Isolation (SI), which detects conflicts at commit time. If a transaction fails due to a conflict, retrying the statement is often successful.
   • Recommended for UPDATE, DELETE, MERGE, and TRUNCATE statements.
   • Especially effective for single-statement transactions.
   • Customers are advised to implement retry logic in stored procedures or ETL pipelines or tools like Qlik replicate. This is the most effective short-term workaround.

In-Product Capabilities

As part of our ongoing investments to improve concurrency and reliability in Fabric Data Warehouse, we’re introducing Compaction Preemption, a new capability that intelligently avoids write-write conflicts between background compaction tasks and user operations. As of October 2025, Compaction preemption is enabled and is handled entirely by the system to reduce write-write conflicts between user workloads and system compaction tasks in Fabric DW, which uses snapshot isolation for concurrency control.

Problem Compaction Preemption Solves

Fabric DW uses Snapshot Isolation (SI), where write-write conflicts occur if two transactions modify the same table concurrently. Background compaction, which rewrites table files to optimize storage can silently conflict with user operations like UPDATE, DELETE, or MERGE, causing failures.

 How Compaction Preemption Works

Compaction Preemption introduces a shared lock mechanism:

• User transactions take a shared lock on a preemption-specific sub resource.
• Compaction checks for this lock:
  • Before execution: If the lock is held, compaction aborts immediately.
  • Before commit: If the lock is held, compaction aborts again to avoid conflict.

Compaction does not take a lock, ensuring it never blocks user workloads.

Users can still create write-write conflicts through their own workload; this feature only addresses conflicts involving compaction, not user-initiated conflicts. If users use explicit transactions and perform non-conflicting work (like inserts) before a conflicting operation (update, delete, merge), compaction may still commit successfully, and a subsequent user operation could still fail due to a conflict.

What’s Next

We are actively investing in improvements to improve concurrency –

• Currently, conflicts are detected at the table level. Introducing more granularity such as file-level conflict detection / row- level conflict detection to improve concurrency.