Update statement performing table lock even though where condition on clustered primary index? -- sql server (alternate)

M Wells
4/6/2004 5:46:22 PM

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,

sql NO[at]SPAM hayes.ch
4/7/2004 1:33:08 AM

[quoted text, click to view]

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

sql server (alternate) : Update statement performing table lock even though where condition on clustered primary index?