Hello everybody, I have a situation where two processes are accessing the same SQLite database. One process only reads from the database, one process reads and writes.
These processes keep a single database connection open for the lifetime of the process. It seems to me that once the reader process accesses the database (after it performs its first SELECT statement), it maintains a lock on the database until the connection is closed (when the program is exited). This prevents the writer process from updating the database. I tried changing to WAL. This made the writer process able to commit its changes, but now the reader does not see any modifications made to the database until it is restarted (It seems to see a snapshot of the DB at the time of its first read). I am using prepared statements: On opening the DB, I create all my prepared statements. When I need to execute a statement, I bind to the statement, call sqlite3_step (possibly multiple times), then call sqlite3_reset. I do not finalise the statements until the program closes. In order to simulate 'save' behaviour, the writer process always holds a transaction open. When the user chooses 'save', the current transaction is committed and a new transaction is begun. (I understand that the reader will not see any changes in the uncommitted transaction, but is not seeing any committed transactions either). I have checked quite thoroughly through my code and cannot find any instances of statements executed without a sqlite3_reset quickly following. Is this intended behaviour - that once a connection has performed a read, it maintains its lock on the database for its lifetime? Is SQLite smart enough to know that the pages it holds in cache of the reader are invalid after the writer has made changes to the DB on disk? If this is not the intended behaviour - is there a way I can find out which statements are causing the lock to be held open? Or can I force SQLite to discard its cache? Any help would be appreciated. Regards, Barry Smith
