读取已提交的快照 VS 快照隔离级别

Question

有人可以帮助我了解何时在 SQL Server 中使用 SNAPSHOT 隔离级别而不是 READ COMMITTED SNAPSHOT 吗？

我知道在大多数情况下 READ COMMITTED SNAPSHOT 有效，但不确定何时进行 SNAPSHOT 隔离。

谢谢

Answer 1

READ COMMITTED SNAPSHOT 执行乐观读取和悲观写入。相反，SNAPSHOT 执行乐观读取和乐观写入。

Microsoft 建议大多数需要行版本控制的应用使用READ COMMITTED SNAPSHOT。

阅读这篇优秀的 Microsoft 文章：选择基于行版本控制的隔离级别。它解释了两种隔离级别的优点和成本。

这是更彻底的：
http://msdn.microsoft.com/en-us/库/ms345124(SQL.90).aspx

Answer 2

[![隔离级别表][2]][2]

请参阅下面的示例：

读取提交的快照

更改数据库属性，如下

ALTER DATABASE SQLAuthority
SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
GO

会话 1 >

USE SQLAuthority
GO
BEGIN TRAN
UPDATE DemoTable
SET i = 4
WHERE i = 1

会话 2

USE SQLAuthority
GO
BEGIN TRAN
SELECT *
FROM   DemoTable
WHERE i = 1

结果 – 会话 2 中的查询显示旧值 (1, ONE)，因为当前事务未提交。这也是避免阻塞和读取已提交数据的方法。

会话 1

COMMIT

会话 2

USE SQLAuthority
GO
SELECT *
FROM   DemoTable
WHERE i = 1

结果 – 会话 2 中的查询未显示任何行，因为行已在会话 1 中更新。因此，我们再次看到已提交的数据。

快照隔离级别

这是新的隔离级别，从 SQL Server 2005 开始提供。对于此功能，应用程序需要进行更改，因为它必须使用新的隔离级别。

使用下面的命令更改数据库设置。我们需要确保数据库中没有事务。

ALTER DATABASE SQLAuthority SET AllOW_SNAPSHOT_ISOLATION ON

结果来更改连接的隔离级别 -

现在，我们还需要使用下面的Session 1

USE SQLAuthority
GO
BEGIN TRAN
UPDATE DemoTable
SET i = 10
WHERE i = 2

Session 2

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO
USE SQLAuthority
GO
BEGIN TRAN
SELECT *
FROM   DemoTable
WHERE i = 2

即使我们将值更改为 10，我们仍然会看到旧的记录在会话 2 (2, TWO) 中。

现在，让我们在会话 1 中提交事务

会话 1

COMMIT

让我们回到会话 2 并再次运行 select。

会话2

SELECT *
FROM   DemoTable
WHERE i = 2

我们仍然会看到该记录，因为会话2已经声明了具有快照隔离的事务。除非我们完成交易，否则我们将看不到最新记录。

会话 2

COMMIT
SELECT *
FROM   DemoTable
WHERE i = 2

现在，我们不应该看到该行，因为它已经更新了。

请参阅：SQL 权威，Safari 在线图书< /a>

Answer 3

如果不讨论快照中可能发生的可怕的“快照更新冲突”异常，但提交快照中则不会，那么对快照和提交快照读的比较就不完整。

简而言之，快照隔离在事务开始时检索已提交数据的快照，然后对读取和写入使用乐观锁定。如果在尝试提交事务时发现其他内容更改了部分相同数据，则数据库将回滚整个事务并引发错误，导致调用代码中出现快照更新冲突异常。这是因为受事务影响的数据版本在事务结束时与开始时不同。

快照读取提交不会遇到此问题，因为它使用写入锁定（悲观写入），并且在每个语句开始时获取所有已提交数据的快照版本信息。

快照和非快照读提交中发生快照更新冲突的可能性是两者之间极其显着的区别。

Answer 4

仍然相关，从比尔的评论开始，我阅读了更多内容并做了可能对其他人有用的笔记。

默认情况下，单个语句（包括 SELECT）对“已提交”数据（READ COMMITTED）起作用，问题是：它们是否等待数据“空闲”并在读取时阻止其他人工作？

通过右键单击数据库“属性 -> 选项 -> 其他”进行设置：

并发/阻塞：读提交快照是否打开 [默认关闭，应打开]：

• 使用SNAPSHOT 用于选择（读取），不等待其他人，也不阻止他们。
• 无需更改代码即可实现操作
• ALTER DATABASESET READ_COMMITTED_SNAPSHOT [ON|OFF]
• 从 sys.databases 中选择名称，is_read_commissed_snapshot_on

一致性：允许快照隔离 [默认关闭，值得商榷 - 确定关闭]：

• 允许客户端跨 SQL 语句（事务）请求 SNAPSHOT。
• 代码必须请求“事务”快照（例如 SET TRANSACTION ...）
• ALTER DATABASESET ALLOW_SNAPSHOT_ISOLATION [ON|OFF]
• SELECT name, snapshot_isolation_state FROM sys.databases

对于问题：读取提交快照和允许之间不是一个或快照隔离。它们是快照的两种情况，并且可以独立打开或关闭，其中允许快照隔离是一个更高级的主题。允许快照隔离允许代码进一步控制快照区域。

如果你考虑一行，这个问题似乎很清楚：默认情况下，系统没有副本，因此如果其他人正在写入，则读取者必须等待，如果其他人正在读取，写入者也必须等待 - 该行必须锁定所有副本时间。启用“读取已提交快照”会激活数据库支持“快照副本”以避免这些锁定。

胡言乱语...

在我看来，对于任何普通的 MS SQLServer 数据库来说，“读取已提交快照已打开”应该为 TRUE，并且默认情况下它提供的是 FALSE，这是一种过早的优化。

然而，我听说单行锁变得更糟，不仅因为您可能跨表寻址多行，而且因为在 SQL Server 中，行锁是使用“块”级锁（锁定与存储邻近性关联的随机行）来实现的，并且多个锁触发表锁定存在一个阈值 - 可能是更“乐观”的性能优化，但存在繁忙数据库中阻塞问题的风险。

Answer 5

我来描述一下没有提到的2点。

首先让我们弄清楚如何使用两者，因为它并不直观。

SNAPSHOT 和 READ_COMMITTED_SNAPSHOT 是两个不同的隔离级别。

SNAPSHOT 是您可以像往常一样在事务中显式使用的隔离级别：

begin transaction
set transaction isolation level snapshot;
-- ...
commit

READ_COMMITTED_SNAPSHOT 不能这样使用。 READ_COMMITTED_SNAPSHOT 既是数据库级别选项，也是隐式/自动隔离级别。要使用它，您需要为整个数据库启用它：

alert database ... set read_committed_snapshot on;

上面的数据库设置的作用是，每次您运行这样的事务时：

begin transaction
set transaction isolation level read committed;
-- ...
commit

启用此选项后，所有 READ_COMMITTED 事务将在 READ_COMMITTED_SNAPSHOT 隔离级别下运行 强>。这会自动发生，影响将此设置设置为 ON 的数据库发出的所有 READ_COMMITTED 事务。无法在 READ_COMMITTED 隔离级别下运行事务，因为具有此级别的所有事务都将自动转换为 READ_COMMITTED_SNAPSHOT。

其次，您不应该盲目使用 READ_COMMITTED_SNAPSHOT 选项。

为了说明它可能产生的问题，假设您有这样的简单事件表：

create table Events (
  id int not null identity(1, 1) primary key,
  name nvarchar(450) not null
  -- ...
)

并且您使用如下查询定期轮询它：

begin transaction
set transaction isolation level read committed; -- automatically set to read committed snapshot when this setting is ON on database level 
select top 100 * from Events where id > ${lastId} order by id asc; 
commit

上面的查询不需要包含在事务和显式隔离级别中。 READ_COMMITTED 是默认隔离级别，如果您调用查询而不将其包装在事务块中 - 它将在 READ_COMMITTED 事务中隐式运行。

您会发现，在 READ_COMMITTED_SNAPSHOT 隔离级别下，自动增量标识值可能会有稍后出现的间隙。

您可以轻松地使用插入来模拟它，如下所示：

begin transaction
insert into Events (name) values ('test 1');
waitfor delay '00:00:10'
commit

...然后是正常插入：

insert into Events (name) values ('test 2');

您在 10 秒内调用的轮询函数将返回 id 为 2 的单行。

更新 lastId 后进行轮询将不会返回任何内容。 id 为 1 的行将在 10 秒后出现。

id 为 1 的事件将被有效跳过。

如果您使用 READ_COMMITTED 而没有 READ_COMMITTED_SNAPSHOT 自动升级选项，则不会发生这种情况。

值得理解这个场景。这与 IDENTITY 列不保证唯一性这一事实无关。这与 IDENTITY 列不保证严格的单调性这一事实无关。即使不违反唯一性和严格的单调性，您仍然会遇到间隙 - 可能会在看到具有较低 id 的提交之前看到具有较高 id 的提交。

在 READ_COMMITTED 下不存在此问题。

在 READ_COMMITTED 下，您还可以看到间隙 - 即。通过回滚的事务。但这些差距将是永久性的——即。您不会跳过事件，因为它们永远不会再次出现。 IE。在您看到较高的 id 后，您将不会再看到较低的 id 再次出现。

在打开 READ_COMMITTED_SNAPSHOT 之前，请先了解上述问题及其影响。

对此选项的控制位于开发人员与数据库管理员责任的灰色地带。如果您是管理员，则不应盲目使用它，因为开发人员在开发应用程序时可能依赖于 READ_COMMITTED 隔离语义，而转向 READ_COMMITTED_SNAPSHOT 可能会以非常隐式的、难以发现错误的方式违反这些假设。

另外请注意：

• 当使用 docker 镜像时，此选项默认为关闭；
• 当使用 azure 实例时，此选项默认为打开

……这很愚蠢，因为开发人员将工作并针对默认情况下不同于打开的内容运行 ci 测试部署的环境。