sql-server - 为什么行级锁定在 SQL Server 中似乎无法正常工作？

Question

这是 When I update/insert a single row should it lock the entire table? 的延续

这是我的问题。

我有一个持有锁的表，这样系统中的其他记录就不必锁定公共(public)资源，但仍然可以对任务进行排队，以便一次执行一个任务。

当我访问此锁定表中的一条记录时，我希望能够锁定它并更新它(仅一条记录)，而任何其他进程都无法执行相同操作。我可以使用锁定提示(例如 updlock)来完成此操作。

但是发生的情况是，即使我使用行锁来锁定记录，它也会阻止对另一个进程的请求，以更改同一个表中完全不相关的行，该行也指定了 updlock 提示以及 rowlock。

您可以通过制作表格来重新创建它

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' 添加一行

然后创建两个查询以同时在事务中运行:

查询 1:

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

查询 2:

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

请注意，我将交易保持打开状态，以便您可以看到此问题，因为如果没有此打开交易，该问题将不可见。

当您运行查询 1 时，该行会出现锁定问题，并且 LockName='A' 的任何后续查询都必须等待。这种行为是正确的。

令人有点沮丧的是，当您运行查询 2 时，您会被阻止，直到查询 1 完成，即使这些是不相关的记录。如果您像上面那样再次运行查询 1，它将提交上一个事务，查询 2 将运行，然后查询 1将再次锁定记录。

请提供一些建议，告诉我如何让它仅正确锁定一行，而不阻止其他项目的更新。

PS。在更新其中一行后，Holdlock 也无法产生正确的行为。

Answer 1

在 SQL Server ，锁定提示应用于扫描的对象，不匹配。

通常，引擎在读取对象(页面等)时会在对象(页面等)上放置共享锁，并在扫描完成后解除它们(或在 SERIALIZABLE 事务中不解除)。

但是，您指示引擎放置(并解除)彼此不兼容的更新锁。

交易B尝试放置 UPDLOCK 时锁定到已经用 UPDLOCK 锁定的行上按交易 A .

如果您创建索引并强制使用它(这样就不会发生读冲突)，您的表将不会锁定:

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'