关于READ_COMMITTED_SNAPSHOT 选项

Question

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

---

jude

Answer 1

如果兼容级别还是80（SQL2000），你启用不了READ_COMMITTED_SNAPSHOT

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

---

Try SQL Server 2008 QQ:315054403 [e-mail]

Answer 2

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

---

Please Mark As Answer if it is helpful.

Answer 3

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

---

jude

Answer 4

不会有脏读

---

Please Mark As Answer if it is helpful.

Answer 5

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

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

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

Answer 6

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额外的负载就越大。所以如果一个应用要从其他隔离级别转向使用行版本控制，需要做特别
--的测试，以确保现有的软硬件配置能支持额外的负荷，应用程序能够达到相似的响应速度

Answer 7

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

---

jack

Answer 8

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

---

jack

是啊，任何东西都有副作用的，LZ一定要考虑好