Sqlite connections isolated even after COMMIT
I'm using journal_mode WAL, synchronous NORMAL and wal_autocheckpoint 0 (with manual checkpoints from time to time)
I have one connection reserved for writers and then one connection for each thread which I use to read.
When I do an INSERT OR REPLACE using the write connection, I can see the inserted value with a SELECT with that writer connection, but if I try the same SELECT from a reader connection, I see no value or an outdated value.
According to https://sqlite.org/isolation.html I should see the value from all connections because it is committed (the INSERT OR REPLACE is automatically wrapped in a transaction. It also doesn't work if I wrap it in an explicit BEGIN EXCLUSIVE transaction)
What is the problem here? How can I fix it?
(My program is written in Rust using the rusqlite library)
Probably the reader is in a transaction that started before the writer transaction, so that the reader continues to see the state of the database before the new content was written. Fix this by committing the reader transaction and starting a new one.
The transaction may come about because you have a prepared statement that is not finalized.
Ah I realized I was keeping an iterator alive the whole time (a long running SELECT) on the same connection as I was using for the test. Is it possible to update the reader's database point without stopping that long running SELECT?