11. Concurrency and transactions

This is the chapter that decides whether DuckDB is the right tool for what you are building. Everything else is performance; this is capability.

The two levels

Concurrency in DuckDB means two completely different things depending on where the boundary is, and conflating them causes most of the confusion.

   INSIDE ONE PROCESS
   ────────────────────────────────────────
   Many connections to the same database.
   Full MVCC. Readers never block writers.
   Multiple concurrent writers, resolved
   optimistically.

   → This works well and is not the
     limitation people mean.


   ACROSS PROCESSES
   ────────────────────────────────────────
   ONE process may hold the database file
   for writing. Others get an error.

   With NO writer, many processes may open
   it read-only, simultaneously.

   → This IS the limitation, and there is
     no server to fix it.

Inside one process

con = duckdb.connect('analytics.duckdb')

c1 = con.cursor()   # an independent connection,
c2 = con.cursor()   # same database, own transaction

Each cursor is a separate connection with its own transaction state. They can run concurrently, on different threads, and MVCC (chapter 3) gives each a consistent snapshot.

   TIME →

   c1: BEGIN ─── SELECT count(*) ──── COMMIT
                   sees 1,000,000

   c2:    BEGIN ── INSERT 500 rows ── COMMIT
                                    ↑
   c1's second SELECT, in the SAME transaction,
   still sees 1,000,000. Snapshot isolation.

Concurrent writers, optimistically

Two transactions writing at once do not take locks up front. They proceed, and conflicts are detected at commit.

   c1: BEGIN; UPDATE t SET x=1 WHERE id=7;
   c2: BEGIN; UPDATE t SET x=2 WHERE id=7;
   c1: COMMIT;   ✓
   c2: COMMIT;   ✗ TransactionContext Error:
                    Could not commit — conflict
                    on table "t"

The conflict granularity is coarser than you might expect. It is not per-row in all cases: concurrent transactions that both modify the same table can conflict even on different rows, depending on what they touched. DuckDB is not designed to be a high-contention transactional store, and it does not pretend to be.

   THE RULES THAT MATTER

   ✓ appends to the same table from multiple
     transactions: generally fine
   ✗ two transactions updating the same row:
     the second fails at commit
   ✗ two transactions altering the same
     table's schema: fails
   ✗ any transaction against a table another
     transaction dropped: fails

   The loser gets an error and must RETRY.
   Nothing is queued for it.

Write your application to catch the conflict and retry. That is the contract, and it is the same contract every optimistic-concurrency system offers.

Across processes — the file lock

   $ python -c "import duckdb; duckdb.connect('db.duckdb'); input()"
     ← holds the write lock

   $ duckdb db.duckdb
   Error: IO Error: Could not set lock on file
   "db.duckdb": Conflicting lock is held in
   /usr/bin/python3 (PID 48210)

The error names the process holding it, which is a small kindness that saves a lot of time.

   WHO CAN OPEN WHAT

   writer open, then...
     another writer      ✗ blocked
     a reader (read_only) ✗ blocked

   NO writer, then...
     reader, reader,
     reader, reader...   ✓ all fine

con = duckdb.connect('analytics.duckdb', read_only=True)

Many read-only processes over one file is fully supported and is the shape most DuckDB deployments end up in: a nightly job writes the file, and any number of dashboards, APIs and analysts read it.

The four patterns

When you need concurrency DuckDB does not give you directly, these are the options, in order of how often they are the right answer.

1. Files are the shared state, not the database

   ┌──────────┐  ┌──────────┐  ┌──────────┐
   │ process  │  │ process  │  │ process  │
   │ + own    │  │ + own    │  │ + own    │
   │ DuckDB   │  │ DuckDB   │  │ DuckDB   │
   └────┬─────┘  └────┬─────┘  └────┬─────┘
        └─────────────┼─────────────┘
                      ↓
         PARQUET in object storage, or an
         ICEBERG table

This is chapter 2’s architecture and it is the answer most of the time. Nobody holds a lock, because nobody owns the data. Writers produce new files; readers read them. If you need atomic visibility of new writes, Iceberg is exactly the mechanism for it — one pointer swap, and every reader sees the new snapshot or the old one, never a mixture.

2. One writer process, many read-only readers

   ┌─────────────────┐
   │  ingest service │  the ONLY process that
   │  (writer)       │  opens read/write
   └────────┬────────┘
            │ writes
            ↓
      analytics.duckdb
            ↑
   ┌────────┴────────┬─────────────┐
   │ read_only       │ read_only   │  many
   │ API worker      │ dashboard   │
   └─────────────────┴─────────────┘

The catch: readers that opened the file see the state at open time. A read-only connection does not pick up the writer’s commits — it has an old snapshot and, in the worst case, the writer’s checkpoint has moved blocks out from under it. Readers must reconnect to see new data, and the robust version is:

   write to  analytics-new.duckdb
   then      mv analytics-new.duckdb analytics.duckdb
   readers   reconnect on a schedule, or on a
             file-mtime change

An atomic rename gives you the same “swap one pointer” guarantee Iceberg gives, at file granularity.

3. Serialise writes through a queue

   many workers → a queue → ONE writer thread
                             → DuckDB

If writes are small and infrequent, funnel them through a single owner. This is the pattern for an application that mostly reads and occasionally records something, and it is a dozen lines of code.

4. Accept that you want a server

If you have genuinely concurrent writers from multiple processes, or many concurrent users each running heavy queries, DuckDB is the wrong tool and no pattern fixes that.

   → many concurrent analytical users
        [ClickHouse](/clickhouse/01-what-and-why/),
        a warehouse, [Trino](/trino/01-what-and-why/)

   → concurrent transactional writes
        [PostgreSQL](/postgresql/01-what-and-why/)

   → you want DuckDB's engine with a server
     in front of it
        MotherDuck, or an HTTP layer you
        write yourself around pattern 3

Chapter 15 makes this the deciding question, because it is the one that most reliably separates “DuckDB is perfect here” from “DuckDB is going to hurt in six months.”

Transactions, in practice

BEGIN TRANSACTION;
  DELETE FROM staging WHERE batch_id = 42;
  INSERT INTO staging SELECT * FROM read_parquet('batch-42/*.parquet');
COMMIT;
-- or ROLLBACK;

   ✓ full ACID within the process
   ✓ DDL is transactional — CREATE TABLE,
     ALTER, DROP all roll back. Postgres has
     this; MySQL does not.
   ✓ snapshot isolation

   ✗ no SERIALIZABLE isolation level to
     choose; snapshot isolation is what
     there is
   ✗ no SELECT ... FOR UPDATE
   ✗ no advisory locks
   ✗ no savepoints
   ✗ no two-phase commit

Transactional DDL is worth noticing. A migration that creates three tables and fails on the third leaves nothing behind, which makes DuckDB unusually pleasant as a target for schema migrations in tests.

Autocommit is on by default. Every statement outside an explicit BEGIN is its own transaction — which means a loop of ten thousand INSERT statements is ten thousand transactions, ten thousand WAL appends and ten thousand fsyncs. Wrap bulk work in an explicit transaction, or better, do it in one statement.

# ✗ ten thousand transactions
for row in rows:
    con.execute("INSERT INTO t VALUES (?, ?)", row)

# ✓ one
con.executemany("INSERT INTO t VALUES (?, ?)", rows)

# ✓✓ best — no round trip at all
con.execute("INSERT INTO t SELECT * FROM df")

Chapter 12 has more on that last line, which is the one that is a hundred times faster than the first.

The honest summary

   DuckDB's concurrency model is:

     ONE PROCESS owns the data.
     Inside it, do what you like.
     Outside it, share FILES, not the
     database.

   That is not a limitation to work around.
   It is the design, and the architecture
   it pushes you towards — disposable
   engines over shared files in object
   storage — is the same one Trino, Spark
   and every lakehouse converged on for
   different reasons.

What to take from this chapter

Two different things called concurrency: inside one process (full MVCC, many connections, concurrent writers — works well) and across processes (one writer, and that is the real limit).
Inside a process, writers conflict optimistically at commit, and the granularity is coarser than per-row. Catch the conflict and retry — nothing is queued for the loser.
The file lock is exclusive to a writer, and the error names the PID holding it. With no writer, unlimited read-only processes can open the same file.
Four patterns, in order: share files not the database (Parquet or Iceberg — the right answer most of the time); one writer, many read-only readers (with an atomic rename so readers see a consistent swap); serialise writes through a queue; and accept that you want a server.
Read-only connections do not see later commits — they must reconnect.
Transactions are ACID with snapshot isolation and transactional DDL, which makes it a pleasant migration target. There is no SERIALIZABLE, no FOR UPDATE, no savepoints, no advisory locks.
Autocommit means a loop of inserts is a transaction per insert. Use executemany, or a single INSERT ... SELECT from a DataFrame.
The design is one process owns the data; share files, not the database — which is the same architecture every lakehouse converged on.

Next: the clients — Python and zero-copy Arrow, the CLI, WASM in the browser, dbt, and MotherDuck.

Written by Sepuri Sai Krishna