Thank you Keith for your answer. It has led me to more questions. "though you may or may not have visited all rows" From the documentation I did not get the impression that you would ever not visit ALL ROWS at least once. Is there a technical reason for this? I would assume a full table scan is walking the un-ordered leaf pages of the B* tree?
"Your outer query should probably be "select auth_id, expiration from AuthTable where expiration <= ? order by +auth_id, +expiration" and binding current_time as the parameter since there is no point in retrieving rows that you will not be updating is there? " You are correct that does make sense. I guess I was trying avoid any ambiguities of a WHERE clause on the SELECT because I do not understand its behavior in this circumstance. You mentioned two database connections to the same database. Is this going to work if I am using Threadsafe mode = 0? Would the second connection be done through an attach? Does this conversation change if I wrap the whole select and updates in one transaction? e.g. BEGIN...END Thanks On Sun, Sep 1, 2019 at 1:32 AM Keith Medcalf <kmedc...@dessus.com> wrote: > > > Having read : https://www.sqlite.org/isolation.html > > Specifically the line "And the application can UPDATE the current row > > or any prior row, though doing so might cause that row to reappear in a > > subsequent sqlite3_step()." > > > Is it possible to create and endless loop > > Eventually you will have no more rows to update and therefore the > underlying structures become stable and the select loop will eventually run > out of rows, though you may or may not have visited all rows, and may visit > some rows two or more times (once before update and more than once after). > > If you change the outer query to "select auth_id, expiration from > AuthTable order by +auth_id, +expiration" then you will PROBABLY never have > a problem since the results will LIKELY be from a sorter and not from the > underlying table, and therefore mutation of the underlying tables and > indexes will not interfere with the result of the outer select, even if > those mutations affect the AuthTable or the indexes on it. Some SQL > varients use a FOR UPDATE clause on a SELECT to tell the query planner that > you intend to dally-about with the underlying datastore without having the > proper isolation in place. The SQLite way of doing this is by requesting a > row sorter not dependent on indexes by using the +columnname syntax in an > order by on the select. > > Your outer query should probably be "select auth_id, expiration from > AuthTable where expiration <= ? order by +auth_id, +expiration" and binding > current_time as the parameter since there is no point in retrieving rows > that you will not be updating is there? > > The correct solution is, of course, to use separate connections so that > you have isolation between the select and the updates. > > You SHOULD be executing the outer select on one connection and the updates > on another connection. This will work for journal mode delete unless the > number of changed pages is too large to fit in sqlite's cache, in which > case you may get an error from the update statement when it needs to spill > the cache, and you will need to kaibosh the whole thing and do the updates > in smaller chunks by putting a limit on the outer select and looping the > whole thing until there are no more rows to process. (or increase the > cache_size to be sufficient). > > You can avoid that particular problem by having the database in > journal_mode=WAL in which case you can even process each update in its own > transaction if you wish (get rid of the db2.beginimmediate() and > db2.commit(), though then you will have to handle the eventuality of > getting errors on the UPDATE). > > db1 = Connection('database.db') > db1.executescript('pragma journal_mode=WAL;') > db2 = Connection('database.db') > current_time = datetime.now() > current_time_plus_one_year = current_time.add(years=1) > sess_id = ... some constant ... > db2.beginimmediate() > for row in db1.execute('select auth_id, expiration from authtable where > expiration <= ?;', > (current_time,)): > db2.execute('update authtable set sesCookie = ?, expiration = ? where > auth_id = ?;', > (generate_ses_id(sess_id), current_time_plus_one_year, > row.auth_id,)) > db2.commit() > > -- > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
