Using WAL mode with multiple processes

(1.1) By Simon Willison (simonw) on 2020-08-09 20:59:01 edited from 1.0 [source]

The documentation at https://www.sqlite.org/wal.html says that you can start WAL mode using:

PRAGMA journal_mode=WAL;

If I have multiple processes running against the same file - one process that is just reading it, another one (or more) that are writing to it, how should I use this pragma?

If I've run it once in a process, will the other processes automatically pick it up?

Do I need to run the pragma before the other processes open their connections to the database file?

Does it even matter which process first executes the PRAGMA?

If my read-only process executes it, will it affect any other processes that attempt to write to that file?

(2) By Kees Nuyt (knu) on 2020-08-10 01:43:19 in reply to 1.1 [link] [source]

The text for PRAGMA journal_mode=wal

says:

The WAL journaling mode uses a write-ahead log instead of a rollback journal to implement transactions. The WAL journaling mode is persistent; after being set it stays in effect across multiple database connections and after closing and reopening the database. A database in WAL journaling mode can only be accessed by SQLite version 3.7.0 (2010-07-21) or later.

Typically, you set WAL mode during the initial creation of the database, and you never have to look back.

WAL journal mode supports one writer and many readers at the same time. A second writer will have to wait until the first write transaction is committed or rolled back. More info can be found in the WAL documentation

-- 
Regards,
Kees Nuyt

(3) By J. King (jking) on 2020-08-10 01:58:28 in reply to 2 [link] [source]

That doesn't answer the question that was posed, though. The WAL documentation does however state:

The WAL journal mode will be set on all connections to the same database file if it is set on any one connection.

Presumably this is done by incrementing the schema version, or via some similar marker in the database header that SQLite checks before preparing a statement.

(4.1) By Simon Willison (simonw) on 2020-08-10 03:02:23 edited from 4.0 in reply to 3 [link] [source]

That's the bit that confused me - it sounded like it meant that it would change for connections that are currently open, but I wasn't sure if it would affect connections that open later on.

If WAL is actually a persistent property of the database file itself then I guess the answer is that any connection can "turn on" WAL for a database file and it will stay as a WAL database file for the rest of its life (unless another connection turns it off again).

(5) By Richard Damon (RichardDamon) on 2020-08-10 02:55:31 in reply to 4.0 [link] [source]

Yes, WAL mode is a persistent property of the database file itself.

(6) By Simon Willison (simonw) on 2020-08-10 03:24:35 in reply to 5 [link] [source]

Thanks, that's exactly what I needed to know! I wrote about this here: Enabling WAL mode for SQLite database files

(7) By Richard Damon (RichardDamon) on 2020-08-10 10:52:48 in reply to 6 [link] [source]

Yes, the issue with the 'Database locked' error is that since WAL mode is a database property and not just a connection property, to set it requires getting a lock to write to the database, and I think it will need an 'Exclusive' lock, so if the database is busy with other connections, it might get locked out from being able to make the change.

(8) By anonymous on 2021-06-17 20:30:56 in reply to 2 [link] [source]

I tried the test where first connection creates table (in transaction) and second connection read. The test failed showing second connection failed to read as transaction is not complete.

I tested with following connection string var connectionString = $"Data Source =test.db;Version=3; Cache = shared; read_uncommitted = true; journal_mode=WAL";

https://sqlite.org/forum/forumpost/99ffc78cee

Regards

Anand