Thread: Locking a row with KEY SHARE NOWAIT blocks

Locking a row with KEY SHARE NOWAIT blocks

From
Heikki Linnakangas
Date:
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

Attachment

Re: Locking a row with KEY SHARE NOWAIT blocks

From
Tom Lane
Date:
Heikki Linnakangas <hlinnaka@iki.fi> 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



Re: Locking a row with KEY SHARE NOWAIT blocks

From
Heikki Linnakangas
Date:
On 03/09/2019 16:31, Tom Lane wrote:
> Heikki Linnakangas <hlinnaka@iki.fi> 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



Re: Locking a row with KEY SHARE NOWAIT blocks

From
Amit Kapila
Date:
On Tue, Sep 3, 2019 at 6:58 PM Heikki Linnakangas <hlinnaka@iki.fi> 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