Postgres Pro
Downloads
Home   >   mailing lists

Thread: deadlocks - sharelocks on transactions

deadlocks - sharelocks on transactions

From
Tim McAuley
Date:
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?

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.

It's a little difficult to debug this issue when it's not identifying
which data accesses are causing the deadlocks.

Does anyone have any information that may help in tracking down the problem?

Many thanks,

Tim

Current set-up:

Postgresql 7.4.1 (Windows 2000, Cygwin) or
Postgresql 7.4   (Linux, Redhat 9)

Re: deadlocks - sharelocks on transactions

From
Stephan Szabo
Date:
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?

Re: deadlocks - sharelocks on transactions

From
Tom Lane
Date:
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