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