deadlocks - sharelocks on transactions

[Tim McAuley]

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)


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Stephan Szabo]

Re: deadlocks - sharelocks on transactions


On Wed, 7 Jan 2004, Tim McAuley wrote:
[color=blue]
> 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?[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Tom Lane]

Re: deadlocks - sharelocks on transactions

Tim McAuley <tech-lists@mothy.net > writes:[color=blue]
> 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?[/color]
[color=blue]
> 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.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)