M Wells <planetquirky@planetthoughtful.org> wrote in message news:<csq570tgfu7ui5rq03i0avtmstv382bctg@4ax.com>. ..
> Hi All,
>
> I have a database that is serving a web site with reasonably high
> traffiic.
>
> We're getting errors at certain points where processes are being
> locked. In particular, one of our people has suggested that an update
> statement contained within a stored procedure that uses a where
> condition that only touches on a column that has a clustered primary
> index on it will still cause a table lock.
>
> So, for example:
>
> UPDATE ORDERS SET
> prod = @product,
> val = @val
> WHERE ordid = @ordid
>
> In this case ordid has a clustered primary index on it.
>
> Can anyone tell me if this would be the case, and if there's a way of
> ensuring that we are only doing a row lock on the record specified in
> the where condition?
>
> Many, many thanks in advance!
>
> Much warmth,
>
> Murray

It's possible that the UPDATE is blocking other processes - if MSSQL
takes an update lock on the row, it will also take intent locks at a
higher level. Those intent locks may prevent other processes getting
the locks they want, because not all lock types are compatible. So if
process A holds a row-level update lock, and intent-exclusive locks on
the table, then process B will not be able to obtain an update lock at
the table level.

In your case, if process B requires a table-level lock, then it may be
blocked by process A's intent exclusive lock. But that's just a guess
- you would need to investigate the locks being held in your system.
See "Understanding and Avoiding Blocking" and "Lock Compatibility" in
Books Online for more information. Erland has a useful tool for
examining locks:

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

Simon