Re: Strange deadlock with object/target of lock : transaction

On Wed, 2026-10-21 at 17:36 +0200, Achilleas Mantzios wrote:
> So, what happened, the INSERT by it self acquires a ROW EXCLUSIVE (RowExclusiveLock)
> on the table even before the BEFORE trigger is called.  A ROW EXCLUSIVE lock does not
> block another session acquiring also a  LOW EXCLUSIVE lock on same table.
>
> But what the BEFORE trigger did was 
>
> LOCK TABLE payments_finalization IN SHARE UPDATE EXCLUSIVE MODE;
>
> which is stronger than ROW EXCLUSIVE and conflicts with itself on the same table.
>
> So what was happening is that :
>
> [...]
> >
> pgsql acquires a ROW EXCLUSIVE on the table for both sessions, ROW EXCLUSIVE is not self-conflicting.
>
> Then both backends calle the BEFORE trigger.
> >
> Both get to execute the explicit  LOCK TABLE payments_finalization IN SHARE UPDATE EXCLUSIVE MODE ;
> the first session waits on the second to release its ROW EXCLUSIVE lock.
> the second session waits on the first to release its ROW EXCLUSIVE lock.
>
> A clear dead-lock situation.

Thanks!

I cannot be certain about your special case, but in my opinion explicitly
locking a table is almost always too strong a measure, and there are better
ways of solving the problem.

Yours,
Laurenz Albe