[thelist] SQL Server, Updates and Locking Hints

Chris at globet.com Chris at globet.com
Tue Jul 5 08:20:39 CDT 2005


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.

Regards

Chris Marsh


More information about the thelist mailing list