Thanks Kevin. I had considered the temporary table approach, but with potentially millions of updates, I am concerned about the time spent writing the 100MB+ of temp data, spoiling the interactive user experience. However, I may try it (as you say, it should be simple) to see the actual performance, now that my "even cheaper", "hacky" proposal (testing returned row keys for monotonic increase) has been explained to be "incorrect".
Fortunately, it looks like updating to a newer SQLite and using WAL mode and a transaction round the SELECT will be straightforward. stephan(); -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Kevin O'Gorman Sent: Thursday, September 08, 2016 10:07 AM To: SQLite mailing list Subject: Re: [sqlite] Clarification on "No Isolation On Same Db Connection" 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 > https://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fmailinglists.sqlite.org%2fcgi-bin%2fmailman%2flistinfo%2fsqlite-users&data=02%7c01%7cStephan.Mueller%40microsoft.com%7c312a3320ef4e4ae2b39d08d3d80aa5d0%7c72f988bf86f141af91ab2d7cd011db47%7c1%7c0%7c636089512652659928&sdata=wI43xdwOejNfxRdlJYbO8e1K6AoglRxoTmLECUF6nb0%3d > -- #define QUESTION ((bb) || (!bb)) /* Shakespeare */ _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org https://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fmailinglists.sqlite.org%2fcgi-bin%2fmailman%2flistinfo%2fsqlite-users&data=02%7c01%7cStephan.Mueller%40microsoft.com%7c312a3320ef4e4ae2b39d08d3d80aa5d0%7c72f988bf86f141af91ab2d7cd011db47%7c1%7c0%7c636089512652659928&sdata=wI43xdwOejNfxRdlJYbO8e1K6AoglRxoTmLECUF6nb0%3d _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
