AFAIK there is no "write mark" in the WAL journal. Instead, each read transaction (either implicit or explicit) has an associated "read mark" that determines which data the transaction will see (i.e. the data from the main file plus any pages in the WAL journal file before the read mark). This is the "CI" in ACID. Whatever the state of any table in the database, the transaction will always see the same state, even when querying the same row more than once (Consistent), irrespective of what other transactions may have "concurrently" changed (Isolation).
Sharing a connection between threads makes it practically impossible for any one thread to tell when a transaction begins or ends. From the point of view of the database connection, the first statement to begin processing opens a transaction and the last statement to end processing (which could be in a totally different thread) closes it. IT ia all too easy to have a thread open a transaction and forget about it later. This causes all the other threads to see consistent (not stale) data. Enabling "read uncommitted" may alleviate the sypmtoms, but it does not remove the cause. Your assertions "a deleted row on one connection is found by a select on the other" and "BEGIN/DELETE/COMMIT and SELECT is happening in the same thread" is perfectly consistent if BEGIN/DELETE/COMMIT happens on one connection and SELECT on the other. As already stated, and per my own experience, each thread should have it's own connection and do whatever it needs to do there, without interference from other threads. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Alex Ward Gesendet: Dienstag, 13. September 2016 22:00 An: sqlite-users@mailinglists.sqlite.org Betreff: [sqlite] When is data committed on one connection seen on another? 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 ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
