SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 02:23 AM
| ||||
| ||||
 Update statement performing table lock even though where condition on clustered primary index? 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      |
|
02-29-2008, 02:23 AM
| ||||
| ||||
| Re: Update statement performing table lock even though where condition on clustered primary index? 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 |
Linear Mode
