deadlocks

It would appear that I am having a strange deadlock error. I have two
different stored procedures that are both inserting into the same table.
Other than that they have nothing in common. One of the procs has a locking
hint of (TABLOCK, HOLDLOCK) on the insert so that the table isn't modified
until after the tranaction is completed. I thought that deadlocking was the
locking of two tables by two processes that that the other wanted? I didn't
think that dead locks could happen with two processes and one table.Hi Wilbur
There are two major / common deadlock
scenarios. "Cyclical" deadlocking is where two connections
acquire locks on objects in reverse order, deadlocking
each other. "Conversion" deadlocking occurs when two
connections acquire shared locks on the same resource (eg
table) but then both want to convert the shared lock to
exclusive. Because they both already hold a shared lock on
that resource, who's to say either should let go first -
therefore they deadlock.
Your scenario sounds to me like a conversion deadlock
scenario. Your use of HOLDLOCK indicates you're acquiring
a shared lock (eg select). If you insert into that same
table, the connections will try to convert that shared
lock they're already holding to exclusive & deadlock each
other.
In short, what you might really want is UPDLOCK, which
acquires a lock that always be converted to exclusive
(almost like acquiring an exclusive in the first place) &
therefore avoids the conversion deadlock scenario.
HTH
Regards,
Greg Linwood
SQL Server MVP
>--Original Message--
>It would appear that I am having a strange deadlock
error. I have two
>different stored procedures that are both inserting into
the same table.
>Other than that they have nothing in common. One of the
procs has a locking
>hint of (TABLOCK, HOLDLOCK) on the insert so that the
table isn't modified
>until after the tranaction is completed. I thought that
deadlocking was the
>locking of two tables by two processes that that the
other wanted? I didn't
>think that dead locks could happen with two processes and
one table.
>
>.
>