Skip to main content

06.3 Delta Lake ACID Transactions, Concurrency & Isolation

The transaction log explains how Delta records table state. This topic explains how Delta safely changes that state when multiple readers and writers operate concurrently.

Databricks documents ACID guarantees for Delta tables and uses optimistic concurrency control. Reads use snapshot isolation, while writes use WriteSerializable isolation by default. Serializable is available when stronger guarantees are required. Databricks — ACID guarantees Databricks — Isolation levels

Delta ACID concurrency architecture

1. The architect mental model​

Think of a Delta write as:

READ SNAPSHOT ↓ PLAN CHANGES ↓ WRITE NEW FILES ↓ VALIDATE AGAINST NEW COMMITS ↓ COMMIT OR FAIL

There is no traditional long-held database lock around the whole operation.

Delta uses optimistic concurrency control (OCC): assume conflicts are uncommon, let work proceed, then validate before commit. Delta Lake concurrency control

2. What ACID means in Delta​

Atomicity​

A transaction either becomes visible completely or does not become part of the table state.

Consistency​

Committed table states obey the Delta table protocol, schema and transaction rules.

Isolation​

Concurrent operations are separated according to the table's isolation semantics.

Durability​

Committed transaction state is persisted in the transaction log and cloud object storage.

Databricks explains that the transaction log controls commit atomicity: data files are written first, and only become visible to readers when the corresponding transaction-log commit is recorded. Databricks — ACID guarantees

3. Atomicity: why partial files do not corrupt the table​

Suppose a job intends to create five files:

file A file B file C file D file E

The job writes these files to object storage.

If the transaction fails before commit, those files are not logically part of the Delta table.

The critical boundary is:

Physical file write ≠ Logical table commit

Only the successful transaction-log commit makes the new files visible as table state.

This is why a failed transaction can leave untracked physical files without corrupting the logical table. VACUUM can eventually remove eligible untracked files. Databricks — ACID guarantees

4. Isolation: two different questions​

When people say isolation, ask two questions:

  1. What does a reader see?
  2. Which concurrent writes are allowed to commit?

Databricks separates these concerns.

READS ↓ Snapshot isolation

WRITES ↓ WriteSerializable by default or Serializable

This distinction is essential for architect-level discussions.

5. Snapshot isolation for reads​

A reader gets a consistent table snapshot.

Imagine:

Reader starts at v10 Writer commits v11 Writer commits v12

The reader can continue working against its original snapshot rather than seeing a half-applied mixture of versions.

Conceptually:

Reader R │ └── Snapshot(v10) │ └── stable logical state

Meanwhile the table can advance to v11 and v12.

Databricks documents snapshot isolation for reads. Databricks — Isolation levels

6. Optimistic concurrency control​

Delta OCC has three major phases.

Phase 1 — Read​

The transaction reads a table snapshot if its operation requires reading the target table.

It identifies which data it may need to modify.

Phase 2 — Write​

The transaction writes new Parquet/data files.

These files are not yet visible as committed table state.

Phase 3 — Validate and commit​

Before committing, Delta checks whether relevant concurrent changes occurred after the transaction's read snapshot.

If no conflict is detected:

COMMIT SUCCESS ↓ new table version

If conflict is detected:

COMMIT REJECTED ↓ conflict exception

This protects table integrity without requiring long-lived locks. Delta Lake concurrency control

7. Example: two concurrent writers​

Start with:

Version 10 customer_id 100 → amount 500

Two jobs start at roughly the same time.

Job A reads v10 Job B reads v10

Job A updates customer 100. Job B also updates customer 100.

Both may initially write new files.

At validation time, Delta detects that both transactions affect overlapping table state.

One transaction can commit; the other must fail and retry or be handled by the application.

A: READ v10 → WRITE → COMMIT v11 ✓ B: READ v10 → WRITE → VALIDATE ✗

The important point is: the failed writer does not silently corrupt the table.

8. WriteSerializable — the default​

Databricks uses WriteSerializable as the default write isolation level.

It guarantees that committed writes can be ordered consistently, while allowing more concurrency than full Serializable isolation.

A subtle but important example is concurrent DELETE and INSERT.

Suppose both start from v0:

t0: DELETE starts t1: INSERT starts t2: INSERT commits v1 t3: DELETE commits v2

Under WriteSerializable, the DELETE can still commit because the resulting behavior can be explained by a serial ordering in which the INSERT logically occurred after the DELETE, even though the physical commit history shows INSERT at v1 and DELETE at v2.

This can surprise engineers who assume history order is always equivalent to serial execution order. Databricks — Isolation levels

9. Serializable isolation​

Serializable is the strongest supported isolation level.

It requires the committed operations to correspond to a valid serial execution that also constrains reads.

Use it when the business requirement cannot tolerate the weaker WriteSerializable behavior.

Example: if a DELETE must guarantee that every matching row existing at its commit boundary is removed, a conflicting concurrent INSERT may need to cause the DELETE to fail rather than allowing the weaker ordering.

Configure it with:

ALTER TABLE my_table SET TBLPROPERTIES ('delta.isolationLevel' = 'Serializable');

Serializable provides stronger guarantees but can reduce write concurrency. Databricks — Isolation levels

10. WriteSerializable vs Serializable​

ConcernWriteSerializableSerializable
DefaultYesNo
Write orderingSerializableSerializable
Read serializabilityNoYes
Concurrent append flexibilityHigherLower
ThroughputGenerally higherGenerally lower
Use caseMost workloadsStronger business consistency

Architect rule: do not switch to Serializable simply because it sounds safer. First identify the business anomaly you are trying to prevent.

11. Conflict taxonomy​

Common concurrency conflicts include:

  • write-write conflict
  • write-read conflict
  • phantom conflict
  • metadata conflict
  • protocol conflict
  • concurrent streaming checkpoint conflict

Databricks documents these transaction-conflict categories and their relationship to isolation levels. Databricks — Transactions

12. Write-write conflict​

Two transactions modify overlapping data.

Example:

Job A: UPDATE customer 100 Job B: DELETE customer 100

They cannot both blindly commit their independent versions of the same logical state.

The conflict must be detected and one operation must be retried or rejected.

13. Write-read conflict​

A transaction reads data and another transaction modifies data that matters to the first transaction.

Databricks documents write-read conflicts as applying to Serializable isolation.

Architecturally:

T1 reads X T2 changes X T1 attempts commit

Serializable can reject T1 if the changed data invalidates the serial execution requirements. Databricks — Transactions

14. Phantom conflicts​

A phantom occurs when another transaction adds rows matching a predicate that another transaction has read.

Example:

T1: DELETE WHERE amount > 1000 T2: INSERT amount = 2000

The inserted row may satisfy T1's predicate even though T1 could not see it in its original snapshot.

Databricks documents phantom-read conflicts for both WriteSerializable and Serializable transaction behavior. Databricks — Transactions

15. Metadata conflicts​

Not all conflicts are about data rows.

Metadata changes can include:

  • schema changes
  • table property changes
  • protocol changes
  • feature enablement
  • feature removal

A metadata change can cause concurrent operations to fail.

For example:

Job A → INSERT Job B → ALTER TABLE ADD COLUMNS

The schema/protocol transition can invalidate assumptions made by Job A.

Databricks recommends changing table properties when concurrent writes are not running. Table properties reference

16. Protocol conflicts​

A table feature can change the protocol requirements.

Conceptually:

Writer A uses protocol vX Writer B upgrades table feature ↓ protocol changes ↓ Writer A may need to restart/retry

A ProtocolChangedException can occur when a table protocol is upgraded while another operation is working.

Protocol changes are therefore platform-level events, not ordinary row-level updates. Delta concurrency control

17. Row-level concurrency​

Modern Databricks supports row-level concurrency for qualifying tables.

The key benefit is that two operations affecting different rows in the same underlying files can have fewer conflicts than traditional file-level conflict detection.

Conceptually:

Traditional file-level model File F ├── row A ├── row B └── row C ↓ update A + update C may conflict

With row-level conflict detection, the system can reason at a finer granularity for supported workloads.

Databricks documents different conflict matrices for tables with and without row-level concurrency. Databricks — Row-level concurrency

18. Row-level concurrency is not magic​

It does not mean every concurrent write is conflict-free.

Metadata changes remain special.

Feature requirements and table design still matter.

There can also be performance trade-offs because finer-grained conflict detection adds work.

Architect rule: design for low-conflict workloads first; treat row-level concurrency as an additional capability, not a substitute for workload design.

19. Conflict exceptions​

Common exceptions include:

ConcurrentAppendException​

A concurrent operation added files in data your operation reads.

ConcurrentDeleteReadException​

A concurrent operation deleted a file your transaction read.

ConcurrentDeleteDeleteException​

Two operations attempted to delete/rewrite overlapping files.

MetadataChangedException​

Concurrent metadata changed.

ProtocolChangedException​

Concurrent protocol/table-feature changes occurred.

ConcurrentTransactionException​

Commonly associated with multiple streaming queries using the same checkpoint location concurrently.

The exact exception and circumstances should be diagnosed from the operation, table layout, isolation level, and conflicting commit. Delta concurrency control

20. Partitioning can reduce conflicts​

Suppose a table is partitioned by business_date.

Job A:

UPDATE ... WHERE business_date = '2026-11-07'

Job B:

UPDATE ... WHERE business_date = '2026-11-08'

If the operations are restricted to disjoint partitions, conflict probability can be much lower.

However, do not partition blindly by high-cardinality columns. Excessive partitions create many small directories/files and can hurt performance.

Databricks recommends aligning partitioning with common conflict-avoidance predicates when appropriate. Delta concurrency control

21. Explicit predicates matter​

A common MERGE anti-pattern is to specify only the business key:

ON s.id = t.id

If the target table is partitioned by date and country, the engine may need to consider a much larger target area than necessary.

A more targeted condition can include known partition boundaries:

ON s.id = t.id AND t.business_date = '2026-11-07' AND t.country = 'IN'

This can reduce both scan work and concurrency conflicts when those predicates genuinely describe the workload.

Databricks specifically documents explicit partition filters as a conflict-reduction technique. Row-level concurrency

22. Append-only writes are special​

A pure append can often commit without reading the existing table state.

Conceptually:

Source batch ↓ create new files ↓ validate schema/protocol ↓ commit add actions

This is one reason append-heavy Bronze ingestion can scale well.

Once the operation needs to inspect target rows—for example a conditional MERGE—the concurrency characteristics change.

Databricks documents conditions under which INSERT/append operations do not read the target table state before committing. Databricks — Isolation levels

23. Retry strategy​

A concurrency failure is not necessarily a system failure.

For transient write conflicts, an application can often:

  1. Capture the conflict
  2. Wait with bounded backoff
  3. Re-read the latest snapshot
  4. Recompute the change set if necessary
  5. Retry
  6. Stop after a controlled retry limit
  7. Alert if repeated conflicts continue

Do not blindly retry a MERGE without understanding its business semantics.

A retry must be safe and idempotent.

24. Idempotency and retries​

Suppose a source event is processed twice.

Bad design:

retry → duplicate business record

Better design:

deterministic business key + idempotent MERGE/write pattern + transaction semantics

Concurrency control protects the table; application-level idempotency protects the business result.

25. Concurrency architecture for an enterprise lakehouse​

A scalable pattern is:

Source systems ↓ Bronze append-only ↓ Silver controlled upserts ↓ Gold serving tables

Reduce the number of independent writers targeting the same Gold table.

Prefer:

many independent Bronze writers ↓ controlled transformation layer ↓ predictable Gold writer topology

This reduces operational contention and makes failures easier to reason about.

26. Streaming concurrency​

A particularly dangerous anti-pattern is starting two streaming queries with the same checkpoint location.

Conceptually:

Stream A ─┐ ├── same checkpoint ── ✗ Stream B ─┘

Each streaming query should have its own checkpoint location and ownership boundary.

Databricks documents ConcurrentTransactionException for this situation. Delta concurrency control

27. Multi-cluster writes on cloud storage​

Modern Databricks supports concurrent Delta writes across multiple clusters/workspaces, subject to the storage and writer compatibility requirements.

For S3, current Databricks documentation notes that Databricks automatically selects the transaction-log commit mechanism and supports concurrent writes from multiple clusters/workspaces; custom/external writers must use compatible atomic commit coordination. Databricks — Delta Lake S3 limitations

Architect implication: transactional guarantees depend on the entire writer ecosystem, not only the Spark code.

If an external engine writes directly into a Delta table, verify its Delta protocol and commit compatibility before allowing concurrent writes.

28. Multi-table transactions​

Modern Databricks also supports transactions spanning multiple statements and multiple tables when the required catalog-commit capability is enabled.

Conceptually:

BEGIN ATOMIC table A change table B change table C change END

The goal is an all-or-nothing logical transaction boundary across the participating tables.

Databricks documents multi-table transactions and catalog commits separately from ordinary single-table Delta transactions. Databricks — Transactions

29. Concurrency vs performance​

Stronger consistency is not free.

There is a spectrum:

More concurrency ←────────────→ Stronger isolation

WriteSerializable generally offers higher concurrency. Serializable can reject more operations and therefore increase retry pressure or reduce throughput.

Row-level concurrency can reduce conflicts for supported workloads but can add conflict-detection overhead.

Architect decision should therefore consider:

  • business correctness
  • write frequency
  • conflict rate
  • table size
  • partitioning
  • latency SLA
  • retry tolerance
  • downstream expectations

30. Troubleshooting workflow​

When a Delta write fails with a concurrency exception, do not immediately change configuration.

Use this sequence:

Step 1 — Identify the exception​

ConcurrentAppend? ConcurrentDeleteRead? MetadataChanged? ProtocolChanged?

Step 2 — Identify both operations​

Which job/table/task was writing concurrently?

Step 3 — Compare predicates​

Did both operations read or modify the same partitions/files/rows?

Step 4 — Check isolation level​

WriteSerializable or Serializable?

Step 5 — Check table design​

Partitioning? Liquid clustering? Row-level concurrency eligibility?

Step 6 — Check writer topology​

Multiple jobs? Multiple workspaces? External writer?

Step 7 — Decide​

Serialize, narrow predicates, redesign ownership, retry safely, or change isolation only when justified.

31. Hands-on Lab — Simulate competing writers​

Create a Delta table with a business key and date column.

Launch two jobs that update overlapping keys concurrently.

Observe:

  • which operation commits
  • which operation fails
  • exception type
  • table history
  • resulting snapshot

Then repeat with disjoint date predicates.

Compare conflict behavior.

32. Hands-on Lab — Compare isolation levels​

Start with default WriteSerializable.

Create a concurrent INSERT + DELETE scenario where the DELETE starts before the INSERT commits.

Observe the final state and history.

Then configure Serializable in a controlled lab environment:

ALTER TABLE demo.delta_concurrency SET TBLPROPERTIES ('delta.isolationLevel' = 'Serializable');

Repeat the scenario.

The goal is not to memorize the result; it is to understand why the result differs.

33. Hands-on Lab — Idempotent retry design​

Build a small job that:

  1. reads a batch ID
  2. generates deterministic business keys
  3. MERGEs into a target
  4. intentionally retries the same batch
  5. verifies that the business result is unchanged

This connects concurrency handling with production data-engineering correctness.

34. Architect scenario — SAP CDC​

Imagine SAP sends CDC changes into Bronze every few minutes.

Multiple Silver jobs attempt to update the same customer dimension.

Poor architecture:

5 independent jobs ↓ same Silver table ↓ frequent MERGE conflicts

Better architecture:

CDC Bronze ↓ partition/time-window isolation ↓ controlled Silver merge ↓ Gold consumers

The goal is not to eliminate concurrency. The goal is to make concurrency predictable.

35. Architect scenario — Finance close​

During month-end close, Finance expects a customer balance table to be internally consistent.

Ask:

  • Can concurrent updates be tolerated?
  • Does the table need Serializable?
  • Can the workload be partitioned by fiscal period?
  • Can a single controlled writer own the Gold table?
  • What is the retry SLA?
  • What happens to downstream reports during retry?

The correct isolation level is a business decision translated into a technical guarantee.

36. Common mistakes​

Mistake 1 — Assuming Delta means no write failures​

Delta prevents corruption; it does not promise every concurrent write will succeed.

Mistake 2 — Using Serializable everywhere​

Stronger isolation can reduce concurrency.

Mistake 3 — Blind retry loops​

Retries without idempotency can duplicate business effects.

Mistake 4 — Partitioning every table aggressively​

Too many partitions can create small-file and metadata problems.

Mistake 5 — Ignoring metadata changes​

Schema/protocol changes can conflict with normal writes.

Mistake 6 — Two streams sharing a checkpoint​

Each streaming query needs its own checkpoint ownership.

Mistake 7 — Ignoring external writers​

Concurrent transactional safety depends on writer compatibility.

37. Architect decision matrix​

SituationPreferred thinking
Append-only Bronze ingestionKeep high concurrency; avoid unnecessary target reads
Frequent independent updatesReduce overlapping write sets
Strong read/write serial semanticsEvaluate Serializable
Same-file concurrent updatesEvaluate row-level concurrency and table design
Frequent metadata changesEstablish schema/protocol change windows
StreamingUnique checkpoint per query
External Delta writersValidate commit/protocol compatibility
High conflict rateFix workload topology before increasing retries

38. Interview questions​

Foundation​

  1. What is optimistic concurrency control?
  2. What are the three OCC phases?
  3. How does Delta provide atomicity?
  4. What isolation level is used for reads?
  5. What is the default write isolation level?

Engineering​

  1. WriteSerializable vs Serializable?
  2. What is a write-write conflict?
  3. What is a phantom conflict?
  4. What causes ConcurrentAppendException?
  5. What causes MetadataChangedException?
  6. What is row-level concurrency?
  7. Why can a transaction fail after successfully writing Parquet files?
  8. Why are append-only writes often less conflict-prone?

Architect​

  1. How would you design a high-concurrency Delta Silver layer?
  2. When would you choose Serializable?
  3. How would you troubleshoot repeated MERGE conflicts?
  4. How would you design retries?
  5. How would you handle multiple workspaces writing the same table?
  6. What changes when an external engine writes Delta?
  7. How would you translate a business consistency requirement into an isolation-level decision?

39. Production checklist​

  • Understand snapshot isolation for reads.
  • Understand WriteSerializable as the default write isolation.
  • Use Serializable only for a defined business requirement.
  • Identify the actual conflicting operations before tuning.
  • Keep write sets as disjoint as practical.
  • Use explicit predicates where they reduce unnecessary target scans.
  • Avoid high-cardinality partitioning.
  • Evaluate row-level concurrency for qualifying workloads.
  • Give every stream a unique checkpoint location.
  • Make retries bounded and observable.
  • Make business writes idempotent.
  • Treat schema/protocol changes as coordinated events.
  • Validate external writer compatibility.
  • Monitor conflict rate as a production metric.

40. Key takeaway​

The most important Delta concurrency model is:

Readers get consistent snapshots. Writers optimistically build changes, validate against concurrent commits, and either create a new version or fail safely.

Then choose the isolation level according to the business requirement:

WriteSerializable → default, high-concurrency choice Serializable → stronger guarantees, potentially more conflicts

At enterprise scale, the best concurrency strategy is usually not “turn on the strongest isolation.” It is designing ownership, predicates, partitioning, writer topology and idempotency so that conflicts are rare and recoverable.

Official references​