Strategies for concurrent read/writing and reading in SQLite

Question

I have an SQLite database that I am keeping open and writing to in process A. I would like to be able to use it from process B on a read-only basis.

According to the document,

• if the database is UNLOCKED the database may not be read (or written) - unsuitable
• if the database is SHARED then two processes can read it but the first can't write - unsuitable
• if a process wants to write it needs an EXCLUSIVE lock which means no other processes can write - unsuitable

The process A will be making lots of little writes so I don't think making a copy on each transaction commit will be efficient.

The only way I can see it is for the reader to wait until the database enters UNLOCKED state, get a SHARED lock for the duration of the read and then release it. Meanwhile process A will want to write and will be blocked until the lock becomes available - if it ever does (what if process B crashes?). This means that process A and process B will be in contention for locks - B wants SHARED and A wants EXCLUSIVE and this will slow things down or even lead to concurrency problems.

Is there any way to achieve my aim of concurrent writing and reading?

Answer 1

Use WAL mode. It supports concurrent readers and one writer.