主页 / dba / 问题 / 40738
Accepted
Travis
Asked: 2013-04-24 12:29:39 +0800 CST

ALLOW_SNAPSHOT_ISOLATION 和 READ_COMMITTED_SNAPSHOT

  • 772

大多数论坛和在线示例总是建议在有人询问快照、行版本控制或类似问题时两者都设置ALLOW_SNAPSHOT_ISOLATION并设置为 ON。READ_COMMITTED_SNAPSHOT

我猜这两个设置中的 SNAPSHOT 这个词都会有点混乱。我认为，为了让数据库引擎对 READ_COMMITTED 默认行为使用行版本控制而不是锁定，无论设置如何，数据库READ_COMMITTED_SNAPSHOT都设置为 ON 。ALLOW_SNAPSHOT_ISOLATION

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

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

我的问题是，我的理解在某种程度上不正确吗？并且这两个设置必须始终一起设置为 ON（尤其是对于 READ COMMITTED 行版本控制）？

sql-server isolation-level

4 个回答

  1. Best Answer

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

    Kim Tripp（SQL Server 的程序员之一，也是 SQLSkills 的一个组成部分）完全按照您在关于快照隔离的 MCM 视频中所说的内容（直接 MP4 下载链接 (544MB)）进行了说明。在视频中快进到 41:45 以到达她回答您问题的部分。

    如果你使用ALLOW_SNAPSHOT_ISOLATION确保你SET TRANSACTION ISOLATION LEVEL SNAPSHOT在你的代码中使用，否则你将不会获得任何好处。

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

    如果你在代码中要求不同的隔离级别，我还没有测试过会发生什么，我怀疑它会覆盖这个选项，但先测试一下。

    使用快照隔离快速查看性能开销。

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

    • 30

  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

    两个设置的第一次测试确认为关闭。

    查询 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 导致 TABLE1 上的排他锁。

    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 行版本控制似乎已经到位。

    添加SET TRANSACTION ISOLATION LEVEL SNAPSHOT;查询 1 和查询 2，并运行查询 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 行版本控制。

    添加SET TRANSACTION ISOLATION LEVEL SNAPSHOT;到查询 1 和查询 2。运行查询 1，然后查询 2。虽然 DMV 显示查询 1 会产生排他锁，但查询 2 会返回带有“原始”的详细信息。快照隔离似乎已经到位。

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

    • 16

  3. 你的理解是正确的。我喜欢这里的简短、干净和简单的定义：

    当 READ_COMMITTED_SNAPSHOT 数据库选项为 ON 时，设置读提交隔离级别的事务使用行版本控制。

    当 ALLOW_SNAPSHOT_ISOLATION 数据库选项为 ON 时，事务可以设置快照隔离级别。

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

    如果将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON，则数据库引擎默认使用行版本控制和快照隔离，而不是使用锁来保护数据。

    但是上面提到的“快照隔离”并不等于set transaction isolation level snapshot所应用的事务的行为。

    至于区别，很好的解释是here。

    如果将 READ_COMMITTED_SNAPSHOT 命名为 READ_COMMITTED_ROW_VERSIONING 或类似名称，可能会更好。:)

    • 7

  4. 我喜欢微软的这个总结：

    设置 READ_COMMITTED_SNAPSHOT ON 选项允许访问默认 READ COMMITTED 隔离级别下的版本化行。如果 READ_COMMITTED_SNAPSHOT 选项设置为 OFF，您必须为每个会话显式设置快照隔离级别才能访问版本化行。

    • 0

相关问题