Maybe adding "order by rowid" to your select statement can help avoid "sawing off the branch you are sitting on". Unless you need to update rowids...
-----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jens Alfke Gesendet: Dienstag, 31. Jänner 2017 04:30 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [sqlite] Bitten by lack of isolation between SELECT and UPDATE on the same connection I’ve just run headlong in to the issues described in "No Isolation Between Operations On The Same Database Connection”. Specifically, I’ve discovered (after some debugging) that if I iterate over the the rows in a table using sqlite3_step, and update each row after it’s returned, Bad Stuff happens. Specifically, my query is just getting the first row over and over and over again, and the iteration runs forever. :( I had been under the impression that, since I’m using the WAL, queries operate on a snapshot of the database as of the time they begin, and are unaffected by subsequent changes. I got this from reading about "snapshot isolation” in a previous section of that document. (Also, another key/value database engine I’ve used recently _does_ behave this way, so it’s what I was expecting.) I now see that the “read transaction” described in that section has to be occurring in a different connection than the write transaction. (Right?) I’m unsure what to do now. I am working on a library whose API exposes iterator objects that run queries; the iterator’s “next()” method internally calls sqlite3_step. Thus the interleaving of the query and updating the database is not under my control; it’s up to the developer using our library, and I do _not_ want to expose inconvenient undefined behavior like this, or tell developers that “you can’t modify the database while you’re iterating it”. I can’t be the first person to run into this. Is there a best practice for enabling concurrent iteration and mutation? I can think of two solutions: A. Batch up all of the query results in memory at the start of the iteration, and have the iterator just read them out of the in-memory list. I’d like to avoid this because of the obvious memory overhead and latency imposed on large queries. Version 1 of our library worked this way, which is why I probably hadn’t noticed the problem until now. B. Create a separate SQLite connection for the query; then it’ll be isolated from any changes being made in the main connection. This seems elegant, but it will of course use more memory for the extra connection (with its own cache.) Moreover, it seems like I’ll need to open an indefinite number of extra connections: if the caller starts a query, makes some changes, and then starts another query (before reading the final row of the first query), I need to open another connection for the second query because it has to see the changes, which aren’t yet visible in the first query's connection … right? —Jens [1]: https://www.sqlite.org/isolation.html _______________________________________________ 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
