Handling Concurrency

DuckDB has two configurable options for concurrency:

1. Read-write mode: one process can both read and write to the database.
2. Read-only mode: multiple processes can read from the database, but no processes can write (access_mode = 'READ_ONLY').

When using read-write mode, DuckDB supports multiple writer threads using a combination of MVCC (Multi-Version Concurrency Control) and optimistic concurrency control (see Concurrency within a Single Process), but all within that single writer process. The reason for this concurrency model is to allow for the caching of data in RAM for faster analytical queries, rather than going back and forth to disk during each query. It also allows the caching of function pointers, the database catalog, and other items so that subsequent queries on the same connection are faster.

DuckDB is optimized for bulk operations, so executing many small transactions is not a primary design goal.

Concurrency within a Single Process

DuckDB supports concurrency within a single process according to the following rules. As long as there are no write conflicts, multiple concurrent writes will succeed. Appends will never conflict, even on the same table. Multiple threads can also simultaneously update separate tables or separate subsets of the same table. Optimistic concurrency control comes into play when two threads attempt to edit (update or delete) the same row at the same time. In that situation, the second thread to attempt the edit will fail with a conflict error.

Writing to DuckDB from Multiple Processes

Writing to DuckDB's native database format from multiple processes is not currently supported (see Handling Concurrency).

If you would like to have read-write access to the same database, consider storing it in the DuckLake format with PostgreSQL as the catalog database. By coordinating through a central PostgreSQL database, you can achieve concurrent read-writes on the same database.

Optimistic Concurrency Control

DuckDB uses optimistic concurrency control, an approach generally considered to be the best fit for read-intensive analytical database systems as it speeds up read query processing. As a result any transactions that modify the same rows at the same time will cause a transaction conflict error:

Transaction conflict: cannot update a table that has been altered!

Tip A common workaround when a transaction conflict is encountered is to rerun the transaction.

Troubleshooting

File locks. DuckDB handles concurrent database access requests using file locks. Exercise extra caution when accessing a DuckDB database file in a shared directory (e.g., from different operating systems using different file systems or on network attached storage).