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
