[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