--什么情况下进行锁捕捉?
cpu较低,但业务响应较慢时,可能是锁超时或死锁引起
注意区分 锁超时和死锁
sql语句的捕捉不需要打开性能监视器
--1、捕捉引起锁超时sql
--打开锁超时捕捉,不然无法输出 db2locktimeout.0. 文件
db2set DB2_CAPTURE_LOCKTIMEOUT=ON
--默认 Lock timeout 为无限等待需要修改
db2 get db cfg for testdb |grep LOCKTIMEOUT
Lock timeout (sec) (LOCKTIMEOUT) = -1
db2 update db cfg for testdb LOCKTIMEOUT 10
db2stop force
db2start
--创建事件监视器 确保/db2home/db2inst1/xjz 路径有足够的空间
db2 connect to testdb
db2 drop event monitor dlockevm
db2 "create event monitor dlockevm for deadlocks with details history write to file '/db2home/db2inst1/xjz'"
--打开
db2 set event monitor dlockevm state=1
--产生db2event.ctl文件
--模拟死锁
db2 "create table t1 (id int, name varchar(20))"
--session A
db2 +c "insert into t1 values (1,'abc')"
db2 +c "select * from t1 where id=1"
--session B
db2 +c "select * from t1 where id=1"
SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "68". SQLSTATE=40001
--关闭
db2 flush event monitor dlockevm
db2 set event monitor dlockevm state=0
--分析锁超时产生的db2locktimeout.0. 文件
ll sqllib/db2dump/
-rw-r----- 1 db2inst1 db2iadm1 2670 Jan 8 05:04 db2locktimeout.0.34.2014-01-08-05-04-54
more db2locktimeout.0.34.2014-01-08-05-04-54
Lock Information:
Lock Name: 02000300040000000000000052
Lock Type: Row
Lock Specifics: Tablespace ID=2, Table ID=3, Row ID=x0400000000000000
Lock Requestor:
Requesting Agent ID: 34
Lock mode requested: .NS
Activity Information:
Package Schema: (NULLID )
Package Name: (SQLC2G13NULLID )
Package Version: ()
Section Entry Number: 201
SQL Type: Dynamic
Statement Type: DML, Select (blockable)
Effective Isolation: Cursor Stability
Statement Unicode Flag: No
Statement: select * from t1 where id=1
Lock Owner (Representative):
Requesting Agent ID: 18
Lock mode held: ..X
List of Inactive SQL Statements from current UOW:
Entry: #1
Identification: UOW ID (5); Activity ID (2)
Package Schema: (NULLID )
Package Name: (SQLC2G13)
Package Version: ()
Section Entry Number: 201
SQL Type: Dynamic
Statement Type: DML, Select (blockable)
Effective Isolation: Cursor Stability
Statement Unicode Flag: No
Statement: select * from t1 where id=1
Entry: #2
Identification: UOW ID (5); Activity ID (1)
Package Schema: (NULLID )
Package Name: (SQLC2G13)
Package Version: ()
Section Entry Number: 203
SQL Type: Dynamic
Statement Type: DML, Insert/Update/Delete
Effective Isolation: Cursor Stability
Statement Unicode Flag: No
Statement: insert into t1 values (1,'abc')
--获得表名
--Lock Specifics: Tablespace ID=2, Table ID=3, Row ID=x0400000000000000
select * from syscat.tables where tableid=3 and tbspaceid=2
TABSCHEMA TABNAME
--------- --------
DB2INST1 T1
#####################################
--2、捕捉引起死锁sql
--打开
db2 set event monitor dlockevm state=1
--产生db2event.ctl文件
--模拟死锁
db2 "create table t2 (id int, name varchar(20))"
db2 "create table t3 (id int, name varchar(20))"
step1:
--session A
db2 +c "insert into t2 values (1,'abc')"
--session B
db2 +c "insert into t3 values (1,'abc')"
step2:
--session A
db2 +c "select * from t3"
--session B
db2 +c "select * from t2"
SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "2". SQLSTATE=40001
--关闭
db2 flush event monitor dlockevm
db2 set event monitor dlockevm state=0
--分析死锁事件文本
db2evmon -path /db2home/db2inst1/xjz > deadlock.txt
more deadlock.txt
5) Deadlock Event ...
Deadlock ID: 1
Number of applications deadlocked: 2 -- 2个参与者
Deadlock detection time: 01/08/2014 05:23:34.741646
Rolled back Appl Id: *LOCAL.db2inst1.140108100443
8) Deadlock statement history ...
Deadlock ID : 1
Participant No : 2 --参与者2
Stmt history ID : 2 --语句2
Type : Dynamic
Section No : 201
Package cache id : 764504178689
Package creator : NULLID
Package name : SQLC2G13
Package version :
Lock timeout value : 10
Nesting level of stmt : 0
Invocation ID : 0
Query ID : 0
Source ID : 0
UOW Sequence number : 0002
Isolation level : Cursor Stability
Stmt first use time : 01/08/2014 05:23:28.475491
Stmt last use time : 01/08/2014 05:23:28.475491
Statement text : select * from t2
9) Deadlock statement history ...
Deadlock ID : 1
Participant No : 2 --参与者2
Stmt history ID : 1 --语句1
Type : Dynamic
Section No : 203
Package cache id : 197568495617
Package creator : NULLID
Package name : SQLC2G13
Package version :
Lock timeout value : 10
Nesting level of stmt : 0
Invocation ID : 0
Query ID : 0
Source ID : 0
UOW Sequence number : 0002
Isolation level : Cursor Stability
Stmt first use time : 01/08/2014 05:23:19.609299
Stmt last use time : 01/08/2014 05:23:19.609299
Statement text : insert into t3 values (1,'abc')
--
12) Deadlock statement history ...
Deadlock ID : 1
Participant No : 1 --参与者1
Stmt history ID : 2 --语句2
Type : Dynamic
Section No : 201
Package cache id : 498216206337
Package creator : NULLID
Package name : SQLC2G13
Package version :
Lock timeout value : 10
Nesting level of stmt : 0
Invocation ID : 0
Query ID : 0
Source ID : 0
UOW Sequence number : 0008
Isolation level : Cursor Stability
Stmt first use time : 01/08/2014 05:23:25.116813
Stmt last use time : 01/08/2014 05:23:25.116813
Statement text : select * from t3
13) Deadlock statement history ...
Deadlock ID : 1
Participant No : 1 --参与者1
Stmt history ID : 1 --语句1
Type : Dynamic
Section No : 203
Package cache id : 627065225217
Package creator : NULLID
Package name : SQLC2G13
Package version :
Lock timeout value : 10
Nesting level of stmt : 0
Invocation ID : 0
Query ID : 0
Source ID : 0
UOW Sequence number : 0008
Isolation level : Cursor Stability
Stmt first use time : 01/08/2014 05:23:14.535303
Stmt last use time : 01/08/2014 05:23:14.535303
Statement text : insert into t2 values (1,'abc')
PS:
db2pd由于在捕捉锁问题上有其局限性,所以推荐使用事件监视器;但由于使用事件监视器会产生大量的跟踪文件,所以要特别注意其路径所在空间是否充足!
添加新评论0 条评论