当另一个事务的事务隔离级别可以使用非冲突过滤器进行序列化时，为什么要插入 TSQL 语句块？

Question

可串行化事务隔离级别通过阻止事务中对表的任何插入（这些插入与其他事务中的任何 select 语句相冲突）来避免幻读问题。我试图用一个例子来理解它，但即使 select 语句中的过滤器不冲突，它也会阻止插入。我希望能解释一下它为什么会这样。

表脚本

CREATE TABLE [dbo].[dummy](
    [firstname] [char](20) NULL,
    [lastname] [char](20) NULL
) ON [PRIMARY]

GO

会话 - 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran
select * from dummy where firstname = 'abc'

会话 - 2

insert into dummy values('lmn', 'lmn') -- Why this blocks?

Answer 1

测试场景中的第一个问题是该表在 firstname 上没有有用的索引。第二个是桌子是空的。

来自 键范围锁定 BOL

在进行键范围锁定之前，
必须满足以下条件
满意：

• 事务隔离级别必须设置为SERIALIZABLE。

• 查询处理器必须使用索引来实现范围过滤器
  谓词。例如，WHERE
SELECT 语句中的子句可以
  以此建立范围条件
  谓词：ColumnX BETWEEN N'AAA' AND N'CZZ'。键范围锁只能是
  如果 ColumnX 被覆盖，则获取
  索引键。

没有合适的索引来获取 RangeS-S 锁，因此为了保证可序列化语义，SQL Server 需要锁定整个表。

如果你尝试如下所示在表的名字列上添加聚集索引并重复实验……

CREATE CLUSTERED INDEX [IX_FirstName] ON [dbo].[dummy] ([firstname] ASC)

你会发现你仍然被阻塞！

尽管现在存在合适的索引，并且执行计划显示正在寻找它来满足查询。

您可以通过运行以下

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT *
FROM   dummy
WHERE  firstname = 'abc'

SELECT resource_type,
       resource_description, 
       request_mode
FROM   sys.dm_tran_locks
WHERE  request_session_id = @@SPID

COMMIT 

命令

+---------------+----------------------+--------------+
| resource_type | resource_description | request_mode |
+---------------+----------------------+--------------+
| DATABASE      |                      | S            |
| OBJECT        |                      | IS           |
| PAGE          | 1:198                | IS           |
| KEY           | (ffffffffffff)       | RangeS-S     |
+---------------+----------------------+--------------+

来了解为什么SQL Server 不只是对您在查询中指定的范围进行范围锁定。

对于唯一索引上的相等谓词，如果有匹配的键，它将只采用常规锁，而不是任何类型的范围锁。

对于非唯一查找谓词，它会获取范围内所有匹配键的锁以及范围末尾的“下一个”键（如果不存在“下一个”键，则在 ffffffffffff 上表示无穷大） ）。 甚至删除的“幽灵”记录在此范围内使用键锁定。

如此处所述 对于相等谓词唯一索引或非唯一索引

如果键不存在，则对键进行“范围”锁定
'next' 键适用于唯一索引和非唯一索引。如果按“下一个”键
不存在，则对“无穷大”值进行范围锁定。

因此，对于空表，SELECT 最终仍会锁定整个索引。您还需要事先在 abc 和 lmn 之间插入一行，然后插入才会成功。

insert into dummy values('def', 'def')

Answer 2

来自 http://msdn.microsoft.com/en-us/library/ms173763 .aspx

可序列化
指定以下内容：

语句无法读取已被其他事务修改但尚未提交的数据。

在当前事务完成之前，任何其他事务都不能修改当前事务已读取的数据。

据我了解，您的插入将被阻止，因为您的 SELECT 运行所在的事务尚未完成。