DB2锁定超时、死锁检测步骤

DB2锁定超时、死锁检测步骤

0. 查看锁定参数
db2 get db cfg for crmdb | grep -i LOCK
在当前home下创建目录 dlock

1. 测试前
db2set DB2_CAPTURE_LOCKTIMEOUT=ON
db2 update dbm cfg using DFT_MON_LOCK on DFT_MON_STMT on

db2 "connect to crmdb"
db2 "CREATE EVENT MONITOR dlock FOR DEADLOCKS WITH DETAILS HISTORY
WRITE TO FILE '/home/db2inst1/dlock'"
db2 "SET EVENT MONITOR dlock STATE 1"

需要重起DB2

2. 测试中

出现交易失败时，查看快照：
db2 get snapshot for locks on crmdb

3. 测试后
db2 connect reset
查看 实例目录下的 db2locktimeout.0.xxx 文件（db2diag.log所在的目录）

根据需要，可执行 db2 "SET EVENT MONITOR dlock STATE 0" 使dlock监控失效，否则将影响一些性能。








--1)打开数据库监控开关
db2 update dbm cfg using DFT_MON_LOCK on DFT_MON_STMT on

db2 update monitor switches using lock ON sort ON bufferpool ON uow ON table ON statement ON


--2)调用执行监控SQL脚本，定位执行较慢 导致锁等待的SQL脚本
select AGENT_ID ,
substr(STMT_TEXT,1,100) as statement,
STMT_ELAPSED_TIME_MS
from table(SNAPSHOT_STATEMENT('SAMPLE',-1)) as B
where AGENT_ID in (
select AGENT_ID_HOLDING_LK
from table(SNAPSHOT_LOCKWAIT('SAMPLE',-1)) as A order by LOCK_WAIT_START_TIME ASC FETCH FIRST 20 ROWS ONLY )
order by STMT_ELAPSED_TIME_MS DESC


--3)监控脚本
#!/usr/bin/ksh

#

dbname=$1

#create a log file

filename=find.locksql.$(date+'%m%d%H%M%S')

touch $filename


#connect to database

echo now,connecting to database: $dbname

db2 "connect to $dbname"

db2 "update dbm cfg using DFT_MON_LOCK on DFT_MON_STMT on"

db2 "update monitor switches using lock ON sort ON bufferpool ON uow ON table ON statement ON"

echo now,finding the SQLs which made lockwait

db2 "select AGENT_ID ,substr(STMT_TEXT,1,100) as statement,STMT_ELAPSED_TIME_MS from table(SNAPSHOT_STATEMENT('$dbname',-1)) as B where AGENT_ID in (select AGENT_ID_HOLDING_LK from table(SNAPSHOT_LOCKWAIT('$dbname',-1)) as A order by LOCK_WAIT_START_TIME ASC FETCH FIRST 20 ROWS ONLY ) order by STMT_ELAPSED_TIME_MS DESC" > $filename


echo The SQLs have saved to the file $filename

--4) 尝试使用 db2advis工具优化索引