Shulou(Shulou.com)06/01 Report--

Database parameter preparation

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

Set the parameter CUR_COMMIT=OFF

Deadlock monitors and data generation:

Before the monitor is created

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. Create a deadlock monitor

Db2 "create event monitor t_lockinfomation for locking write to unformatted event table"

Db2 "set event monitor t_lockinfomation state 1" (record event statement)

Db2 "select evmonname,EVENT_MON_STATE (evmonname) as state from syscat.eventmonitors"

2. Create a deadlock

A. Prepare database tables

Db2 "create table T11 (col char (10))"

Db2 "create table T21 (col char (10))"

B. start deadlock simulation

Open two CLP windows, connect to the sample library, and then:

Execute in CLP1:

$db2 + c "insert into T11 values ('aaa')"

Db2 + c "insert into T33 values ('aaa')"

Execute in CLP2:

$db2 + c "insert into T21 values ('bbb')"

After the insert operation, execute the following query synchronously in CLP1 and CLP2 as much as possible:

Execute in CLP1:

$db2 + c "select * from T21"

Execute in CLP2:

$db2 + c "select * from T11"

It won't take long to find that an error is reported in the CLP window:

CLP2 output:

$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. Get lock waiting event data

Db2 "call EVMON_FORMAT_UE_TO_TABLES ('LOCKING', 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

The following table is generated

LOCK_ACTIVITY_VALUES

LOCK_EVENT

LOCK_PARTICIPANTS

LOCK_PARTICIPANT_ACTIVITIES

4. Query deadlock related statements

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 "

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.