COMMIT, what locking behaviour when current lock is merely SHARED?

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

COMMIT, what locking behaviour when current lock is merely SHARED?

Olivier Mascia
Hello,

I generally understand the locking states of SQLite well enough, I think.  Though this specific case below caught me off guard.  I couldn't give an immediate intelligible answer to my coworker.  Nor could I find the answer on the website, or I'm blind today.

A connection holds a SHARED lock. It did start a DEFERRED transaction then read something. Now it executes COMMIT.  Will the lock be upgraded from SHARED to EXCLUSIVE for the very short duration of the COMMIT (the connection made no writing, as evidenced by its SHARED lock), or will this specific case elude upgrading the lock and simply exit the transaction, releasing the SHARED-lock to NO lock?  Sounds logical, but is it true?  In other words, can I say that the lock upgrade to EXCLUSIVE at COMMIT time only happens if the current lock state was RESERVED (and not simply SHARED)?

—  
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: COMMIT, what locking behaviour when current lock is merely SHARED?

Igor Tandetnik-2
On 8/9/2019 11:00 AM, Olivier Mascia wrote:
> A connection holds a SHARED lock. It did start a DEFERRED transaction then read something. Now it executes COMMIT.  Will the lock be upgraded from SHARED to EXCLUSIVE for the very short duration of the COMMIT (the connection made no writing, as evidenced by its SHARED lock), or will this specific case elude upgrading the lock and simply exit the transaction, releasing the SHARED-lock to NO lock?

It must be the latter, otherwise readers won't be able to get out in the presence of RESERVED or PENDING lock from a prospective writer. You'd have deadlocks all the time.
--
Igor Tandetnik

_______________________________________________
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: COMMIT, what locking behaviour when current lock is merely SHARED?

Richard Hipp-3
In reply to this post by Olivier Mascia
On 8/9/19, Olivier Mascia <[hidden email]> wrote:

> Hello,
>
> I generally understand the locking states of SQLite well enough, I think.
> Though this specific case below caught me off guard.  I couldn't give an
> immediate intelligible answer to my coworker.  Nor could I find the answer
> on the website, or I'm blind today.
>
> A connection holds a SHARED lock. It did start a DEFERRED transaction then
> read something. Now it executes COMMIT.  Will the lock be upgraded from
> SHARED to EXCLUSIVE for the very short duration of the COMMIT (the
> connection made no writing, as evidenced by its SHARED lock), or will this
> specific case elude upgrading the lock and simply exit the transaction,
> releasing the SHARED-lock to NO lock?  Sounds logical, but is it true?  In
> other words, can I say that the lock upgrade to EXCLUSIVE at COMMIT time
> only happens if the current lock state was RESERVED (and not simply SHARED)?

You are one of the rollback journaling modes, not WAL mode, right?
Different rules apply for WAL mode.

In rollback mode, the connection automatically promotes to EXCLUSIVE
while committing the transaction.  This is necessary to make sure no
other connections are simultaneously reading the database, because it
would not work for the committer to change content out from under the
other readers.  After the COMMIT, it falls back to unlocked.
--
D. Richard Hipp
[hidden email]
_______________________________________________
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: COMMIT, what locking behaviour when current lock is merely SHARED?

David Raymond
>> A connection holds a SHARED lock. It did start a DEFERRED transaction then
>> read something. Now it executes COMMIT.

>You are one of the rollback journaling modes, not WAL mode, right?
>Different rules apply for WAL mode.
>
>In rollback mode, the connection automatically promotes to EXCLUSIVE
>while committing the transaction.  This is necessary to make sure no
>other connections are simultaneously reading the database, because it
>would not work for the committer to change content out from under the
>other readers.  After the COMMIT, it falls back to unlocked.
>--
>D. Richard Hipp
>[hidden email]


I'm pretty sure you missed the bit where he said that there was only a read, and no changes were made or requested.

Otherwise, as Igor said, there's be deadlocks with any concurrent access.

(Or my brain is broken on a Friday, which has been known to happen)
_______________________________________________
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: COMMIT, what locking behaviour when current lock is merely SHARED?

Richard Hipp-3
On 8/9/19, David Raymond <[hidden email]> wrote:
>
> I'm pretty sure you missed the bit where he said that there was only a read,
> and no changes were made or requested.
>

You are correct - I missed that part.  In that case, it just drops the
SHARED lock.
--
D. Richard Hipp
[hidden email]
_______________________________________________
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: COMMIT, what locking behaviour when current lock is merely SHARED?

Olivier Mascia
> Le 9 août 2019 à 17:58, Richard Hipp <[hidden email]> a écrit :
>
> On 8/9/19, David Raymond <[hidden email]> wrote:
>>
>> I'm pretty sure you missed the bit where he said that there was only a read,
>> and no changes were made or requested.
>>
>
> You are correct - I missed that part.  In that case, it just drops the
> SHARED lock.
> --
> D. Richard Hipp
> [hidden email]

Thanks for the confirmation. :)

—  
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