DDIA Chapter 7: Transactions — How We Chose ClawMetry’s Isolation Levels

Chapter 7 of Designing Data-Intensive Applications is titled “Transactions.” If Chapter 3 taught me to pick the right storage engine for the right query shape, Chapter 7 taught me to pick the right isolation level for the right concurrency shape. The two lessons are related: you can’t reason about isolation without knowing what the underlying store does when it reads and writes data.

When we built ClawMetry’s data layer, we made four distinct isolation decisions. At the time, I couldn’t fully articulate why each was right. Reading Chapter 7 gave me the vocabulary. Here’s how it maps to the real code.

The isolation level hierarchy DDIA lays out

Kleppmann walks through the classic hierarchy of database isolation levels, from weakest to strongest:

1. Read uncommitted — a reader can see writes that haven’t been committed yet (dirty reads). Almost no one uses this deliberately.
2. Read committed — readers only see committed data, but the same row might read differently within a single transaction if another writer commits in between (non-repeatable reads).
3. Snapshot isolation / repeatable read — readers see a consistent snapshot of the database as of their transaction’s start time. Write skew is still possible: two transactions can both read the same data, reach a decision based on it, then write different records that conflict with each other’s premise.
4. Serializable — transactions execute as if they ran one at a time. No anomalies. Highest cost.

Each step up the hierarchy costs throughput. Serializable isolation typically means either two-phase locking (2PL), which blocks concurrent writers, or serializable snapshot isolation (SSI), which detects conflicts at commit time and aborts one of the transactions. The question Kleppmann forces you to ask is: which anomalies does your workload actually produce, and how much are you willing to pay to prevent them?

Decision 1: Serializable writes for ingest

The ClawMetry sync daemon (clawmetry/sync.py) is the only process that writes to the DuckDB store. Not “the primary writer” — the only writer. It holds the DuckDB file’s exclusive write lock for the entire session.

This is the strongest possible write isolation: serial execution. No two ingest passes run concurrently; the daemon queues them. You cannot get write skew, phantom reads, or dirty writes on the ingest path because there’s only one writer and it runs one pass at a time.

We didn’t arrive at this by carefully reading DDIA. We arrived at it because DuckDB’s architecture forces it: DuckDB does not support concurrent writers from separate processes. The file-level write lock is the mechanism. DDIA Chapter 7 gave me the language to explain why this is not a limitation but an architectural choice: we accepted the throughput cost of serial writes in exchange for zero write conflicts and zero isolation anomalies on the write path. For our workload — a background daemon ingesting append-only agent events in batches every few seconds — a single serial writer is the right tradeoff. We are not a high-write-throughput OLTP system.

# clawmetry/sync.py — the daemon is the only writer
# DuckDB holds the file-level write lock for the process lifetime.
# No other process may open a write connection while this runs.

def _run_ingest_pass(config, conn):
    # One writer, one connection, one pass at a time.
    # Write skew: impossible. Phantom reads on writes: impossible.
    # Cost: no concurrent ingest from multiple sources.
    _ingest_jsonl_sessions(config, conn)
    _ingest_gateway_events(config, conn)
    _ingest_otlp_batches(config, conn)
    _ingest_keepalive_heartbeat(config)  # never starve the relay

Decision 2: Snapshot isolation for reads

The dashboard and all HTTP route handlers open read-only DuckDB connections through a lightweight localhost proxy (clawmetry/local_server.py). They connect to the same .duckdb file but never acquire the write lock.

DuckDB’s MVCC implementation (multi-version concurrency control) means these read connections see a consistent snapshot of the database as of when they opened their connection. A slow API handler that takes 500ms to aggregate 90 days of session data sees a stable snapshot for the entire duration of that query, even if the daemon commits a new ingest pass while the query runs.

This is snapshot isolation, Chapter 7’s third tier. It gives us consistent reads at no write-blocking cost. The dashboard never blocks ingest; ingest never blocks the dashboard. The proxy architecture is the implementation mechanism:

# clawmetry/local_server.py
# Opened read-only: conn = duckdb.connect(db_path, read_only=True)
# Each request gets a fresh read-only connection from the pool.
# Snapshot as of connection time — no dirty reads, no non-repeatable reads.
# Write skew on reads: not applicable (reads don't write anything back).

Chapter 7 also covers why MVCC makes snapshot isolation cheap to implement: instead of blocking readers when a writer commits, the database keeps old row versions and gives each reader the version that was current when they started. Readers never wait on writers. DuckDB does this at the column-chunk level, which fits its columnar layout.

Decision 3: Idempotent ingest instead of transaction rollback

Chapter 7 covers a scenario that comes up whenever you process data in batches: if the process crashes mid-transaction, you roll back and retry from the last checkpoint. In theory, this gives you exactly-once semantics: either the whole batch commits or nothing does.

In practice, for ClawMetry’s ingest path, we made a different choice: idempotent writes instead of transactional rollback. Every ingest pass records a high-water mark (the last-processed event timestamp per source). On restart, the daemon replays from that mark and re-ingests events it may have partially processed before the crash.

Why not rely on transaction rollback? Because our ingest sources are external: JSONL files on disk, a WebSocket connection to the gateway, an OTLP HTTP receiver. A transaction boundary only covers what’s inside DuckDB; it has no authority over the external sources. If we committed 10,000 events and crashed before recording the high-water mark, a rollback would leave us with no record of what we had already processed, and we’d re-ingest the same events on restart.

The Chapter 7 framing for this is the distinction between atomicity (all-or-nothing within the database) and exactly-once processing (which requires coordination with the external source). Atomicity doesn’t solve exactly-once; idempotency does. Our ingest keys events on (source_id, event_ts, event_hash), so re-processing the same event on restart produces the same row with an ON CONFLICT DO NOTHING clause rather than a duplicate:

conn.execute("""
    INSERT INTO session_events (session_id, timestamp, event_hash, model, tokens_in, tokens_out, tool_name)
    SELECT session_id, timestamp, hash(event_json), model,
           event_json->>'$.input_tokens', event_json->>'$.output_tokens',
           event_json->>'$.tool_name'
    FROM read_json_auto(?)
    ON CONFLICT (session_id, event_hash) DO NOTHING
""", [jsonl_path])

We get at-least-once delivery from the ingest loop and idempotent deduplication inside DuckDB. The combination gives us exactly-once semantics at the application layer without relying on distributed transaction coordination that would add latency and complexity to every ingest pass.

Decision 4: Avoiding write skew in budget alerts

This is the one that Chapter 7 made me realize we had gotten right somewhat by accident.

Budget alerts work like this: the daemon periodically checks whether a session’s cumulative token spend has crossed a threshold. If it has, it fires an alert via Slack or PagerDuty. The check-then-act pattern is exactly the scenario Kleppmann uses to illustrate write skew.

Write skew, from DDIA: two transactions both read the same data, each sees a state where some condition holds, each writes based on that condition, and the writes together violate the condition even though neither write was wrong on its own. The classic example is two on-call doctors both seeing “at least two doctors are on call” and both going off call, leaving nobody on call.

The budget alert analog: two alert-check processes both read “total spend for session X is $49.80, threshold is $50.” Both see spend below threshold. One fires; before it records the “alerted” flag, the other fires too. Two alerts are sent for one breach.

We avoided this by design, not by adding serializable isolation to the alert check. Because the sync daemon is the only writer and runs checks serially as part of the ingest pass, there is only ever one alert-check “transaction” running at a time. The single-writer model that prevents write conflicts on ingest also prevents write skew on alerts. Serial execution is write-skew-proof by definition: there is no concurrent transaction to interleave with.

The time-series layer: read committed is enough

The optional history.py module polls the gateway every 60 seconds and stores aggregate summaries in a separate SQLite database. Queries against this store look like: “give me token-per-minute for the last 24 hours, for model X.”

SQLite’s default isolation (WAL mode, read-committed) is the right level here. The query is a range scan over time: all rows where ts BETWEEN now-24h AND now, ordered by time. There is one writer (the poll loop) and it commits one summary row per minute. The read-committed guarantee is enough: each summary row is either in or out of the result set based on whether it was committed when the query ran. A non-repeatable read within a single query is impossible because summary rows don’t change after they’re written.

This is Chapter 7’s point about matching isolation to anomaly risk. We don’t need snapshot isolation for the time-series layer because the write pattern (append-only, one row per minute) doesn’t produce the anomalies that snapshot isolation prevents. Paying for snapshot isolation here would be overhead with no benefit.

The full isolation map

What we gave up

Honest accounting:

• Concurrent ingest from multiple sources. If we had two independent sync processes, they could ingest in parallel and improve throughput. We can’t: one writer, full stop. Our workaround is that the single daemon batches all sources in sequence within one pass, which is fast enough for our update cadence (a few seconds per cycle).
• Cross-store transactions. DuckDB and SQLite are separate databases. There is no transaction spanning both. An alert that writes to DuckDB’s alert_log and updates SQLite’s aggregate counters in the same atomic operation is impossible. We handle this with ordering: DuckDB is written first; SQLite is updated on the next poll cycle. The two stores can be transiently inconsistent by up to one poll interval (60 seconds).
• Fine-grained rollback. If the ingest pass crashes after writing 5,000 events but before committing the high-water mark, we re-process those 5,000 events on restart. The ON CONFLICT DO NOTHING clause makes this harmless but not free — we re-parse and re-hash every event to deduplicate. On a 50,000-event backlog, restart recovery takes a noticeable few seconds.

The meta-lesson from DDIA Chapter 7

Kleppmann’s Chapter 7 is really about one decision: which anomalies does your workload actually produce, and how much isolation do you need to prevent them? Every isolation level above read uncommitted has a cost. The question is whether the anomalies at the lower level actually happen in your system.

For ClawMetry, the decisions fell out naturally from the workload shape:

• Ingest is serial by architecture (DuckDB single-writer), so we get serializability for free.
• Reads are analytical (long-running aggregations), so snapshot isolation via MVCC is the right fit — consistent reads without blocking ingest.
• Alerts use check-then-act logic, so write skew is a real risk — serial ingest mitigates it.
• Time-series history is append-only with point-in-time reads, so read committed is enough.

What I took from Chapter 7: write down the anomalies your workload could produce at each isolation level before choosing one. Don’t default to “serializable for everything” (it’s expensive) and don’t default to “read committed for everything” (it lets write skew through). The chapter gives you the vocabulary to have that conversation explicitly rather than just picking whichever isolation level your ORM defaults to.

For agent observability specifically: your writes are mostly append-only events and your reads are mostly aggregations. That profile pushes you toward snapshot isolation on reads and serial (or serializable) execution on writes. Budget alerts and approval state are the anomaly-sensitive paths; design them explicitly.

Budget alerts in ClawMetry: the check-then-act pattern that write skew would break, protected by serial ingest execution