Re: Lock mode in ExecMergeMatched()

> On Fri, 10 Mar 2023 at 21:42, Alexander Korotkov <aekorotkov(at)gmail(dot)com> 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 pushed the patch for bug #17809, which in the end didn't directly
touch this code. I considered including the change of lockmode in that
patch, but in the end decided against it, since it wasn't directly
related to the issues being fixed there, and I wanted more time to
think about what changing the lockmode here really means.

I'm wondering now if it really matters what lock mode we use here. If
the point of calling table_tuple_lock() after a concurrent update is
detected is to prevent more concurrent updates, so that the retry is
guaranteed to succeed, then wouldn't even LockTupleNoKeyExclusive be
sufficient in all cases? After all, that does block concurrent updates
and deletes.

Perhaps there is an issue with using LockTupleNoKeyExclusive, and then
having to upgrade it to LockTupleExclusive later? But I wonder if that
can already happen -- consider a regular UPDATE (not via MERGE) of
non-key columns on a partitioned table, that initially does a simple
update, but upon retrying needs to do a cross-partition update (DELETE
+ INSERT).

But perhaps I'm thinking about this in the wrong way. Do you have an
example test case where this makes a difference?

Regards,
Dean