oracle 死锁超时,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 "