Re: Deadlock Checker doesn't see foreign-key locks

Stephan Szabo wrote:

>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

>
>