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 00:44:21 -0500
Message-ID: <3EC32935.5040005@spaceship.com> (raw)
In-Reply-To: <24809.1052282819@sss.pgh.pa.us>
References: <3EB82DA1.7020809@spaceship.com>
<24809.1052282819@sss.pgh.pa.us>
From what I understand, two UPDATEs are trying to update different
fields of the same records at the same time. Sometimes one of the
updates gets this error, sometimes the other. I can code around it, but
I want to be able to prevent this in new situations in the future.
Here are the updates:
UPDATE txn
SET batchid = 19391
WHERE txn.terminalid = 38 AND yadayada
UPDATE txn
SET drawerid = 50123
WHERE txn.terminalid = 38 AND yadayada
The above updates tend to operate on 500 records each.
Under some situations the EXACT same records get hit at the EXACT same time.
I did the same thing with informix and had no trouble. I'm wondering
how the locks differ and what I can do to resolve this. I guess the
problem is that the two updates hit the records in different orders (as
though they were going in opposite directions through the list), so they
end up waiting on each other in a deadlock -- my bad luck, I guess.
Instead, I *could* select the primary keys based on the where clauses
above, then loop and update each of the records independetly, one at a
time, however that is slow and cumbersome. Once I have converted to a
full-fledged application server, I WILL be updating a single record at a
time anyway, so I suppose I ought to just go ahead and do that. :(
If I do that, and I have two threads on two different connections, both
starting a txn then updating a list of records (both using the same
list), might this happen then, too? I mean, is the problem that it is
all happening inside 2 long transactions, or does this only happen with
multi-record-update statements?
Thanks!
Tom Lane wrote:
>
>>I just got a "deadlock detected" SQL error from the backend. I've never
>>received one of these before, but I just released new pooling code, so
>>I'm concerned.
>
> It means user A is waiting for a lock held by user B, while user B
> is waiting for a lock held by user A. For better info you'll need
> to offer more details about what your applications are doing ...
>
> regards, tom lane
>
--
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: <3EC32935.5040005@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