Feedback-ID: ia2694551:Fastmail
Date: Mon, 13 Mar 2023 18:47:38 +0100
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Alexander Korotkov <aekorotkov@gmail.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Lock mode in ExecMergeMatched()
Message-ID: <20230313174738.6gozdobp5gd6xfx7@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <CAEZATCXXv65CCG2p_S79Puf50joORZJqO4+eWH2jqvsXcTZ21w@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20230313174738.6gozdobp5gd6xfx7%40alvherre.pgsql>
Precedence: bulk

On 2023-Mar-13, Dean Rasheed wrote:

> 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.

The difference in lock mode should be visible relative to concurrent
transactions that try to SELECT FOR KEY SHARE the affected row.  If you
are updating a row but not changing the key-columns, then a KEY SHARE
against the same tuple should work concurrently without blocking.  If
you *are* changing the key columns, then such a lock should be made to
wait.

DELETE should be exactly equivalent to an update that changes any
columns in the "key".  After all, the point is that the previous key (as
referenced via a FK from another table) is now gone, which happens in
both these operations, but does not happen when an update only touches
other columns.

Two UPDATEs of the same row should always block each other.


Note that the code to determine which columns are part of the key is not
very careful: IIRC any column part of a unique index is considered part
of the key.  I don't think this has any implications for the discussion
here, but I thought I'd point it out just in case.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/