MSSQL:如果行不存在，HOLDLOCK是如何工作的?

Question

我运行这个查询:

IF EXISTS(SELECT * FROM dbo.Foo WITH (UPDLOCK, HOLDLOCK) WHERE Col1 = @Col1 AND Col2 = @Col2)
...

让我们假设Col1和Col2都不是主键，并且查询不匹配任何行:

锁在哪个实体上?桌子本身?

Answer 1

看起来，如果没有相关的索引，它将range - u锁定所有聚集的索引键，或者在堆的情况下，它将采用排他表锁。如

use tempdb
go
drop table if exists Foo
go
create table Foo(id int primary key /*nonclustered*/, Col1 int, Col2 int);
go
with q as
(
select row_number() over (order by (select null)) i
from sys.messages 
)
insert into foo(id,Col1,Col2) 
select top 10 i, i*10, i* 5 
from q

begin transaction
declare @Col1 int = 15
declare @col2 int = 5
SELECT * FROM dbo.Foo WITH (UPDLOCK, HOLDLOCK) 
WHERE Col1 = @Col1 AND Col2 = @Col2
select *
from sys.dm_tran_locks
where request_session_id = @@spid 
rollback