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
