SP_LOCK Question

[Frank Py]

On a hung query and using the SP_Lock to reveal 2 contending SPID's:
Is there ever a time when you would justify the release one SPID instead
of both SPIDs that were contending for the same ObjID? would this depend
on the modes the SPIDs hold? Help appreciated.

Frank




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Re: SP_LOCK Question

[posted and mailed, please reply in news]

Frank Py (fpy@proactnet. com) writes:[color=blue]
> On a hung query and using the SP_Lock to reveal 2 contending SPID's:
> Is there ever a time when you would justify the release one SPID instead
> of both SPIDs that were contending for the same ObjID? would this depend
> on the modes the SPIDs hold? Help appreciated.[/color]

Just because they are at the same object id, does not mean that there is a
conflict. They might be accessing different rows. Or both may be SELECT
queries.

To see if there is any blocking, use sp_who and notice the Blk column. If
this column has a non-zero value, the spid on this row is blocked by the
spid in the Blk column.

Notice also, that if the processes are waiting for each other, SQL Server
will detect this, and select one of the processes as a deadlock victim
and roll it back.

One reason a query might appear to be hung, may be that the query for
one reason or another takes several hours to run. This can happens
if there are no good indexes for the query.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp