On Tue, 14 Feb 2017 00:49:29 +0700 Dan Kennedy <danielk1...@gmail.com> wrote:
> SQLite updates the first row (with a=1) and sets column "b" to 2. > But then, when it goes to update the next row, it runs the correlated > query a second time. And this time it returns 3. So you end up > setting "b" in the second row to 3 instead of 2. Thank you for that distillation, Dan. You saved me the effort of understanding the problem report. > It's surprising, but a consequence of the way SQLite has always > worked. For some value of "worked", yes. It's another example of the ramifications of SQLite's nonatomic update. Your explanation leaves out fundamental step 0: First, [dbms] isolates the UPDATE statement, such that no changes made to the database affect the statement's input during execution. Your representation "runs the correlated query a second time" is the correct way to think about it logically *if* you treat the table as stable during the query's execution. SQLite's nonatomic update deserves its own documentation page. Users who understand atomicity correctly recognize it as a bug, both because it's not how SQL defines UPDATE and because it's not documented. Not to put too fine a point on it, the claim "Transactions in SQLite are SERIALIZABLE" is false because -- as you just explained -- the update transaction is not isolated from *itself*. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
