This is the I (Isolation) in ACID. WAL mode allows the writer to pretend that no transactions are outstanding and begin and even commit a write transaction. This change sits in the Wal file until all prior transactions have been completed and the change can be copied to the db. As long as your threads are within a transaction begun before the change happened, they will continue to see the old state.
Maybe your threads have unfinished statements (missing call to sqlite3_reset() or sqlite3_finalize() functions), which cause the implicit transaction to be very much longer than expected. -----Ursprüngliche Nachricht----- Von: Daniel Polski [mailto:dan...@agelektronik.se] Gesendet: Dienstag, 11. November 2014 12:59 An: General Discussion of SQLite Database Betreff: [sqlite] Multiple threads reading different data I'm accessing a database from multiple threads, where each thread has a separate database connection. When some specific event happens, I want the threads to evaluate the situation and act accordingly. I have setup a temporary trigger which fires at the event and which in turn calls a user defined function updating a timestamp with the time of the event. The threads compare this timestamp with a local timstamp storing the last synchronization, and if they mismatch they fetch data from the database and process it. Now here's where it starts to seem a little tricky to me. It seems like the separate connections don't "see" the update made to the database from another thread if the fetching is done quickly after the update (as soon as the timestamps mismatch). I'm thinking that this maybe has to do with me using WAL mode, and that the update is not yet "processed enough" for the other threads to fetch the new data (so they still selects the old data), even though the trigger is set to fire "after update". Is this maybe the case? If so, any suggestions how to solve it? Regards, Daniel _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users