并发事务下各数据库外部表现实测之一（SQL Server篇）

当同时有多个事务访问相同数据时，DBMS会采取锁或MVCC的机制确保数据的完整性。反映到应用程序上的表现可能就是等待或报错。不同DBMS因为采取的机制或策略不同，其外部表现也有很大差异。于是笔者决定通过测试一探究竟。先以SQL Server作为对象进行测试。

一、 背景知识
对并发事务的数据完整性保护的强弱可通过隔离级别控制，如果过强会影响并发性能，过弱可能不满足应用的需要。SQL 标准用三个存在并发的事务时应该避免的现像定义了四个级别的事务隔离。 
脏读 
  一个事务读取了另一个未提交事务写入的数据。
不可重复读 
  一个事务重新读取前面读取过的数据，发现该数据已经被另一个已提交事务修改。
幻读 
  一个事务重新执行一个查询，返回一套符合查询条件的行，发现这些行因为其它最近提交的事务而发生了改变。

SQL 事务隔离级别

隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读已提交	不可能	可能	可能
可重复读	不可能	不可能	可能
可串行化	不可能	不可能	不可能

二、测试准备
1. 测试环境
OS:Windows 7
DBMS:SQL Server 2008 Express


2. 测试观点
考虑以下因素组合的的情况下2个并发事务的相互影响。
1）事务隔离级别
  读未提交，读已提交，可重复读，可串行化
2）DML语句
  select,insert,update,delete
3）访问对象
 表，同一行，不同行

3. 数据定义
使用下面具有代性的表定义，并插入两条记录
create table tb1(id int primary key,name varchar(30));
insert into tb1 values(1,'a');
insert into tb1 values(2,'a');

三、测试方法
同时开2个终端并开始事务块，先在终端1上执行一个SQL语句，不提交，然后在终端2上执行第二个SQL语句，观察第二个SQL语句是否受第一个SQL语句影响。
先执行的SQL语句根据作用范围，分为单行和整表的查询/更新/删除(单行处理一般会走索引，而整表处理则顺序扫描)；后执行的SQL语句根据前一SQL语句作用范围，分为同一行，不同行和整表的操作。
在不同的事务隔离级别下，分别做以上测试。

先执行的SQL语句如下:

名称	SQL语句
单行查询	select * from tb1 where id = 1
整表查询	select * from tb1
插入	insert into tb1 values(5,'b')
单行更新	update tb1 set name = 'b' where id = 1
整表更新	update tb1 set name = 'b'
单行删除	delete from tb1 where id = 1
整表删除	delete from tb1

后执行的SQL语句，根据先执行的SQL语句有所不同。先执行的SQL语句是单行查询、单行更新或单行删除时，后执行的SQL语句如下：

名称	SQL语句
同一行查询	select * from tb1 where id = 1
非同行查询	select * from tb1 where id = 2
整表查询	select * from tb1
同一行插入	insert into tb1 values(1,'c')
非同行插入	insert into tb1 values(6,'c')
同一行更新	update tb1 set name = 'c' where id = 1
非同行更新	update tb1 set name = 'c' where id = 2
整表更新	update tb1 set name = 'c'
同一行删除	delete from tb1 where id = 1
非同行删除	delete from tb1 where id = 2
整表删除	delete from tb1

先执行的SQL语句是整表查询、整表更新或整表删除时，非同行的查询、更新和删除的对象为不存在的行，其他和先行SQL是单行操作时相同。

名称	SQL语句
同一行查询	select * from tb1 where id = 1
非同行查询	select * from tb1 where id = 100
整表查询	select * from tb1
同一行插入	insert into tb1 values(1,'c')
非同行插入	insert into tb1 values(6,'c')
同一行更新	update tb1 set name = 'c' where id = 1
非同行更新	update tb1 set name = 'c' where id = 100
整表更新	update tb1 set name = 'c'
同一行删除	delete from tb1 where id = 1
非同行删除	delete from tb1 where id = 100
整表删除	delete from tb1

注：黄色代表和先行SQL是单行操作时不同的地方

先执行的SQL语句是插入时，同一行代表和插入语句的id相同，非同行代表完全新的行。

名称	SQL语句
同一行查询	select * from tb1 where id = 5
非同行查询	select * from tb1 where id = 2
整表查询	select * from tb1
同一行插入	insert into tb1 values(5,'c')
非同行插入	insert into tb1 values(6,'c')
同一行更新	update tb1 set name = 'c' where id = 5
非同行更新	update tb1 set name = 'c' where id = 2
整表更新	update tb1 set name = 'c'
同一行删除	delete from tb1 where id = 5
非同行删除	delete from tb1 where id = 2
整表删除	delete from tb1

注：黄色代表和先行SQL是单行操作时不同的地方

四、测试结果
4种隔离级别下的测试结果如下

读未提交:

先执行SQL\后执行SQL	同一行查询	非同行查询	整表查询	同一行插入	非同行插入	同一行更新	非同行更新	整表更新	同一行删除	非同行删除	整表删除
单行查询	OK	OK	OK	主键冲突	OK	OK	OK	OK	OK	OK	OK
整表查询	OK	OK	OK	主键冲突	OK	OK	OK	OK	OK	OK	OK
插入	OK(*)	OK	OK(*)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)
单行更新	OK(*)	OK	OK(*)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)
整表更新	OK(*)	OK	OK(*)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)
单行删除	OK(*)	OK	OK(*)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)
整表删除	OK(*)	OK	OK(*)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)

OK(*):基于更新后数据，即发生了脏读
等待(*):如果先行的SQL提交，则基于更新后的数据，否则基于原来的数据

读已提交:

先执行SQL\后执行SQL	同一行查询	非同行查询	整表查询	同一行插入	非同行插入	同一行更新	非同行更新	整表更新	同一行删除	非同行删除	整表删除
单行查询	OK	OK	OK	主键冲突	OK	OK	OK	OK	OK	OK	OK
整表查询	OK	OK	OK	主键冲突	OK	OK	OK	OK	OK	OK	OK
插入	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)
单行更新	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)
整表更新	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)
单行删除	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)
整表删除	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)

等待(*):如果先行的SQL提交，则基于更新后的数据，否则基于原来的数据
注：黄色代表和读未提交不同的地方

可重复读:

先执行SQL\后执行SQL	同一行查询	非同行查询	整表查询	同一行插入	非同行插入	同一行更新	非同行更新	整表更新	同一行删除	非同行删除	整表删除
单行查询	OK	OK	OK	等待	OK	等待	OK	等待	等待	OK	等待
整表查询	OK	OK	OK	等待	OK	等待	OK	等待	等待	OK	等待
插入	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)
单行更新	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)
整表更新	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)
单行删除	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)
整表删除	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)

等待(*):如果先行的SQL提交，则基于更新后的数据，否则基于原来的数据
注：黄色代表和读已提交不同的地方

可串行化：

先执行SQL\后执行SQL	同一行查询	非同行查询	整表查询	同一行插入	非同行插入	同一行更新	非同行更新	整表更新	同一行删除	非同行删除	整表删除
单行查询	OK	OK	OK	等待	OK	等待	OK	等待	等待	OK	等待
整表查询	OK	OK	OK	等待	等待	等待	等待	等待	等待	等待	等待
插入	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)
单行更新	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)
整表更新	等待(*)	OK	等待(*)	等待(*)	等待(*)	等待(*)	等待(*)	等待(*)	等待(*)	等待(*)	等待(*)
单行删除	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)
整表删除	等待(*)	OK	等待(*)	等待(*)	等待(*)	等待(*)	等待(*)	等待(*)	等待(*)	等待(*)	等待(*)

等待(*):如果先行的SQL提交，则基于更新后的数据，否则基于原来的数据
注：黄色代表和可重复读不同的地方

不难看出上述4种隔离级别是通过锁实现的，SQL Server中还支持基于行版本控制(MVCC)的隔离级别。启用这个功能需要打开下面2个选项

ALTER DATABASE dbname SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE dbname SET READ_COMMITTED_SNAPSHOT ON

注：详见

打开READ_COMMITTED_SNAPSHOT开关后，读已提交的实现方式及外部表现就不一样了。
读已提交(READ_COMMITTED_SNAPSHOT=ON)：

先执行SQL\后执行SQL	同一行查询	非同行查询	整表查询	同一行插入	非同行插入	同一行更新	非同行更新	整表更新	同一行删除	非同行删除	整表删除
单行查询	OK	OK	OK	主键冲突	OK	OK	OK	OK	OK	OK	OK
整表查询	OK	OK	OK	主键冲突	OK	OK	OK	OK	OK	OK	OK
插入	OK(**)	OK	OK(**)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)
单行更新	OK(**)	OK	OK(**)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)
整表更新	OK(**)	OK	OK(**)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)
单行删除	OK(**)	OK	OK(**)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)
整表删除	OK(**)	OK	OK(**)	等待(*)	OK	等待(*)	OK	等待(*)	等待(*)	OK	等待(*)

OK(**):基于更新前的数据，即看到是查询时的快照
等待(*):如果先行的SQL提交，则基于更新后的数据，否则基于原来的数据
注：黄色代表和READ_COMMITTED_SNAPSHOT=OFF时的读已提交不同的地方

打开ALLOW_SNAPSHOT_ISOLATION开关后，可以使用SQL Server扩展的一种隔离级别SNAPSHOT，也称作SI（SNAPSHOT ISOLATION）。
SNAPSHOT满足SQL规范的可串行化隔离级别定义，即脏读、不可重复读和幻读都不会出现，但SNAPSHOT并不是真正的可串行化，关于这一点准备以后详细进行说明。

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

SNAPSHOT：

先执行SQL\后执行SQL	同一行查询	非同行查询	整表查询	同一行插入	非同行插入	同一行更新	非同行更新	整表更新	同一行删除	非同行删除	整表删除
单行查询	OK	OK	OK	主键冲突	OK	OK	OK	OK	OK	OK	OK
整表查询	OK	OK	OK	主键冲突	OK	OK	OK	OK	OK	OK	OK
插入	OK(***)	OK	OK(***)	等待(*)	OK	等待(**)	OK	等待(**)	等待(**)	OK	等待(**)
单行更新	OK(***)	OK	OK(***)	等待(*)	OK	等待(**)	OK	等待(**)	等待(**)	OK	等待(**)
整表更新	OK(***)	OK	OK(***)	等待(*)	OK	等待(**)	OK	等待(**)	等待(**)	OK	等待(**)
单行删除	OK(***)	OK	OK(***)	等待(*)	OK	等待(**)	OK	等待(**)	等待(**)	OK	等待(**)
整表删除	OK(***)	OK	OK(***)	等待(*)	OK	等待(**)	OK	等待(**)	等待(**)	OK	等待(**)

OK(***):基于更新前的数据，查询看到的是事务开始时的快照
等待(*):如果先行的SQL提交，则基于更新后的数据，否则基于原来的数据
等待(**):如果先行的SQL提交，则报更新冲突的错误
注：黄色代表和READ_COMMITTED_SNAPSHOT=ON时的读已提交不同的地方


五、小结
SQL Server完整实现了SQL标准定义的4个隔离级别。并且还提供了基于MVCC的读未提交和SNAPSHOT隔离级别，可避免读和写之间的锁定提高并发性能。