为什么SQL Server中的行级锁定似乎无法正常工作？

Question

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

我遇到了一个问题。

我有一张表用于保存锁，这样系统中的其他记录就不必为常用资源申请锁，但仍然可以排队执行任务。

当我访问此锁表中的一条记录时，我想锁定并更新它（只更新一条记录），而不允许其他进程进行相同的操作。我可以使用像updlock这样的锁提示来实现这一点。

然而，尽管我使用了rowlock来锁定记录，但它阻止了另一个进程请求更改同一张表中完全无关的行，该进程也将指定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'

Query 2:

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

请注意，我保持交易开放，以便您可以看到此问题，因为如果没有这个开放的交易，它是不可见的。

当您运行“查询1”时，会为该行和任何后续查询“LockName ='A'”发出锁定，这种行为是正确的。

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

请提供一些建议，以便我能够正确锁定“仅”一个行，并防止其他项目被更新。

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

- Middletone

Answer 1

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

通常，在读取对象（页面等）时，引擎会对其进行共享锁定，并在扫描完成后释放它们（或在 SERIALIZABLE 事务中不释放）。

但是，您可以指示引擎放置（和解除）互不兼容的更新锁。

当事务 A 通过一个 UPDLOCK 对行进行锁定时，事务 B 在尝试将一个 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'

- Quassnoi

---

那是一个很好的答案，感谢您发布了有用的代码。有没有一种方法可以使SQL默认处于此模式？ - Middletone

@Middletone：什么是“default”？ - Quassnoi

很抱歉，我应该问如何让SQL使用索引而不是扫描整个表，因为这可能是导致它不使用表上的索引的原因。是否有一种方法可以使其始终锁定主键，无论它如何在表中找到记录？这是我的默认视图。 - Middletone

@Middletone：恐怕你不能（可靠地）这样做。你可以尝试一些邪恶的魔法，涉及到使用不同锁定选项的表的自连接，并强制访问嵌套循环。 - Quassnoi

Answer 2

对于查询2，尝试使用READPAST提示 - 如下所示：

指定数据库引擎不读取被其他事务锁定的行。在大多数情况下，页面也是如此。当指定READPAST时，跳过行级和页级锁定。也就是说，数据库引擎跳过行或页而不是阻止当前事务直到锁定释放。

这通常用于队列处理类型的环境中 - 因此，多个进程可以从队列表中取出下一个项目，而不会被其他进程阻塞（当然，使用UPDLOCK防止多个进程选择相同的行）。

编辑1：
如果LockName字段上没有索引，则可能会导致此问题。有了索引，查询2可以进行索引查找以获得精确的行。但是，如果没有索引，则会执行扫描（检查每一行），这意味着它被第一个事务卡住。因此，如果未索引，请尝试对其进行索引。

- AdaTheDev

---

如果我使用readpast，而另一个进程已经锁定了b，那么将不会返回任何内容，这同样是一个问题。这也无法解决行级锁定不正确的根本问题。 - Middletone

再次更改索引并没有什么效果。我已经费心贴出了所有的代码，请在回答之前测试一下您的解决方案。 - Middletone

@Middletone - 很好。很多时候，要立即复制某人遇到的特定问题实际上是很困难的 - 除非可以这样做，否则不总是可能说“这就是解决您问题的方法”。我有可行的解决方案，这是我在尝试帮助您时基于的答案 - 还有我的时间和精力。 - AdaTheDev

谢谢你尝试了一下。我仍然很感激。 - Middletone

Answer 3

我不确定你想要实现什么，但通常那些遇到类似问题的人想要使用sp_getapplock。由Tony Rogerson撰写：通过创建自己的锁（SQL中的Mutexs）来协助并发

- A-K

Answer 4

如果你想在SQL Server中使用队列，请使用UPDLOCK，ROWLOCK，READPAST提示。它有效。

我会考虑改变你的方法，而不是试图改变SQL Server的行为...

- gbn