【转】informix的事务、并发控制、锁机制、隔离级别

1、事务

      事务是指作为单个逻辑工作单元执行的一系列操作。 事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单 元，可以简化错误恢复并使应用程序更加可靠。数据库服务器保证在事务范围内执行的操作完整且正确地提交至磁盘，否则数据库会复原至事务启动之前的状态。
     一个逻辑工作单元要成为事务，必须满足所谓的ACID属性。ACID的具体含义如下：
     1）A（Atomicity）：操作序列要么完整的执行，否则什么都不做；
     2）C（Consistency）：一致性，事务执行后，保证数据库从一个一致性状态到另外一个一致性状态；
     3）I（Isolation）：隔离，一个事务的中间状态对其他事务不可见，即每个用户都感觉他们在单独使用数据库。隔离级别用来定义多大程度的隔离多个不同的事务；
     4）D（Durability）：持久性，事务的有效性，不会应用硬件或软件的失败而丢失。
2、并发控制
      1）相关概念
       i）隔离（+一致性） => 并发控制；
       ii）多个事务可以访问或修改相同的资源；
       iii）只要多个进程共享资源，就需要对访问进程进行排队控制；
       iv）在进行并发控制时，数据库内部将生成多个并发事务访问资源的操作序列表，并且每一个事务内部的各个操作都需要序列化。
       2）串行调度存在的问题
       在串行调度中，采用操作序列，一个事务完成了再完成另外一个，即使两个事务T1、T2更新的是数据库中不同的对象。此种方式从并发和性能角度考虑，都不能很好的利用计算机资源。
       为了改善性能，需要采用非串行调度，即允许事务并发执行，即一个事务内的操作可以在其他事务提交前开始执行。
        3）并发调度的常见问题
        i）脏读：事务T2读取到了事务T1没有提交的结果
         例如如下的操作序列会导致脏读：
         事务T1读取记录，然后更新记录；
         事务T2读取了更新后的记录；
         若T1后续操作失败，会导致更新的记录回滚，而同时事务T2已经使用了这个没有提交的值。
         ii）不可重复读：事务T1中多个读操作返回不同的结果
         事务T1读取一个对象；
         事务T2读取并更新同一个对象；
         事务T1再次读取同一个对象返回了不同的值。
         iii）幻影读：如果事务T1在同样的情况下多次执行select读取的结果不同
         事务T1从一个表中检索满足特定条件的记录返回m条记录；
         事务T2往该表中insert/delete其他满足相同条件的记录；
         事务T1再次以相同的条件检索该表的数据返回的数据记录不为m条。
3、锁机制
       1）相关概念
        i）用户可以锁定一个对象，可以防止 其它用户修改锁定的对象；
        ii）在多个用户并发访问数据库的情况下，为保证数据完整性，锁是很有必要的；
        iii）程序可以显式的对数据枷锁，数据库系统可以隐式的对对象进行加锁。
       2）锁的类型
       i）共享锁（Shared locks） ：多个用户可以读取相同的记录
       如果一个对象上没有排他锁，则共享锁可以加在该对象上，它可以防止其他事务更新数据，但同时，其他事务可以读取该数据，多个事务可以在同一个对象上加多个共享锁。
       ii）排他锁（Exclusive locks） ：同一时间仅仅有一个用户可以读取相同的记录
       如果一个对象上没有任何锁，排他锁才可以加在该对象上。一旦在记录上加了排他锁，则不能在该记录上增加任何锁了，直至锁释放。它可以防止其他事务读取和更新数据。
       iii）提升/更新锁（Promotable/Update lock） ：可以对锁进行升级或者降级
       在更新游标时使用，由游标在含有“for update”选项执行时产生，只能在没有排他锁或其他更新锁的记录上加更新锁。当锁定的记录真正执行的时候，更新锁将提升为排他锁。
       iv）专一锁（Intent lock） ：是一种表级锁，标识在该表上有一个游标在读取数据
       由IDS自动分配，如果一条记录上的记录被更新，一个排他锁将分配在该记录上，同时将该记录的表上自动加上专一锁，这能保证没有session可以在该表上增加排他锁，只要该表中有记录被增加了排他锁。
       3）锁的有效期
        i）程序可以控制数据库锁的有效期；
        ii）当数据库关闭后，数据库锁将被释放；
        iii）根据数据库使用了事务的情况，表锁的有效期不同。如果数据没有使用事务（没有使用事务日志，也不使用commit work语句），显式对一个表lock，当执行unload table时，锁将被释放；
        iv）当数据库使用了事务，事务结束时，将释放事务所有的锁。
        4）不同粒度的锁
         i）数据库级别的锁 ：数据库管理活动，比如imports和exports。例如：

DATABASE  database_name EXCLUSIVE;

         ii）表级别的锁 ：当整个表或表的大部分数据需要更新，加表级锁效率高。参考语句如下：
         创建排他锁实例：

LOCK  TABLE  tab1  IN  EXCLUSIVE MODE;

         创建共享锁实例：

LOCK  TABLE  tab2  IN  SHARE MODE;

         取消表上的锁实例：

UNLOCK  TABLE  tab1;

         iii）页级锁 ：当按数据物理顺序进行访问和更新时，页级锁效率高。修改锁模似为页级锁的参考语句如下：

ALTER   TABLE  tab1 LOCK MODE PAGE;

        页级锁模式是数据库默认的表锁模式。
         iv）行级锁 ：OLTP事务采用行级锁效率高。创建表时指定为行级锁的参考语句如下：

CREATE   TABLE  tab1 (col1 ) LOCK MODE ROW;

         将某表修改为行级别的语句参考如下：

ALTER   TABLE  tab1 LOCK MODE (ROW);

         5）何种情况下用何种锁？
         i）如果更新表中相对较小一部分数据的时候，采用行级锁将获取最好的性能，如果一个事务只访问表中的一小部分数据，那就采用行级锁。
         ii）如果一个事务频繁访问整个表中的数据，设置更粗的力度，比如表级锁；
         iii）如果更新数据库中大部分表的大部分数据的情况下，采用数据库级别的锁；
         iv）informix默认的锁模式为页级锁。
         v）ONCONFIG参数DEF_TABLE_LOCKMODE用于设置默认锁模式；
         vi）查看表锁模式的方法如下：
         方法1：

oncheck –pt dbname:tablename;

         方法2：

dbschema –d dbname –t tablename  - ss;

4、事务和会话的隔离级别
        1）Dirty Read（脏读）
        设置为Dirty Read隔离级别的参考语句如下：

SET   ISOLATION   TO  DIRTY  READ ;

         采用这种隔离级别，数据库服务器不会分配任何锁。查询过程中，可以查询到任何数据行，甚至那些被修改但尚未提交的记录。非日志数据库中只有Dirty-Read一种隔离级别。它一般用在如下应用场景：
         静态表（没有更新，只读的表），速度比100%准备更重要的情况，以及不能等待锁的释放的情况中。
         2）Commited Read（只读已提交的数据）
         设置为该级别的参考语句如下：

SET   ISOLATION   TO   COMMITTED   READ ;

         这种隔离级别能确保所有的记录都是提交到数据库的，因而能避免读取到脏数据，能确保所有读取到的记录都是已提交的，当一个进程读完记录后，其它进程就可以修改。
         在读取数据前，数据库服务器尝试在记录上加共享锁。加锁前，需要先检查是否可以对对象加共享锁；如果可以加锁，则要保证要加锁的记录没有其他进程正在更新；当继续正在更新时，记录上有排他锁，此时我们不能对记录加共享锁。
          3）Cursor Stability（游标稳定性）
          参考设置语句如下：

SET   ISOLATION   TO   CURSOR  STABILITY;

         给更新Cursor，游标读的所有记录加上共享锁。通过游标检索数据，共享锁将一直保持到执行下一个FETCH语句。不仅可以看到提交 的记录，也可以保证看到的记录不会被更新，其它进程不能更新或删除你锁看到的记录，当移动到下一行时，锁才会释放，记录就可以进行修改。
         4）Repeatable Read（可重复读） 
         参考设置语句如下：

SET   ISOLATION   TO   REPEATABLE   READ ;

      数据库在读取的记录上加共享锁，验证是否可以读取数据，直到事务提交，锁才能释放，其他用户可以读取数据，但是不能修改。可以保证在同一事务中前后两次读 取记录是一致的。当必须要信任所有读取的记录，保证记录不被修改，我们可以采用repeatable read，例如统计数据（银行账号的余额情况）、关联查询多个表等。
         5）Last Committed Read（读取最后提交的数据）
         这是一种乐观锁，它解决了Commited Read的不足，因为Commited Read在记录被锁时，其它进程需要等待。
         这种隔离级别常被用在WEB应用系统中，例如在电子商务系统中，可以选择商品添加到购物篮中，但是此时后台可能你正在对商品的价格进行更新，当再次检查的时候，可能发现商品的价格已经发生变化。
         设置语句参考如下：

    SET   ISOLATION   TO   COMMITTED   READ  LAST  COMMITTED ;

          相对Committed Read而言，提高了并发量和系统的吞吐量。可以通过ONCONFIG参数设置为隔离级别：USELASTCOMMITTED。表需要设置为行级锁，不能是页级锁。
          6）几种隔离级别的比较
          隔离级别                          脏读？              不可重复读？              幻影读？
          Dirty Read                          Yes                         Yes                             Yes
          Last Commited Read         No                          Yes                             Yes          
          Commited Read                  No                          Yes                             Yes
          Cursor Stability                  No                          No                              Yes
          Repeatable Read                No                          No                               No            
5、设置事务/会话的锁模式
         1）不等待锁的释放
         为默认的锁模式，如果数据库对象被锁，则立即返回错误。错误消息参考如下：

- 244 : Could  not  do a physical - order   read   to   fetch  the  next  row
•  107 : ISAM error: record  is  locked

         设置为此种锁模式的参考语句如下：

SET  LOCK MODE  TO   NOT  WAIT;

          2）一直等待锁的释放
          一个事务可能发生死锁或挂住，等待资源的释放。设置为此种模式的语句如下：

SET  LOCK MODE  TO  WAIT;

          3）在锁释放前，等待n秒
          直到等待n秒后，如果锁一直没有释放将返回锁等待超时错误信息。参考设置语句如下：

SET  LOCK MODE  TO  WAIT  20 ;

6、锁的监控
         1）监控session的隔离级别
         使用: onstat –g sql 或者 onstat –g ses，例如在笔者测试服务器上运行onstat -g sql，运行结果如下：

IBM Informix Dynamic Server Version  10.00 .UC1      --  On-Line (Prim) -- Up 102 days 05:26:42 -- 1507468 Kbytes

Sess  SQL             Current             Iso Lock       SQL  ISAM F.E.
Id    Stmt type       Database            Lvl Mode       ERR  ERR  Vers Explain    
880     -               sdp                CR  Wait  20      0      0      9.03   Off         
878     SELECT          sdp                CR   Not  Wait    0      0      9.03   Off         
877     -               sdp                CR  Wait  20      0      0      9.03   Off         
756     -               sdp                CR  Wait  20      0      0      9.03   Off         
755     -               sdp                CR  Wait  20      0      0      9.03   Off         
754     -               sdp                CR  Wait  20      0      0      9.03   Off         
753     -               sdp                CR  Wait  20      0      0      9.03   Off         
752                   sdp                CR  Wait  20      0      0      9.03   Off         
751     -               sdp                CR  Wait  20      0      0      9.03   Off         
750     -               sdp                CR  Wait  20      0      0      9.03   Off         
480     -               sdp                CR  Wait  20      0      0      9.03   Off         
479     -               sdp                CR  Wait  20      0      0      9.03   Off         
478     -               sdp                CR  Wait  20      0      0      9.03   Off         
477     -               sdp                CR  Wait  20      0      0      9.03   Off         
476     -               sdp                CR  Wait  20      0      0      9.03   Off         
475     -               sdp                CR  Wait  20      0      0      9.03   Off         
474     -               sdp                CR  Wait  20      0      0      9.03   Off         
473     -               sdp                CR  Wait  20      0      0      9.03   Off         
472     -               sdp                CR  Wait  20      0      0      9.03   Off         
471     -               sdp                CR  Wait  20      0      0      9.03   Off         
470     -               sdp                CR  Wait  20      0      0      9.03   Off         
469     -               sdp                CR  Wait  20      0      0      9.03   Off         
468     -               sdp                CR  Wait  20      0      0      9.03   Off         
467     -               sdp                CR  Wait  20      0      0      9.03   Off         
466     -               sdp                CR  Wait  20      0      0      9.03   Off         
465     -               sdp                CR  Wait  20      - 232   0      9.03   Off         
464     -               sdp                CR  Wait  20      0      0      9.03   Off         
463     -               sdp                CR  Wait  20      0      0      9.03   Off         
43      -               sdp                CR  Wait  20      0      0      9.03   Off         
40      -               sdp                CR  Wait  20      0      0      9.03   Off     

        查看sid的详细信息: onstat –g sql sid，例如查看878这个sid的消息，可使用：onstat -g sql 878，参考返回结果如下：

Sess  SQL             Current             Iso Lock       SQL  ISAM F.E.
Id    Stmt type       Database            Lvl Mode       ERR  ERR  Vers Explain    
878     -               sdp                CR  Wait  20      0      0      9.03   Off         

Last parsed SQL statement :
   select   count ( * )  from  

       2）查看用户进程锁等待情况
       使用: onstat –u。
       3）查看锁使用和等待情况
       使用: onstat -k
       4）监控数据库事务及其状态
       使用: onstat -x