Re: Lock mode in ExecMergeMatched()

On 2023-Mar-11, Alexander Korotkov wrote:

> I wonder why does ExecMergeMatched() determine the lock mode using
> ExecUpdateLockMode(). Why don't we use lock mode set by
> table_tuple_update() like ExecUpdate() does? I skim through the
> MERGE-related threads, but didn't find an answer.
>
> I also noticed that we use ExecUpdateLockMode() even for CMD_DELETE.
> That ends up by usage of LockTupleNoKeyExclusive for CMD_DELETE, which
> seems plain wrong for me.

I agree that in the case of CMD_DELETE it should not run
ExecUpdateLockMode() --- that part seems like a bug.

As I recall, ExecUpdateLockMode is newer code that should do the same as
table_tuple_update does to determine the lock mode ... and looking at
the code, I see that both do a bms_overlap operation on "columns in the
key" vs. "columns modified", so I'm not sure why you say they would
behave differently.

Thinking about Dean's comment downthread, where an UPDATE could be
turned into a DELETE, I wonder if trying to be selective would lead us
to deadlock, in case a concurrent SELECT FOR KEY SHARE is able to
lock the tuple while we're doing UPDATE, and then lock out the MERGE
when the DELETE is retried.

If this is indeed a problem, then I can think of two ways out:

1. if MERGE contains any DELETE, then always use LockTupleExclusive:
otherwise, use LockTupleNoKeyExclusive. This is best for concurrency
when MERGE does no delete and the key columns are not modified.

2. always use LockTupleExclusive. This is easier, but does not allow
MERGE to run concurrently with SELECT FOR KEY SHARE on the same tuples.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/