Transactions and Concurrency

DuckDB is an in-process analytical engine. A single database file supports full ACID transactions, and DuckDB enforces a simple concurrency model: one writer at a time, many concurrent readers.

ACID Transactions

BEGIN TRANSACTION;
INSERT INTO orders VALUES (1, 'widget', 9.99);
INSERT INTO orders VALUES (2, 'gadget', 19.99);
COMMIT;

BEGIN TRANSACTION;
UPDATE orders SET price = 0 WHERE id = 2;
ROLLBACK;

If the process crashes mid-transaction, DuckDB recovers through its write-ahead log: committed data survives, uncommitted changes are discarded.

Single Writer, Multiple Readers

A write transaction locks the database file, but other connections can still run read queries against the last committed state. A second writer trying to start its own write transaction waits, or raises a concurrency error if it cannot acquire the lock in time.

Read-Only and Multi-Process Access

Any number of processes can open the same file read-only at the same time. That is the recommended pattern for serving dashboards from a file that one writer process maintains:

import duckdb

ro = duckdb.connect("mydb.duckdb", read_only=True)
print(ro.sql("SELECT count(*) FROM events").fetchone())

Practical Tips

• Keep write transactions short; batch INSERTs instead of committing row by row.
• For parallel ingestion, write from multiple processes into separate files, then merge with a final INSERT ... SELECT or ATTACH.
• Use explicit BEGIN/COMMIT for multi-statement work rather than relying on autocommit.

Summary

DuckDB gives you ACID transactions with WAL recovery, a single-writer/multi-reader concurrency model, and safe multi-process read-only access to the same file. Use BEGIN/COMMIT/ROLLBACK for explicit transactions, open read_only connections for readers, and design ingestion pipelines to avoid writer contention.