db2死鎖和鎖超時

數據庫參數準備
db2 create db mydb  using codeset utf-8 territory CN
db2 update db cfg using cur_commit DISABLED

db2 get db cfg show detail |grep -i cur
 Description                                   Parameter   Current Value              Delayed Value
 Currently Committed                        (CUR_COMMIT) = DISABLED                   DISABLED 
設置參數CUR_COMMIT=OFF

死鎖監視器和數據產生：

未創建監視器前的
db2 list  tables  for  all  |grep  -i lock
[db2inst1@ora10 ~]$ db2 list  tables  for  all  |grep  -i lock
LOCKS_HELD                      SYSIBMADM       V     2018-08-25-20.10.48.564505
LOCKWAITS                       SYSIBMADM       V     2018-08-25-20.10.48.568740
SNAPLOCK                        SYSIBMADM       V     2018-08-25-20.10.48.015699
SNAPLOCKWAIT                    SYSIBMADM       V     2018-08-25-20.10.48.046247

1、創建死鎖監視器
db2  "create event monitor t_lockinfomation for locking  write to unformatted event table"
db2  "set  event  monitor   t_lockinfomation  state 1"(記錄事件語句）
db2 "select evmonname,EVENT_MON_STATE(evmonname) as state from syscat.eventmonitors"

2、創建死鎖
a、準備數據庫表
db2  "create table t11 (col char(10))"
db2  "create table t21 (col char(10))"

b、開始進行死鎖模擬
打開兩個CLP窗口，分別連接到sample庫，然后：

在CLP1中執行：
$ db2 +c  "insert into t11 values('aaa')"
db2 +c  "insert into t33 values('aaa')"

$ 在CLP2中執行：
$ db2 +c "insert into t21 values('bbb')"

 
執行完插入操作后，在CLP1和CLP2中盡量同步執行以下查詢：
在CLP1中執行：
$ db2 +c "select * from t21"

在CLP2中執行：
$ db2 +c "select * from t11"
 
不用多少時間，就會發現，CLP窗口中報錯：
CLP2輸出：
$ db2 +c "select * from t11"
SQL0911N  The current transaction has been rolled back because of a deadlock
or timeout.  Reason code "2".  SQLSTATE=40001

3、獲取鎖等待事件數據
db2 "call EVMON_FORMAT_UE_TO_TABLES ( 'LOCKING', NULL, NULL, NULL, NULL, NULL, 'RECREATE_FORCE', -1, 'SELECT * FROM T_LOCKINFOMATION ORDER BY event_timestamp')"
db2  "set  event  monitor   t_lockinfomation  state 0"
db2 list  tables  for  all  |grep  -i lock
LOCK_ACTIVITY_VALUES            DB2INST1        T     2018-08-25-20.53.42.365349
LOCK_EVENT                      DB2INST1        T     2018-08-25-20.53.41.996997
LOCK_PARTICIPANTS               DB2INST1        T     2018-08-25-20.53.42.073427
LOCK_PARTICIPANT_ACTIVITIES     DB2INST1        T     2018-08-25-20.53.42.162554
T_LOCKINFOMATION                DB2INST1        T     2018-08-25-20.15.24.159899
LOCKS_HELD                      SYSIBMADM       V     2018-08-25-20.10.48.564505
LOCKWAITS                       SYSIBMADM       V     2018-08-25-20.10.48.568740
SNAPLOCK                        SYSIBMADM       V     2018-08-25-20.10.48.015699
SNAPLOCKWAIT                    SYSIBMADM       V     2018-08-25-20.10.48.046247
產生如下表
LOCK_ACTIVITY_VALUES         
LOCK_EVENT                   
LOCK_PARTICIPANTS            
LOCK_PARTICIPANT_ACTIVITIES  

4、查詢死鎖相關的語句
db2 "
select c.EVENT_ID,
            c.EVENT_TIMESTAMP,
            c.EVENT_TYPE,
            c.PARTICIPANT_TYPE,
            c.APPL_ID,
            c.APPL_NAME,
            c.AUTH_ID,
            c.CLIENT_WRKSTNNAME,
            c.LOCK_ESCALATION,
            c.LOCK_MODE_REQUESTED,
            c.LOCK_MODE,
            c.LOCK_OBJECT_TYPE,
            c.TABLE_NAME,
            c.TABLE_SCHEMA,
            d.ACTIVITY_TYPE,
            d.PACKAGE_NAME,
            d.PACKAGE_SCHEMA,
            d.SECTION_NUMBER,
            d.EFFECTIVE_ISOLATION,
            d.STMT_TEXT
from
   (select a.XMLID,
           a.EVENT_ID,
           a.EVENT_TIMESTAMP,
           a.EVENT_TYPE,
           b.PARTICIPANT_NO,
           b.PARTICIPANT_TYPE,
           b.APPL_ID,
           b.APPL_NAME,
           b.AUTH_ID,
           b.CLIENT_WRKSTNNAME,
           b.LOCK_NAME,
           b.LOCK_ATTRIBUTES,
           b.LOCK_ESCALATION,
          case (b.LOCK_CURRENT_MODE)
   when 0    then 'No Lock'
   when 1    then 'IS'
   when 2    then 'IX'
   when 3    then 'S'
   when 4    then 'SIX'
   when 5    then 'X'
   when 6    then 'IN'
   when 7    then 'Z'
   when 8    then 'U'
   when 9    then 'NS'
   when 10   then 'NX'
   when 11   then 'W'
   when 12   then 'NW' end LOCK_CURRENT_MODE ,
   case (b.LOCK_MODE_REQUESTED)
   when 0    then 'No Lock'
   when 1    then 'IS'
   when 2    then 'IX'
   when 3    then 'S'
   when 4    then 'SIX'
   when 5    then 'X'
   when 6    then 'IN'
   when 7    then 'Z'
   when 8    then 'U'
   when 9    then 'NS'
   when 10   then 'NX'
   when 11   then 'W'
   when 12   then 'NW' end LOCK_MODE_REQUESTED,
   case (b.LOCK_MODE)
   when 0    then 'No Lock'
   when 1    then 'IS'
   when 2    then 'IX'
   when 3    then 'S'
   when 4    then 'SIX'
   when 5    then 'X'
   when 6    then 'IN'
   when 7    then 'Z'
   when 8    then 'U'
   when 9    then 'NS'
   when 10   then 'NX'
   when 11   then 'W'
   when 12   then 'NW' end LOCK_MODE,
   b.LOCK_OBJECT_TYPE,
   b.TABLE_NAME,
   b.TABLE_SCHEMA
   ----b.LOCK_WAIT_START_TIME,
   ----b.LOCK_WAIT_END_TIME
   from LOCK_EVENT a,
        LOCK_PARTICIPANTS b
   where a.XMLID=b.XMLID) c,
   (select e.XMLID,
            e.PARTICIPANT_NO,
            e.ACTIVITY_TYPE ,
            e.PACKAGE_NAME,
            e.PACKAGE_SCHEMA,
            e.SECTION_NUMBER,
            e.EFFECTIVE_ISOLATION,
            e.STMT_TEXT
            from LOCK_PARTICIPANT_ACTIVITIES e ,
      (select XMLID,
              PARTICIPANT_NO,
              max(ACTIVITY_ID) ACTIVITY_ID
             from LOCK_PARTICIPANT_ACTIVITIES
             group by XMLID,PARTICIPANT_NO) f
   where e.XMLID=f.XMLID and e.PARTICIPANT_NO=f.PARTICIPANT_NO and
        e.ACTIVITY_ID=f.ACTIVITY_ID) d
where
c.XMLID=d.XMLID
and c.PARTICIPANT_NO=d.PARTICIPANT_NO
order by c.EVENT_ID "