SQL Row Level Record Locking

[Varasano]

Despite the protestations in the manuals, I need
to lock a row during user input. Obviously, I need to maintain read/write
access to other rows. I thought this would be a straightforward use of
ROWLOCK, but I am having problems.


DECLARE LockTest CURSOR
GLOBAL SCROLL DYNAMIC SCROLL_LOCKS TYPE_WARNING FOR
SELECT * FROM TestTable WITH (ROWLOCK)
WHERE UID = @MyUID
FOR UPDATE

OPEN GLOBAL LockTest
FETCH FIRST from LockTest

Note the following:
1- UID is an Indentity Column. If I use UID or any numeric column in the
where clause (whether indexed or not indexed), this cursor seems to
block the formation of any other FOR UPDATE cursor on this table,
even for different values of @MyUID. However, if I use a varchar
column everything works as expected.
WHERE Code = @MyStringCode

Using the varchar column, I time-out if two connections use the same
@MyStringCode, but can create multiple cursors if @MyStringCode is unique.

Obviously, I want to use my identity column virtually 100% of the time
for this type of operation, not a varchar column.

2- SP_LOCKS returns the exact same entries for both varchar and int
WHERE clauses


3- I thought that KEYSET was a better choice than DYNAMIC. However
I get a runtime error:
"FOR UPDATE Cannot be specified with a READ ONLY cursor"
Removing the FOR UPDATE clause, causes me to get
"The cursor is READ ONLY"
despite the fact that I declared it SCROLL_LOCKS