By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,087 Members | 1,520 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,087 IT Pros & Developers. It's quick & easy.

Update statement performing table lock even though where condition on clustered primary index?

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
M Wells <pl**********@planetthoughtful.org> wrote in message news:<cs********************************@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
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.