[thelist] SQL Server, Updates and Locking Hints

[Chris at globet.com]

All

I have a web page that I believe is having problems when another
application updates data in the database. To try and fix the problem, I
have taken the following approach.

If I run the following in one SQL Server Query Analyser (QA) window, I
will be locking Table1 until I commit or rollback the transaction:

 BEGIN TRAN
UPDATE TABLE1
   SET FIELD1 = '1'
 WHERE FIELD2 = '0'

In another QA window, I run the following SQL:

UPDATE TABLE1
   SET FIELD1 = '2'
 WHERE FIELD2 = '3'

As I would expect, this process is blocked until I either commit or
rollback the first transaction. Is there any way that I can get around
this lock? I have been looking at the MSDN documentation on locking
hints, but although ROWLOCK seems to be documented as allowing what I
want, in practice I cannot see the results that I am aiming for.

Any comment would be gratefully received.

Regards

Chris Marsh