One Writer, and What to Build Around It

Chapter 3 established the rule: many readers or one writer, on the whole file. Chapter 15 showed that inside one process, concurrency is a solved problem — eight threads wrote 1,600 rows through cursors with no errors.

This chapter is about the boundary between those two facts, because that is where designs go wrong. Everything that works inside a process stops working the moment there are two, and the way it stops is worse than “it is slower”. It is an immediate hard failure with no queue behind it.

Everything here was run against DuckDB 1.5.5 with real separate processes on a 2-million-row database file.

Readers are genuinely concurrent

Four processes, each opening the same file read-only and running the same aggregate:

duckdb.connect('conc.duckdb', read_only=True).execute("select sum(d) from t")

reader0 OK   4 ms
reader1 OK   3 ms
reader2 OK   3 ms
reader3 OK   2 ms

All four succeeded, all four were fast, and none waited on any other. This is the genuinely permissive part of the model, and it covers a bigger fraction of analytical work than it sounds. A dashboard, a notebook, a scheduled report and an ad-hoc query can all hit the same file at once.

If nothing is writing, you do not have a concurrency problem.

A reader is refused while a writer holds the file

Now start a writer that keeps the file open, and half a second later open a reader — read-only, promising to touch nothing:

writer  OK    (held 1.5s)
reader9 FAIL  IO Error: Could not set lock on file ".../conc.duckdb"

Read-only was refused. Chapter 3 stated this; here it is across real processes. There is no flag that grants a read while a writer holds the lock, because the lock is on the file and the operating system is the only arbiter.

Two writers, predictably:

writer1 OK
writer2 FAIL  IO Error: Could not set lock on file ".../conc.duckdb": Conflicting lock

Same error, same cause. The lock does not distinguish between “I want to write” and “I want to look” — it distinguishes between exclusive and shared, and a writer takes exclusive.

The part that determines your architecture

Here is the behaviour that decides how you build, and it is easy to assume wrong. When a process cannot get the lock, does it wait?

One process holds the file for four seconds. Another tries to open it:

while a separate process holds it for writing:
    failed after 5 ms: IO Error: Could not set lock on file ".../hold.duckdb"

after it released:
    opened after 12 ms

Five milliseconds, then an error. No waiting, no timeout, no queue.

That is very different from what a client-server database does. Postgres blocks you on a lock and you wait your turn; the queue is invisible and the operation eventually succeeds. DuckDB has no server to hold a queue, so there is nothing to wait in. The call fails.

Two consequences follow directly.

Retrying is your job. If two processes might contend, the losing one must back off and try again in your code. Nothing does it for you, and a bare duckdb.connect() in a job that runs on a schedule will simply fail on the day it overlaps with something else.

Failure is immediate and total. There is no partial degradation to notice in a metric. It either got the lock or it did not, in five milliseconds. The error appears wherever you opened the connection, which is often at startup — before any of your error handling for queries exists.

What survives

Four shapes work. They are all versions of the same idea: make sure only one process ever wants to write.

One writer process. A single job, a single service, a single worker owns the file. Everything else opens read_only=True. This is the default answer and it is usually enough — the four readers above are exactly this.

Separate the write window from the read window. Build overnight, query during the day. The writer finishes and closes; readers have the file to themselves. Simple, and the failure mode is a job that overruns its window, so give the writer a hard deadline and alert on it.

Snapshot by copying. From chapter 3, and the one people forget:

cp analytics.duckdb snapshot.duckdb

A copy reads cleanly while a writer holds the original, and the copy opens freely. You get a point-in-time view rather than a live one, which for “yesterday’s results while today’s job runs” is exactly right. On any modern filesystem the copy is close to free.

Do not persist at all. If a job reads Parquet, computes and writes Parquet, an in-memory database never takes a file lock and cannot contend with anything. Chapter 5 argued you often do not need the file; this is the operational reason.

And inside a process, use what chapter 15 measured: one connection, a cursor per thread. A service that needs concurrent writes should get them from threads, not processes. That works, and it is the difference between a design that scales to your cores and one that fails at two.

What does not

Three designs come up constantly and none of them survive contact with the lock.

A web service with several worker processes writing. Gunicorn with four workers, each opening the same DuckDB file for writing: one wins, three fail at startup with a lock error. This is the most common way people discover the rule, and the fix is not configuration — it is a single writer, or a different database.

A nightly job writing while an analyst queries. The analyst’s read-only connection is refused, and the error tells them nothing about the job. Copy the file for the analyst, or move the job.

Scaling writes by adding processes. There is no version of this that works. Writes do not parallelise across processes here, and adding more just adds failures.

The honest framing: DuckDB’s concurrency model is a deliberate exclusion, not a limitation to work around. It bought the thing the whole book is about — no server, no ports, no provisioning, a database that is a file. That trade is excellent for analytics, where one process builds and many read. It is wrong for a transactional workload with many concurrent writers, and no amount of care makes it right.

When to leave

Two exits, and knowing which one you need is worth more than any workaround.

You need concurrent writers to shared tables. That is OLTP, and it is what Postgres is for. Chapter 1 drew this line, and this chapter is where it becomes concrete rather than theoretical. Do not try to build a transactional service on a file lock.

You need many processes reading and writing one analytical dataset. That is not OLTP, and the answer is not Postgres. It is a table format with real commits — chapter 6’s DuckLake, where the metadata lives in a database that can arbitrate and the data files are ordinary Parquet. With the right catalog behind it, several processes can write at once, and the next chapter measures exactly how well. One warning to carry there: a DuckLake whose catalog is itself a .ducklake file inherits this chapter’s lock unchanged, because that file is a DuckDB database.

Final thoughts

The lock is the sharpest edge in DuckDB, and everything about it is a consequence of one design decision. No server means no coordinator, which means the filesystem arbitrates, and a filesystem lock is blunt: exclusive or shared, whole file, nothing in between.

The measurement to keep is the five milliseconds. A blocked process does not wait — it fails immediately, and there is no queue behind it. Every other database you have used blocks and eventually succeeds, so this is the assumption most likely to be wrong in a design you have already sketched.

Build around one writer and everything above is a non-issue. Build around several and there is no configuration that saves it.

Next: Past the Edge of One Machine — MotherDuck, DuckLake as a shared catalog, and the remote protocol that undoes the “strictly in-process” framing this book opened with.