If you intend to update the database (ie, write to it) during a transaction, you should begin that transaction with BEGIN IMMEDIATE; This signals that you intend to WRITE to the database in that transaction and will prevent any other connection from obtaining an INTENT (to write) lock.
BEGIN IMMEDIATE means that this transaction intends to update the database. If it is successful, then this transaction will (eventually) be able to commit successfully. Other drivers are permitted to enter town for the purpose of reading the signs but they will be prevented from signaling intent to update (that is, they may drive into town but they may not change anything in town, and it they attempt to do so they will be denied). BEGIN EXCLUSIVE means that when that statement is successful EVERYONE ELSE is parked out of town and all the traffic signals are RED prohibiting them from entering town for any reason whatsoever until you commit (or rollback) the EXCLUSIVE transaction. --- 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 Igor Tandetnik >Sent: Monday, 17 June, 2019 18:28 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Please explain multi-access behaviour to me > >On 6/17/2019 8:21 PM, Simon Slavin wrote: >> On 18 Jun 2019, at 1:09am, Igor Tandetnik <i...@tandetnik.org> >wrote: >> >>> A connection doesn't need to check locks on every statement - only >when it tries to spill to disk, most commonly during commit. >> >> I think I understand what you wrote. >> >> So the bit of my program can think that its changes were written to >the database and only later might my program find that they weren't ? > >Why would it think that, when it didn't successfully commit the >transaction? > >> Should I have used BEGIN EXCLUSIVE ? > >If that's what your application's logic calls for, then yes, sure. >This way, you'll get an error on BEGIN EXCLUSIVE statement. Note >that, once BEGIN EXCLUSIVE succeeds, all readers will be locked out >until the write transaction commits or rolls back, thus reducing the >concurrency. >-- >Igor Tandetnik > >_______________________________________________ >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
