"UPDATE #1 completes" and "SELECT #1 completes" should be read as the first sqlite3_step of that statement returns some sort of success indicator ...
Note that the sequence: BEGIN; SELECT #1; UPDATE #1 may result in a deadlock in that although SELECT #1 may be able to obtain a SHARED lock, UPDATE #1 may not be able to upgrade to a RESERVED lock. The only remedy is to abort the transaction entirely and start over again from the very beginning (this is because the principle that all required locks should be acquired at once and in the same order was violated). Using BEGIN IMMEDIATE will eliminate this possibility since it will (correctly) acquire all required locks at once ... (though you still need to get an EXCLUSIVE lock for the commit, you may still have to wait for SHARED locks to clear before the transaction can commit, although you may starve if you designed your application badly, you cannot deadlock). I suppose you could use BEGING EXCLUSIVE to make sure that you have the required exclusive lock as well, but this will lock all other readers out of the database and defeats concurrency. Note that in WAL mode the deadlock condition may not be detected until commit time since the transaction (not being started with BEGIN IMMEDIATE) may not be the "top" snapshot and therefore cannot commit (ever). Also note that it is possible for ANY STATEMENT which updates the database, even if a RESERVED lock is held by the transaction, to return SQLITE_BUSY. For example an EXCLUSIVE lock is required to spill pages to disk from the cache and this may occur before COMMIT time. So generally, you need to always check the return codes and do the needful ... (and what is needful depends on what you were doing and what the return code is and why you got that return code -- and the application programmer knows that with certainty, or at least should). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >Sent: Tuesday, 18 June, 2019 08:25 >To: SQLite mailing list >Subject: Re: [sqlite] Please explain multi-access behaviour to me > > >On Tuesday, 18 June, 2019 07:12, Thomas Kurz <sqlite.2...@t-net.ruhr> >wrote: > >>This has been a very informative and helpful discussion. Thank you. > >>So have I understood correctly, that in an application, this kind of >>SQLITE_BUSY handling is sufficient: > >>BEGIN >>UPDATE #1 >>SELECT #2 >>UPDATE #3 >>COMMIT <----- check for busy here and retry only the commit on >failure > >UPDATE #1 could fail with SQLITE_BUSY -- it may be unable to obtain a >RESERVED lock. Assuming that the UPDATE #1 completes then the >transaction holds a RESERVED lock and the rest of the statements will >not get an SQLITE_BUSY. The COMMIT needs to obtain an EXCLUSIVE lock >to write the changes, so it may return SQLITE_BUSY if there are other >connections having SHARED locks which might need to clear before the >transaction can commit. > >Since you know that the transaction will require a RESERVED lock to >complete, you should use BEGIN IMMEDIATE to acquire that lock >immediately. Then if the BEGIN IMMEDIATE succeeds you only need to >worry about lock escalation again when you commit (and not on the >interim statements which do not need to acquire any additional >locks). > > >>And second, what is the best place for busy-handling when only >>reading? Examle: > >>BEGIN <---- sufficient here? >>SELECT #1 >>SELECT #2 >>SELECT #3 >>ROLLBACK >> >>Can I assume that if the "begin" succeeds, I have the right to >>perform all following selects without having to fear getting an >>SQLITE_BUSY? Or do I have to repeat the whole block? > >Again, you need to check for SQLITE_BUSY on SELECT #1 since that is >where the SHARED lock is obtained. Once SELECT #1 has completed the >transaction holds a shared lock and the rest of the statements will >proceed without requiring additional locks. > >--- >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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
