On Tuesday, 30 July, 2019 15:40, Olivier Mascia <o...@integral.be> wrote:
> Keith, in the context of WAL mode, I fail to see why it would be > beneficial to obtain any lock immediately, when the transaction being > setup using BEGIN (DEFERRED) is intended to only read. Until it > actually has started to read (which it will always be able to do), > why would it matter that a writer did write and commit between the > "reader" BEGIN and its first read? What do I miss here? BEGIN [[DEFERRED] [TRANSACTION]] does not acquire any locks when it is executed. The locks are acquired by the first statement thereafter that requires some locks (be they a shared lock as in a SELECT statement or shared and intent locks if the statement is an UPDATE statement) This, of course, means that any statement subsequent to the begin may fail because if cannot acquire the locks it requires -- even the first SELECT statement if some other connection currently holds an EXCLUSIVE lock (such as during a commit or after the commencement of a cache spill by another connection when not in WAL journal mode). BEGIN IMMEDIATE [TRANSACTION] acquires the shared and intent locks immediately and if it succeeds, then the following statements will also succeed (up until the commit or an update statement which has to spill the cache since when in regular journal mode a lock escalation to exclusive is still required to write the actual database, and a concurrent shared lock will prohibit that -- though in WAL mode the commit will not fail either). Theoretically, a BEGIN IMMEDIATE SHARED TRANSACTION would guarantee that subsequent SELECT statements cannot fail because a shared lock cannot be acquired, since that lock will already be held. I do not personally see the benefit of moving the repeatable read guarantee to the BEGIN point rather than the first database read after the BEGIN because only fully committed transactions will be visible anyway -- and if there are dependencies between transactions (that is it takes multiple transactions to validly mutate database state) then the problem is that the transaction design is flawed, and not that the repeatable-read is being commenced at the wrong time. I can see where a BEGIN IMMEDIATE SHARED would be useful in non-WAL mode though. I will grant that there may be cases where it might be useful in WAL mode, even though I cannot think of any. -- 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
