问题描述
可序列化的事务隔离级别通过阻止与其他事务中的任何选择语句冲突的事务中表的任何插入来避免幻读问题.我试图用一个例子来理解它，但它会阻止插入，即使 select 语句中的过滤器没有冲突.我希望能解释一下它为什么会这样.
Serializable transaction isolation levels avoids the problem of phantom reads by blocking any inserts to a table in a transaction which are conflicting with any select statements in other transactions. I am trying to understand it with an example, but it blocks insert even if when the filter in the select statement is not conflicting. I would appreciate any explanation on why it behaves in that way.
表格脚本
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?
推荐答案
测试场景中的第一个问题是该表在 firstname 上没有有用的索引.第二个是表是空的.
The first issue in your test scenario is that the table has no useful index on firstname. The second is that the table is empty.
来自 BOL 中的键范围锁定
在键范围锁定发生之前，必须满足以下条件满意:
事务隔离级别必须设置为
SERIALIZABLE.
查询处理器必须使用索引来实现范围过滤器谓词.例如，WHERESELECT 语句中的子句可以用这个建立一个范围条件谓词:ColumnX BETWEEN N'AAA' ANDN'CZZ'.键范围锁只能是如果 ColumnX 被一个索引键.
The query processor must use an index to implement the range filter predicate. For example, the WHERE clause in a SELECT statement could establish a range condition with this predicate: ColumnX BETWEEN N'AAA' AND N'CZZ'. A key-range lock can only be acquired if ColumnX is covered by an index key.
没有合适的索引来获取 RangeS-S 锁，因此为了保证 SQL Server 需要锁定整个表的可序列化语义.
There is no suitable index to take RangeS-S locks on so to guarantee serializable semantics SQL Server needs to lock the whole table.
如果您尝试在表的名字列上添加聚集索引，如下所示并重复实验...
If you try adding a clustered index on the table on the first name column as below and repeat the experiment ...
CREATE CLUSTERED INDEX [IX_FirstName] ON [dbo].[dummy] ([firstname] ASC)
...你会发现你还是被屏蔽了！
... you will find that you are still blocked!
尽管现在存在合适的索引并且执行计划显示它被搜索以满足查询.
Despite the fact that a suitable index now exists and the execution plan shows that it is seeked into to satisfy the query.
您可以通过运行以下命令了解原因
You can see why by running the following
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 不只是对您在查询中指定的范围进行范围锁定.
SQL Server does not just take out a range lock on exactly the range you specify in your query.
对于唯一索引上的相等谓词，如果有匹配的键，它将只需要一个普通锁，而不是任何类型的范围锁.
For an equality predicate on a unique index if there is a matching key it will just take a regular lock rather than any type of range lock at all.
对于非唯一的搜索谓词，它会锁定范围内的所有匹配键以及范围末尾的下一个"键(或在 ffffffffffff 上表示无穷大，如果没有下一个"" 键存在).即使是被删除的幽灵"记录用于此范围键锁定.
For a non unique seek predicate it takes out locks on all matching keys within the range plus the "next" one at the end of the range (or on ffffffffffff to represent infinity if no "next" key exists). Even deleted "ghost" records can be used in this range key locking.
如此处所述用于等式对唯一索引或非唯一索引的谓词
As described here for an equality predicate on either a unique or non unique index
如果密钥不存在，则在唯一和非唯一索引的next"键.如果下一步"键不存在，则对无穷大"值进行范围锁定.
因此，对于一个空表，SELECT 仍然会锁定整个索引.您还需要之前在 abc 和 lmn 之间插入一行，然后您的插入就会成功.
So with an empty table the SELECT still ends up locking the entire index. You would need to also have previously inserted a row between abc and lmn and then your insert would succeed.
insert into dummy values('def', 'def')
这篇关于当另一个事务的事务隔离级别可使用非冲突过滤器进行序列化时，为什么要插入 TSQL 语句块?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！