Duckdb: Question: MVCC Concurrency

[asad-awadia]

Question more than an issue - I couldn't find any documentation around expected concurrent usage of duckDB?

SQLite is well known to be avoided in environments where many concurrent writes are expected because of the DB lock

But duckDB uses MVCC - so it should be fine to use any number of writers and readers without exceptions or errors?

What are the limitations around concurrent usage of duckDB?

[Alex-Monahan]

DuckDB can only perform 1 write at a time (single writer), but can accommodate many readers at the same time without throwing exceptions. It is designed to address that SQLite pain point you mentioned! Those writes can be quite fast too.

However, that is only within the same host process. Across multiple host processes, only multiple read-only connections are supported.

[asad-awadia]

so within the same process I can have multiple concurrent writes going to DuckDB and they just get serialised automatically by duckdb?

[Alex-Monahan]

Yes, that is my understanding! Essentially, threads should work fine, but not separate processes. (My experience is in the Python client, but I believe that is true in all clients)

[Mytherin]

What Alex is saying is correct, but is more about the logistics of how to use concurrency in DuckDB. We don't support multiple different writers from different processes connecting to the same database file. Instead, you need to create multiple different writers from the same process (i.e. writer threads). This is supported.

To expand on the concurrency model and concurrency limitations a bit:

DuckDB uses optimistic concurrency control alongside MVCC. What that means is that, as long as there are no write conflicts, multiple concurrent writes work just fine. For example, if you have two threads that update different tables, or different subsets of the same table, there are no problems. There are no global locks or anything of the sort - if two clients write to two separate tables, they will have zero conflicts. There are several local mutexes (there is one mutex for around every 20~80k rows, depending on width of the rows), but these are generally only held for a short time (while performing the physical update). There should never be any deadlocks.

Optimistic concurrency control bites you when there are conflicts between writers, that is to say, when two threads try to update the same row at the same time. When this happens, a concurrency conflict exception will be triggered.

To explain this a bit better, suppose you have two transactions. The first transaction executes this:

-- Transaction #1
BEGIN TRANSACTION;
UPDATE tbl SET val='new_value' WHERE id=3;

Now the transaction has edited the row with an id of 3. After the update statement is completed, no locks are held. However, the row with id 3 is marked as edited by Transaction #1. That means that if any other transaction tries to edit that row, a concurrency conflict will be triggered and that transaction will be aborted. For example:

-- Transaction #2
BEGIN TRANSACTION;
-- No conflict, this works fine!
UPDATE tbl SET val='new_value' WHERE id=2;
-- TransactionContext Error: Conflict on update!
UPDATE table SET val='new_value' WHERE id=3;

This is sort of an "implicit lock", that gets released when Transaction #1 either uses rollback or commit to terminate the active transaction. After Transaction #1 finishes in that way, a new transaction can once again modify the row.

I would add that, despite the fact that we support concurrent writers, DuckDB is not aimed at transactional workloads. That is to say, it is aimed primarily at connections doing bulk operations (e.g. inserting a lot of data, updating a lot of data, deleting a lot of data). It is not optimized for scenarios where you have 20 threads updating individual rows. For that scenario it would be better to use Postgres (or another transactional system).

[Alex-Monahan]

That is super helpful detail! So, if I have multiple threads that could be writing to the same location in DuckDB, I should implement my own locking in my application code?

[Alex-Monahan]

Or, if I were doing append only operations to the same table, would multiple threads be fine without more locks in that case? It would just be updates and deletes that could get me into trouble?

[asad-awadia]

DuckDB is not aimed at transactional workloads. That is to say, it is aimed primarily at connections doing bulk operations (e.g. inserting a lot of data, updating a lot of data, deleting a lot of data). It is not optimized for scenarios where you have 20 threads updating individual rows.

So in the context of a webserver where each request triggers a single new row insert - that is fine?

[Mytherin]

That is super helpful detail! So, if I have multiple threads that could be writing to the same location in DuckDB, I should implement my own locking in my application code?

If they are updating the same rows concurrently, then you could expect a percentage of those transactions to be aborted. You would either need to retry the transaction on abort, or implement locking (retrying is probably easier).

Or, if I were doing append only operations to the same table, would multiple threads be fine without more locks in that case? It would just be updates and deletes that could get me into trouble?

Appends will never conflict. Only deletes or updates can conflict. Appends create new rows, and conflicts can only happen when two transactions touch the same row.

So in the context of a webserver where each request triggers a single new row insert - that is fine?

That should work fine and won't cause any conflicts, as long as you are only doing inserts. However, individual row inserts are not what DuckDB is optimized for. DuckDB is optimized for performing large-scale analytical queries (e.g. aggregations/joins over entire tables). As a webserver backend you are probably looking for an OLTP system, as you are doing individual row inserts, updates and deletes. In that case, you will likely get better performance using e.g. Postgres.