On Monday, 17 June, 2019 18:46, Simon Slavin <slav...@bigfraud.org> wrote:
>I think I see my error. I thought that the lock was promoted from >read to read/write when the INSERT command was processed. At this >point, SQLite knows that it is going to need to write. >Instead, although SQLite knows that it is going to have to write, it >does not try to promote the lock until COMMIT. That is correct, because no one needs to know until the transaction is COMMITted. Maybe it will be ROLLedBACK instead, in which case no one else ever even needs to know that it was contemplated that the database might be updated. >In some ways I can appreciate this: it allows other threads to finish >their work. But in others it seems wrong, as if SQLite is ignoring a >potential problem, in a way that may make it an even bigger problem >in the future. No, it prevents other users of the database from being impacted by mayhaps yes and mayhaps no (that is, other connections should not know nor care about an update to the database until it is committed -- that is the purpose of transactions). If you know that you will be updating the database in this transaction then you should start the transaction appropriately (with BEGIN IMMEDATE). This is the ATOMICITY and ISOLATION of ACID https://en.wikipedia.org/wiki/ACID_(computer_science) --- 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