[thelist] SQL Server, Updates and Locking Hints
Paul Cowan
evolt at funkwit.com
Tue Jul 5 07:21:46 CDT 2005
On 5/07/2005 12:14 PM +0100 Chris at globet.com wrote:
> Is there any way that I can get around this lock?
Three things:
1) Are you using WITH (ROWLOCK) on BOTH queries? If one's only taking a row
lock, but one's taking a page or table lock, you'll still get blocking.
2) WITH (ROWLOCK) will only work if SQL Server can find the row in question
without doing a table scan -- which means the table needs to be indexed
appropriately. In your example:
UPDATE TABLE1
SET FIELD1 = '1'
WHERE FIELD2 = '0'
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!)
3) ROWLOCK is one way to reduce locking, but remember the #1 golden rule of
reducing contention: get locks late, release them early. Make sure updates
are as fast as they can be (that's where indexes etc. can come into play)
and always BEGIN TRAN at the last minute, COMMIT as soon as you can. Seems
obvious, but following this rule is one way to help reduce contention in
the database.
If you want a great rundown of locking and other performance-type issues, I
can't recommend the "Inside SQL Server" books from Microsoft Press --
previously by Soukup and Delaney, now just by Soukup (or Delaney, I forget
which; whichever one killed the other one and buried them in the back
garden, anyway[1]).
Hope this helps,
Paul
[1] Oops, forgot the <libel> </libel> tags.
More information about the thelist
mailing list