事务隔离级别

测试表脚本：

SQL SERVER

CREATE TABLE  [ Customer ](

    [ CustID ] [ int ]  NOT NULL ,

    [ Fname ] [ nvarchar ]( 20 ),

    [ Lname ] [ nvarchar ]( 20 ),

    [ Address ] [ nvarchar ]( 50 ),

    [ City ] [ nvarchar ]( 20 ),

    [ State ] [ nchar ]( 2 ) DEFAULT ( 'CA' ),

    [ Zip ] [ nchar ]( 5 )  NOT NULL ,

    [ Phone ] [ nchar ]( 10 )

)

insert into customer values ( 1 ,  'Gary' ,  'Mckee' ,  '111 Main' ,  'Palm Springs' ,  'CA' ,  94312 ,  7605551212 )

insert into customer values ( 2 ,  'Tom' ,  'Smith' ,  '609 Geogia' ,  'Fresno' 'JP' ,  33045 ,  5105551212 )

insert into customer values ( 3 ,  'Jams' ,  'bond' ,  'ST Geogie 21' ,  'Washington' ,  'NY' ,  20331 ,  4405551864 )



ORACLE

CREATE TABLE Customer (

     CustID int NOT NULL ,

     Fname nvarchar2 ( 20 ),

     Lname nvarchar2 ( 20 ),

     Address nvarchar2 ( 50 ),

     City nvarchar2 ( 20 ),

     State nchar ( 2 ) DEFAULT  'CA' ,

     Zip nchar ( 5 )  NOT NULL ,

     Phone nchar ( 10 )

);

insert into customer values ( 1 ,  'Gary' ,  'Mckee' ,  '111 Main' ,  'Palm Springs' ,  'CA' ,  94312 ,  7605551212 );

insert into customer values ( 2 ,  'Tom' ,  'Smith' ,  '609 Geogia' ,  'Fresno' ,  'JP' ,  33045 ,  5105551212 );

insert into customer values ( 3 ,  'Jams' ,  'bond' ,  'ST Geogie 21' ,  'Washington' ,  'NY' ,  20331 ,  4405551864 );



1。Sqlserver与oracle单条语句处理对比

SQL SERVER单条语句默认自动提交，即单条语句自动作为一个事务处理；而oracle的原则是尽量延后提交，除非遇到显式提交命令或者DDL语句。

SQL SERVER

打开事务1 :

运行：select  *  from customer

可以看到表有3条记录

运行：insert into customer values ( 4 ,  'Hello' ,  'world' ,  'paradise road 01' ,  'heaven' ,  'XY' ,  00001 ,  1234564321 )

转到事务2：

运行：select  *  from customer

可以看到事务1中刚插入的custid为4的记录。



ORACLE

打开事务1，运行：

select  *  from customer ;

可以看到表有3条记录，运行：

insert into customer values ( 4 ,  'Hello' ,  'world' ,  'paradise road 01' ,  'heaven' ,  'XY' ,  00001 ,  1234564321 );

转到事务2，运行：

select  *  from customer ;

能看到的还是3条记录，事务1中刚插入的一条记录未自动提交，看不到。

转到事务1，运行：

commit ;

转到事务2，运行：

select  *  from customer ;

现在能看到4条记录了。





2. 丢失更新

Sqlserver完全兼容ANSI 92标准定义的4个隔离级别。它的默认隔离级别是提交读（read committed），在该级别下，可能会有丢失更新的问题。Oracle的默认情形也一样。故不再重复。

SQL SERVER

打开事务1运行：

set transaction isolation level read committed

begin tran

select  *  from customer         -- 看到3条记录

现在切换到事务2，此时事务1还未结束。在事务2中运行：

set transaction isolation level read committed

begin tran

select  *  from customer         -- 看到3条记录，和事务1中相同

现在假设事务1事务继续运行，修改数据并提交：

update customer set state  =  'TK'  where CustID  =  3

commit

回到事务2，事务2根据先前查询到的结果修改数据：

update customer set Zip  =  99999 where state  =  'NY'

commit

结 果因为事务1已经修改了事务2的where条件数据，事务2未成功修改数据（其实准确的说应该算是幻象读引起的更新失败。不过若满足条件的记录数多的话， 事务2的update可能更新比预期的数量少的记录数，也可算“丢失”了部分本应完成的更新。个人认为只要明白实际上发生了什么即可，不必过分追究字 眼）。丢失更新还可能有别的情形，比如事务2也是

update customer set state  =  'KO'  where CustID  =  3

两个事务都结束后，事务2的结果反映到数据库中，但事务1的更新丢失了，事务2也不知道自己覆盖了事务1的更新。





3．脏读演示

sqlserver的默认隔离级别是提交读（read committed），当手工将其改为未提交读时，事务可以读取其它事务没提交的数据；oracle由于自身特殊实现机制，可以理解为自身基础的隔离级别就是可重复读（与ANSI标准还是有区别的，后面例子会说明）。

SQL SERVER

打开事务1，运行：

begin tran

select  *  from customer

    update customer set state  =  'TN'  where CustID  =  3

转到事务2，运行：

set transaction isolation level read uncommitted

begin tran

select  *  from customer

此时看到的数据是事务1已经更新但还未提交的（3号记录state值TN）。而如果事务1发觉数据处理有误，转到事务1，进行回滚：

    Rollback

此时事务2如根据刚读取的数据进一步处理，会造成错误。它读取的数据并未更新到数据库，是“脏”的。



ORACLE

ANSI 定义未提交读（read uncommitted）级别本意不是为了故意引入错误，而是提供一种可能的最大并发程度级别，即一个事务的数据更新不影响其它 事务的读取。Oracle从内核层面实现了更新数据不阻塞读。可以说它提供未提交读级别的兼容，但没有脏读问题。（详情参考对应PPT文档）故 oracle没有手工设置read uncommitted级别的语句。





4．不可重复读

Sql server 的默认级别没有脏读问题，但存在不可重复读问题。Oracle默认级别也是提交读，不过它因为自身特殊机制，在语句一级不存在不可重复读问题。也就是说当 运行时间较长的查询时，查询结果是与查询开始时刻一致的（即使查询过程中其它事务修改了要查询的数据），而SQL SERVER就存在问题 （sql server 2005新特性提供了可选的语句一级一致性支持，叫做行版本机制，实际上可以说是照着oracle的多版本来的，大体原理差不 多）。

由于语句一级的事务一致性难以演示，下面例子是事务一级，提交读隔离级别下发生的不可重复读现象：

SQL SERVER

打开事务1，运行：

set transaction isolation level read committed

begin tran

select  *  from customer where State  =  'CA'

可以得到1条记录，这个时候事务2中运行：

set transaction isolation level read committed

begin tran

update Customer set state  =  'JP'  where state  =  'CA'

commit

事务2插入一条记录并提交。回到事务1，事务1继续运行，此时它再次相同的查询，并借此作进一步修改，却发现读取到的数据发生了变化。

select  *  from customer where State  =  'CA'

-- 2次读取不一致，之后的数据处理应该取消。否则不正确

update Customer set city  =  'garden'  where state  =  'CA'

commit

读取未能获得记录。也就是说在同一事务中两次相同的查询获得了不同的结果，产生读取不可重复现象。



ORACLE

尽管oracle在默认隔离级别下提供了语句级的事务读一致性，但在事务级仍然是会出现不可重复读现象。和sql server一样，故不再重复。





5．幻像读

当sqlserver的隔离级别设置为可重复读（repeatable read），可以解决上面例子出现的问题。其内部是通过事务期间保持读锁来实现的。

SQL SERVER

开始事务1，修改事务级别为可重复读，执行：

set transaction isolation level repeatable read

begin tran

select  *  from customer where State  =  'CA'

和上例一样得到1条记录，这个时候事务2中运行：

set transaction isolation level repeatable read

begin tran

update Customer set state  =  'JP'  where state  =  'CA'

commit

会发现事务2一直等待，并不结束。返回事务1，运行：

select  *  from customer where State  =  'CA'         -- 2次读取结果一致

update Customer set city  =  'garden'  where state  =  'CA'

commit

事务2成功结束后，再返回事务1，发现事务1也完成了。通过锁机制阻塞其它事务的修改，保持了事务期间读取的一致性。然而，如果是插入数据，则还是会出现问题：

开始事务1，修改事务级别为可重复读，执行：

set transaction isolation level repeatable read

begin tran

select  *  from customer where State  =  'CA'

得到1条记录，这个时候事务2中运行：

set transaction isolation level repeatable read

begin tran

insert into customer values ( 4 ,  'hellow' ,  'world' ,  'paradise 001' ,  'garden' ,  'CA' ,  00000 ,  1119995555 )

commit

发现事务2立刻提交并正常结束了。返回事务1，运行：

select  *  from customer where State  =  'CA'

会发现得到了2条记录。这种现象就叫做幻像读。



ORACLE

由于自身特殊的机制，oracle没有提供一致读隔离级别的选项，想要获得一致读的效果，实际上需要将事务提升到串行化等级，即serializable。





6.串行化级别不同数据库实现

在这个级别，可以认为事务中的数据无论何时都是一致的，此级别使它显得好像没有其它用户在修改数据，数据库在事务开始时候被“冻结”（至少，对于本事务涉及的数据如此）。然而在不同数据库中，其实现机制也不同。

SQL SERVER

开始事务1，运行：

set transaction isolation level serializable

begin tran

select  *  from customer where State  =  'CA'

会得到1条记录，这时事务2开始运行：

set transaction isolation level serializable

begin tran

insert into customer values ( 4 ,  'hellow' ,  'world' ,  'paradise 001' ,  'garden' ,  'CA' ,  00000 ,  1119995555 )

commit

会发现事务2挂起，它在等待事务1结束。回到事务1，继续：

select  *  from customer where State  =  'CA'

update Customer set city  =  'garden'  where state  =  'CA'

commit

在片刻的等待以后，事务1得到类似以以下格式消息：

消息1205，级别13，状态56，第1 行

事务 ( 进程ID 51 ) 与另一个进程被死锁在锁资源上，并且已被选作死锁牺牲品。请重新运行该事务。

而 事务2更新了数据并正常结束。这是因为两个事务都设置成了串行化级别，当遇到冲突时候，sql server根据一定的规则选择牺牲掉其中一个事务，来保 证事务的串行性。上面的例子，如果将事务2的隔离级别改为提交读，那么事务2会等待事务1完成，之后自己正常完成（因为事务2没有串行需求，不会有死 锁）。



ORACLE

在oracle中，通过多版本，可以在一定程度上避免死锁。

开始事务1，运行：

set transaction isolation level serializable ;

select  *  from customer where State  =  'CA' ;    -- set tran语句隐式开始事务

得到1条记录，然后事务2开始运行：

set transaction isolation level serializable ;

insert into customer values ( 4 ,  'hellow' ,  'world' ,  'paradise 001' ,  'garden' ,  'CA' ,  00000 ,  1119995555 );

commit ;

可以发现事务2立刻完成，没有阻塞。回到事务1继续：

select  *  from customer where State  =  'CA' ;

update Customer set city  =  'garden'  where state  =  'CA' ;

commit ;

事务1中的第二次查询和事务开始时刻一致，就好像事务2已经完成的提交不存在。事务最终正常更新完毕，并保持了“事务开始”时刻的数据一致性。

然而，如果事务1，2修改同样的数据行，也会有错误，

开始事务1，运行：

set transaction isolation level serializable ;

select  *  from customer where State  =  'CA' ;    -- set tran语句隐式开始事务

得到1条记录，然后事务2开始运行：

set transaction isolation level serializable ;

update customer set state  =  'KO'  where state  =  'CA' ;

commit ;

可以发现事务2立刻完成，没有阻塞。回到事务1继续：

select  *  from customer where State  =  'CA' ;

update Customer set city  =  'garden'  where state  =  'CA' ;

commit ;

出现错误信息：

第 1 行出现错误 :

ORA - 08177 :  无法连续访问此事务处理

总的来说，oracle利用多版本的方式实现串行化级别更少造成死锁，除非两个事务修改了相同的数据行，一般也不会造成冲突。







7．不同隔离级别的相互影响

前 面的例子基本都是两个相同隔离级别事务的情况。如果不同隔离级别的事务发生冲突，会有什么不同吗？实际上，对于一个事务来说，其它事务的隔离级别对它来说 是未知的，更进一步，甚至数据库中有没有其它事务，有多少事务也不知道。影响事务访问数据就两方面因素：该事务自身的隔离级别，该事务要访问的数据上面锁 的状态。

SQL SERVER

开始事务1，运行：

set transaction isolation level serializable

begin tran

select  *  from customer where State  =  'CA'

事务1的查询获得1条记录，转到事务2，运行：

set transaction isolation level read uncommitted

begin tran

select  *  from customer

事务2查询获得3条记录，回到事务1，运行：

update Customer set city  =  'garden'  where state  =  'CA'

切换到事务2，运行：

select  *  from customer

update customer set state  =  'KO'  where state  =  'CA'

commit ;

因为事务2隔离级别为未提交读，因此事务1中刚作的修改可以立刻从查询看到，即使事务1还未结束。进一步的update因为事务1对记录加了独占锁，因此事务2挂起。回到事务1将其提交：

Commit

事务1正常结束，独占锁释放，从而让事务2得以继续修改数据，并最终完成。



ORACLE

Oracle数据库的隔离级别设置语句只有read committed和serializable（read only暂不讨论），加上其特殊锁机制，不同隔离级别事务间的影响除了上例（例6）中两个都为serializable的情况，其它都可视为互不阻塞。



8．页锁与行锁（限sql server）

Sql server的锁可以到行一级。然而它又存在自动的锁定扩大，锁定转换。因此存在一些意想不到的情况。下面是演示：

开始事务1，运行：

set transaction isolation level read committed

begin tran

select  *  from customer where State  =  'CA'

update Customer set city  =  'garden'  where state  =  'CA'

理论上来说，在提交读级别下，上面的update语句只是在state值为CA的数据行上加了独占锁，表中其它数据应该可以被其它事务更新，然而，如下事务2开始：

set transaction isolation level read committed

begin tran

select  *  from customer

update customer set state  =  'KO'  where state  =  'JP'

commit

发现事务2陷入阻塞状态。尽管它们更新的不是同一条记录。回到事务1，运行：

Commit

事务1结束后事务2才继续运行至结束。

如果我们在表上加入索引，如下：

CREATE NONCLUSTERED INDEX  [ idx_state ]  ON  [ dbo ].[ Customer ] (    [ State ])

再重复上面的步骤，会发现阻塞不再存在。

PS：这种现象应该和数据库默认加锁参数 / 机制有关，应该可以调整，但目前手中没有进一步资料。故仅罗列了现象。



ORACLE

Oracle在数据一级只有一种数据行上的锁，因此不存在sql server中这些问题。



9．Set transaction语句的作用周期

前面所有的例子，都是在会话窗口中进行的演示。一旦使用了set transaction语句，会影响整个会话。除非再显式改变隔离级别，否则将保持到会话结束。例如：

开始事务1，假设会话一开始处于SQL SERVER的默认隔离级别（read committed）：

begin tran

select  *  from customer where State  =  'CA'

select  *  from sys . dm_tran_locks

系统视图sys . dm_tran_locks可以查看当前的加锁情况，到目前位置，只有数据库级的锁。继续运行：

set transaction isolation level repeatable read

select  *  from customer where State  =  'CA'

select  *  from sys . dm_tran_locks

commit

接下来的语句改变了隔离级别到可重复读，接下来的查询，会看到行级锁的记录。在上面事务提交后，运行：

begin tran

select  *  from customer where State  =  'CA'

select  *  from sys . dm_tran_locks

commit

仍然会从视图sys . dm_tran_locks看到行级锁记录。整个会话期间都受到影响。



但是，如果调用存储过程，函数，则过程 / 函数中事务隔离级别的改变并不会对调用环境造成影响。可以通过以下例子说明，首先，创建一个存储过程脚本：

CREATE PROCEDURE  [ dbo ].[ test_tran_level ]

AS

BEGIN

    BEGIN TRAN

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

    SELECT  *  FROM CUSTOMER

    UPDATE CUSTOMER SET STATE  =  'SS'  WHERE CustID  =  3

    SELECT  *  FROM sys . dm_tran_locks

    COMMIT

END

然后，在会话窗口调用该过程，会话窗口当前隔离级别为默认的提交读：

Exec test_tran_level

运行的结果可以看到读取锁信息，再在会话中运行：

begin tran

select  *  from customer where State  =  'CA'

select  *  from sys . dm_tran_locks

commit

视图sys . dm_tran_locks并未有读锁的记录，说明事务隔离级