On 6 Sep 2013, at 1:50pm, Marc L. Allen <mlal...@outsitenetworks.com> wrote:
> No one commented on my second thread (written after I actually understood the > problem!). > > But, I proposed a two update sequence to do it. > > UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >= seq_to_insert > AND Name = name_to_insert > > UPDATE table SET Sequence = -Sequence WHERE Sequence < 0 AND Name = > name_to_insert > > I've used this system many times to avoid conflicts, but it may not work > where the table needs to be accessed concurrently, as rows will sort of > disappear temporarily (or at least change to an unusable state). Yep, this is a find, acceptable, and reasonably fast work-around. Nice one. On 6 Sep 2013, at 5:21am, James K. Lowden <jklow...@schemamania.org> wrote: > Simon Slavin <slav...@bigfraud.org> wrote: > >> If all the updates are done inside a COMMIT, >> then the conflict may not be recognised because by the time the first >> change is written back to the table, the conflicting entry has >> already been renumbered. > > I was puzzled by this thread, so ran my own little test. To my dismay, > UPDATE is not atomic in SQLite. Right. As I posted in my message that I had tested, this isn't being done correctly. A conflict isn't a conflict until the write, and the write doesn't happen until the COMMIT. Therefore conflict testing needs to happen at the commit, for /all/ the changes in the transaction, whether they're all the consequence of one UPDATE or even of separate commands within the same transaction. Possibly another problem that could be fixed in SQLite4. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
