sql-server - UPDLOCK 和 HOLDLOCK 查询未创建预期的锁

Question

我有下表:

CREATE TABLE [dbo].[table1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NULL,
 CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

我正在学习 SQL 锁的工作原理，并且我正在尝试测试一种情况，在这种情况下我想锁定一行以防止读取和更新。此任务中的一些灵感来自此 article , 这是 original problem我正在尝试解决。

当我运行此 T-SQL 时:

BEGIN TRANSACTION

SELECT * FROM dbo.table1 WITH (UPDLOCK, HOLDLOCK)
WAITFOR DELAY '00:00:15'

COMMIT TRANSACTION

我希望在表上放置一个独占锁，特别是针对行(如果我在主键上有一个 WHERE 语句)

但是运行这个查询，我可以看到 GRANTed LOCK 是针对请求模式 IX。

SELECT * FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID() AND resource_associated_entity_id = OBJECT_ID(N'dbo.table1');

此外，在单独的 SSMS 窗口中，我可以在事务运行时完全查询表。

为什么 MSSQL 不遵守锁定提示？

(SQL 服务器 2016)

编辑 1
任何有关这些锁如何工作的信息都值得赞赏，但是，手头的问题是 SQL Server 似乎没有强制执行我指定的锁。我的直觉是，这与行版本控制或相关内容有关。

编辑2
我创建了这个 Github gist .它需要 .NET 和外部库 Dapper 才能运行(可通过 Nuget 包获得)。

这是我注意到的有趣的事情:

• SELECT 语句可以针对 table1 运行，即使先前使用 UPDLOCK, HOLDLOCK 的查询已被请求。
• INSERT 语句不能在锁存在时运行
• 当锁定存在时，无法运行针对现有 记录的 UPDATE 语句
• 可以针对不存在的记录运行 UPDATE 语句。

这是该 Gist 的控制台输出:

Run locking SELECT Start - 00:00:00.0165118
Run NON-locking SELECT Start - 00:00:02.0155787
Run NON-locking SELECT Finished - 00:00:02.0222536
Run INSERT Start - 00:00:04.0156334
Run UPDATE ALL Start - 00:00:06.0259382
Run UPDATE EXISTING Start - 00:00:08.0216868
Run UPDATE NON-EXISTING Start - 00:00:10.0236223
Run UPDATE NON-EXISTING Finished - 00:00:10.0268826
Run locking SELECT Finished - 00:00:31.3204120
Run INSERT Finished - 00:00:31.3209670
Run UPDATE ALL Finished - 00:00:31.3213625
Run UPDATE EXISTING Finished - 00:00:31.3219371

Answer 1

and I'm trying to test a situation where I want to lock a row from being read and updated

如果你想锁定一行不被读取和更新，你需要一个独占锁，但是UPDLOCK锁提示请求更新锁，而不是独占锁。查询应该是:

SELECT * FROM table1 WITH (XLOCK, HOLDLOCK, ROWLOCK)
WHERE Id = <some id>

此外，在 READ COMMITTED SNAPSHOT 下和 SNAPSHOT隔离级别，SELECT语句不请求共享锁，只请求模式稳定性锁。因此，SELECT尽管存在独占锁，语句仍可以读取该行。令人惊讶的是，在 READ COMMITTED 隔离级别下，SELECT 语句可能不会请求行级共享锁。您需要向 SELECT 添加查询提示防止它读取锁定行的语句:

SELECT * FROM dbo.Table1 WITH (REPEATABLEREAD)
WHERE id = <some id>

与 REPEATABLEREAD锁定提示，SELECT语句将请求共享锁并在事务期间持有它们，因此它不会读取独占锁定的行。请注意，使用 READCOMMITTEDLOCK这还不够，因为 SQL Server 在某些情况下可能不会请求共享锁，如 this 中所述博文。

请看一下 Lock Compatibility Table

在默认隔离级别下READ COMMITTED ，并且没有锁定提示，SELECT statements 为其读取的每一行请求共享锁，并且这些锁在读取该行后立即释放。但是，如果您使用 WITH (HOLDLOCK) ，共享锁一直持有到事务结束。考虑到锁兼容性表，SELECT在 READ COMMITTED 下运行的语句, 可以读取任何未被独占锁定的行(IX、SIX、X 锁)。 INSERT 请求独占锁, UPDATE和 DELETE声明或 SELECT带有 XLOCK 的语句提示。

I would expect an exclusive lock to be placed on the table, and specifically for the row (if I had a WHERE statement on the primary key)

I need to understand WHY SQL Server is not respcting the locking directives given to it. (i.e. Why is an exclusive lock not on the table, or row for that matter?)

UPDLOCK hint 不请求独占锁，它请求更新锁。此外，可以在行本身以外的其他资源上授予锁，可以在表、数据页、索引页和索引键上授予锁。 SQL Server 可以锁定的资源类型的完整列表是:DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, and ALLOCATION_UNIT .当ROWLOCK如果指定了提示，SQL Server 将锁定行，而不是页、范围或表，SQL Server 将锁定的实际资源是 RID的和KEY的