We think we are seeing the case where a read on one WAL mode Sqlite database connection using the C API is reading stale data after a transaction has committed data changes on another connection.
For instance, a deleted row on one connection is still found by a select on the other. The BEGIN/DELETE/COMMIT then SELECT (prepare/step/finalize for each) is happening in the same thread. We expected the commit of the write to be the point in time after which any read would read that committed data. This does not seem to be the case here. What is the standard idiom to avoid stale data while still allowing all but a writing thread not to see uncommitted data? Is there a window of time between a commit on one connection and the data being available on another connection? Is that deterministic? When is the WAL mode "end mark" moved on a connection past other changes? Is it possible that we have an end mark on the read connection that is still behind the write on the other connection? What would trigger it to move? Would other threads doing reads in parallel on the same connection affect when the end mark is moved? In our test we serialized reads so there could only be one happening at a time in an attempt to remove any chance of this. But we still saw stale data. We are trying to implement a system where all writes occur on one connection (in a SQL transaction where a writing thread would see the uncommitted data) and all other reads on a second connection. Does it sound like we are doing something fundamentally wrong by trying to share connections across threads in this way? Any advice greatly appreciated Alex _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
