From: Matt Mello <alien@spaceship.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: "deadlock detected" documentation
Date: Thu, 15 May 2003 11:37:13 -0500
Message-ID: <3EC3C239.7070708@spaceship.com> (raw)
In-Reply-To: <7737.1053003625@sss.pgh.pa.us>
References: <3EB82DA1.7020809@spaceship.com>
<24809.1052282819@sss.pgh.pa.us>
<3EC32935.5040005@spaceship.com>
<7737.1053003625@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: alien@spaceship.com, tgl@sss.pgh.pa.us
Subject: Re: "deadlock detected" documentation
In-Reply-To: <3EC3C239.7070708@spaceship.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox