On Monday, 17 June, 2019 21:36, Simon Slavin <slav...@bigfraud.org> wrote:
>I understand about the RESERVED lock. I read the documentation. My >surprise was at this, from further down the same page: >" No EXCLUSIVE lock is acquired until either the memory cache fills >up and must be spilled to disk or until the transaction commits. " >and that's what I was asking about. I was surprised that by default >SQLite waits until the last possible moment to gain an exclusive >lock, risking failure after the program may have forgotten which >changes were made. I suspect that a lot of software is using BEGIN >when it should be using BEGIN EXCLUSIVE. Generally speaking you should leave your READS (SELECTs) in AUTOCOMMIT mode. Unless you require REPEATABLE READ, or data stability between multiple select statements, in which case you should put them in a BEGIN DEFERRED transaction. Transactions in which you intend (or might) do updates should be BEGIN IMMEDIATE transactions. This applies generally for all DBMS systems -- and they all support some method of signifying intent to update. And you should NEVER hold a transaction of any kind open for longer than absolutely necessary and especially never during "user interaction" (you never know when the user might need to go for a pee and get hit by a bus and killed in the process leaving you with a farked up database to unfark). So for applications which interact with a user, the general process is: 1) read the data for the user in autocommit or inside a deferred transaction if you need more than one select statement to collect the data the user needs to see and that you need to verify that the users' update is valid, and commit or rollback that transaction BEFORE INTERACTING WITH THE USER OR UPDATING THE DISPLAY TO THE USER. 2) display the data for the user to fiddle with 3) start a transaction with BEGIN IMMEDIATE and repeat step (1) making sure the data has not changed while the very slow user diddled about with the user interface and then if and only if the data is unchanged and the update is valid, make the updates and commit the transaction. If the commit fails because there are other active readers, then try committing again (and commit again (and commit again) ... until success is obtained). If the data does not verify (ie, some other connection updated the necessary data while the user piddled about, or the update is otherwise invalid, rollback the transaction and use the data collected in this step to go back to step 2). 4) Lather rinse repeat until the user bails out or the heat death of the universe, whichever comes first. In other words, never hold a transaction longer than is absolutely necessary. The uses for a BEGIN EXCLUSIVE transaction are very rare special cases. I think I may have used an exclusive transaction once or twice in the past three decades ... --- 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
