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





RE: [GENERAL] 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
Tue, 14 Jan 2003 07:16:52 -0700
"Orr, Steve" <sorr@rightnow.com>
Re: Server error and deadlocks 
"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

Tue, 14 Jan 2003 10:31:14 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Server error and deadlocks 
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


Tue, 14 Jan 2003 11:50:26 -0500
Andrew Sullivan <andrew@libertyrms.info>
Re: Server error and deadlocks
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.


Tue, 14 Jan 2003 10:35:07 -0700 (MST)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: Server error and deadlocks 