8.2 ACID Transactions, Concurrency Control, & Conflict Resolution

8.2 ACID Transactions, Concurrency Control, & Conflict Resolution

Enterprise lakehouses frequently support dozens of concurrent data pipelines, streaming micro-batches, and interactive analytical queries operating simultaneously on shared Delta tables. Without robust concurrency control, concurrent write operations on cloud object storage would suffer from race conditions, lost updates, and dirty reads.

Delta Lake solves this by implementing Optimistic Concurrency Control (OCC) coupled with Multi-Version Concurrency Control (MVCC) to provide full ACID transaction guarantees on top of Azure Data Lake Storage Gen2.

---

1. ACID Guarantees in Delta Lake

Delta Lake enforces all four pillars of ACID transactions directly on cloud object storage:

---

2. Optimistic Concurrency Control (OCC) Workflow

Delta Lake uses Optimistic Concurrency Control, which operates on the assumption that concurrent transactions rarely modify the exact same files or rows. Rather than locking tables upfront with pessimistic locks, transactions proceed optimistically and validate conflicts at the moment of commit.

+-------------------------------------------------------------------------+
|                   THE 3-PHASE OCC TRANSACTION PROTOCOL                  |
+-------------------------------------------------------------------------+
|                                                                         |
|  PHASE 1: READ SNAPSHOT                                                 |
|  - Engine reads _delta_log to obtain the current table version (e.g. v5)|
|  - Identifies active Parquet files for query processing                 |
|                                                                         |
|  PHASE 2: COMPUTE & WRITE (STAGE)                                       |
|  - Spark executes transformation logic across worker nodes              |
|  - Writes new Parquet files to ADLS Gen2 with unique UUID filenames     |
|                                                                         |
|  PHASE 3: VALIDATE & COMMIT                                             |
|  - Engine attempts to write commit file 00000000000000000006.json       |
|  - If version 6 does not exist -> Commit SUCCESS                        |
|  - If version 6 already committed by another writer -> CONFLICT CHECK   |
|    * If operations are non-conflicting -> Automatically replay & commit |
|    * If operations conflict -> Throw ConcurrentModificationException    |
+-------------------------------------------------------------------------+

Atomic Commit Mechanics on ADLS Gen2

On ADLS Gen2 (which provides true atomic directory renames and POSIX multi-action file creation via the ABFSS driver), Delta Lake ensures mutual exclusion during Phase 3. Only one writer can successfully write commit 00000000000000000006.json. Any secondary writer attempting the same file name receives a file collision error from storage and enters conflict resolution.

---

3. Delta Lake Isolation Levels: WriteSerializable vs. Serializable

Delta Lake supports two formal transaction isolation levels, configurable via table properties:

ALTER TABLE sales_silver 
SET TBLPROPERTIES ('delta.isolationLevel' = 'WriteSerializable'); -- or 'Serializable'

Comparison of Isolation Levels

Exam Tip: WriteSerializable is the default isolation level in Azure Databricks because it maximizes write throughput by allowing concurrent append operations to proceed without false-positive conflict failures.

---

4. Conflict Detection Matrix

When two transactions execute concurrently on the same Delta table, Delta Lake validates whether their file-level and partition-level modifications overlap:

---

5. Handling ConcurrentModificationException & Retry Protocols

When a conflict is detected during Phase 3, Delta Lake checks if the conflict is reconcilable:

                          CONFLICT EVALUATION LOGIC
                                      
  [ Conflict Detected at Commit ] ---> ( Did Tx1 touch files read by Tx2? )
                                                    |
                        +---------------------------+---------------------------+
                        | NO (Files are disjoint)                               | YES (Overlapping files)
                        v                                                       v
             [ Automatic Rebase & Commit ]                             [ Automatic Retry Loop ]
             - Update Tx2 start version to v+1                         - Roll back staged files
             - Write JSON commit log                                   - Re-execute query logic
             - Return SUCCESS                                          - Exhausted? Throw Exception

Automatic Retry Loop Configuration

Delta Lake automatically retries conflicting transactions on the Spark driver up to a configurable maximum count before surfacing an exception to the user application:

-- Configure maximum driver commit retry attempts (default: 10 million)
SET spark.databricks.delta.maxCommitAttempts = 100;

If the maximum retry attempts are exhausted, or if the concurrent operation fundamentally invalidates the read dataset, the driver raises:

org.apache.spark.sql.delta.ConcurrentModificationException: 
This table was modified by a concurrent update. Please try the operation again.

---

6. Architectural Best Practices to Prevent Concurrency Conflicts

Data engineers must design pipelines to minimize concurrent write collisions:

1. Use Partitioning or Liquid Clustering: Ensure concurrent writers target disjoint partitions or clustering keys (e.g., pipeline A writes region = 'US-East' while pipeline B writes region = 'EU-West').
2. Narrow MERGE Predicates: Avoid broad merge predicates (e.g., ON 1=1); always specify partition filters and specific primary keys (e.g., ON target.date = source.date AND target.id = source.id) so Delta Lake only locks necessary files.
3. Separate Compaction from Real-Time Writers: Schedule OPTIMIZE jobs during lower-traffic windows or rely on Auto-Compaction to minimize file churn.
4. Group Streaming Micro-Batches: In streaming workloads, increase the trigger interval (e.g., processingTime = '30 seconds') to reduce the frequency of rapid-fire commit contention on the transaction log.