Re: Server error and deadlocks
You're right about the lack of foreign keys... it's a foreign concept to the
DUHvelopers. I suspect table locks but are there any database level tracing
tools to show the SQL? Will the trace_locks and debug_deadlocks parameters
help and how do I use them?
TIA
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, January 13, 2003 9:18 PM
To: Stephan Szabo
Cc: Orr, Steve; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Server error and deadlocks
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
On Mon, 13 Jan 2003, Orr, Steve wrote:
Finally, there are LOTS of deadlocks and I'm thinking it's because they
are
not doing "SELECT ... FOR UPDATE" or are explictly locking tables. Or
they
are constantly updating the same rows. It seems our duhvelopers need to
get
Well, if you're using foreign keys, you might be running into a deficiency
in the foreign key implementation.
If these folk are immigrants from MySQL, I bet they're not using foreign
keys. Taking table-level locks seems like a more likely route to
deadlock. But this is all guesswork --- we need to see some info about
the queries being issued before we can opine about the real cause.
regards, tom lane
"Orr, Steve" <sorr@rightnow.com> writes:
I suspect table locks but are there any database level tracing
tools to show the SQL? Will the trace_locks and debug_deadlocks parameters
help and how do I use them?
I do not think those will help you; they are low-level debugging aids
and are probably far too verbose to be useful for tracing an occasional
application problem. Besides which, they're not even compiled in by
default.
It seems to me that it would be appropriate for DeadLockCheck to emit
more information about the problem it's found than just "there's a
deadlock". How do people feel about reporting the detected cycle as
a series of NOTICE messages? It would look pretty similar to the
pg_locks view:
NOTICE: Proc <pid> waits for <lockmode> on <rel> <db>; blocked by <pid>
NOTICE: Proc <pid> waits for <lockmode> on <rel> <db>; blocked by <pid>
NOTICE: Proc <pid> waits for <lockmode> on <rel> <db>; blocked by <pid>
ERROR: Deadlock detected
If that seems acceptable, I could code it up in short order. While I
wouldn't want to apply it to the REL7_3_STABLE branch, I see no reason
Steve wouldn't be able to use the patch locally to identify his problem.
regards, tom lane
On Tue, Jan 14, 2003 at 10:31:14AM -0500, Tom Lane wrote:
deadlock". How do people feel about reporting the detected cycle as
a series of NOTICE messages? It would look pretty similar to the
Yes, please!!
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
On Tue, 14 Jan 2003, Orr, Steve wrote:
You're right about the lack of foreign keys... it's a foreign concept to the
DUHvelopers. I suspect table locks but are there any database level tracing
tools to show the SQL? Will the trace_locks and debug_deadlocks parameters
help and how do I use them?
Actually, what you need is a code audit and some training for your
developers on how real transactions work and why they don't need to use
table level locks.
I'd have them study and learn from the section in the manual on MVCC and
then go back in and change their code to use transactions with select for
update instead of table locks.