none
关于READ_COMMITTED_SNAPSHOT 选项 RRS feed

  • 问题

  • 阅读死锁相关的一些文章时看到2008R2提供了READ_COMMITTED_SNAPSHOT 这么一个选项,如果开启了,在查询时可以不在加上NOLOCK而避免查询时产生的死锁,这个选项没具体用过,不敢冒然在业务库上直接就启用,想问下有没人比较了解这个选项的,如果在在用的业务库上直接开启这个设置,有没有风险的,另外备注下这个业务库是从2000升级到2008R2的,使用的是兼容模式

    jude

    2013年8月10日 6:51

答案

  • 如果兼容级别还是80(SQL2000),你启用不了READ_COMMITTED_SNAPSHOT

    对于读多写少的,启用这个有好处


    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com

    2013年8月12日 0:40
  • 开启这个设置,如果数据更改比较大的话,可能会增加TEMPDB的负担。因为行版本信息是保存在TEMPDB的。所以开启这个选项要多做测试确保不会产生副作用。

    Please Mark As Answer if it is helpful.

    2013年8月12日 2:25
  • 简单地说, 在这种隔离级别下, 读取的数据如果在更新中, 那么读取到的是更新前的快照(条版本),  修改前的COMMIT数据, 所以这个不产生脏读

    NOLOCK提示读取的是更新中的数据(没有 COMMIT), 是脏数据

    而为了给数据读取提供可用的行版本, 对于数据更新而言, 它在更新数据前, 就需要为要更新的数据生成行版本(快照), 这是一个额外的开销, 在单个事务中更新的数据量越大, 这个开销越大

    2013年8月12日 12:36
  • LZ 我有READ_COMMITTED_SNAPSHOT的资料您可以看一下

    SQL有两种行版本控制:
    --(1)行版本控制的已提交读隔离(read_committed_snapshot)
    --(2)直接使用snapshot事务隔离级别
    
    --(1)(read_committed_snapshot):read_committed_snapshot数据库选项为ON时,read_committed事务通过使用行
    --版本控制提供语句级读取一致性
    
    --(2)(snapshot事务隔离级别)allow_snapshot_isolation数据库选项为ON时,snapshot事务通过使用行版本
    --控制提供事务级读取一致性
    
    
    --下列示例可以说明使用普通已提交读事务,行版本控制的快照隔离事务和行版本
    --控制的已提交读事务的行为差异
    
    --示例:
    
    --A 普通已提交事务
    --在此示例中,一个普通read committed事务将读取数据,然后由另一事务修改此数据。执行
    --完的读操作不阻塞由其他事务执行的更新操作。但是,在其他事务已经做了更新操作后,读
    --操作会被阻塞住,直到更新操作事务提交为止
    
    --在会话1上:
    USE [AdventureWorks]
    GO
    BEGIN TRAN
    --查询1
    --这个查询将返回员工有48小时休假时间
    SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    -----------------------------------------------------------------------------------------------
    
    --在会话2上:
    USE [AdventureWorks]
    GO
    BEGIN TRAN
    --修改1
    --休假时间减去8
    --修改不会被阻塞,因为会话1不会持有S锁不放
    UPDATE [HumanResources].[Employee]
    SET [VacationHours]=[VacationHours]-8
    WHERE [EmployeeID]=4
    
    --查询1
    --现在休假时间只有40小时
    SELECT [VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    --------------------------------------------------------------------------------------------------
    
    
    --在会话1上:
    --重新运行查询语句,会被会话2阻塞
    --查询2
    SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    -----------------------------------------------------------------------------------------------
    
    --在会话2上:
    --提交事务
    COMMIT TRAN
    GO
    
    -----------------------------------------------------------------------------------------------
    
    --在会话1上:
    --此时先前被阻塞的查询结束,返回会话2修改好的新数据:40
    --查询3
    --这里返回40,因为会话2已经提交了事务
    SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    --修改2
    --这里会成功
    UPDATE [HumanResources].[Employee]
    SET [SickLeaveHours]=[SickLeaveHours]-8
    WHERE [EmployeeID]=4
    
    SELECT [SickLeaveHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    --可以回滚会话1的修改
    --会话2的修改不会受影响
    ROLLBACK TRAN
    GO
    
    SELECT [SickLeaveHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    -------------------------------------------------------------------------------------------
    
    
    --B 使用快照隔离
    --此示例中,在快照隔离下运行的事务将读取数据,然后由另一个事务修改此数据。快照事务
    --不阻塞由其他事务执行的更新操作,他忽略数据的修改继续从版本化的行读取数据。也就是
    --说,读取到的是数据修改前的版本。但是,当快照事务尝试修改已由其他事务修改的数据时,
    --他将生成错误并终止
    
    --在会话1上:
    USE [AdventureWorks]
    GO
    
    --启用快照隔离
    ALTER DATABASE [AdventureWorks] SET ALLOW_SNAPSHOT_ISOLATION ON
    GO
    
    --设置使用快照隔离级别
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT
    GO
    
    BEGIN TRAN
    --查询1
    --查询返回员工有48小时假期
    SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    ---------------------------------------------------------------------------------------------
    
    --在会话2上:
    USE [AdventureWorks]
    GO
    
    BEGIN TRAN
    --修改1
    --假期时间减8
    --修改不会被会话1阻塞
    UPDATE [HumanResources].[Employee]
    SET [VacationHours]=[VacationHours]-8
    WHERE [EmployeeID]=4
    
    --查询1
    --确认值已经被改成40
    SELECT [VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    -----------------------------------------------------------------------------------------------
    
    --在会话1上:
    --查询2
    --再次运行查询语句
    --还是返回48(修改前的值),因为会话1是从版本化的行读取数据
    SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    ------------------------------------------------------------------------------------------------
    
    --在会话2上:
    --提交事务
    COMMIT TRAN
    GO
    
    -------------------------------------------------------------------------------------------------
    
    --在会话1上:
    --查询3
    --再次运行查询语句
    --还是返回48(修改前的值),因为会话1还是从版本化的行读取数据
    SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    --修改2
    --因为数据已经被会话2修改过,会话1想做任何修改时
    --会遇到3960错误
    --事务会自动回滚
    UPDATE [HumanResources].[Employee]
    SET [SickLeaveHours]=[SickLeaveHours]-8
    WHERE  [EmployeeID]=4
    
    --会话1的修改会回滚
    --会话2的修改不会回滚
    ROLLBACK TRAN
    GO
    
    ----------------------------------------------------------------------------------------------
    
    
    
    --C 使用行版本控制的已提交读
    --在此示例中,使用行版本控制的已提交读事务与其他事务并发运行。已提交读事务的行为与快照事务的行为
    --有所不同。与快照事务相同的是,即使其他事务修改了数据,已提交读事务也将读取版本化的行。
    --与快照事务不同的是,已提交读将执行下列操作:
    
    --(1)在其他事务提交数据更改之后,读取修改的数据
    --(2)能够更新由其他事务修改的数据,而快照事务不能
    
    --在会话1上:
    USE [AdventureWorks]
    GO
    --启用行版本控制的已提交读
    --注意运行这句话的时候,不可以有其他连接同时使用[AdventureWorks]
    ALTER DATABASE [AdventureWorks] SET READ_COMMITTED_SNAPSHOT ON
    GO
    
    --设置使用已提交读隔离级别
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    GO
    
    BEGIN TRAN
    --查询1
    --这里将返回初始值48
    SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    ----------------------------------------------------------------------------------------------------
    
    --在会话2上:
    USE [AdventureWorks]
    GO
    
    BEGIN TRAN
    --修改1
    --假期时间减8
    --修改不会被会话1阻塞
    UPDATE [HumanResources].[Employee]
    SET [VacationHours]=[VacationHours]-8
    WHERE [EmployeeID]=4
    
    --查询1
    --确认值已经被修改为40
    SELECT [VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    ------------------------------------------------------------------------------------------------
    
    --在会话1上:
    --查询2
    --再次运行查询语句
    --还是返回48(修改前的值),因为会话2还没有提交
    --会话1是从版本化的行读取数据
    SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    -------------------------------------------------------------------------------------------------
    
    --在会话2上:
    --提交事务
    COMMIT TRAN
    GO
    --------------------------------------------------------------------------------------------------
    
    --在会话1上:
    --查询3
    --这里和范例B不同,会话1始终返回已提交的值
    --这里返回40,因为会话2已经提交了事务
    SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    --修改2
    --这里会成功
    UPDATE [HumanResources].[Employee]
    SET [SickLeaveHours]=[SickLeaveHours]-8
    WHERE [EmployeeID]=4
    
    --可以回滚会话1的修改
    --会话2的修改不会受影响
    ROLLBACK TRAN
    GO
    
    
    ------------------------------------------结论-----------------------------------------------------------
    --从上面的测试结果可以看到,原先会发生阻塞的两个会话在使用行版本控制的隔离级别后,都不会遇到阻塞了。
    --但是两种行版本控制的结果又有不同
    
    --但是行版本控制并不是消除阻塞和死锁的万灵药。在决定使用之前,必须考虑下面两个问题
    
    --(1)最终用户是否接受行版本控制下的运行结果?  行版本控制:数据库级别
    --上面的3个测试返回的结果都各有不同。在不同的事务阶段,有的被阻塞住,有的读到的是旧版本值,
    --有的读到新版本值。用户期望的行为是什么?他是希望哪怕被阻塞住也要读到最新版本数据,还是
    --能容忍读到旧版本数据呢?某些应用程序依赖于读隔离的锁定和阻塞行为,例如生成一个串行的流水号
    --之类的操作。改成行版本控制,原先的处理逻辑就不能正常工作了。所以在采用新的隔离级别之前,
    --一定要做好测试,确保应用按预期的逻辑运行
    
    
    
    
    --(2)SQL是否能支持行版本控制带来的额外负荷?
    --开启了行版本控制之后,SQL会把行版本存放在tempdb里。修改的数据越多,需要存储的信息越多
    --对SQL额外的负载就越大。所以如果一个应用要从其他隔离级别转向使用行版本控制,需要做特别
    --的测试,以确保现有的软硬件配置能支持额外的负荷,应用程序能够达到相似的响应速度
    
    
    
    


    2013年8月13日 2:18

全部回复

  • 如果兼容级别还是80(SQL2000),你启用不了READ_COMMITTED_SNAPSHOT

    对于读多写少的,启用这个有好处


    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com

    2013年8月12日 0:40
  • 开启这个设置,如果数据更改比较大的话,可能会增加TEMPDB的负担。因为行版本信息是保存在TEMPDB的。所以开启这个选项要多做测试确保不会产生副作用。

    Please Mark As Answer if it is helpful.

    2013年8月12日 2:25
  • 另外补充个,启用这个后,会不会导致脏读,比如说在存储过程里使用一些查询赋予变量值的时候,如果只是简单的开启这个选项而不作任何其它诸如过程、函数上的脚本改造的话

    jude

    2013年8月12日 8:29
  • 不会有脏读


    Please Mark As Answer if it is helpful.

    2013年8月12日 9:05
  • 简单地说, 在这种隔离级别下, 读取的数据如果在更新中, 那么读取到的是更新前的快照(条版本),  修改前的COMMIT数据, 所以这个不产生脏读

    NOLOCK提示读取的是更新中的数据(没有 COMMIT), 是脏数据

    而为了给数据读取提供可用的行版本, 对于数据更新而言, 它在更新数据前, 就需要为要更新的数据生成行版本(快照), 这是一个额外的开销, 在单个事务中更新的数据量越大, 这个开销越大

    2013年8月12日 12:36
  • LZ 我有READ_COMMITTED_SNAPSHOT的资料您可以看一下

    SQL有两种行版本控制:
    --(1)行版本控制的已提交读隔离(read_committed_snapshot)
    --(2)直接使用snapshot事务隔离级别
    
    --(1)(read_committed_snapshot):read_committed_snapshot数据库选项为ON时,read_committed事务通过使用行
    --版本控制提供语句级读取一致性
    
    --(2)(snapshot事务隔离级别)allow_snapshot_isolation数据库选项为ON时,snapshot事务通过使用行版本
    --控制提供事务级读取一致性
    
    
    --下列示例可以说明使用普通已提交读事务,行版本控制的快照隔离事务和行版本
    --控制的已提交读事务的行为差异
    
    --示例:
    
    --A 普通已提交事务
    --在此示例中,一个普通read committed事务将读取数据,然后由另一事务修改此数据。执行
    --完的读操作不阻塞由其他事务执行的更新操作。但是,在其他事务已经做了更新操作后,读
    --操作会被阻塞住,直到更新操作事务提交为止
    
    --在会话1上:
    USE [AdventureWorks]
    GO
    BEGIN TRAN
    --查询1
    --这个查询将返回员工有48小时休假时间
    SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    -----------------------------------------------------------------------------------------------
    
    --在会话2上:
    USE [AdventureWorks]
    GO
    BEGIN TRAN
    --修改1
    --休假时间减去8
    --修改不会被阻塞,因为会话1不会持有S锁不放
    UPDATE [HumanResources].[Employee]
    SET [VacationHours]=[VacationHours]-8
    WHERE [EmployeeID]=4
    
    --查询1
    --现在休假时间只有40小时
    SELECT [VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    --------------------------------------------------------------------------------------------------
    
    
    --在会话1上:
    --重新运行查询语句,会被会话2阻塞
    --查询2
    SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    -----------------------------------------------------------------------------------------------
    
    --在会话2上:
    --提交事务
    COMMIT TRAN
    GO
    
    -----------------------------------------------------------------------------------------------
    
    --在会话1上:
    --此时先前被阻塞的查询结束,返回会话2修改好的新数据:40
    --查询3
    --这里返回40,因为会话2已经提交了事务
    SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    --修改2
    --这里会成功
    UPDATE [HumanResources].[Employee]
    SET [SickLeaveHours]=[SickLeaveHours]-8
    WHERE [EmployeeID]=4
    
    SELECT [SickLeaveHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    --可以回滚会话1的修改
    --会话2的修改不会受影响
    ROLLBACK TRAN
    GO
    
    SELECT [SickLeaveHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    -------------------------------------------------------------------------------------------
    
    
    --B 使用快照隔离
    --此示例中,在快照隔离下运行的事务将读取数据,然后由另一个事务修改此数据。快照事务
    --不阻塞由其他事务执行的更新操作,他忽略数据的修改继续从版本化的行读取数据。也就是
    --说,读取到的是数据修改前的版本。但是,当快照事务尝试修改已由其他事务修改的数据时,
    --他将生成错误并终止
    
    --在会话1上:
    USE [AdventureWorks]
    GO
    
    --启用快照隔离
    ALTER DATABASE [AdventureWorks] SET ALLOW_SNAPSHOT_ISOLATION ON
    GO
    
    --设置使用快照隔离级别
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT
    GO
    
    BEGIN TRAN
    --查询1
    --查询返回员工有48小时假期
    SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    ---------------------------------------------------------------------------------------------
    
    --在会话2上:
    USE [AdventureWorks]
    GO
    
    BEGIN TRAN
    --修改1
    --假期时间减8
    --修改不会被会话1阻塞
    UPDATE [HumanResources].[Employee]
    SET [VacationHours]=[VacationHours]-8
    WHERE [EmployeeID]=4
    
    --查询1
    --确认值已经被改成40
    SELECT [VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    -----------------------------------------------------------------------------------------------
    
    --在会话1上:
    --查询2
    --再次运行查询语句
    --还是返回48(修改前的值),因为会话1是从版本化的行读取数据
    SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    ------------------------------------------------------------------------------------------------
    
    --在会话2上:
    --提交事务
    COMMIT TRAN
    GO
    
    -------------------------------------------------------------------------------------------------
    
    --在会话1上:
    --查询3
    --再次运行查询语句
    --还是返回48(修改前的值),因为会话1还是从版本化的行读取数据
    SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    --修改2
    --因为数据已经被会话2修改过,会话1想做任何修改时
    --会遇到3960错误
    --事务会自动回滚
    UPDATE [HumanResources].[Employee]
    SET [SickLeaveHours]=[SickLeaveHours]-8
    WHERE  [EmployeeID]=4
    
    --会话1的修改会回滚
    --会话2的修改不会回滚
    ROLLBACK TRAN
    GO
    
    ----------------------------------------------------------------------------------------------
    
    
    
    --C 使用行版本控制的已提交读
    --在此示例中,使用行版本控制的已提交读事务与其他事务并发运行。已提交读事务的行为与快照事务的行为
    --有所不同。与快照事务相同的是,即使其他事务修改了数据,已提交读事务也将读取版本化的行。
    --与快照事务不同的是,已提交读将执行下列操作:
    
    --(1)在其他事务提交数据更改之后,读取修改的数据
    --(2)能够更新由其他事务修改的数据,而快照事务不能
    
    --在会话1上:
    USE [AdventureWorks]
    GO
    --启用行版本控制的已提交读
    --注意运行这句话的时候,不可以有其他连接同时使用[AdventureWorks]
    ALTER DATABASE [AdventureWorks] SET READ_COMMITTED_SNAPSHOT ON
    GO
    
    --设置使用已提交读隔离级别
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    GO
    
    BEGIN TRAN
    --查询1
    --这里将返回初始值48
    SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    ----------------------------------------------------------------------------------------------------
    
    --在会话2上:
    USE [AdventureWorks]
    GO
    
    BEGIN TRAN
    --修改1
    --假期时间减8
    --修改不会被会话1阻塞
    UPDATE [HumanResources].[Employee]
    SET [VacationHours]=[VacationHours]-8
    WHERE [EmployeeID]=4
    
    --查询1
    --确认值已经被修改为40
    SELECT [VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    ------------------------------------------------------------------------------------------------
    
    --在会话1上:
    --查询2
    --再次运行查询语句
    --还是返回48(修改前的值),因为会话2还没有提交
    --会话1是从版本化的行读取数据
    SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    -------------------------------------------------------------------------------------------------
    
    --在会话2上:
    --提交事务
    COMMIT TRAN
    GO
    --------------------------------------------------------------------------------------------------
    
    --在会话1上:
    --查询3
    --这里和范例B不同,会话1始终返回已提交的值
    --这里返回40,因为会话2已经提交了事务
    SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
    
    --修改2
    --这里会成功
    UPDATE [HumanResources].[Employee]
    SET [SickLeaveHours]=[SickLeaveHours]-8
    WHERE [EmployeeID]=4
    
    --可以回滚会话1的修改
    --会话2的修改不会受影响
    ROLLBACK TRAN
    GO
    
    
    ------------------------------------------结论-----------------------------------------------------------
    --从上面的测试结果可以看到,原先会发生阻塞的两个会话在使用行版本控制的隔离级别后,都不会遇到阻塞了。
    --但是两种行版本控制的结果又有不同
    
    --但是行版本控制并不是消除阻塞和死锁的万灵药。在决定使用之前,必须考虑下面两个问题
    
    --(1)最终用户是否接受行版本控制下的运行结果?  行版本控制:数据库级别
    --上面的3个测试返回的结果都各有不同。在不同的事务阶段,有的被阻塞住,有的读到的是旧版本值,
    --有的读到新版本值。用户期望的行为是什么?他是希望哪怕被阻塞住也要读到最新版本数据,还是
    --能容忍读到旧版本数据呢?某些应用程序依赖于读隔离的锁定和阻塞行为,例如生成一个串行的流水号
    --之类的操作。改成行版本控制,原先的处理逻辑就不能正常工作了。所以在采用新的隔离级别之前,
    --一定要做好测试,确保应用按预期的逻辑运行
    
    
    
    
    --(2)SQL是否能支持行版本控制带来的额外负荷?
    --开启了行版本控制之后,SQL会把行版本存放在tempdb里。修改的数据越多,需要存储的信息越多
    --对SQL额外的负载就越大。所以如果一个应用要从其他隔离级别转向使用行版本控制,需要做特别
    --的测试,以确保现有的软硬件配置能支持额外的负荷,应用程序能够达到相似的响应速度
    
    
    
    


    2013年8月13日 2:18
  • 谢谢各位的回复,看来我想省点事,通过这个来避免因为程序上的写法欠考虑导致的阻塞和死锁,还是不大现实,还是得老老实实的发现一处问题,改一处了

    jack

    2013年8月13日 2:34
  • 谢谢各位的回复,看来我想省点事,通过这个来避免因为程序上的写法欠考虑导致的阻塞和死锁,还是不大现实,还是得老老实实的发现一处问题,改一处了

    jack

    是啊,任何东西都有副作用的,LZ一定要考虑好
    2013年8月13日 2:44