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.