Igor 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.
I see that now -- only WAL provides snapshot isolation. " > 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. My thinking (in the absence of WAL) was that committing earlier would result in my SELECT connection potentially seeing the updates. " > 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. This is the current situation that I need to correct. My SQLite is old enough that it doesn't prevent committing in this case (nor does it support WAL mode). My next step is to update my SQLite. " > 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. So I'd better address this, to minimize chance of my hard drive being reformatted :-) Many thanks, stephan(); _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
