Обсуждение: Deadlock Checker doesn't see foreign-key locks
It appears to me that the Deadlock Checker doesn't see (and thus release) foreign-key-based locks (see below for details). Am I missing something? Is there a configuration item I am unaware of? As far as I can tell (yes, I tested thoroughly) if I create a normal deadlock situation as follows, the deadlock detector will work properly: Thread A: BEGIN WORK; UPDATE [table A, row X] Thread B: BEGIN WORK; UPDATE [table B, row Y] Thread A: UPDATE [table B, row Y] Thread B: UPDATE [table A, row X] This is recognized by the deadlock detector (after the specified delay), one of the two is rolled back, and the other completes happily. HOWEVER, if I have a foreign-key-related lock, as follows, it is not recognized: Thread A: BEGIN WORK; UPDATE [table A, row W] /* This has a foreign key into table F, row P */ Thread B: BEGIN WORK; UPDATE [table B, row Y] /* This has a foreign key into table G, row Q */ Thread A: UPDATE [table B, row Z] /* This has a foreign key into table F, row P */ Thread B: UPDATE [table A, row X] /* This has a foreign key into table G, row Q */ Note that none of the UPDATEs step on the same actual row of the same table, but they step (and lock) the same rows in the same tables via foreign keys. In this case (specifically tested), there is no deadlock detection. NOTE - I am currently using v7.2.1, although my search through the release notes lead me to believe that the problem was not addressed subsequently.
On Mon, 13 Jan 2003, Andrew Biagioni wrote: > It appears to me that the Deadlock Checker doesn't see (and thus > release) foreign-key-based locks (see below for details). Am I missing > something? Is there a configuration item I am unaware of? We're going to need a runnable example, I'm not 100% sure which tables are referencing which other tables and how given the text below. I have gotten deadlock messages from the foreign keys in the past though. > HOWEVER, if I have a foreign-key-related lock, as follows, it is not > recognized: > > Thread A: BEGIN WORK; > UPDATE [table A, row W] > /* This has a foreign key into table F, row P */ > > Thread B: BEGIN WORK; > UPDATE [table B, row Y] > /* This has a foreign key into table G, row Q */ > > Thread A: UPDATE [table B, row Z] > /* This has a foreign key into table F, row P */ > > Thread B: UPDATE [table A, row X] > /* This has a foreign key into table G, row Q */ > > Note that none of the UPDATEs step on the same actual row of the same > table, but they step (and lock) the same rows in the same tables via > foreign keys. > > In this case (specifically tested), there is no deadlock detection. Do you get a deadlock? Given the text above, I wouldn't expect one since both transactions have the locks already when the second request for the same lock comes in (unless you meant to swap A and B in the bottom two).
Stephan Szabo wrote:
I tried to create a runnable example, and as it turns out I can't reproduce the problem in a proof-of-concept configuration. I'll have to do some more homework to determine the REAL scenario in which my problem occurs (and thus, the real cause of the problem).
Thanks,
Andrew
On Mon, 13 Jan 2003, Andrew Biagioni wrote:It appears to me that the Deadlock Checker doesn't see (and thus release) foreign-key-based locks (see below for details). Am I missing something? Is there a configuration item I am unaware of?We're going to need a runnable example, I'm not 100% sure which tables are referencing which other tables and how given the text below. I have gotten deadlock messages from the foreign keys in the past though.
I tried to create a runnable example, and as it turns out I can't reproduce the problem in a proof-of-concept configuration. I'll have to do some more homework to determine the REAL scenario in which my problem occurs (and thus, the real cause of the problem).
HOWEVER, if I have a foreign-key-related lock, as follows, it is not recognized: Thread A: BEGIN WORK; UPDATE [table A, row W] /* This has a foreign key into table F, row P */ Thread B: BEGIN WORK; UPDATE [table B, row Y] /* This has a foreign key into table G, row Q */ Thread A: UPDATE [table B, row Z] /* This has a foreign key into table F, row P */ Thread B: UPDATE [table A, row X] /* This has a foreign key into table G, row Q */ Note that none of the UPDATEs step on the same actual row of the same table, but they step (and lock) the same rows in the same tables via foreign keys. In this case (specifically tested), there is no deadlock detection.Do you get a deadlock? Given the text above, I wouldn't expect one since both transactions have the locks already when the second request for the same lock comes in (unless you meant to swap A and B in the bottom two).
Thanks,
Andrew