SHARED lock vs READ transaction

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

SHARED lock vs READ transaction

Kira Backes
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SHARED lock vs READ transaction

Olivier Mascia
> Le 12 août 2019 à 12:11, Kira Backes <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SHARED lock vs READ transaction

Kira Backes
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SHARED lock vs READ transaction

Kira Backes
In reply to this post by Olivier Mascia
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 <[hidden email]> wrote:

>
> > Le 12 août 2019 à 12:11, Kira Backes <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SHARED lock vs READ transaction

Olivier Mascia
In reply to this post by Kira Backes
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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SHARED lock vs READ transaction

Kira Backes
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 <[hidden email]> 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users