隔离级别的学习和使用

chuxu

   事务指定的隔离级别定义一个事务必须与其他事务所修改的资源或数据的隔离程度。隔离级别的确定标准，是允许哪些并发操作的副作用，如脏读或幻读。选择事务隔离级别不影响保护数据修改所需的锁定。事务始终对其修改的任何数据进行独占锁定，并在事务完成前保留该锁定，而不管为该事务设置的隔离级别。对于读取操作，事务隔离级别主要定义保护级别，以使其不受其他事务所作修改的影响。


一、隔离级别对锁定的影响
SQL 标准为 SQL 事务定义了四种隔离级别。每种隔离级别指定当前事务执行时所不允许的交互作用的类型
－即事务间是否相互隔离，或它们是否可以读取或更新被另一事务所使用的信息。较高隔离级别包括由较
低级别所施加的限制。

编号        名称说明
0        读取未提交的允许事务读取未提交的数据更改
1        读取已提交的仅允许事务读取已提交的数据更改
2        可重复读取事务可重复同一查询，事务读取过的任何行都不会被更新或删除
3        可串行化读取事务可重复同一查询，且得到完全相同的结果。不能插入任何将出现在结果集中的行


可以在会话期间为所有的 select 查询选择隔离级别，或者也可以在事务中为特定查询或表选择隔离级别。
在所有隔离级别上，所有更新都会获取排它锁，并在事务持续期间一直持有。
以下表作为例子。
Create table T_sample
(id char(4)
val int
)lock datarows

create index T_sample_idx on T_sample(id);
insert into T_sample(‘100’,100);
insert into T_sample(‘200’,200);
insert into T_sample(‘300’,300);
insert into T_sample(‘400’,400);
insert into T_sample(‘500’,500);
insert into T_sample(‘600’,600);



1、隔离级别 0，读取未提交的
级别 0 （又称读取未提交的）允许任务读取数据库中未提交的数据更改。这也称为脏读，
原因是任务会显示以后被回退的结果。下表显示了一个执行脏读的 select 查询。

序列      事务T１               事件                   事务Ｔ２
1      Begin tran               T1，T2开始事务            Begin tran
2    Update T_sample        T1修改Val值
    set val=val-100
      Where id=’1’        
3                                   T2在隔离级别0            select * from T_sample where id=’1’
                                     读取锁定数据                           at isolation 0
4                                    T2结束                    Commit tran
5     Rollback tran              T1结束         

如果 T2在 T1 更新表后但在回退更改前查询该表， T2所计算的数量会少 100。
事务 T1 中的 update 语句在 T_sample 上获取一个排它锁。但是事务 T2 在查询
T_sample 前并不试图获取共享锁，因此它并没有被 T1 阻塞。反过来也一样。
如果 T1 在 T2 开始之前开始以隔离级别 0 查询T_sample，在 T2 执行时，
T1 仍然能够在 T_sample 上获取其排它锁，原因是 T2 在它所读取的页上不持有任何锁。

2、隔离级别 1，读取已提交的
级别 1 （又称读取已提交的）可防止脏读。级别 1 的查询只读取已提交的数据更改。
在隔离级别 1，如果事务需要读取被另一会话中未完成的事务修改过的行，该事务将等待，
直到第一个事务完成（提交或回退）
序列    事务T3                   事件                 事务Ｔ4
1       Begin tran            T3，T4开始事务          Begin tran
2      Update T_sample    T3修改Val值
     set val=val-100
      Where id=’1’                                       
3                                 T4试图获得共享锁       select * from T_sample where id=’1’
                                   以查询帐户，但是
                                   必须等到 T3 释放其锁。
        
4      Rollback tran           T3结束并释放锁
5                                  T4获得共享锁，查       Commit tran  
                                    询，并结束
        

3、隔离级别 3，可串行化读取
级别 3 可防止幻像。在一个事务读取了满足搜索条件的一组行，而第二个事务修改了数据
（通过 insert、delete 或 update 语句）时，便会产生幻像行。如果第一个事务按相同的
搜索条件重复读取，它将获得一组不同的行。在下表中，在隔离级别 1 运行的事务 T6 在
第二个查询中发现了一个幻像行。

        事务中的幻像（在隔离级别1的条件下）
序列     事务T5                 事件                         事务　Ｔ6
1      Begin tran              T5，T6开始事务            Begin tran
2                                 T6读取数据                   Select val from T_sample where id=’1’
3   Update T_sample       T5修改数据  
    set val=0
      Where id=’1’        
4    Commit tran             T5结束
5                                 T6查询获得了一个新值     Select val from T_sample where id=’1’
6                                 T6结束                        Commit tran

4、避免事务中的幻像（在隔离级别3的条件下）
序列         事务Ｔ7             事件                        事务　Ｔ8
1          Begin tran        T7，T8开始事务                Begin tran
2                                T7读取数据，并         Select val from T_sample where id=’1’
                                 获取共享锁        
3     Update T_sample      T8试图修改数据，
         set val=0               但排它锁被T7的
        Where id=’1’        共享锁阻塞
4                                T8查询获得了一个       Select val from T_sample where id=’1’
                                 相同的值
5                              T8结束，并释放共享锁          Commit tran
6      Commit tran        T7获取排它锁完成修改，结束
        

5、Adaptive Server 缺省隔离级别
Adaptive Server 的缺省隔离级别是 1，即防止脏读。可以用
set transaction isolation level *来指定隔离级别。
可通过在 select 或 readtext 命令中将 holdlock、noholdlock 和 shared
选项应用到单个表来替换某个会话的锁定级别：
使用级别   关键字            作用
1         noholdlock       直到事务结束再持有锁；在级别 3 使用以强制执行级别 1
2，3     holdlock          持有共享锁直到事务完成；在级别 1使用以强制执行级别 3
无        shared            在为更新操作打开游标时，对 select语句应用共享锁而非更新锁

6、使用 at isolation 子句
可通过使用带 select 或 readtext 命令的 at isolation 子句，更改查询中所有
表的隔离级别。at isolation 子句中的选项有：

使用级别     选项                   作用
0         read uncommitted     读取未提交的更改；使用级别 1、2或 3 查询执行脏读（级别 0）
1         read committed        只读取已提交的更改；等待锁被释放；在级别 0 使用，仅读取已提交的更改，但不持有锁
2         repeatable read        持有共享锁直到事务完成；使用级别 0 或级别 1 查询以强制执行级别2
3          serializable             持有共享锁直到事务完成；使用级别 1 或级别 2 查询以强制执行级别3

二 esql/c中隔离级别应用
1、esql/c缺省的配置项目
[DEFAULT]
;This is the default section loaded by applications that use the
;external configuration feature, but which do not specify their
;own application name. Initially this section is empty.Defaults
;from all properties will be the same as earlier releases of
;Open Client libraries.
[ANSI_ESQL]
;This section defines configuration which an ANSI conforming
;Embedded SQL application should use to get ANSI-defined
;behavior from SQL Servers and Open Client libraries. This set of
;configuration ;properties matches the set which earlier
;releases of Embedded SQL (version 10.0.x) automatically set for
;applications duringexecution of a CONNECT statement.
CS_CAP_RESPONSE=CS_RES_NOSTRIPBLANKS
CS_EXTRA_INF=CS_TRUE
CS_ANSI_BINDS=CS_TRUE
CS_OPT_ANSINULL=CS_TRUE
CS_OPT_ANSIPERM=CS_TRUE
CS_OPT_STR_RTRUNC=CS_TRUE
CS_OPT_ARITHABORT=CS_FALSE
CS_OPT_TRUNCIGNORE=CS_TRUE
CS_OPT_ISOLATION=CS_OPT_LEVEL3
CS_OPT_CHAINXACTS=CS_TRUE
CS_OPT_CURCLOSEONXACT=CS_TRUE
CS_OPT_QUOTED_IDENT=CS_TRUE
;End of default sections

在esql/c的开发中，应用缺省的隔离级别为3。利用上面的各种隔离级别所做的分析，可以在应用中加以灵活的使用。

2、案例一：应用读取T_sample表中相应ID的val值，根据输入的val值等情况对val进行修改。

exec sql select val into :wk_val from T_sample where id =:wk_id;
If (wk_val<wk_input_val)
{
  perform_a();
  wk_val=wk_val + wk_input_val;
}
else
{
  perform_b();
  wk_val=wk_val + wk_input_val;
}
exec sql update T_sample set val=:wk_val where id=:wk_id;

假设我们手工用set transaction isolation level 1的语句将程序的隔离级别置成了1。
2．1、假设两个程序串行执行。
序列     事务T9               事件                           事务　Ｔ10
1         Begin tran           T9，T10开始事务        Begin tran
2   select val into            T9
  :wk_val from T_sample   id＝’1’
  where id =:wk_id;         wk_input_val=50         
   wk_val的值为100        
3 wk_val=wk_val +          T9 wk_val=150
   wk_input_val               
4  Update T_sample         T9
   Set val=150                 执行修改，将val值
  Where id=’1’              改成了150
5                                  T10                         select val into :wk_val from
                                    id＝’1’,                     T_sample where id =:wk_id;
                                    wk_input_val=150            wk_val的值为150         
6                                   T10 wk_val=300          wk_val=wk_val + wk_input_val
7                                    T10                         Update T_sample
                                执行修改，将val值           Set val=300
                                     改成了300                  Where id=’1’

2．2、考虑两个程序并行处理的可能。

序列           事务Ｔ9                   事件                             事务　Ｔ10
1       Begin tran                 T9，T10开始事务                  Begin tran
2       select val into            T9
   wk_val from T_sample      id＝’1’
  where id =:wk_id;            wk_input_val=50        
     wk_val的值为100        
3                                    T10                                 select val into :wk_val
                                        id＝’1’,                       from T_sample where id =:wk_id;
                                      wk_input_val=150               wk_val的值为100
4   wk_val=wk_val +           T9 wk_val=150
      wk_input_val               
5                                     T10 wk_val=250                wk_val=wk_val + wk_input_val
6   Update T_sample           T9
      Set val=150                 执行修改，将val值改成了150
      Where id=’1’        
7                                      T10                                     Update T_sample
                                        执行修改，将val值改成了250          Set val=250
                                                                                  Where id=’1’  

2．3、也存在可能由于perform_a的耗时笔perform_b耗时长，导致T10先进行Update。那最后的结果将变成。
序列           事务Ｔ9                   事件                             事务　Ｔ10
6                                          T10                             Update T_sample
                                       执行修改，将val值改成了250        Set val=250
                                                                                  Where id=’1’   
7    Update T_sample            T9
      Set val=150               执行修改，将val值改成了150
      Where id=’1        
同一个程序，由于执行顺序的不同造成了不同的结果，显而易见这个结果并不是程序想要真正获得的，
而是由于出现了幻像导致结果不真实。

2．4 在缺省的隔离级别为3的情况下，串行执行的情况不会出现错误，
考虑并行执行的情况，看sybas是如何避免这种幻像的。
序列          事务　Ｔ9                   事件                              事务　Ｔ10
1            Begin tran               T9，T10开始事务                      Begin tran
2           select val                  T9
                into :wk_val            id＝’1’
             from T_sample            wk_input_val=50
              where id =:wk_id;     并在T_sample表上加共享锁。
           wk_val的值为100        
3                                          T10                                       select val into :wk_val
                                           id＝’1’,                               from T_sample where id =:wk_id;
                                            wk_input_val=150                 wk_val的值为100
                                            并在T_sample表上加共享锁         
4             wk_val=wk_val +       T9 wk_val=150
               wk_input_val               
5                                          T10 wk_val=250                     wk_val=wk_val + wk_input_val
6             Update T_sample       T9  
               Set val=150             但T9上的排它锁将        
          Where id=’1’             被T10上的共享锁所阻塞
7                                          T10                                      Update T_sample
                                            试图在T_sample表上排它锁，      Set val=250
                                            从而产生死锁。                        Where id=’1’
                                            系统会自动会退一个事务来解锁。        
2．6 当然，上面的例子也可以通过手工对表加锁的方法来避免幻像。
exec sql update T_sample set val=val where id=:wk_id;
exec sql select val into :wk_val from T_sample where id =:wk_id;
If (wk_val<wk_input_val)
{
  perform_a();
  wk_val=wk_val + wk_input_val;
}
else
{
  perform_b();
  wk_val=wk_val + wk_input_val;
}
exec sql update T_sample set val=:wk_val where id=:wk_id;
通过第一个update val=val的语句在该行记录上产生排它的锁。从而阻塞其它的访问请求，
但这种情况下，其它共享锁在该记录上也不能获得，只读的请求也无法相应，系统的相应性
较差。

3、案例二。对表T_sample执行以下操作，更新id=’X’的记录的val值到执行的数，如果没有
id=’X’的记录就插入一条记录。
我们通常用下面的语句来执行

EXEC SQL UPDATE T_sample SET val=:wk_val WHERE id=:wk_id;
if (sqlca.sqlcode==100)
   {
                EXEC SQL INSERT T_sample VALUES(:wk_id,:wk_val);
        }

3．1、在缺省隔离级别为3的情况下，当执行 UPDATE T_sample SET val=100 WHERE id=’100’ ,
T_sample表有id=’100’的记录。sp_lock 显示：
fid    spid   loid        locktype                     table_id    page
         row    dbname          class
         context
------ ------ ----------- ---------------------------- ----------- -----------
         ------ --------------- ------------------------------
         ----------------------------
      0     29          58 Ex_intent                      735738288           0
              0 tempdb          Non Cursor Lock

      0     29          58 Ex_row                         735738288      104552
              0 tempdb          Non Cursor Lock
         Range
该sql会在表T_sample的104554的第0行加上Ex_row锁，在表上加Ex_intent锁。

当执行 UPDATE T_sample SET val=110 WHERE id=’110’ ,T_sample表中没有id=’110’的记录。
用sp_lock 显示:
fid    spid   loid        locktype                     table_id    page
         row    dbname          class
         context
------ ------ ----------- ---------------------------- ----------- -----------
         ------ --------------- ------------------------------
         ----------------------------
      0    361         722 Ex_intent                      735738288           0
              0 tempdb          Non Cursor Lock

      0    361         722 Sh_row                        735738288      104554
              1 tempdb          Non Cursor Lock
         Range
该sql语句会在104554页的第二行上加Sh_row锁。（“Range”表示范围锁，用于以事务隔离级别 3
执行的一些范围查询。）和前面的能查到记录时的Ex_row锁有所不同。这个不同也会给应用程序造成意想不到的结果。

3．2、当使用上述的程序id=200 val=400，和id=300，val＝600 作为参数，分别带入进行测试。
序列            事务　Ｔ11                事件                          事务　Ｔ12
1             Begin tran              T11，T12开始事务             Begin tran
2          Update T_sample        T11 执行
         set val=200                  会在T_sample 104554
             where id=’200’       页的第1行上加一个Ex_row锁        
3                                         T12 执行                      Update T_sample set
                                           在T_sample 104554页     val=300 where id=’300’
                                           的第2行上加一个Ex_row锁        
4         Commit tran                 T11 提交
5                                          T12 提交                     Commit tran

3．3、将id=200 val=400，和id=150，val＝300 作为参数分别带入进行测试。
序列           事务　Ｔ13                  事件                                 事务  Ｔ14
1              Begin tran               T13，T14开始事务                  Begin tran
2             Update T_sample      T13 执行
                 set val=200           会在T_sample 104554页
              where id=’200’      的第1行上加一个Ex_row锁        
3                                           T14 执行由于在表中              Update T_sample set
                                             没有id＝’150’的记录，将      val＝300 where id=’150’
                                             在T_sample 104554页
                                             的第1行上加一个Sh_row锁，
                                             但这个共享锁将被T13在该
                                             行上的排它锁阻塞        
4         Commit tran                   T13        提交
5                                            T14 获得锁                            Insert        
6                                            T14 执行insert                     Commit tran
                                                   并提交        

3．4、将id=310 ，val=620和id=320，val＝640  这两个都不存的记录作为参数，分别带入进行测试。
序列          事务　Ｔ15                 事件                                事务　Ｔ16
1           Begin tran                 T15，T16开始事务                   Begin tran
2         Update T_sample          T15 执行由于没有id=’310’
            set val=620                的记录会在T_sample  
           where id=’310’          104554页的第2行
                                            上加一个Sh_row锁        
3                                         T16 执行由于在表中                 Update T_sample set
                                            没有id＝’320’的记录，将      val＝640 where id=’320’
                                           在T_sample 104554页的
                                           第2行上加一个Sh_row锁。         
4    Insert into T_sample            T15 执行，试图插入
   values(‘310’,620)                记录在104554的第2行
                                             上插入记录，但被T16的
                                               Sh_row阻塞。
5                                            T16 执行，也试图插入         Insert into T_sample
                                              记录在104554的第2行上        values(‘320’,640)
                                              插入记录，但被T15的阻
                                              塞产生死锁。        
                          

4、再在隔离级别为1的情况下来看上面案例二。
当我们执行 UPDATE T_sample SET val=100 WHERE id=’100’ ,T_sample表有id=’100’的记录。sp_lock 显示：
fid    spid   loid        locktype                     table_id    page
         row    dbname          class
         context
------ ------ ----------- ---------------------------- ----------- -----------
         ------ --------------- ------------------------------
         ----------------------------

0    229         458 Ex_intent                      735738288           0
              0 tempdb          Non Cursor Lock

0    229         458 Ex_row                         735738288      104552
              0 tempdb          Non Cursor Lock

当我们执行 UPDATE T_sample SET val=110 WHERE id=’110’ ,T_sample表中没有id=’110’的记录。
用sp_lock 显示:
fid    spid   loid        locktype                     table_id    page
         row    dbname          class
         context
------ ------ ----------- ---------------------------- ----------- -----------
         ------ --------------- ------------------------------
         ----------------------------

      0     94         188 Ex_intent                      735738288           0
              0 tempdb          Non Cursor Lock
和隔离级别为3的情况相比，没有那个Sh_row锁。
   4．1、将id=310 ，val=620和id=320，val＝640  这两个都不存的记录作为参数，分别带入进行测试。考虑并行的情况。
序列     事务Ｔ17                  事件                         事务　Ｔ18
1         Begin tran              T17，T18开始事务        Begin tran
2  Update T_sample           T17 执行由于没有
   set val=620                   id=’310’的记录
   where id=’310’            只会在T_sample
                                      上产生表级的Ex_intent锁        
3                                   T18 执行由于在表中          Update T_sample set
                                    没有id＝’320’的记录，     val＝640 where id=’320’
                                    只会在T_sample上产生
                                    表级的Ex_intent锁
4Insert into T_sample        T17 执行，插入数据
  values(‘310’,620)                 
5                                   T18 执行，插入数据。      Insert into T_sample values(‘320’,640)
6 Commit tran                  T17完成。        
7                                   T18完成。                      Commit tran
在隔离级别为1的情况下，同样的例子就不会造成死锁。

[ 本帖最后由 chuxu 于 2007-8-28 11:13 编辑 ]

chenfeng825

好文！够详细

IBMUSER

  详细的好文，对于编写多并发模式的应用，尤其具有指导意义

b02213131

太好了