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

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'