> > WAL does this already. You just need to BEGIN a transaction when you > want it to BEGIN and COMMIT or ROLLBACK when you are done with it. > > I was under the apparently mistaken impression that starting any > transaction would block writes, even in WAL mode. > > Just to be clear, this would work: > > On multiple concurrent reader threads/connections: > > BEGIN; > SELECT col FROM table; > SELECT col FROM table; > ROLLBACK; > > On a single concurrent writer thread/connection: > > BEGIN EXCLUSIVE; > UPDATE table SET col = z; > COMMIT; > > In a WAL database, none of the threads will block (the BEGIN EXCLUSIVE > will > not block the readers, and vice-versa), and the two SELECT's will always > get the same value?
Why on earth are you using BEGIN EXCLUSIVE? Have you looked up the meaning of EXCLUSIVE in a lexicon of the English Language (or the SQLITE documentation)? And if you have and you really want EXCLUSIVE, then what you get is what you asked for (which is not Repeatable Read transactions in other connections). Perhaps you meant BEGIN IMMEDIATE ... I also note that you said concurrent "threads/connections". Note that you need to have separate connections. A connection shared between threads is still one connection and that transactions apply to connections, not threads (which are irrelevant). Also, you must not be using shared cache since that changes the semantics considerably (multiple shared cache connections are only a single connection to the database).
