数据库事务隔离级别

Contents

1. 一、SQL标准隔离级别
2. 二、其他隔离级别
1. 2.1. MVCC-Based Read committed
2. 2.2. 快照隔离级别

参考资料：
http://dblab.xmu.edu.cn/post/1359/
http://www.cnblogs.com/lyhabc/articles/3219693.html

一、SQL标准隔离级别

SQL92标准中对数据库事务隔离级别进行了分类：

read uncommitted
read committed
repeatable read
serializable

SQL92事务隔离级别的定义是基于悲观锁的思想，例如教科书上的三级封锁协议的定义；商用数据库的实现过程中，多采用基于乐观锁的MVCC技术；商用数据库对事务隔离级别的定义，跟SQL92的标准是有所差别的。

1.1 read uncommitted

读写操作互不阻塞，但读操作可能读到未提交的脏数据。
实现时无需加锁。

1.2 read committed

读写操作会相互阻塞，因为每个读操作都要读到已提交的最新数据，当事务1读取数据项x，而事务2修改了x且执行commit trans之前，事务1的查询语句执行会阻塞，等待事务2完成commit操作之后才能读取到。
这个现象是语句级的，即事务中可能有多条语句，每条读写语句都遵循上述规则，一条查询语句必须等待其他针对相同数据项的更新语句（若存在的话）的提交才能执行。
实现方式是，读数据前必须首先获得一个读操作锁，一旦数据读取之后该锁被立即释放。
–示例：

在此示例中，我们用两个session分别开启事务，一个读事务将读取数据，然后另一事务修改此数据。执行完的读操作（即事务内的select语句）不阻塞由其他事务的更新操作，即执行完毕的读不会阻塞写。但是，在其他事务已经做了更新操作后，读操作会被阻塞住，直到更新操作事务提交为止。

–Session 1：
USE [db_test]
GO
BEGIN TRAN
–查询1
–这个查询将返回4号员工有48小时休假时间：
SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4

–Session 2：
USE [db_test]
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

–Session 1：
–重新运行查询语句，会被会话2阻塞
–查询2
SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4

–Session 2：
–提交事务
COMMIT TRAN
GO

–Session 1：
–此时先前被阻塞的查询结束，返回会话2修改好的新数据：40
–查询3
–这里返回40，因为会话2已经提交了事务：
SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
–修改2
–将该员工的时间再次减8，这里会成功：
UPDATE [HumanResources].[Employee]
SET [SickLeaveHours]=[SickLeaveHours]-8
WHERE [EmployeeID]=4
SELECT [SickLeaveHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
–回滚会话1的修改
–会话2的修改将不受影响，此时值恢复为40：
ROLLBACK TRAN
GO

SELECT [SickLeaveHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4

1.3 repeatable read

一个事务中的前后两次读操作总是保证能够读到相同的值，这是与read committed不同的地方(行级锁且锁住读操作的间隔，而read committed不锁间隔)。
可重复读可以避免脏读、不可重复读现象，但可能会出现幻想读（幻象即之前没出现的数据像幽灵一样突然出现），这是因为该隔离级别可以锁住已有数据，但可能其他事务又插入了新的符合检索条件的数据。
实现方式是，事务在请求读数据之前必须获得一个锁，并且保持该锁直到事务结束。

1.4 serializable

最高隔离级别，事务的执行仿佛是串行化的，或并发的事务执行结果是完全可串行化的。
实现方式是，每个事务都直接锁住整张表。

二、其他隔离级别

在商用数据库中比较常见的有快照隔离、游标稳定性隔离、读一致等。
基于多版本并发控制MVCC技术可以实现多种隔离级别，如快照隔离、以及mvcc-based read committed（基于多版本的已提交读隔离级别）。
在SQL标准的read committed隔离级别下，一个读操作会和一个写操作相互阻塞。有些关系型数据库（例如ORACLE）使用的是另一种处理方式，在任何一个修改之前，先对修改前的版本做一个复制，后续的一切读操作都会去读这个复制的版本，修改将创建一个新的版本。在这种处理方式下，读、写操作不会互相阻塞。使用这种行版本控制机制的好处，是程序的并发性比较高，但是缺点是用户读到的虽然不是一个脏数据，但是可能是个正在被修改马上就要过期的数据值。如果根据这个过期的值做数据修改，会产生逻辑错误。有些用户可能为了更高的并发性而不在乎这种缺点，为了满足这部分用户的需求，SQL2005也引入了这种机制，来实现类似的功能。所以选取行版本并发控制隔离级别也可以成为消除阻塞和死锁的一种手段。
SQL有两种基于行版本并发控制的隔离级别：

已提交读隔离（read_committed_snapshot）
快照隔离级别

MVCC-Based Read committed

示例：
– 使用行版本控制的已提交读
– 在此示例中，使用行版本控制的已提交读事务与其他事务并发运行。已提交读事务的行为与快照事务的行为
– 有所不同。与快照事务相同的是，即使其他事务修改了数据，已提交读事务也将读取版本化的行。
– 与快照事务不同的是，已提交读将执行下列操作：
–（1）在其他事务提交数据更改之后，读取修改的数据
–（2）能够更新由其他事务修改的数据，而快照事务不能
– 在会话1上：

USE [db_test]
GO
–启用行版本控制的已提交读
–注意运行这句话的时候，不可以有其他连接同时使用[db_test]
ALTER DATABASE [db_test] 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

–在会话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上：
USE [db_test]
GO

– 启用快照隔离
ALTER DATABASE [db_test] 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 [db_test]
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