阻塞与死锁 之三 事务隔离级别与锁的申请和释放

数据库有并发操作的时候，修改数据的事务会影响同时要去读取或修改相同数据的其他事务。如果数据存储系统没有并发控制，则事务可能会看到以下负面影响：

·        脏读：当一个事务开始更新数据，但是这一个事务并没有完成提交，这时候，第二个事务开始读取数据，把第一个事务所更改的数据读了出来。第二个事务读取的数据是临时的，而且很危险的，因为有可能第一个事务最终做rollback操作

·        不可重复读：在一个事务中，我们读取某一行，得到数据，这时候，第二个事务对该行数据进行了修改，然后第一个事务再次读取这一行时，发现数据被改变了。也就是在一个事务中，多次读取某一行数据，可能会得到不同的结果，这称为不可重复读。

·        幻读：在一个事务中，我们读取数据，发现没有特定的行，第一个事务还没有结束。这时候，第二个事务插入了该行数据，然后在第一个事务再次读取时，我们会发现该行突然出现了。这称为幻读。

上面情况的具体定义，可在SQLServer的联机丛书里查到。当许多人试图同时修改数据库中的数据时，必须实现一个控制系统，使一个人所做的修改不会对他人所做的修改产生负面影响，这就称为并发控制。

需要注意的是，不同性质的应用程序对并发控制会有不一样的需求。例如一个银行ATM系统，可能就不允许不可重复读的出现。而一个报表系统，可能对脏读的敏感度就不会那么高。要防止的负面影响越多，隔离级别就越高，程序的并发性也就越差。并不是每个应用程序都需要将上面几种问题全部避免的。

数据库系统通过定义事务的隔离级别来定义使用哪一级的并发控制。SQLServer数据库引擎支持如下的隔离级别：

·        未提交读（隔离事务的最低级别，只能保证不读取物理上损坏的数据）。

·        已提交读（数据库引擎的默认级别，可以防止脏读）

·        可重复读（可以防止脏读和不可重复读，但有时候可能会出现幻影数据）

·        快照：使用行版本控制来防止幻读，同时减少阻塞。

·        可序列化（隔离事务的最高级别，它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行）

 

表14-4显示了不同隔离级别允许的并发副作用。

表14-4 不同隔离级别允许的并发副作用

隔离级别	脏读	不可重复读	幻读
未提交读	是	是	是
已提交读	否	是	是
可重复读	否	否	是
快照	否	否	否
可序列化	否	否	否

            未提交读（READ UNCOMMITTED）

指定语句可以读取已由其他事务修改但尚未提交的行。也就是说，允许脏读。

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

换句话说，未提交读的意思也就是，读的时候不申请共享锁。所以它不会被其他人的排他锁阻塞，它也不会阻塞别人申请排他锁。

已提交读（READ COMMITTED）

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

数据库引擎会在读的时候使用共享锁防止其他事务在当前事务执行读取操作期间修改行。共享锁还会阻止语句在其他事务完成之前读取由这些事务修改的行。但是，语句运行完后便会释放共享锁，而不是等到事务提交的时候。

可重复读（REPEATABLE READ）

指定语句不能读取已由其他事务修改但尚未提交的行，并且指定，其他任何事务都不能在当前事务完成之前修改由当前事务读取的数据。

在这个隔离级别上，对事务中的每个语句所读取的全部数据都设置了共享锁，并且该共享锁一直保持到事务完成为止。这样可以防止其他事务修改当前事务读取的任何行。其他事务可以插入与当前事务所发出语句的搜索条件相匹配的新行。如果当前事务随后重试执行该语句，它会检索新行，从而产生幻读。

由于共享锁一直保持到事务结束，而不是在每个语句结束时释放，所以并发性低于默认的READCOMMITTED隔离级别。此选项只在必要时使用。

快照（SNAPSHOT）

在SQL Server标准的已提交读（READ COMMITTED）隔离级别下，一个读操作会和一个写操作相互阻塞。未提交读（READUNCOMMITTED）虽然不会有这种阻塞，但是读操作可能会读到脏数据，这是大部分用户不能接受的。有些关系型数据库（例如Oracle）使用的是另一种处理方式。在任何一个修改之前，先对修改前的版本做一个复制，后续的一切读操作都会去读这个复制的版本，修改将创建一个新的版本。在这种处理方式下，读、写操作不会相互阻塞。使用这种行版本控制机制的好处，是程序的并发性比较高，但是缺点是用户读到的虽然不是一个脏数据，但是可能是个正在被修改马上就要过期的数据值。如果根据这个过期的值做数据修改，会产生逻辑错误。

有些用户可能为了更高的并发性而不在乎这种缺点，所以更喜欢Oracle的那种处理方法。为了满足这部分用户的需求，SQL Server也引入了这种机制，来实现类似的功能。所以选取行版本控制隔离级别也可以成为消除阻塞和死锁的一种手段。

SQL Server有两种行版本控制，使用行版本控制的已提交读隔离（READ_COMMITTED_SNAPSHOT）和直接使用SNAPSHOT事务隔离级别。READ_COMMITTED_SNAPSHOT数据库选项为ON时，READ_COMMITTED事务通过使用行版本控制提供语句级读取一致性。ALLOW_SNAPSHOT_ISOLATION数据库选项为ON时，SNAPSHOT事务通过使用行版本控制提供事务级读取一致性。这两种方法也是有区别的，我们会在后面进一步描述它们之间的区别。

可序列化（SERIALIZABLE）

可序列化的要求是除了满足可重复读要求以外，还要求在当前事务完成之前，其他事务不能使用当前事务中任何语句读取的键值进行插入和删除操作。

SQL Server通过加范围锁的方式来实现可序列化。范围锁处于与事务中执行的每个语句的搜索条件相匹配的键值范围之内。这样可以阻止其他事务删除或插入任何行，从而限定当前事务所执行的任何语句。这意味着如果再次执行事务中的任何语句，则这些语句便会读取同一组行。在事务完成之前将一直保持范围锁。

这是限制最多的隔离级别，因为它锁定了键的整个范围，并在事务完成之前一直保持范围锁。因为并发级别最低，所以应只在必要时才使用该选项。该选项的作用与在事务内所有SELECT语句中的所有表上设置HOLDLOCK相同。

通过上面的介绍可以看出，SQL Server主要是通过对共享锁申请和释放机制的不同处理，来实现不同事务隔离级别的。表14-5显示了不同隔离级别（除了快照隔离级别）对共享锁处理的不同方式

表14-5 不同隔离级别对共享锁的不同处理方式

隔离级别	是否申请 共享锁	何时释放	有无范围锁
未提交读	不申请	--	无
已提交读	申请	当前语句做完时	无
可重复读	申请	事务提交时	无
可序列化	申请	事务提交时	有

也就是说，事务隔离级别越高，共享锁被持有的时间越长。而可序列化还要申请粒度更高的范围锁，并一直持有到事务结束。所以，如果阻塞发生在共享锁上面，可以通过降低事务隔离级别得到缓解。

需要说明的是，SQL Server在处理排他锁的时候，每个除SNAPSHOT的事务隔离级别都是一样的。都是在修改的时候申请，直到事务提交的时候释放（而不是语句结束以后立即释放）。如果阻塞是发生在排他锁上面，是不能通过降低事务隔离级别得到缓解的。

 

下面我们以例子来说明不同ISOLATIONLEVEL设定的下，事务的不同表现，以表格纵方向为时间进度。表14-6显示未提交读和已提交读的不同表现。当事务隔离级别是未提交读的时候，我们读到的数据，有可能是一个临时值。也就是脏读。

表14-6 未提交读和已提交读的不同表现

 

未提交读	已提交读	其他事务
SELECT VacationHours FROM HumanResources.Employee WHERE EmployeeID = 4   （显示员工号为4的员工休假时间为48小时）	SELECT VacationHours FROM HumanResources.Employee WHERE EmployeeID = 4   （显示员工号为4的员工休假时间为48小时）
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED （事务设定为未提交读）	SET TRANSACTION ISOLATION LEVEL READ COMMITTED （事务设定为已提交读）
		BEGIN TRAN   UPDATE HumanResources.Employee SET VacationHours = VacationHours   + 4 WHERE EmployeeID = 4   （其他事务把该员工的休假时间增加4个小时，这时该事务并没有结束）
SELECT VacationHours FROM HumanResources.Employee WHERE EmployeeID = 4   （查询该员工的休假时间，返回结果是52个小时）但该数据并不一定准确，因为其他事务并没有真正提交。	SELECT VacationHours FROM HumanResources.Employee WHERE EmployeeID = 4   （执行该查询时，会被阻塞）
	阻塞消失，得到信息，该员工的休假时间是48个小时	ROLLBACK TRAN (由于种种原因，导致该员工的休假时间不能增加，该事务回滚)

 

 

表14-7显示已提交读和可重复读的不同表现。当事务隔离级别是已提交读的时候，我们在一个事务内，多次读取数据，返回的结果可能会有不同。

表14-7 已提交读和可重复读的不同表现

 

已提交读	可重复读	其他事务
SET TRANSACTION ISOLATION LEVEL READ COMMITTED   （事务设定为已提交读）	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ （事务设定为可重复读）
BEGIN TRAN   SELECT VacationHours FROM HumanResources.Employee WHERE EmployeeID = 4   （开启事务，显示员工号为4的员工休假时间为48小时，事务还没有结束）	BEGIN TRAN   SELECT VacationHours FROM HumanResources.Employee WHERE EmployeeID = 4   （开启事务，显示员工号为4的员工休假时间为48小时，事务还没有结束）
		BEGIN TRAN UPDATE HumanResources.Employee SET VacationHours = VacationHours   + 4 WHERE EmployeeID = 4 COMMIT TRAN （其他事务想把该员工的休假时间增加4个小时。如果前面的会话为已提交读模式，这个更新是可以执行的。但是如果前面的会话是可重复读模式，这个更新会被阻塞。）
SELECT VacationHours FROM HumanResources.Employee WHERE EmployeeID = 4 （在同一个事务中，我们再次查询，发现这次查询得到的员工休假时间成了52个小时了）	SELECT VacationHours FROM HumanResources.Employee WHERE EmployeeID = 4 （在同一个事务中，我们再次查询，查询得到的员工休假时间还是48个小时）
COMMIT TRAN (在一个事务内，对数据多次读取，结果会不一样，这可能对事务内部的计算，带来不确定因素)	COMMIT TRAN (事务结束)	如果前面的会话为可重复读模式，阻塞消除，更新开始执行。

 

 

表14-8显示可重复读和序列化的不同表现。当事务隔离级别是可重复读的时候，我们在一个事务内，多次查询符合某个条件的行数。不同的查询，返回的行数可能会不一样。

表14-8 可重复读和序列化的不同表现

 

可重复读	序列化	其他事务
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ （事务设定为可重复读）	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE （事务设定为序列化）
BEGIN TRAN   SELECT COUNT(*) FROM HumanResources.Employee   （开启事务，显示当前我们有290名员工，但事务没有结束）	BEGIN TRAN   SELECT COUNT(*) FROM HumanResources.Employee   （开启事务，显示当前我们有290名员工，但事务没有结束）
		BEGIN TRAN INSERT INTO HumanResources.Employee   VALUES( 481168328, 1009, 'adventure-works\jens1', 263, 'Tool Designer', '1949-08-29 00:00:00.000', 'M', 'M', '1998-01-11 00:00:00.000', 0, 13, 26, 1, NEWID(), '2004-07-31  00:00:00.000') COMMIT TRAN （其他事务新增加了一名员工。如果前面的会话为可重复读模式，这个插入是可以执行的。但是如果前面的会话是序列化模式，这个插入会被阻塞。）
SELECT COUNT(*) FROM HumanResources.Employee （在同一个事务中，我们再次查询，发现这次查询得到的员工数变成了291名了）	SELECT COUNT(*) FROM HumanResources.Employee （在同一个事务中，我们再次查询，查询得到的员工数还是290名）
COMMIT TRAN (在一个事务内，对数据多次读取，符合条件的行数会不一样，这可能对事务内部的计算，带来不确定因素)	COMMIT TRAN (事务结束)	如果当前面的会话为序列化模式，阻塞消失，插入开始执行

 

 

表14-9显示已提交读，行版本控制已提交读和快照隔离的不同表现。

A. 普通已提交事务

在此示例中，一个普通READCOMMITTED事务将读取数据，然后由另一事务修改此数据。执行完的读操作不阻塞由其他事务执行的更新操作。但是，在其他事务已经做了更新操作后，读操作会被阻塞住，直到更新操作事务提交为止。

B. 使用快照隔离

此示例中，在快照隔离下运行的事务将读取数据，然后由另一事务修改此数据。快照事务不阻塞由其他事务执行的更新操作，它忽略数据的修改继续从版本化的行读取数据。也就是说，读取到的是数据修改前的版本。但是，当快照事务尝试修改已由其他事务修改的数据时，它将生成错误并终止。

C. 使用行版本控制的已提交读

在此示例中，使用行版本控制的已提交读事务与其他事务并发运行。已提交读事务的行为与快照事务的行为有所不同。与快照事务相同的是，即使其他事务修改了数据，已提交读事务也将读取版本化的行。与快照事务不同的是，已提交读将执行下列操作：

·        在其他事务提交数据更改后，读取修改的数据。

·        能够更新由其他事务修改的数据，而快照事务不能。

表14-9 已提交读，行版本控制已提交读和快照隔离的不同表现

 

已提交读	行版本控制已提交读	快照隔离	其他事务
事务设定为已提交读。 SET TRANSACTION ISOLATION LEVEL READ COMMITTED	事务设定为行版本控制已提交读。 ALTER DATABASE AdventureWorks set READ_COMMITTED_SNAPSHOT ON   SET TRANSACTION ISOLATION LEVEL READ COMMITTED	事务设定为快照隔离。 ALTER DATABASE AdventureWorks set ALLOW_SNAPSHOT_ISOLATION ON   SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN 查询员工号为4的员工休假时间，返回结果为48	BEGIN TRAN 查询员工号为4的员工休假时间，返回结果为48	BEGIN TRAN 查询员工号为4的员工休假时间，返回结果为48
			BEGIN TRAN 增加员工号为4的员工休假时间4小时
查询员工号为4的员工休假时间，查询被阻塞	查询员工号为4的员工休假时间，得到结果48	查询员工号为4的员工休假时间，得到结果48
这时，阻塞解决，返回休假时间为52			COMMIT TRAN 提交事务
再次查询员工号为4的员工的休假时间，返回52个小时。	再次查询员工号为4的员工的休假时间，返回52个小时。	再次查询员工号为4的员工的休假时间，返回48个小时。
改变员工号为4的员工的Title为Design Engineer, 成功.	改变员工号为4的员工的Title为Design Engineer, 成功.	改变员工号为4的员工的Title为Design  Engineer,失败，报如下错误 Msg 3960, Level 16, State 2, Line 1 Snapshot isolation transaction aborted due to update  conflict.

 

 

行版本控制并不是消除阻塞和死锁的万灵药。在决定使用之前，必须考虑下面两个问题。

1. 最终用户是否接受行版本控制下的运行结果？

上面的3个测试返回的结果都各有不同。在不同的事务阶段，有的被阻塞住，有的读到的是旧版本值，有的能读到新版本值。用户期望的行为是什么呢？他是希望哪怕被阻塞住也要读到最新版本的数据，还是能容忍读到旧版本数据呢？某些应用程序依赖于读隔离的锁定和阻塞行为，例如生成一个串行的流水号之类的操作。改成行版本控制，原先的处理逻辑就不能正常工作了。所以在采用新的隔离级别之前，一定要做好测试，确保应用会按预期的逻辑运行。

2. SQL Server是否能支持行版本控制带来的额外负荷？

开启了行版本控制后，SQL Server会把行版本存放在tempdb里。修改的数据越多，需要存的信息越多，对SQL Server额外的负载就越大。所以如果一个应用要从其他隔离级别转向使用行版本控制，需要做特别的测试，以确保现有的软硬件配置能够支持额外的负荷，应用程序能够达到相似的响应速度。

 

表14-10显示不同隔离级别的优点，缺点，和使用场景。

表14-10 不同隔离级别的优缺点和使用场景

隔离级别	优点	缺点	使用场景
未提交读	读数据的时候，不申请共享锁，所以不会被阻塞	读到的数据，可能是脏的，不一致的。	如做年度、月度统计报表，数据并不一定要非常精确
已提交读	比较折中，而且是缺省的设置。	有可能有阻塞，在一个事务里，多次读取相同数据行，得到的结果可能不同	最常见的且是缺省的使用场景。
可重复读	在一个事务里，多次读取相同数据行，结果可确保不变的。	更严重的阻塞。而且在一个事务里，读取符合某查询条件的行数，会有变化	如某旅馆预定系统,允许客户按先到先服务的原则预定房间。当顾客扫描某个日期段内的可用房间列表和价格时，要求旅馆经理无法更改那些房间在指定日期范围内的房价，并且其他客户不能抢先预定，直到该事务终止。
可序列化	最严格的数据保护，在一个事务里，读取符合某查询条件的行数，也是确定的。事务之间不会有互相影响。	最严重的阻塞	如某旅馆预定系统,允许客户按先到先服务的原则预定房间。当顾客扫描某个日期段内的可用房间列表和价格时，除了要求旅馆经理无法更改房价，并且其他客户不能抢先预定，还要求对于事务内新增加的房间（如由于别人在该日期段内退房等原因）不予考虑，直到该事务终止。
行版本控制已提交读	阻塞大大减少	维护行版本需要额外开销	如果缺省方式阻塞比较严重，推荐用行版本控制已提交读来改善性能
快照隔离	阻塞大大减少	维护行版本需要额外开销，且可能读到旧的数据。	允许读取稍微比较旧版本信息的情况下