[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