PS: thank you for your long answer! It's an interesting read and I think I will learn things.
But if "read transaction" is used dozens of times through the documentation you shouldn't just say there is no such thing as a read transaction if the documentation claims otherwise at so many places. If there really is no such thing as a read transaction then the documentation should be completely cleaned of that term to reduce confusion. kind regards, Kira Backes On Mon, 12 Aug 2019 at 13:11, Olivier Mascia <[email protected]> wrote: > > > Le 12 août 2019 à 12:11, Kira Backes <[email protected]> a écrit : > > > > I have one question which popped up in my other thread: What are the > > differences between a SHARED lock and a READ transaction? Are there > > any differences at all? If so, are there also differences for WAL > > databases? > > There is no such thing as a "READ transaction". > > There are transactions (DEFERRED, IMMEDIATE or EXCLUSIVE) which you can > control and there are database locks (NONE, SHARED, RESERVED, EXCLUSIVE) > which you don't control (directly). > > Transactions are either explicitly controlled by you (BEGIN, COMMIT, > ROLLBACK) or implicitly wrapping isolated statements when there is no > explicit transaction (which is also referred to as auto-commit mode). > > The big picture (without the numerous details) looks like this: > > - upon reading, a SHARED lock will be requested ; > - upon writing, a RESERVED lock will be requested (or a SHARED one upgraded > to RESERVED) ; > > I'm intentionally leaving out the details (behaviours when not being able to > acquire one of these locks). > > A BEGIN DEFERRED enters a transaction, but does not yet request any lock. It > will happen on the first read or write. > A BEGIN IMMEDIATE enters a transaction, and does request a RESERVED lock > immediately, showing your intent to write. > A BEGIN EXCLUSIVE enters a transaction, and does request an EXCLUSIVE lock > immediately. > A COMMIT will either abandon the SHARED lock (if no writes occurred during > the transaction) or request an EXCLUSIVE lock. It will release locks when > done. > A ROLLBACK will abandon locks. > > What looks the most as a "READ transaction" is a transaction started with > BEGIN DEFERRED which then takes care of not executing any statement writing > to the DB. It will then seek a SHARED lock, and simply abandon it on COMMIT > or ROLLBACK. > > Non-WAL: > The existence of a SHARED lock will block a writer (which has got a RESERVED > lock) to upgrade to EXCLUSIVE when attempting COMMIT. SQLITE_BUSY might then > get returned from the attempt to execute COMMIT. The transaction state is not > lost. And assuming the SHARED locks from readers disappear, COMMIT can be > retried and succeeds. > > WAL: > The existence of SHARED locks won't block a writer attempting COMMIT. This is > because the readers won't see the changes made by the writer until they > COMMIT/ROLLBACK. WAL brings stable, long-standing view of the DB to > connections which are only reading, for the duration of their transaction. > This won't stop another connection to write and commit. Albeit the WAL file > might grow quite indefinitely if there are always readers within > long-standing transactions. > > This is quite an over-simplified view at the subject, but it should get you > the big picture. The documentation has all the details. > > — > Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten > Grüßen, > Olivier Mascia > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
