[thelist] SQL Server, Updates and Locking Hints

Ken Schaefer Ken at adOpenStatic.com
Tue Jul 5 08:44:15 CDT 2005


I second the recommendation on the "Inside SQL Server 2000" book by Kalen
Delaney. It's a bit dated, but one of the best books in SQL Server internals
you can currently get - well worth the cost.

Cheers
Ken

--
www.adOpenStatic.com/cs/blogs/ken/ 

: -----Original Message-----
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Chris at globet.com
: Sent: Tuesday, 5 July 2005 11:21 PM
: To: thelist at lists.evolt.org
: Subject: RE: [thelist] SQL Server, Updates and Locking Hints
: 
: Paul
: 
: > Three things:
: >
: > 1) Are you using WITH (ROWLOCK) on BOTH queries? If one's
: 
: Yes.
: 
: > 2) WITH (ROWLOCK) will only work if SQL Server can find the
: > row in question without doing a table scan -- which means the
: 
: [..]
: 
: > if there's no index on FIELD2, then the only way that SQL
: > will be able to FIND the row to update it is if it does a
: > table scan -- inspecting every row in the table to see if it
: > meets the predicate. As soon as you do that, you lose the
: > ability to lock only the rows being updated, as it has to
: > look at them all anyway. SQL Server will happily ignore
: > ROWLOCK hints (or, indeed, any other kind) if it thinks you
: > don't know what you're doing. I'm guessing this is the
: > problem in your case (that you don't have indexes, not that
: > you don't know what you're doing!)
: 
: This makes total sense, as the fields are NOT indexed appropriately for
: these queries.
: 
: [..]
: 
: > Hope this helps,
: 
: Immensely, thanks very much Paul.



More information about the thelist mailing list