读提交快照与快照隔离级别

Question

读提交快照与快照隔离级别

sql-serversnapshotisolation-levelread-committed-snapshot

84

请问有人可以帮助我理解在 SQL Server 中何时使用快照隔离级别而不是读提交的快照隔离级别吗？

我明白大多数情况下读提交的快照隔离级别可用，但不确定何时应该选择快照隔离级别。

谢谢

- user173552

5个回答

65

[![隔离级别表][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

第二节课

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

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

会话1

COMMIT

第二节课程

USE SQLAuthority
GO
SELECT *
FROM   DemoTable
WHERE i = 1

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

快照隔离级别

这是一种新的隔离级别，从 SQL Server 2005 开始提供。对于此功能，需要更改应用程序，以使用新的隔离级别。

使用以下方法更改数据库设置。我们需要确保数据库中没有事务。

ALTER DATABASE SQLAuthority SET AllOW_SNAPSHOT_ISOLATION ON

现在，我们还需要通过以下方式更改连接的隔离级别

会话 1

USE SQLAuthority
GO
BEGIN TRAN
UPDATE DemoTable
SET i = 10
WHERE i = 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

让我们回到第二个会话并再次运行select语句。

第二个会话

SELECT *
FROM   DemoTable
WHERE i = 2

由于Session 2已经使用快照隔离启动了事务，因此我们仍将看到该记录。除非我们完成事务，否则我们将无法看到最新的记录。

Session 2

COMMIT
SELECT *
FROM   DemoTable
WHERE i = 2

现在，由于该行已经被更新，因此我们不应该再看到该行。

参见：SQL Authority，Safari Books Online

- Akira Yamamoto

5

这个回答比被选中的回答更好，因为它有例子。 - jyao

1

同意，这是最好的答案，提供了很多有用的外部资源。 - Sanjiv Jivan

很好的解释，只有一件事需要强调，即READ_COMMITTED_SNAPSHOT不是隔离级别，而是更改READ COMMITTED事务隔离级别行为的数据库选项。 - Frank Myat Thu

17

没有讨论“快照更新冲突”异常的比较是不完整的，这个异常可能在快照隔离级别中发生，但在快照读提交隔离级别中不会发生。

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

快照读提交隔离级别不会出现这个问题，因为它在写操作上使用锁定（悲观写入），并在每个语句开始时获取所有已提交数据的快照版本信息。

快照隔离级别和快照读提交隔离级别之间发生快照更新冲突的可能性是两者之间的一个非常重要的区别。

- user3444696

4

这份笔记是关于IT技术的，从Bill的评论开始阅读后，我做了些笔记，可能对别人有用。

默认情况下，单个语句（包括SELECT）使用“已提交”数据（READ COMMITTED），问题是：在读取时它们等待数据处于“空闲”状态并停止其他工作吗？

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

并发/阻止：是否开启读取已提交时的快照 [默认关闭，应该开启]：

对于“选择”（读取），使用快照，不等待其他进程，也不阻止它们。
无需更改代码即可影响操作
ALTER DATABASE <dbName> SET READ_COMMITTED_SNAPSHOT [ON|OFF]
SELECT name, is_read_committed_snapshot_on FROM sys.databases

一致性：允许快照隔离 [默认关闭，存在争议 - 关闭OK]：

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

对于这个问题：Read Committed Snapshot和Allow Snapshot Isolation并不是一种选择，它们都是快照的两种情况，可以独立开启或关闭其中的任何一种。Allow Snapshot Isolation更为高级。它允许代码进入控制快照领域的下一步。

如果你想到一个行的问题，那么这个问题似乎就很清楚了：默认情况下，系统没有副本，因此读者必须等待任何其他人写入，而写者在任何其他人正在读取时也必须等待-该行必须始终锁定。启用“Is Read Committed Snapshot On”将激活DB以支持“快照副本”，以避免这些锁定。

废话连篇...

我认为，对于任何普通的MS SQL Server数据库，“Is Read Committed Snapshot On”应该为TRUE，并且默认情况下设置为FALSE是一种过早的优化。

然而，我被告知，一行锁不仅变得更糟，因为您可能正在处理跨表的多行，而且在SQL Server中，行锁使用“块”级锁来实现（锁定由存储位置相关的随机行），而且有一个阈值，多个锁会触发表锁-可能会导致繁忙数据库中出现阻塞问题的更“乐观”的性能优化。

- Cris Mooney

我认为这可能更多是兼容性问题，他们不改变默认设置，就像默认情况下使用UTF-16排序规则创建数据库一样，这在今天也非常愚蠢。至少SQL Azure默认开启了快照。 - John

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_SNAPSHOT自动提升选项的READ_COMMITTED模式，将不会发生这种情况。

值得理解这种情景。这与IDENTITY列不保证唯一性无关。这与IDENTITY列不保证严格单调性无关。即使既不违反唯一性也不违反严格单调性，仍然可能出现间隙 - 在看到较低id的提交之前，可能会先看到较高id的提交。

在READ_COMMITTED模式下，不存在这个问题。

在READ_COMMITTED模式下，您也可以看到间隙 - 即回滚的事务。但这些间隙是永久的 - 也就是说，您不会因为事件不会重新出现而跳过它们。即在看到较高id后，您不会再看到较低id重新出现。

在启用READ_COMMITTED_SNAPSHOT之前，请理解上述问题及其影响。

这个选项的控制权属于开发人员与数据库管理员责任之间的灰色地带。如果你是管理员，不应该盲目使用它，因为开发人员在开发应用程序时可能依赖于READ_COMMITTED隔离语义，而打开READ_COMMITTED_SNAPSHOT可能会违反这些假设，导致非常隐晦、难以找到的错误。

此外，请注意以下几点：

- 使用Docker镜像时，默认情况下此选项处于关闭状态。 - 使用Azure实例时，默认情况下此选项处于打开状态。

这简直太愚蠢了，因为开发人员将在与部署环境默认不同的环境中工作和运行CI测试。

- Mirek Rusin

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Bill Paetzke · Accepted Answer

READ COMMITTED SNAPSHOT采用乐观读和悲观写技术，而SNAPSHOT采用乐观读和乐观写技术。

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

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

以下是更详细的一篇文章： http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx