ALLOW_SNAPSHOT_ISOLATION和READ_COMMITTED_SNAPSHOT

每当有人问快照，行版本控制或类似问题时，大多数论坛和在线示例始终建议将ALLOW_SNAPSHOT_ISOLATION和READ_COMMITTED_SNAPSHOT都设置为ON。

我猜这两种设置中的SNAPSHOT这个词都会让人有些困惑。我认为，为了使数据库引擎使用行版本控制而不是针对READ_COMMITTED默认行为进行锁定，无论将READ_COMMITTED_SNAPSHOT设置为什么，都将数据库ALLOW_SNAPSHOT_ISOLATION设置为ON。

ALLOW_SNAPSHOT_ISOLATION设置仅设置为ON。不管READ_COMMITTED_SNAPSHOT的设置如何，在启动事务（例如SET TRANSACTION ISOLATION LEVEL SNAPSHOT）时允许快照隔离。

将这两个设置设置为ON的唯一原因是它需要进行READ COMMITTED行版本控制和快照隔离。

我的问题是，我的理解不正确某种方式？而且这两个设置必须始终一起设置为ON（特别是对于READ COMMITTED行版本控制）？

#1 楼

您的理解是正确的。确实有点令人困惑。

Kim Tripp（SQL Server的程序员之一和SQLSkills的组成部分）完全按照您在Snapshot Isolation上的MCM视频中所述的内容进行操作。在视频中快到41:45转到她回答问题的部分。

如果您使用ALLOW_SNAPSHOT_ISOLATION，请确保在代码中使用SET TRANSACTION ISOLATION LEVEL SNAPSHOT，否则您将无法获得任何好处。

如果设置SET READ_COMMITTED_SNAPSHOT ON，则无需修改任何代码。 MS SQL Server自动为该表应用快照隔离。

我还没有测试过，如果您在代码中要求使用不同的隔离级别会发生什么，我怀疑它会覆盖此选项，但先对其进行测试。

快速了解使用快照隔离的性能开销。

关于快照隔离如何改变应用程序预期行为的好文章。它显示了更新语句和选择语句如何返回完全不同的意外结果的示例。

感谢您的链接。像其他BOL一样，她独立和集体讨论了这两种设置（这会使它变得有些混乱，或者也许我认为过分）。我必须测试一下才能获得更好的理解。

–特拉维斯
13年4月24日在14:01

这是一个很好的答案，我只想澄清几个问题。首先，如果您只是扫描视频，请从23:18以及41:45开始。早期时间增加了更多细节。尽管Kim提到了原始问题的答案，但是如果同时使用两者，则仍然需要修改代码。 Read_Committed_Snapshot是语句级隔离，换句话说，仅适用于当前正在运行的语句。 Allow_Snapshot_Isolation是事务级隔离，介于Begin Tran和Commit之间。它们可以分别使用，但是每行建立相同的14字节开销。

–豪华
2013年6月6日14:12

感谢您添加有关正在建立的14字节开销的其他详细信息。 Kim在视频中对它进行了详细介绍，但在文本中也将其包含在其中非常有用。

–阿里·拉泽吉（Ali Razeghi）
18年11月26日在18:01

#2 楼

好的，回到家进行测试。这是观察值。

CREATE DATABASE TEST;
GO
CREATE TABLE TABLE1
(
    ID tinyint,
    Details varchar(10)
);
GO
INSERT INTO TABLE1
VALUES (1, 'Original');
GO

SELECT
    name,
    snapshot_isolation_state_desc,
    is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'TEST';
GO

两项设置均确认为OFF的首次测试。

查询1

USE TEST;

BEGIN TRAN
UPDATE TABLE1
SET Details = 'Update'
WHERE ID = 1;

--COMMIT;
--ROLLBACK;
GO

查询2

USE TEST;

SELECT ID, Details
FROM TABLE1
WHERE ID = 1;
GO

在此测试中，查询2正在等待查询1提交，dm_tran_locks DMV显示查询1在表1上引起的排他锁。

USE TEST;

SELECT
    DB_NAME(tl.resource_database_id) AS DBName,
    resource_type,
    OBJECT_NAME(resource_associated_entity_id) AS tbl_name,
    request_mode,
    request_status,
    request_session_id
FROM sys.dm_tran_locks tl
WHERE 
    resource_database_id = db_id('TEST')
    AND resource_type = 'OBJECT'

第二个测试，回滚上一个事务，将READ_COMMITTED_SNAPSHOT设置为ON但将ALLOW_SNAPSHOT_ISOLATION设置为OFF。

ALTER DATABASE TEST
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;
GO

运行查询1，并运行查询2。DMV显示查询1产生互斥锁，但是查询2返回带有“原始”的详细信息而不查询1提交事务。似乎已完成READ_COMMITTED行版本控制。

在查询1和查询2上添加SET TRANSACTION ISOLATION LEVEL SNAPSHOT;，然后运行查询1或查询2返回错误-快照隔离事务无法访问数据库'TEST'，因为快照隔离是在此数据库中不允许。使用ALTER DATABASE允许快照隔离。

第三项测试，回滚先前的事务。将READ_COMMITTED_SNAPSHOT设置为OFF，将ALLOW_SNAPSHOT_ISOLATION设置为ON。

ALTER DATABASE TEST
SET READ_COMMITTED_SNAPSHOT OFF
WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE TEST
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

运行查询1，然后查询2。DMV显示查询1引起的互斥锁。查询2似乎正在等待查询。 1完成。启用ALLOW_SNAPSHOT_ISOLATION似乎没有启用READ COMMITTED行版本控制。

在查询1和查询2中都添加SET TRANSACTION ISOLATION LEVEL SNAPSHOT;。运行查询1，然后查询2。虽然DMV显示查询1导致排他锁，但查询2使用“原始”返回详细信息。快照隔离似乎已经就绪。

测试显示，无论READ_COMMITTED_SNAPSHOT设置如何，ALLOW_SNAPSHOT_ISOLATION本身都会启用/禁用READ COMMITTED行版本控制，反之亦然。

#3 楼

您的理解是正确的。
我喜欢这里的简短，简洁的定义：

当READ_COMMITTED_SNAPSHOT数据库选项为ON时，设置读取提交隔离级别的事务将使用行版本控制。 />当ALLOW_SNAPSHOT_ISOLATION数据库选项为ON时，事务可以设置快照隔离级别。

似乎很多误解来自MS本身。例如，在这里他们说：

如果将READ_COMMITTED_SNAPSHOT数据库选项设置为ON，则数据库引擎将默认使用行版本控制和快照隔离，而不是使用锁来保护数据。 >
但是提到的“快照隔离”并不等同于应用了set transaction isolation level snapshot的事务的行为。
关于区别，这里有一个很好的解释。
如果READ_COMMITTED_SNAPSHOT可能会更好被命名为READ_COMMITTED_ROW_VERSIONING或类似名称。 :)

#4 楼

我喜欢Microsoft的摘要：

设置READ_COMMITTED_SNAPSHOT ON选项可以在默认的READ COMMITTED隔离级别下访问
版本化的行。如果
READ_COMMITTED_SNAPSHOT选项设置为OFF，则必须显式设置
为每个会话设置快照隔离级别，以便访问版本化的行。

编程黑洞网

ALLOW_SNAPSHOT_ISOLATION和READ_COMMITTED_SNAPSHOT

#1 楼

评论

#2 楼

#3 楼

#4 楼