Why does row level locking not appear to work correctly in SQL server?

Question

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 system don’t have to take locks out on common resources, but can still queue the tasks so that they get executed one at a time.

When I access a record in this locks table I want to be able to lock it and update it (just the one record) without any other process being able to do the same. I am able to do this with a lock hint such as updlock.

What happens though is that even though I’m using a rowlock to lock the record, it blocks a request to another process to alter a completely unrelated row in the same table that would also have specified the updlock hint along with rowlock.

You can recreate this be making a table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Locks](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [LockName] [varchar](50) NOT NULL,
    [Locked] [bit] NOT NULL,
 CONSTRAINT [PK_Locks] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Locks] ADD  CONSTRAINT [DF_Locks_LockName]  DEFAULT ('') FOR [LockName]
GO
ALTER TABLE [dbo].[Locks] ADD  CONSTRAINT [DF_Locks_Locked]  DEFAULT ((0)) FOR [Locked]
GO

Add two rows for a lock with LockName=‘A’ and one for LockName=‘B’

Then create two queries to run in a transaction at the same time against it:

Query 1:

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

Query 2:

select * From Locks with (updlock rowlock) where LockName='B'

Please note that I am leaving the transaction open so that you can see this issue since it wouldn’t be visible without this open transaction.

When you run Query 1 locks are issues for the row and any subsequent queries for LockName=’A’ will have to wait. This behaviour is correct.

Where this gets a bit frustrating is when you run Query 2 you are blocked until Query 1 finishes even thought these are unrelated records. If you then run Query 1 again just as I have it above, it will commit the previous transaction, Query 2 will run and then Query 1 will once again lock the record.

Please offer some suggestions as to how I might be able to have it properly lock ONLY the one row and not prevent other items from being updated as well.

PS. Holdlock also fails to produce the correct behaviour after one of the rows is updated.

Answer 1

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.

Answer 2

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'

Answer 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)

Answer 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...