Locking a row with KEY SHARE NOWAIT blocks

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Locking a row with KEY SHARE NOWAIT blocks

Heikki Linnakangas
When you lock a row with FOR KEY SHARE, and the row's non-key columns
have been updated, heap_lock_tuple() walks the update chain to mark all
the in-progress tuple versions also as locked. But it doesn't pay
attention to the NOWAIT or SKIP LOCKED flags when doing so. The
heap_lock_updated_tuple() function walks the update chain, but the
'wait_policy' argument is not passed to it. As a result, a SELECT in KEY
SHARE NOWAIT query can block waiting for another updating transaction,
despite the NOWAIT modifier.

This can be reproduced with the attached isolation test script.

I'm not sure how to fix this. The logic to walk the update chain and
propagate the tuple lock is already breathtakingly complicated :-(.

- Heikki

nowait-keyshare-bug.spec (1K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Locking a row with KEY SHARE NOWAIT blocks

Tom Lane-2
Heikki Linnakangas <[hidden email]> writes:
> When you lock a row with FOR KEY SHARE, and the row's non-key columns
> have been updated, heap_lock_tuple() walks the update chain to mark all
> the in-progress tuple versions also as locked. But it doesn't pay
> attention to the NOWAIT or SKIP LOCKED flags when doing so. The
> heap_lock_updated_tuple() function walks the update chain, but the
> 'wait_policy' argument is not passed to it. As a result, a SELECT in KEY
> SHARE NOWAIT query can block waiting for another updating transaction,
> despite the NOWAIT modifier.

> This can be reproduced with the attached isolation test script.

> I'm not sure how to fix this. The logic to walk the update chain and
> propagate the tuple lock is already breathtakingly complicated :-(.

Why are we locking any but the most recent version?

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Locking a row with KEY SHARE NOWAIT blocks

Heikki Linnakangas
On 03/09/2019 16:31, Tom Lane wrote:

> Heikki Linnakangas <[hidden email]> writes:
>> When you lock a row with FOR KEY SHARE, and the row's non-key columns
>> have been updated, heap_lock_tuple() walks the update chain to mark all
>> the in-progress tuple versions also as locked. But it doesn't pay
>> attention to the NOWAIT or SKIP LOCKED flags when doing so. The
>> heap_lock_updated_tuple() function walks the update chain, but the
>> 'wait_policy' argument is not passed to it. As a result, a SELECT in KEY
>> SHARE NOWAIT query can block waiting for another updating transaction,
>> despite the NOWAIT modifier.
>
>> This can be reproduced with the attached isolation test script.
>
>> I'm not sure how to fix this. The logic to walk the update chain and
>> propagate the tuple lock is already breathtakingly complicated :-(.
>
> Why are we locking any but the most recent version?

Define "most recent". In KEY SHARE mode, there can be multiple UPDATEd
versions of the tuple,  such that the updating transactions are still
in-progress, but we can still acquire the lock. We need to lock the most
recent version, including any in-progress transactions that have updated
the row but not committed yet. Otherwise, the lock would be lost when
the transaction commits (or if the in-progress transaction updates the
same row again). But locking that tuple is not enough, because otherwise
the lock would be lost if the in-progress transaction that updated the
row aborts. We also need to lock the latest live tuple (HEAPTUPLE_LIVE),
to avoid that. And if there are subtransactions involved, we need to be
prepared for a rollback/commit of any of the subtransactions.

Hmm. I think this could be fixed by locking the tuples in reverse order,
starting from the latest in-progress updated version, walking the update
chain backwards. While we're walking the chain, if we find that an
updating transaction has committed, so that we have already acquired a
lock on the now live version, we can stop. And if we find that the
transaction has aborted, we start from scratch, i.e. find the now latest
INSERT_IN_PROGRESS tuple version, and walk backwards from there.

Walking an update chain backwards is a bit painful, but you can walk
forwards from the live tuple and remember the path, and walk backwards
the same path once you reach the end of the chain.

- Heikki


Reply | Threaded
Open this post in threaded view
|

Re: Locking a row with KEY SHARE NOWAIT blocks

akapila
In reply to this post by Heikki Linnakangas
On Tue, Sep 3, 2019 at 6:58 PM Heikki Linnakangas <[hidden email]> wrote:

>
> When you lock a row with FOR KEY SHARE, and the row's non-key columns
> have been updated, heap_lock_tuple() walks the update chain to mark all
> the in-progress tuple versions also as locked. But it doesn't pay
> attention to the NOWAIT or SKIP LOCKED flags when doing so. The
> heap_lock_updated_tuple() function walks the update chain, but the
> 'wait_policy' argument is not passed to it. As a result, a SELECT in KEY
> SHARE NOWAIT query can block waiting for another updating transaction,
> despite the NOWAIT modifier.
>
> This can be reproduced with the attached isolation test script.
>
> I'm not sure how to fix this. The logic to walk the update chain and
> propagate the tuple lock is already breathtakingly complicated :-(.
>

Can't we pass the wait_policy parameter to heap_lock_updated_tuple and
do the same handling as we do in the caller (heap_lock_tuple)?
Basically, whenever we need to wait on any tuple version do the same
as we do in heap_lock_tuple for 'require_sleep' case.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com