can I open duckdb file in read-only mode while other process writing to the the same database file?

Question

I am running simple script which creates db file, creates table and doing inserts into that table (see below).

While the script is running, I am trying to use duckdb cli tool to connect as readonly to the same db file.

duckdb -readonly db.db

It gave me the error: Error: unable to open database "db.db": IO Error: Could not set lock on file "db.db": Resource temporarily unavailable

Is it a bug or feature of duckdb? Can anyone explain the meaning of 'read-only' from duckdb point of view?

import duckdb, time
import numpy as np

if __name__ == "__main__":
    conn = duckdb.connect("db.db", read_only = False)
    conn.sql("create table if not exists quotes (ts float, bid float, ask float)")
    ts = 0
    mq = 20000.0
    ts = 0
    while True:
        mq += np.random.rand()
        conn.execute("insert into quotes values(?, ?, ?)", [ts, mq - 100, mq + 100])
        ts += 1
        time.sleep(1)

    conn.close()

Answer 1

This is a design decision of DuckDB.

How does DuckDB handle concurrency?

DuckDB has 2 configurable options for concurrency. 1. One process can both read and write to the database. 2. Multiple processes can read from the database, but no processes can write (access_mode = 'READ_ONLY'). When using option 1, DuckDB does support multiple writer threads using a combination of MVCC (Multi-Version Concurrency Control) and optimistic concurrency control (see below), but all within that single writer process.

So, you can have multiple read-only processes read the database, but one and only one connection when it is writable. It is an exclusive lock for both read and write on the whole database.