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自动为该表应用快照隔离。 我还没有测试过,如果您在代码中要求使用不同的隔离级别会发生什么,我怀疑它会覆盖此选项,但先对其进行测试。
快速了解使用快照隔离的性能开销。
关于快照隔离如何改变应用程序预期行为的好文章。它显示了更新语句和选择语句如何返回完全不同的意外结果的示例。
#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,则必须显式设置
为每个会话设置快照隔离级别,以便访问版本化的行。
评论
感谢您的链接。像其他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