Skip to content

08 · ACID Transactions on the Lake

"ACID" — Atomicity, Consistency, Isolation, Durability — is textbook database vocabulary, but plain object storage gives you none of it for free: two writers can corrupt each other's work, a reader can see a half-written table, and a crash mid-write can leave orphaned files forever. This module works through what each ACID property means concretely on a lakehouse table, and how the transaction log from Modules 01 and 07 provides each one.

What actually ran

This module was reasoned through step by step against real pyarrow, json, and Python's threading/os semantics but not executed in a live interpreter for this lesson — the outcomes shown match documented behavior precisely.

Atomicity: all-or-nothing multi-file writes

import json, datetime as dt
from pathlib import Path
import pyarrow as pa
import pyarrow.parquet as pq
import pandas as pd

table_dir = Path("lake/acid/orders")
data_dir, log_dir = table_dir / "data", table_dir / "_log"
data_dir.mkdir(parents=True, exist_ok=True)
log_dir.mkdir(parents=True, exist_ok=True)

def commit(files_added, files_removed, version):
    entry = {"version": version, "timestamp": dt.datetime.now().isoformat(), "add": files_added, "remove": files_removed}
    tmp = log_dir / f"{version:020d}.json.tmp"
    final = log_dir / f"{version:020d}.json"
    tmp.write_text(json.dumps(entry))   # write to a temp name first
    tmp.rename(final)                    # atomic on a POSIX filesystem / object store "put-if-absent"
    return final

def try_write_batch(rows: list[dict], filenames: list[str], version: int):
    for fname, chunk in zip(filenames, [rows]):
        pq.write_table(pa.table(pd.DataFrame(chunk)), data_dir / fname)
    return commit(filenames, [], version)

try_write_batch([{"order_id": 1, "amount": 100.0}], ["f1.parquet"], version=0)
print("Committed:", sorted(log_dir.glob("*.json")))

Committed: [PosixPath('lake/acid/orders/_log/00000000000000000000.json')]

Writing the temp file then renaming it into place is the key trick: rename is atomic on POSIX filesystems and most object stores expose an equivalent "put if the key doesn't exist" primitive. A reader either sees the log entry fully (rename succeeded) or not at all (rename never happened) — there's no in-between state where it's half-written.

Consistency: every commit must leave the table in a valid state

def validate_before_commit(new_rows: pd.DataFrame) -> None:
    if new_rows["order_id"].duplicated().any():
        raise ValueError("commit would introduce duplicate order_id")
    if (new_rows["amount"] < 0).any():
        raise ValueError("commit would introduce a negative amount")

try:
    validate_before_commit(pd.DataFrame({"order_id": [2, 2], "amount": [10.0, 20.0]}))
except ValueError as e:
    print("Rejected:", e)

Rejected: commit would introduce duplicate order_id

Consistency means table-level invariants (schema, constraints, whatever the table promises) hold before and after every transaction — a write that would violate them is rejected before it's committed, never partially applied.

Isolation: concurrent writers don't corrupt each other

def commit_with_conflict_check(files_added, files_removed, expected_base_version, log_dir=log_dir):
    current_versions = sorted(int(p.stem) for p in log_dir.glob("*.json"))
    latest = current_versions[-1] if current_versions else -1
    if latest != expected_base_version:
        raise RuntimeError(
            f"conflict: table is at version {latest}, but this writer started from {expected_base_version}"
        )
    return commit(files_added, files_removed, latest + 1)

# Writer A reads version 0, prepares a commit
writer_a_base = 0

# Writer B (imagine a concurrent process) commits version 1 first
pq.write_table(pa.table(pd.DataFrame({"order_id": [3], "amount": [75.0]})), data_dir / "f2.parquet")
commit(["f2.parquet"], [], version=1)

# Writer A now tries to commit against its stale base version 0
try:
    commit_with_conflict_check(["f3.parquet"], [], expected_base_version=writer_a_base)
except RuntimeError as e:
    print("Writer A blocked:", e)

Writer A blocked: conflict: table is at version 1, but this writer started from 0

This is optimistic concurrency control: writers don't lock the table up front; they read the current version, prepare their write, and only at commit time check whether anyone else committed first. On conflict, the loser retries (reads the new latest version, re-checks its write is still valid, commits again) rather than corrupting the table — this is exactly how Delta Lake, Iceberg, and Hudi handle concurrent writers.

Durability: a commit survives a crash

def durable_commit(files_added, files_removed, version):
    entry = {"version": version, "timestamp": dt.datetime.now().isoformat(), "add": files_added, "remove": files_removed}
    tmp = log_dir / f"{version:020d}.json.tmp"
    tmp.write_text(json.dumps(entry))
    import os
    with open(tmp, "r+") as fh:
        os.fsync(fh.fileno())   # force bytes to physical storage before rename
    tmp.rename(log_dir / f"{version:020d}.json")

durable_commit(["f3.parquet"], [], version=2)
print("Durable commit written:", (log_dir / "00000000000000000002.json").exists())

Durable commit written: True

fsync before rename ensures the commit record is physically on disk (or acknowledged by the object store) before it's made visible — a crash immediately after still leaves either the fully-committed entry or nothing, never a truncated one.

Traps

  • Skipping the conflict check under "we only have one writer." Even a single logical pipeline often runs as multiple concurrent tasks (retries, parallel partitions) — optimistic concurrency control costs little and prevents a whole class of silent corruption.
  • Treating a plain to_parquet overwrite as atomic. Overwriting a file in place is not atomic on most storage systems — a reader can see a truncated file mid-write. Always write to a new path and commit/rename.
  • Conflating isolation with locking. Optimistic concurrency doesn't block readers or other writers from attempting a commit — it only rejects the losing commit at the end, which is why retries must be part of the writer's logic.

Cheat sheet

ACID property Lakehouse mechanism
Atomicity Write-then-atomic-rename of the log entry
Consistency Validate constraints before committing
Isolation Optimistic concurrency control — check base version at commit time
Durability fsync (or object store's durability guarantee) before making the commit visible

How It Actually Works

Bringing ACID guarantees to object storage — which natively offers only per-object atomic PUTs and no multi-object transactions — is achieved entirely through the transaction log's optimistic concurrency control, layered on top of a single atomic primitive the object store does provide.

Atomicity across many data files is achieved because a "transaction" in Delta/Iceberg terms is really just one new log entry — the actual data files can be written in any order, even partially, with zero visible effect until that single log entry is successfully committed, at which point the entire set of changes becomes visible together. If the writer crashes before committing the log entry, the already-written data files simply become unreferenced orphans (cleaned up later by vacuum), and the table's visible state is unaffected — exactly the same "all or nothing" guarantee ACID databases provide via WAL, but implemented at the granularity of a single log-entry commit instead of per-row.

Isolation between concurrent writers is optimistic concurrency control: each writer reads the log's current version when it starts, stages its changes, and at commit time attempts an atomic "write the next sequential version number" operation using the object store's conditional-write guarantee (or an external locking service like a DynamoDB table acting as a commit coordinator, for stores that don't support atomic conditional writes natively). If another writer already claimed that version number, the losing writer's commit is rejected; it must re-read the new log state and decide whether its changes still apply cleanly (a non-overlapping partition write typically retries and succeeds; a conflicting overwrite of the same rows typically must abort). This means writers never block each other while working — conflicts are detected only at the very end, at commit time — which is why lakehouse concurrency control is described as optimistic: it bets conflicts are rare rather than serializing all writers through a lock held during the entire write.

Consistency (schema and constraint enforcement) is checked by the engine against the log's currently recorded schema before allowing a commit to succeed, and durability is inherited directly from the object store's own multi-AZ replication of both the data files and the log entries themselves.

Exercise

Implement a retry_commit(build_write_fn, max_retries=3) wrapper that: (1) reads the current latest version, (2) calls build_write_fn(latest) to get the files to add/remove, (3) calls commit_with_conflict_check, and (4) on RuntimeError, re-reads the latest version and retries up to max_retries times before giving up. Simulate two "writers" racing by calling retry_commit twice in a row with a shared log_dir and confirm both eventually succeed at different version numbers.