Thread: "deadlock detected" documentation

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.

Can someone point me to documentation on what this is and how to prevent 
it?  [My search on the newsgroups yielded far too much for me to read.]

TIA!

-- 
Matt Mello

> 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.
> 
> Can someone point me to documentation on what this is and how to prevent 
> it?  [My search on the newsgroups yielded far too much for me to read.]

Deadlock occurs when two transactions try to wait for each other. In other
words, if two processes try to update two tables which have a reference to
same table, deadlock is detected. I donot know whether it is a bug or
bottle neck in postgresql. Mostly it can be avoided using locks. For
details, refer,
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=sql-lock.html

regards,
bhuvaneswaran

Matt Mello <alien@spaceship.com> writes:
> 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.

> Can someone point me to documentation on what this is and how to prevent 
> it?  [My search on the newsgroups yielded far too much for me to read.]

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

 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

Matt Mello <alien@spaceship.com> writes:
>  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

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?
        regards, tom lane

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

Matt Mello <alien@spaceship.com> writes:
> There are no triggers or rules except for the foreign keys.
> The txn table has several foreign keys.

You are probably getting deadlocks on the rows referenced by the foreign
keys, then.  As of the current release, each UPDATEd row will cause the
foreign-key triggers to grab a row lock (SELECT FOR UPDATE lock) on the
referenced row in the other table.  If you have concurrent updates
touching rows that reference the same other-table rows, it's possible to
get a deadlock depending on the order in which the updates happen to
occur.

There is no particularly good SQL-level workaround for this; the only
one I can think of is to do the updates one row at a time (or at least
one foreign-key value at a time), which sucks.

There is a patch in CVS tip that alleviates the problem substantially
by not taking a foreign-table lock when the foreign key value isn't
being changed by the UPDATE, which is commonly the case (although I'm
not entirely sure that it will help you, since you do seem to be
updating the referencing column).  If you want to try it, I believe Jan
Wieck posted a 7.3 version of the patch a month or so ago.
        regards, tom lane

Tom Lane wrote:> You are probably getting deadlocks on the rows referenced by the foreign> keys, then.  As of the
currentrelease, each UPDATEd row will cause the> foreign-key triggers to grab a row lock (SELECT FOR UPDATE lock) on
the>referenced row in the other table.  If you have concurrent updates> touching rows that reference the same
other-tablerows, it's possible to> get a deadlock depending on the order in which the updates happen to> occur.
 
> There is a patch in CVS tip that alleviates the problem substantially> by not taking a foreign-table lock when the
foreignkey value isn't> being changed by the UPDATE, which is commonly the case (although I'm> not entirely sure that
itwill help you, since you do seem to be> updating the referencing column).  If you want to try it, I believe Jan>
Wieckposted a 7.3 version of the patch a month or so ago.
 

I see.  One item I forgot to mention ...  Both update statements have a 
where clause containing txn.terminalid=38.  Txn.terminalid is a foreign 
key column in that table (txn.terminalid->terminal.terminalid).  Could 
this be the source of the trouble?

If so, I can work around this for now, but the patch you mention sounds 
like it would work in almost all situations where this might adversely 
(and unexpectedly) affect us.

Thanks!

-- 
Matt Mello

Matt Mello <alien@spaceship.com> writes:
> I see.  One item I forgot to mention ...  Both update statements have a 
> where clause containing txn.terminalid=38.  Txn.terminalid is a foreign 
> key column in that table (txn.terminalid->terminal.terminalid).  Could 
> this be the source of the trouble?

Not per se.  The problem depends on which rows get updated and in what
sequence --- the contents of the WHERE clause aren't the issue, except
insofar as they determine the set of rows to be updated.
        regards, tom lane

Okay, so one last question on this...

If I do the following, assuming I haven't applied the patch, will it 
still result in a deadlock (in other words, does this only happen with 
multi-record updates, or can it happen with independent single-record 
updates inside transactions)?

THREAD1:                                                THREAD2:

BEGIN;
update txn set ... where txn.terminalid = 1;
                                                          BEGIN;                    update txn set ... where
txn.terminalid= 2;
 

(I anticipate the next statement will give me a deadlock.)

update txn set ... where txn.terminalid = 2;
                    update txn set ... where txn.terminalid = 1;
COMMIT;
                                                         COMMIT;

Thanks, again!


Tom Lane wrote:
> Matt Mello <alien@spaceship.com> writes:
> 
>>I see.  One item I forgot to mention ...  Both update statements have a 
>>where clause containing txn.terminalid=38.  Txn.terminalid is a foreign 
>>key column in that table (txn.terminalid->terminal.terminalid).  Could 
>>this be the source of the trouble?
> 
> 
> Not per se.  The problem depends on which rows get updated and in what
> sequence --- the contents of the WHERE clause aren't the issue, except
> insofar as they determine the set of rows to be updated.
> 
>             regards, tom lane
> 
> 

-- 
Matt Mello
512-350-6900

Okay, I finally found in the docs where it says that all locks acquired 
continue until rollback or commit (end of txn), which answers my 
question in previous email (bottom).

So, it seems like having an UPDATE ... ORDER BY would help this.  At 
least I could force which order rows are locked that way.  As mentioned 
earlier, though, I can do that manually as multiple updates in a single 
transaction.

So, if I have 2 DB connections, and each one is doing the same update 
but on 2 different rows in the same table, and both of those rows have 
foreign keys to the same rows in other tables, is there a possibility 
that the foreign key locks could be acquired in different orders?  In 
other words, are there situations with foreign keys where the only way I 
can prevent a deadlock with update/insert is to NOT HAVE foreign keys?

The two situations I can think of are:
1) Two FK fields in a single row pointing to 2 different FK tables, for 
which any 2 updates get locks in opposite order (seems like a deadlock).
2) Two FK fields pointing to only 1 FK table (both fields are references 
to the same table), for which each update gets independent locks instead 
of one lock for both fields, so there is a potential that #1 problem 
could impact this as well.

Is this the right maillist for this?

Thanks again!


Matt Mello wrote:
> If I do the following, assuming I haven't applied the patch, will it 
> still result in a deadlock (in other words, does this only happen with 
> multi-record updates, or can it happen with independent single-record 
> updates inside transactions)?

Matt Mello <alien@spaceship.com> writes:
> The two situations I can think of are:
> 1) Two FK fields in a single row pointing to 2 different FK tables, for 
> which any 2 updates get locks in opposite order (seems like a deadlock).

I don't think this is possible, at least in 7.3.  All backends are
guaranteed to run the triggers of a given table in the same order,
so the foreign-row locks should be acquired in the same column order
in all cases.

> 2) Two FK fields pointing to only 1 FK table (both fields are references 
> to the same table), for which each update gets independent locks instead 
> of one lock for both fields, so there is a potential that #1 problem 
> could impact this as well.

Not clear what you mean by "independent locks"; there is only one row
lock on a row.

The cases that I'd be concerned about are not within-row conflicts, but
multi-row conflicts...
        regards, tom lane

Tom Lane wrote:
> Matt Mello <alien@spaceship.com> writes:
> 
>>The two situations I can think of are:
>>1) Two FK fields in a single row pointing to 2 different FK tables, for 
>>which any 2 updates get locks in opposite order (seems like a deadlock).
> 
> 
> I don't think this is possible, at least in 7.3.  All backends are
> guaranteed to run the triggers of a given table in the same order,
> so the foreign-row locks should be acquired in the same column order
> in all cases.

Except the sessions set deferred modes differently.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Jan, can you explain more to a newby like me what you mean by "the 
sessions set deferred modes differently?"

And, do these locks cascade?  If I choose to do an update on table A, 
and it has a foreign key to table B, which has a foreign key to table C, 
does the update-induced lock on A cause a lock on B /and/ C?

My entire system is setup with foreign keys.  Almost all of my tables 
are part of a "tree" of foreign keys, which can all be traced back to 
one root table.  If a lock on a leaf table causes locks all the way back 
to the root table, then foreign keys can't be used in my system (and it 
seems they wouldn't be useful in many other systems ... where this also 
true).

However, if the recent FK-lock bug fix stops this unnecessary locking, 
things should work well, I guess.  I have built and installed the 
7.3STABLE tip, but not tested this situation yet.

Thanks again!


Jan Wieck wrote:
> Tom Lane wrote:
> 
>> Matt Mello <alien@spaceship.com> writes:
>>
>>> The two situations I can think of are:
>>> 1) Two FK fields in a single row pointing to 2 different FK tables, 
>>> for which any 2 updates get locks in opposite order (seems like a 
>>> deadlock).
>>
>>
>>
>> I don't think this is possible, at least in 7.3.  All backends are
>> guaranteed to run the triggers of a given table in the same order,
>> so the foreign-row locks should be acquired in the same column order
>> in all cases.
> 
> 
> Except the sessions set deferred modes differently.
> 
> 
> Jan
> 

-- 
Matt Mello

On Mon, 19 May 2003, Matt Mello wrote:

> Jan, can you explain more to a newby like me what you mean by "the
> sessions set deferred modes differently?"

If a constraint is deferred in one transaction and not in another the
order the locks are grabbed could be different.  For example you have two
constraints A and B, and two transactions T1 and T2.  T1 sets constraint A
to be deferred, T2 has constraint A being immediate. T1 will get the lock
for B first (at end of statement) and then get lock A second (at end of
transaction).  T2 will get lock A first (at end of statement) and B second
(at end of statement).  This could cause deadlock if both transactions do
something that locks the same rows for A and B and T1 gets the B lock,
then T2 gets the A lock, then T2 waits on T1 for the B lock, then T1 waits
for T2 on the A lock when it goes to commit.

> And, do these locks cascade?  If I choose to do an update on table A,
> and it has a foreign key to table B, which has a foreign key to table C,
> does the update-induced lock on A cause a lock on B /and/ C?

Not for checks since those don't change the table in question.  It's
possible for locks to cascade through referential action effects (although
that effect is partially minimized by the bug fix mentioned for
update unless the referencing column is itself the one being referenced)

Stephan Szabo wrote:> [...]

Yupp

>>And, do these locks cascade?  If I choose to do an update on table A,
>>and it has a foreign key to table B, which has a foreign key to table C,
>>does the update-induced lock on A cause a lock on B /and/ C?
> 
> 
> Not for checks since those don't change the table in question.  It's
> possible for locks to cascade through referential action effects (although
> that effect is partially minimized by the bug fix mentioned for
> update unless the referencing column is itself the one being referenced)

Normally referential actions like ON DELETE CASCADE will cascade top
town, parent->child->grandchild. If an update to table A cascades
through a referential integrity constraint into an update to table B,
there is no possible deadlock through this action by itself. The primary
key colum(s) in A being updated must have a UNIQUE constraint (as per
SQL standard). Having a lock for that column(s) in A means (logically in
this context) having a lock on all referencing rows in B. So noone else
can attempt to update these rows "through this constraints referential
action".

The other way around there can be multiple path's upward from many 
tables to common RI ancestors. But the checks done do not cascade up 
since they only lock for update. With my patch they don't even do that 
"if the referencing columns did not change.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #