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

On Wed, 7 Jan 2004, Tim McAuley wrote:

Hi,

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?

My first guess would be waiting on row level locks. Are you doing
anything with FOR UPDATE or foreign keys?