Why Does Row Level Locking Not Appear To Work Correctly In Sql Server?

This is a continuation from When I update/insert a single row should it lock the entire table? Here is my problem. I have a table that holds locks so that other records in the syst

Solution 1:

In SQL Server, the lock hints are applied to the objects scanned, not matched.

Normally, the engine places a shared lock on the objects (pages etc) while reading them and lifts them (or does not lift in SERIALIZABLE transactions) after the scanning is done.

However, you instruct the engine to place (and lift) the update locks which are not compatible with each other.

The transaction B locks while trying to put an UPDLOCK onto the row already locked with an UPDLOCK by transaction A.

If you create an index and force its usage (so no conflicting reads ever occur), your tables will not lock:

CREATE INDEX ix_locks_lockname ON locks (lockname)

Begin transaction
select*From Locks with (updlock rowlock INDEX (ix_locks_lockname)) where LockName='A'Begin transaction
select*From Locks with (updlock rowlock INDEX (ix_locks_lockname)) where LockName='B'

Solution 2:

For query 2, try using the READPAST hint - this (quote):

Specifies that the Database Engine not read rows that are locked by other transactions. Under most circumstances, the same is true for pages. When READPAST is specified, both row-level and page-level locks are skipped. That is, the Database Engine skips past the rows or pages instead of blocking the current transaction until the locks are released

This is typically used in queue-processing type environments - so multiple processes can pull off the next item from a queue table without being blocked out by other processes (of course, using UPDLOCK to prevent multiple processes picking up the same row).

Edit 1: It could be caused if you don't have an index on the LockName field. With the index, query 2 could do an index seek to the exact row. But without it, it would be doing a scan (checking every row) meaning it gets held up by the first transaction. So if it's not indexed, try indexing it.

Solution 3:

I am not sure what you are trying to accomplish, but typically those who are dealing with similar problems want to use sp_getapplock. Covered by Tony Rogerson:Assisting Concurrency by creating your own Locks (Mutexs in SQL)

Solution 4:

If you want queueing in SQL Server, use UPDLOCK, ROWLOCK, READPAST hints. It works.

I'd consider changing your approach rather than trying to change SQL Server behaviour...