> 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
