Why does row level locking not work correctly on an SQL server?

Question

Is this a continuation of When do I update / insert one row if it locks the entire table?

Here is my problem.

I have a table in which locks are stored, so that other records in the system do not need to do locks on shared resources, but they can still queue tasks so that they are performed one at a time.

When I access an entry in this lock table, I want to be able to lock it and update it (only one entry), without any other process that can do the same. I can do this with a lock hint like updlock .

What happens is that although Im uses a lock to lock records, it blocks the request to another process in order to change an absolutely unrelated row in the same table, which would also indicate updlock tell me with rowlock .

You can recreate this by creating 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 lines to lock with LockName = 'A and one for LockName =' B

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

Request 1:

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

Request 2:

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

Please note that I leave the transaction open so that you can see this problem, since it will not be visible without this open transaction.

When Query 1 starts, locks are problems for the row, and any subsequent requests for LockName = A will have to wait. This is the correct behavior.

If this is a little disappointing when you start Query 2 , you are locked until Query 1 ends, even think that these are unrelated entries. If you run Query 1 again, as soon as I can, it will transfer the previous transaction, Query 2 will start, and then Query 1 will block the record again.

Please offer some tips on how I can correctly pin ONLY one line, and also not prevent other elements from being updated.

PS. Holdlock also does not create the correct behavior after updating one of the rows.

Answer 1

In SQL Server lock hints apply to checked objects, not to them.

Typically, the engine places a general lock on objects (pages, etc.) while they are being read and raises them (or does not raise them in a SERIALIZABLE transaction) after the scan is completed.

However, you instruct the engine to place (and raise) update locks that are incompatible with each other.

Operation B blocked when trying to put UPDLOCK on an already locked row using UPDLOCK on transaction A

If you create an index and force it (so that no conflicting readings are ever encountered), your tables will not be locked:

 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 2

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

Indicates that the database engine does not read rows that are blocked by other transactions. In most cases, circumstances, the same is true for the page. When READPAST is specified, row-level and page-level locks are skipped. That is, the Engine database skips rows or pages instead of locking the current transaction until the locks are released

This is usually used in environments such as queue processing - therefore, several processes can display the next element from the queue table without blocking other processes (of course, using UPDLOCK to prevent several processes occupying the same line).

Change 1:
This may be caused if you do not have an index in the LockName field. With an index, query 2 can make an index tending to the exact row. But without it, he will do a check (checking each row), which means that it is held by the first transaction. Therefore, if it is not indexed, try indexing it.

Answer 3

I'm not sure what you are trying to execute, but usually those who deal with similar problems want to use sp_getapplock. Covered by Tony Rogerson: Helping Concurrency by Creating Custom Locks (Mutexs in SQL)

Answer 4

If you need a queue in SQL Server, use the UPDLOCK, ROWLOCK, READPAST prompts . He works.

I would rather change your approach, rather than trying to change the behavior of SQL Server ...