By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,692 Members | 1,998 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,692 IT Pros & Developers. It's quick & easy.

deadlocks - sharelocks on transactions

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

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?

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #2

P: n/a
Tim McAuley <te********@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.