为什么SQL Server使用不同的密钥范围锁定SELECT

Question

我有两个相继启动的事务：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
select * from MyTable WITH (XLOCK) WHERE Id = 1
WAITFOR DELAY '00:00:10';
COMMIT TRANSACTION

第二个几乎相同(只是没有延迟和另一个id(

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
select * from MyTable WITH (XLOCK) WHERE Id = 2
COMMIT TRANSACTION

我在Id上确实有一个非唯一索引。至少在Id上有主键的情况下，这似乎确实如预期的那样有效。

据我所知，第一个事务应该真正获得Id为1的密钥范围锁，而另一个事务应该获得Id为2的密钥范围锁定。

显然，它并不是这样工作的，因为第二个事务一直被卡住，直到第一个事务完成。我是错过了什么，还是无法强制使用专用钥匙范围锁？

以下是我的示例的完整创建脚本：

CREATE TABLE [dbo].[MyTable](
[Id] [bigint] NOT NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Answer 1

据我所知，第一个事务应该真正获得Id=1的钥匙范围锁定，而另一个应该获得id 2的钥匙范围锁定。

无论哪种方式都采用键范围锁定，范围具有较低的&上限和索引类型(唯一或非唯一(定义了两个边界的值/限制：

假设对索引的现有值的查询(如问题中的id＝1和id＝2(，当索引为：时

唯一：主键范围下限=主键范围上限=索引/行值

非唯一：下范围边界=索引/行值&上范围边界=索引的下一个值(如果有(。

当index是唯一的时，第一个查询选择Id=1，这将独占地锁定(XLOCK(从index_value=1到index_value=1的键范围。对于Id=2，第二个查询可以选择行，因为Id=2未被锁定。

当索引是非唯一的时，第一个查询(Id=1(独占地锁定从index_value=1到index_value=2的关键字范围。第二个查询(对于Id=2(无法选择行，因为Id=2被第一个查询(独占(锁定。

如果表有第三行，值为3..5..10，那么选择这些值中任何一个的第二个查询都可以正常工作，因为键范围锁定是从Id 1到2。

create table mytable(Id bigint not null, index idxId /*unique*/ nonclustered (id));
go
insert into mytable(Id) values (1), (2), (5), (7), (20), (21), (22);
go

set transaction isolation level serializable;
begin transaction
select * from mytable with(xlock) where id = 1;
--nonunique index: rangeXX, 
--........locked values: 1&2 for select...Id=1
--........locked values: 2&5 for select...Id=2
select tl.request_mode, tl.request_type, tl.request_status, tl.resource_description, irs.*
from
sys.dm_tran_locks as tl 
left join
(
select %%lockres%% as idxresourcedescription, Id as [column:Id/value]
from mytable with (index(idxId), nolock)
) as irs
on tl.resource_description = irs.idxresourcedescription;
--rollback transaction
go

--in another session/window
select * from mytable with(xlock, serializable) where id = 5; --this is not blocked...
raiserror('', 0, 0) with nowait;
select * from mytable with(xlock, serializable) where id = 2; --...but this is blocked
go

它变得稍微更多"；复杂的"；在尝试选择(独占和可序列化(不存在的值时评估范围锁(正如您在评论中注意到的，关于(ffffffffff(范围锁(。

在上面的示例/代码中，选择不存在的Id=34将锁定范围Id=22-∞(数据类型的最大范围(。尝试选择Id=25的第二个查询(在另一个会话中(将被阻止(因为它也需要锁定范围22-∞(。sys.dm_tran_locks或sp_lock将只报告keyrange的一个值(上限(：(ffffffffff(==∞。下限(推断？(=最大值(id(。

因此，选择Id=-20将锁定密钥范围[-∞]-1，并且sys.dm_tran_locks报告一个边界(Id=1(。下边界(推断(=[-∞]。

您可以尝试猜测，当表具有Id(7(、(20(并且查询选择Id=15(具有非唯一和/或唯一索引(时，keyrange锁定。从概念上讲，在可序列化事务中有一个keyrange锁，并且需要对现有的键/值进行锁(没有Id为15的行，锁必须在不同的键/价值上(。