On Tuesday, 3 September, 2019 15:01, Kees Nuyt <k.n...@zonnet.nl> wrote:
>On Tue, 3 Sep 2019 18:26:01 +0100, you wrote: >>> // do SELECT on db1 >>> // do UPDATE on db2 >> Do you expect the SELECT to see the results of the previous >> UPDATE ? It won't, until the transaction has ended >> (unless you arrange this explicitly). >That's the nice thing about this construct: >isolation between the SELECT and the UPDATE, >the pattern is indeed: >* Iterate over unchanged rows, > without seeing DELETEs, UPDATEs and INSERTs > on the tables in the select, >* Be free to act upon the original rows > without disturbance of the read. >I think it only works for WAL journal mode. It will work just fine in non-WAL mode provided that the number of changed pages made by the updates does not exceed the size of the cache (that is, the changes on db2 do not have to spill the cache and thus obtain an exclusive lock, that it will not be able to obtain, prior to commit time). You of course have to start an ordinary transaction on db1 and an immediate transaction on db2 before starting the query/change loop, and commit db1 before committing db2 after the loop runs out of rows (so that you do not deadlock yourself when the commit on db2 attempts to upgrade to an exclusive lock). In WAL journal mode you do not have to worry any of that at all since an open read will not prevent a write on a different connection from spilling the cache or committing. You can do it all on one connection using only a single immediate transaction (whether in WAL journal mode or not) if you are sure the query in the outer loop cannot be using an index modified by the inner update/delete/insert operations. One way to do this is to require the use of a sorter by that query so that all the results must be gathered before the first row is returned (such as by using the ORDER BY +colname implementation detail). -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
