X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id B2218476964
	for <pgsql-sql@postgresql.org>; Thu, 15 May 2003 14:00:17 -0400 (EDT)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	ESMTP id 61642-05 for <pgsql-sql@postgresql.org>;
	Thu, 15 May 2003 13:59:45 -0400 (EDT)
Received: from ms-smtp-01.texas.rr.com (ms-smtp-01.texas.rr.com
 [24.93.36.229])
	by postgresql.org (Postfix) with ESMTP id 9DB8E476819
	for <pgsql-sql@postgresql.org>; Thu, 15 May 2003 12:37:09 -0400 (EDT)
Received: from spaceship.com (cs24243211-239.austin.rr.com [24.243.211.239])
	by ms-smtp-01.texas.rr.com (8.12.5/8.12.2) with ESMTP id h4FGbE8b027708;
	Thu, 15 May 2003 11:37:14 -0500 (CDT)
Message-ID: <3EC3C239.7070708@spaceship.com>
Date: Thu, 15 May 2003 11:37:13 -0500
From: Matt Mello <alien@spaceship.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4a) Gecko/20030401
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: "deadlock detected" documentation
References: <3EB82DA1.7020809@spaceship.com> <24809.1052282819@sss.pgh.pa.us>
	<3EC32935.5040005@spaceship.com> <7737.1053003625@sss.pgh.pa.us>
In-Reply-To: <7737.1053003625@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200305/327
X-Sequence-Number: 13492

Tom Lane wrote:
 > I can't see a reason for bare UPDATEs of that sort to get deadlocks.
 > There must be some other factor involved.  Do you have triggers, rules,
 > or foreign keys on this table?  Other locks being taken in the same
 > transaction?

We haven't finished implementing our transaction system yet, so no, 
there are no other things running in the txn.  Each of those statements 
is a complete txn.  <blush>

This problem only happens on our production server (Murphy's Law).  I 
haven't been able to duplicate it on our test system.  The production 
system is a heavily multi-threaded environment that has lots of data 
entering the system all the time.  This makes it difficult to detect 
which SQL statements are affecting each other.  :(

There are no triggers or rules except for the foreign keys.

The txn table has several foreign keys.  The two that seem to be of 
interest are:
txn.drawerid -> drawer.drawerid
txn.batchid -> batch.batchid

I certainly don't mind posting a LOT more info on this.  I just didn't 
want to spam the list with useless information.  If I understood more 
about what can cause this kind of error, I could probably find and post 
the pertinent info.  Can I assume that a plain old select has nothing to 
do with this?  If so, I can remove the select's from my log and that 
will eliminate about 80% of the SQL, making it easier to tell what is 
happening.

Thanks, Tom!


-- 
Matt Mello