Can I read and write to a SQLite database concurrently from multiple connections?

Question

I have a SQLite database that is used by two processes. I am wondering, with the most recent version of SQLite, while one process (connection) starts a transaction to write to the database will the other process be able to read from the database simultaneously?

Answer 1

I collected information from various sources, mostly from sqlite.org, and put them together:

First, by default, multiple processes can have the same SQLite database open at the same time, and several read accesses can be satisfied in parallel.

In case of writing, a single write to the database locks the database for a short time, nothing, even reading, can access the database file at all.

Beginning with version 3.7.0, a new “Write Ahead Logging” (WAL) option is available, in which reading and writing can proceed concurrently.

By default, WAL is not enabled. To turn WAL on, refer to the SQLite documentation.

Answer 2

SQLite3 explicitly allows multiple connections:

(5) Can multiple applications or multiple instances of the same application access a single database file at the same time?

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

For sharing connections, use SQLite3 shared cache:

Starting with version 3.3.0, SQLite includes a special "shared-cache" mode (disabled by default)

In version 3.5.0, shared-cache mode was modified so that the same cache can be shared across an entire process rather than just within a single thread.

5.0 Enabling Shared-Cache Mode

Shared-cache mode is enabled on a per-process basis. Using the C interface, the following API can be used to globally enable or disable shared-cache mode:

int sqlite3_enable_shared_cache(int);

Each call sqlite3_enable_shared_cache() effects subsequent database connections created using sqlite3_open(), sqlite3_open16(), or sqlite3_open_v2(). Database connections that already exist are unaffected. Each call to sqlite3_enable_shared_cache() overrides all previous calls within the same process.

Answer 3

I had a similar code architecture as you. I used a single SQLite database which process A read from, while process B wrote to it concurrently based on events. (In python 3.10.2 using the most up to date sqlite3 version). Process B was continually updating the database, while process A was reading from it to check data. My issue was that it was working in debug mode, but not in "release" mode.

In order to solve my particular problem I used Write Ahead Logging, which is referenced in previous answers. After creating my database in Process B (write mode) I added the line:

cur.execute('PRAGMA journal_mode=wal') where cur is the cursor object created from establishing connection.

This set the journal to wal mode which allows for concurrent access for multiple reads (but only one write). In Process A, where I was reading the data, before connecting to the same database I included:

time.sleep(0.5)

Setting a sleep timer before a connection was made to the same database fixed my issue with it not working in "release" mode.

In my case: I did not have to manually set any checkpoints, locks, or transactions. Your use case might be different than mine however, so research is most likely required. Nevertheless, I hope this post helps and saves everyone some time!