DB2锁的理解

继续温故知新，关于锁，DB2这块貌似做的很严格，正因为过犹不及，也是被同行所诟病的，并发性明显不如Oracle。主要原因还是在于本身锁的设计上面，oracle有回滚段，不会因为某些更改操作而导致整个表被hold住。用户还是可以读，对于某些更改数据的读，oracle读的是before image，也就是不是更改中的值，而是更改前的值。所以往往业务的需求大于理性的设计。
     用锁的原因，不用多说，下面3个主因
     1，脏读              读取到未提交的数据，如果rollback，会导致读取不存在的数据
     2，幻想读           会读取到其他事务增加的新行
     3，不可重复读    会读取到其他事务修改的行，或者压根读不到，被删除了的行
    DB2采取的方法，4个isolation（读操作）
     1，RR  最严格，可以重复读，代价是扫描过的每行都会被加S锁，事务不被submit或rollback，都不会被释放
        优点：保证该事务内读到的数据都是稳定的
        缺点：并发性很差，其他事务不能对该事务扫描过的数据加X锁，也就是不能修改
     2，RS，比较严格，稳定读，比如你用了谓词，走的是表扫描，扫描过的行不会被加锁，只有你谓词选中的行才会被加NS锁
          但是表会被加IS锁
        优点：选中的行能保持稳定性（不会出现脏读，不可重复读）
        缺点：并发性也不好，不能阻止幻想读，比如你用同样的搜索标准重新打开被处理过的cursor，结果集可能不一样，可能有新 数据插入，修改的数据符合搜索条件同样会被搜索出来
      3，CS，游标稳定，引用行上面才会加锁，也是CLP的默认隔离级别，能保证最大的并行性。只能保证不会脏读。
      4，UR，可以读脏数据，也就是可以看到没有落实的数据行
          但要注意的是，如果事务中使用了可更新游标，运行效果和在CS下面是一样的）
          如果应用程序在US级别跑的话，可能会使用CS级别，因为应用程序是用的模糊游标，利用blocking选项，默认值是UNAMBIG，意味着模糊游标是可更新的。
          防止升级的方法是
          1,将程序中的游标改成非模糊
             declare <游标名> cursor for  select语句 for read only
          2，使用预编译程序或BLOCKING ALL和STATICREADONLY YES来绑定,允许运行该程序时任何时候都将模糊游标视为 只读
          关于应用程序如何指定隔离级别，一般是ODBC或者JDBC里面，有function是用来设置的，比如JDBC的是setTransactionIsolation()，如果用中间件Webshere的话，就要在里面设置，优先级JDBC<-WAS<-CLP，最后都没设置才会用DB2环境的，也就是CS（CLP默认），我们也可以修改他（session级别）
         db2 change isolation to ur
         db2 connect reset

       锁转换
       表锁：IN->IS->S->IX->U->X->Z
       行锁：S->U->X
      其中S锁和IX锁的转换是特殊的，当某个程序持有表的S锁，当他要求IX锁的时候，会转化成SIX。同样持有IX锁的时候，会转化成IXS锁（不同点在于，IX转成IXS的时候，可能需要进行锁等待。因为IX锁和IX锁是兼容的，这个表的某些数据可能正在被修改。但实现是S锁的话，就不需要进行锁等待，直接转换成SIX了）
   关于U锁的设计：防止两个程序同时拥有一个表的S锁，并需要X锁的时候，互相等待对方释放，从而造成死锁。U锁的好处在于，如果程序发现得不到表的X锁，他可以先得到表的U锁，得到U锁后，他就比其他程序更容易得到表的X锁。U锁其实在一个等待修改的中间状态（可以读表的所有数据，且等待X锁，以便可以修改所有数据）

      锁升级
      锁的获得和释放也是会消耗cpu和内存的，所以DB2会在下面两种情况下进行锁升级，从行锁升级成表锁的话，锁会减少很多。资源也就得到释放。
      1，当某个程序的锁数量超过了LOCKLIST乘以MAXLOCKS的百分比后，数据库会进行锁升级，直到低于这个值
      2，当数据库下面所有程序的锁数量快要接近LOCKLIST的值后，数据库会选择一个表上最多行锁的连接，并将这些行锁转换成表锁。
      锁升级一般情况下说，是不好的。影响并发性。本来一个程序对表持有IS锁的，其他程序还可以得到该表的IX锁，以便进行数据修改，但是锁升级之后，这个程序对表将升级成S锁，其他程序就不能对该表进行修改了。会处于LockWaiting的状态。
      锁升级还有可能会失败，很好理解，接着上面的，本来是IS的，另外的IX锁还没释放，锁升级成S锁，但S和IX不兼容，这时候锁升级就失败了。应用程序会接到一个-912的SQLCODE。

     最后还想说下3个缓解锁性能的参数。
     当在session1里面插入条数据，并没有commit，我们在session2里面去select整张表，这是就会处于lockwaiting的状态。（UR除外）
不管是走的索引扫描还是走的表扫描都会被锁住。无论是否提交。（索引和表扫描都是这样）
     有谓词的情况下
      有索引（不管是不是唯一）：会根据谓词去栓选数据，如果尚未提交的数据（insert or delete）包含在谓词里面，会发生锁等待。
      无索引（走表扫描）：这种情况下，在验证行是否满足谓词查询之前，DB2会锁住所有要访问的行（和select整张表一样），不管数据是否被提交，这种情况下谓词验证是毫无意义的。因为DB2需要扫描所有的表（包括未提交的），当他读到未提交数据的时候，就会被hold住，未提交的数据往往是被加了X锁，是不兼容其他读锁的。因此session2就进入了Lockwaiting的状态，直到回滚（LOCKTIME_OUT的值相关）。强调下，即使你的谓词范围里面不包括uncommited数据也是会被hold住的，因为走的是表扫描。

       对于第二种情况（无索引），用户往往是不愿意看见的，因为用户并不清楚到底发生了什么，往往他们只会关心系统的运行性能。SAP最先对上述情况提出了些意见，使得IBM在DB2V8版本之后引入了3个注册变量用来缓解锁等待，提供并发性（原理就是提前进行谓词扫描，允许读扫描推迟或避免行锁）索引类型必须是Type-2。

        DB2_EVALUNCOMMITED
        这个参数我觉得是针对第二种情况，也就是走表扫描的时候，DB2会提前计算谓词，排除谓词外行锁定。对走索引扫描没有影响，因为索引扫描本身就是为了避免表扫描的。(需要注意的是，就算表上面有索引，也不一定就会走索引扫描，要根据你的谓词，以及select字段来看走什么扫描)
       CHILD表没建索引
       db2 +c "insert into CHILD_TABLE (CHILD_JOIN_COLUMN,CHILD_DATA_COLUMN) values ('10','ii')"
       db2 "select * from CHILD_TABLE where CHILD_JOIN_COLUMN='1'" （执行成功）
       对于包含在谓词中的数据，insert和update的时候（为commit），select还是会被hold住的。但delete的时候，却会无视掉无提交的数据，牛哥觉得不合理，这不就是UR读吗！？-_-!! 所以删除测试的时候还是要小心
       db2 +c "delete from CHILD_TABLE where CHILD_JOIN_COLUMN='1'"
       db2 "select * from CHILD_TABLE where CHILD_JOIN_COLUMN='1'"

CHILD_JOIN_COLUMN CHILD_DATA_COLUMN  
----------------- --------------------

  0 record(s) selected.

       DB2_SKIPDELETED
       上面设置了DB2_EVALUNCOMMITED=on，表扫描的时候确实会skip掉未commit的delete数据（即使有索引）。如果走索引扫描的话，未提交数据是不会被skip掉的，因为这时候还存在伪索引键。所以如果想skip掉索引扫描的未commit的数据的话，就要设置DB2_SKIPDELETED的值。
      单独设置该值
      不管走表扫描还是索引扫描，对于未commit的delete的数据都会skip掉

       DB2_SKIPINSERTED
      同上面的相同，不管走表扫描还是索引扫描，对于未commit的insert的数据都会skip掉

原文地址： http://www.db2china.net/home/space.php?uid=24415&do=blog&id=10472