SQLite Forum

Exclusive write-only lock? (Allow only one process read-write, and multiple read-only)
Well, if each process knows if it's A reader or THE writer, then they can just play nicely with each other. So I'm assuming that at the time of connection, each process doesn't know whether it's going to become THE writer or not.

One thing you could do is to have the writer greedily hold a reserved lock the whole time. When you connect, attempt a BEGIN IMMEDIATE. If it succeeds then hey, you're the new writing process and are blocking anyone else from writing while still allowing them to both connect and read. If it fails then well, guess you're a reader. The reserved lock held by the BEGIN IMMEDIATE won't block new connections until it's upgraded to a pending lock, which won't be until you first have to write something to the file. In the meantime new connections can be made, and any other connection can read with a shared lock just fine.

Whenever the writer does a COMMIT/ROLLBACK it should then immediately try another BEGIN IMMEDIATE to maintain its hold on the lock. That would have a race condition though, where between the COMMIT/ROLLBACK and the BEGIN another process could swoop in and grab it. I'm pretty sure there's no way to say "commit, but keep a reserved lock when done instead of unlocking completely."