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
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
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
)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]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Locks] ADD CONSTRAINT [DF_Locks_LockName] DEFAULT ('') FOR [LockName]
ALTER TABLE [dbo].[Locks] ADD CONSTRAINT [DF_Locks_Locked] DEFAULT ((0)) FOR [Locked]
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:
select * From Locks with (updlock rowlock) where LockName='A'
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.