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

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).