On Mon, 30 Jan 2017 19:29:40 -0800 Jens Alfke <j...@mooseyard.com> wrote:
> if I iterate over the the rows in a table using sqlite3_step, and > update each row after it?s returned, Bad Stuff happens. Specifically, > my query is just getting the first row over and over and over again, > and the iteration runs forever. I think you've solved your immediate problem and come to grips with SQLite's behavior. I thought it might be helpful to explain why it works that way, and that what you want is also valid, but goes by another name: a cursor. According the SQL standard, every SQL statement is atomic. SELECT has no beginning and no end: the results it returns reflect the state of the database as of the moment the statement was executed. If you fetch the last row six days after the first, it still belongs to the database as it stood when you began. SQLite in WAL mode gives you that isolation. You weren't bitten by a *lack* of isolation; you were bitten by isolation you didn't expect. The idea of updating rows as they are read -- without completing the transaction -- is supported in SQL with a cursor. Standard SQL has DECLARE CURSOR syntax; some cursors can be declared as FOR UPDATE and have behavior much like what you expected. That syntax, as you know, is not supported by SQLite. Technically speaking the product of ORDER BY is also a cursor, and it's not hard to find references to sqlite3_step as using "a cursor". All meaning stands in context, and those should not be confused with an SQL cursor. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
