It seems to me that the simplest, most portable approach for this sort of thing would to be having the SELECT create a temporary table of the desired actions, and not apply them until after the select has concluded. This would work in any database -- it does not depend on precise semantics of WAL, for instance.
Of course, it could be that this is inefficient for some reason, and that might cause you to take a different course, but all the alternatives are going to be more troublesome to understand and maintain. It's up to you whether this is worth it. On Wed, Sep 7, 2016 at 7:24 PM, Igor Tandetnik <i...@tandetnik.org> wrote: > On 9/7/2016 6:11 PM, Stephan Mueller wrote: > >> I understand that a way to ensure "SELECT is unperturbed" semantics is to >> use separate connections for SELECT and updates. >> > > If you go down that route, make sure you are using WAL journaling mode; it > won't work otherwise. > > This is undesirable since I'd have to (IIUC) do all my updates (possibly >> millions) in a single transaction. >> > > I don't see how this follows. > > I'd prefer to commit after each update >> > > You can't commit on a single connection either, while there's an > unfinalized SELECT statement traversal going on. So you aren't gaining > anything by trying to interleave SELECT and updates on the same connection. > > That is, if I ever receive a record that ought to have arrived earlier >> because of ORDER BY, it must be a since-SELECT-began update, and should be >> ignored. >> > > When data is modified under SELECT's feet, phantom rows are just one > problem; it's also possible for the statement to skip rows it would have > otherwise returned, and to return rows containing stale data. Basically, > undefined behavior is undefined. > -- > Igor Tandetnik > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- #define QUESTION ((bb) || (!bb)) /* Shakespeare */ _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
