Re: Hit deadlock with 2 threads updating DIFFERENT tables! Why?

"theRat" <john_hilgedick_at_yahoo.com> wrote in news:1147387305.904309.213710 @u72g2000cwu.googlegroups.com:

> Brian,
>
> I'm not sure what you mean when you say "Session 9 is looking for an
> exclusive lock but it cannot obtain the lock because sessions 56 and
> 125 have a shared lock on that
> common resource. ".
>
> Also, there is a relationship between the Task table and the Manager
> table. The Task table has a column named "managerId" which is a
> foreign key to the Manager table (a task has a manager). Is it
> possible that since sessions 56 and session 125 *ARE* updating the
> managerId of their respectives tasks in the Task table, that this is
> putting some sort of lock on the entire Managers table - just briefly -
> to make sure the values the sessions are assigning to the managerId
> column are a valid ids in the Manager table? In other words, when you
> update a foreign key in one row from value A to value B (foreign keys
> to another table) does that place any sort of lock on row A or row B or
> the "entire table" in the other table?
>

It depends.
When you have FK and no corresponding index, then the whole table gets locked. Received on Thu May 11 2006 - 20:48:50 CDT