为什么行级别锁定在SQL Server中似乎无法正常工作? [英] Why does row level locking not appear to work correctly in SQL server?
问题描述
这是我的问题.
我有一个拥有锁的表，这样系统中的其他记录就不必对公共资源进行锁了，但是仍然可以将任务排队，以便一次执行一个任务.
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.
当我访问此锁定表中的一条记录时，我希望能够锁定它并更新它(仅一条记录)，而没有任何其他过程能够做到这一点.我可以使用诸如 updlock 之类的锁定提示来做到这一点.
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.
尽管发生的是，即使我使用行锁来锁定记录，它也会阻止对另一个进程的请求，以更改同一张表中一个完全不相关的行，该行也已经指定了 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
为带有 LockName ='A'的锁添加两行，为 LockName ='B'
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:
查询1:
Commit
Begin transaction
select * From Locks with (updlock rowlock) where LockName='A'
查询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.
当您运行查询1 时，该行就会出现锁定问题，随后对 LockName ='A'的所有查询都必须等待.这种行为是正确的.
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.
当您运行查询2 时，令人感到沮丧的是，直到查询1 完成，即使您认为这些都是无关的记录，您也会被阻止.如果您再次像上面一样运行查询1 ，它将提交上一个事务，查询2 将运行，然后查询1 将再次锁定记录.
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.在更新其中一行后，保持锁也无法产生正确的行为.
PS. Holdlock also fails to produce the correct behaviour after one of the rows is updated.
推荐答案
在SQL Server中，锁定提示将应用于扫描的对象，但不匹配.
In SQL Server, the lock hints are applied to the objects scanned, not matched.
通常，引擎在读取对象时在页面(页面等)上放置一个共享锁，并在扫描完成后将其提起(或在SERIALIZABLE事务中不提起).
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.
事务B锁定，同时尝试将UPDLOCK放置到已被事务A锁定了UPDLOCK的行上.
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'
这篇关于为什么行级别锁定在SQL Server中似乎无法正常工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
