每当有人问快照,行版本控制或类似问题时,大多数论坛和在线示例始终建议将ALLOW_SNAPSHOT_ISOLATIONREAD_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,则必须显式设置
为每个会话设置快照隔离级别,以便访问版本化的行。