Re: [sqlite] SHARED lock vs READ transaction
I understand your semantic point but this helps no one. Coming from other databases and SQL in general the term "transaction" has a very specific meaning. So if the documentation talks about read transactions in some places and shared locks in other places I think these are different things. Let's say I use a MySQL client, do not request a read transaction but I see somewhere that the MySQL server will need an internal shared lock to satisfy the SELECT query, what do I care? If what you say is true then I think it would greatly help the documentation to replace all occurrences of "read transaction" with "shared lock" and thereby introduce ubiquitous language and reduce confusion. mit freundlichen Grüßen, Kira Backes On Mon, 12 Aug 2019 at 13:30, Olivier Mascia wrote: > > Could you please understand that this is only a matter of language? > > There is no hard thing as a read transaction. But it is commonly intuitive to > name a transaction as « read » as long as it did not started with write > intent and self-restraint itself from doing writes. > > -- > Best regards, Meilleures salutations, Met vriendelijke groeten, > Olivier Mascia (from mobile device) > > Le 12 août 2019 à 13:19, Kira Backes a écrit : > > >> There is no such thing as a "READ transaction". > > > > Could you please open the following google query: > > > > https://www.google.com/search?q=%22read+transaction%22+site%3Asqlite.org > > > > There are 300 mentions of "read transaction" in the documentation and > > commits > ___ > 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
Re: [sqlite] SHARED lock vs READ transaction
Could you please understand that this is only a matter of language? There is no hard thing as a read transaction. But it is commonly intuitive to name a transaction as « read » as long as it did not started with write intent and self-restraint itself from doing writes. -- Best regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia (from mobile device) Le 12 août 2019 à 13:19, Kira Backes a écrit : >> There is no such thing as a "READ transaction". > > Could you please open the following google query: > > https://www.google.com/search?q=%22read+transaction%22+site%3Asqlite.org > > There are 300 mentions of "read transaction" in the documentation and commits ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SHARED lock vs READ transaction
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 wrote: > > > Le 12 août 2019 à 12:11, Kira Backes 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 > 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
Re: [sqlite] SHARED lock vs READ transaction
> There is no such thing as a "READ transaction". Could you please open the following google query: https://www.google.com/search?q=%22read+transaction%22+site%3Asqlite.org There are 300 mentions of "read transaction" in the documentation and commits mit freundlichen Grüßen, Kira Backes ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SHARED lock vs READ transaction
> Le 12 août 2019 à 12:11, Kira Backes 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SHARED lock vs READ transaction
Dear mailing list, 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? Because from Rowan's reply it seems like it's the same. Is it really? If so, could we document this? :) kind regards, Kira Backes ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
