Blocking problem

SQL2K, sp3
Active Server Pages front-end

I am having a blocking problem with a spid executing a stored
procedure that does no updates. When I look at the locks the blocking
spid has, some of them have lock mode "IS". Looking in BOL, I see "S"
is shared, but I don't see "IS". Can anyone tell me what lock mode
"IS" is?

And how could a stored procedure that does no updates be guilty of
blocking?
There are no cursors in it.

Thanks![posted and mailed, please reply in news]

Hal (hforman1@.cfl.rr.com) writes:
> I am having a blocking problem with a spid executing a stored
> procedure that does no updates. When I look at the locks the blocking
> spid has, some of them have lock mode "IS". Looking in BOL, I see "S"
> is shared, but I don't see "IS". Can anyone tell me what lock mode
> "IS" is?

Intent Shared. If memory serves, if a process has a lock on row level,
it will also take out an intent lock on table level, at least under
some circumstances.

> And how could a stored procedure that does no updates be guilty of
> blocking?

It could block a writer that wishes to update one of the rows that the
reader locks.

To get a better hold of the locking situation, it's probably better to
lock at the blocking processes, and see what lock it is blocked on. I
have a routine on my web site that is conventient for this kind of thing.
You get all active processes (and if a process holds a lock it's active
by definition), their locks and their current statements. Spot the blocked
processes and lock for locks with status WAIY. You find it at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp