Transaction And Lock--READ COMMITTED隔离级别下的"脏读"

 

在READ UNCOMMITTED事务隔离级别下或使用WITH(NOLOCK)来查询数据时，会出现脏读情况，因此对于一些比较"关键"的业务，会要求不能使用WITH(NOLOCK)或允许在READ UNCOMMITTED事务隔离级别下，于是我们使用默认的READ COMMITTED隔离级别来访问数据，但是这样真的就没有问题么？

让我们来做个小实验

准备测试数据

--=======================================
--创建测试表
CREATE TABLE TB106
(
    C0 INT IDENTITY(1,1) PRIMARY KEY,
    C1 INT,
    C2 CHAR(100),
    C3 NVARCHAR(4000)
)

GO
--=======================================
--创建一个非聚簇索引
CREATE INDEX IX_C1_C2
ON TB106(C1,C2)
GO
--=======================================
--向表中填充1000条数据
DECLARE @ID INT 
SET @ID=0
WHILE(@ID<1000)
BEGIN
INSERT INTO TB106(C1,C2,C3)
SELECT @ID,@ID,
REPLICATE('A',3800)


SET @ID=@ID+1

END
GO
--=================================
--查看表中数据，共1000行
SELECT * FROM TB106

 

开启回话1，运行以下脚本

--======================
--开启事务，更新C0为100的数据
BEGIN  TRAN
UPDATE TB106
SET C1=101
WHERE C0=100

开启回话2，运行以下脚本

--=======================
--查询数据
SELECT C1,C2,C0 FROM TB106

我们会发现回话2被回话1阻塞，但是已经有少量数据开始被读取

我们再次回到回话1，继续执行以下脚本

--=====================
--更新C0为5的数据，并提交事务
UPDATE TB106
SET C1=1000
WHERE C0=5

COMMIT

伴随着回话1事务的提交，回话2没有了阻塞，顺利完成查询，但是奇迹出现了

表中只有1000行数据，为什么我们能查出1001行数据来呢？

我们来分析下执行结果，不难发现c0=5的数据被读取了两遍，更新前后的数据都被读取到，这不科学！在c0=5的数据被更新前，数据被读取了一遍，然后当读到c0=100的时候，回话被阻塞，然后c0=5的数据被更新，更新后的数据记录存放位置变动，移到了索引尾部，当阻塞结束后，该记录又再次被读取，从而导致一行记录被读取两遍。

 

--=====================================================================

这并不是MS的bug,让我们来仔细阅读下各种隔离级别的解释:

READ UNCOMMITTED 指定语句可以读取已由其他事务修改但尚未提交的行。 在 READ UNCOMMITTED 级别运行的事务，不会发出共享锁来防止其他事务修改当前事务读取的数据。READ UNCOMMITTED 事务也不会被排他锁阻塞，排他锁会禁止当前事务读取其他事务已修改但尚未提交的行。设置此选项之后，可以读取未提交的修改，这种读取称为脏读。在事务结束之前，可以更改数据中的值，行也可以出现在数据集中或从数据集中消失。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 NOLOCK 相同。这是隔离级别中限制最少的级别。

READ COMMITTED 指定语句不能读取已由其他事务修改但尚未提交的数据。这样可以避免脏读。其他事务可以在当前事务的各个语句之间更改数据，从而产生不可重复读取和幻像数据。该选项是 SQL Server 的默认设置。

REPEATABLE READ 指定语句不能读取已由其他事务修改但尚未提交的行，并且指定，其他任何事务都不能在当前事务完成之前修改由当前事务读取的数据。 对事务中的每个语句所读取的全部数据都设置了共享锁，并且该共享锁一直保持到事务完成为止。这样可以防止其他事务修改当前事务读取的任何行。其他事务可以插入与当前事务所发出语句的搜索条件相匹配的新行。如果当前事务随后重试执行该语句，它会检索新行，从而产生幻读。由于共享锁一直保持到事务结束，而不是在每个语句结束时释放，所以并发级别低于默认的 READ COMMITTED 隔离级别。此选项只在必要时使用。

 

--=====================================================================

误区：不知道有多少朋友和我一样，错误认为REPEATABLE READ分离级别只是为了保证两次SQL查询的数据不发生变化，而忽略了在一次查询期间内数据发生变化导致的问题。而由于导致该问题的发生概率比较低，往往不能引起我们足够重视，从而错误地认为READ COMMITTED隔离级别可以胜任类似需求。

 

--======================================================================

依旧是妹子压贴