为什么 SQL Server 使用不同的键范围锁定 SELECT? [英] Why does SQL Server lock SELECT with a different key range?

问题描述

我有两个事务一个接一个地开始:

I have two transactions that start one after another:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

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

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

Second one is almost the same (just no delay and another id)

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION
select * from MyTable WITH (XLOCK) WHERE Id = 2
COMMIT TRANSACTION

我确实有一个关于 Id 的非唯一索引.至少当 Id 上有主键时，这似乎确实按预期工作.

I do have a non-unique index on Id. It does seem that this work as expected when there is a primary key on Id at least.

据我所知，第一个事务应该真正获得Id = 1的key-range锁，而另一个应该获得id 2的key-range锁.

From what I understand the first transaction should really obtain a key-range lock for the Id = 1, while the other should obtain a key-range lock for id 2.

显然它不会像那样工作，因为在第一个完成之前，第二个事务被卡住了.我在这里遗漏了什么或者我不能强制独占键范围锁定?

Apparently it does not work like that since the transaction two is stuck until the first one completes. Do I miss something here or I cannot force an exclusive key-range lock?

这是我的示例的完整创建脚本:

Here is the full create script for my example:

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]

推荐答案

据我了解，第一笔交易应该真正获得Id = 1 的键范围锁，而另一个应该获得一个id 2 的键范围锁.

From what I understand the first transaction should really obtain a key-range lock for the Id = 1, while the other should obtain a key-range lock for id 2.

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

A key-range lock is taken either way, a range has lower&upper boundaries and the type of index (unique or non-unique) defines the values/limits for the two boundaries:

假设查询索引的现有值(如问题中的 id=1 & id=2)，当索引为:

Assuming, a query for existing values of the index (like in the question, id=1 & id=2), when index is:

唯一:下键范围边界 = 上键范围边界 = 索引/行值

Unique : lower key-range boundary = upper key-range boundary = index/row value

非唯一:范围下限=索引/行值&范围上限=索引的下一个值(如果有).

Non-unique : lower range boundary = index/row value & upper range boundary= next value of index (if any).

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

When index is unique, the first query selects Id=1 and this locks exclusively (XLOCK) the key-range from index_value = 1 to index_value=1. The second query, for Id=2, can select the row, because Id=2 is not locked.

当 index 不唯一时，第一个查询 (Id=1) 只锁定从 index_value = 1 到 index_value=2 的键范围.第二个查询(对于 Id=2)无法选择行，因为 Id=2 被第一个查询(独占)锁定.

When index is non-unique, the first query (Id=1) locks exclusively the key range from index_value = 1 to index_value=2. The second query (for Id=2) can not select the row since Id=2 is locked (exclusively) by the first query.

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

If the table had a third row, with values 3..5..10, then a second query selecting any of these values would work just fine, because the key-range lock is from Id 1 to 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

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

It gets slightly more "complicated" to assess the range locks when trying to select (exclusively&serializable) a value that does not exist (as you have noticed, in your comment, about the (ffffffffffff) range lock).

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

In the example/code above, selecting Id=34, which does not exist, will lock the range Id= 22 - ∞ (max range of datatype). A second query (in another session) trying to select Id = 25 will be blocked (because it needs to lock the range 22 - ∞ too). sys.dm_tran_locks or sp_lock will report only one value(upper boundary) for the keyrange : (ffffffffffff)==∞. The lower boundary (inferred?) = max(id).

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

Accourdingly, selecting Id=-20 will lock the keyrange [-∞] - 1 and sys.dm_tran_locks reports one boundary (Id=1). The lower boundary (inferred) = [-∞].

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

You could try and guesstimate, the keyrange lock when table has Ids (7), (20) and a query selects Id=15 (with a nonunique and/or unique index). Conceptually, in serializable transaction there is a keyrange lock and locks need to be taken on existing keys/values (there is no row with Id=15, locks have to be on different keys/values).

这篇关于为什么 SQL Server 使用不同的键范围锁定 SELECT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！