Re: deadlocks - sharelocks on transactions

Tim McAuley <tech-lists@mothy.net> writes:
> I have a stored procedure that is causing deadlocks when called multiple
> times synchronously. The odd issue is that the deadlock seems to be
> happening on different threads waiting for locks on transactions. What
> exactly is this transaction lock?

> ERROR:  deadlock detected
> DETAIL:  Process 1740 waits for ShareLock on transaction 1488; blocked
> by process 1716.
>          Process 1716 waits for ShareLock on transaction 1490; blocked
> by process 1740.

What you've got here is transactions deadlocked by trying to
update/delete the same rows (unfortunately the lock manager doesn't know
exactly which rows, so the DETAIL isn't too helpful).

Most of the complaints I've seen about this sort of problem are not
really from updates per se, but the SELECT FOR UPDATE locking that
is done for foreign key references.  Are you inserting multiple rows
that might reference the same foreign keys?

            regards, tom lane