DuckDB: “Could not set lock on file”

Q: How do I see which process has a DuckDB file open on a Mac?

Run lsof mydata.duckdb in Terminal. It lists each program holding the file and its process ID, the same ID the error message gives.

Another program already has the database open. Nothing is wrong with the file. DuckDB lets one program write to a database at a time, and this is it refusing a second.

IO Error: Could not set lock on file "/Users/you/mydata.duckdb":
Conflicting lock is held in /opt/homebrew/Cellar/duckdb/1.5.5/bin/duckdb
(PID 30205) by user you.
See also https://duckdb.org/docs/stable/connect/concurrency

The rule behind it

One writer, and while it has the file, nobody else gets in, not even to read. Opening read-only gets the same error.
Any number of readers can share the file, as long as every one of them opened it read-only.
A reader keeps writers out. When that is what happened, the error adds “However, you would be able to open this database in read-only mode”.

Find what has it open

The error names the program and its process ID. If it has scrolled away, ask macOS:

lsof mydata.duckdb

The usual culprits:

A notebook. A Jupyter kernel keeps its connection open after the cell finishes. Call con.close(), or restart the kernel.
A duckdb shell still running in another terminal tab. Type .quit there.
A database app with the file open, Datapuddle included. Close the database in it.
A script that is stuck or still running. If you cannot stop it any other way, kill 30205 with the ID from the error. DuckDB recovers committed work from its write-ahead log the next time the file is opened; the transaction that was in progress is lost.

If you only need to read

Open it read-only, and make sure the other program did too:

duckdb -readonly mydata.duckdb

In Python that is duckdb.connect("mydata.duckdb", read_only=True), and from inside DuckDB, ATTACH 'mydata.duckdb' (READ_ONLY). Two read-only programs can share the file all day. One writer anywhere and both are shut out.

If two programs both need to write

DuckDB is not built for that, and no setting changes it. What works instead:

Send every write through one process, and let everything else read from what it produces.
Have the writer export what others need, for example COPY orders TO 'orders.parquet'. Any number of programs can read a Parquet file, and none of them can lock it.
Copy the database when nothing is writing to it, and point readers at the copy.

If many writers really do have to share live data, that is a job for a server database such as Postgres.

In Datapuddle

Datapuddle follows the same rule, and does the sensible thing within it. It opens a database for writing, and if that is refused it tries again read-only:

Another program is reading: Datapuddle opens the file read-only alongside it.
Another program is writing: Datapuddle cannot open it either, for the reason above. Close the other program first.
Datapuddle has it open: your scripts get this error. Close the database in Datapuddle, or open it in the script read-only.

7-day trial, then $39.99 once · Apple silicon, macOS 26 or later

Questions

Does “Could not set lock on file” mean my DuckDB file is corrupted?

No. The file is fine. Another program has it open, and DuckDB is protecting it from two writers at once.

Why does read-only mode still give the lock error?

Because the other program has the file open for writing. A writer keeps everyone out, readers included. Read-only only helps when every other program is reading too.

How do I see which process has a DuckDB file open on a Mac?

Run lsof mydata.duckdb in Terminal. It lists each program holding the file and its process ID, the same ID the error message gives.

Can two Python scripts write to the same DuckDB file?

Not at the same time from separate processes. Have one process do the writing, or have each script write its own file and combine them afterwards.

Datapuddle on the Mac App Store — 7 day trial, then $39.99 once. Apple silicon, macOS 26 or later. · Docs Guides · Home