DB2数据库锁问题的解决

最新推荐文章于 2021-06-08 08:36:01 发布

原创最新推荐文章于 2021-06-08 08:36:01 发布 · 631 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

数据库维护

本文详细介绍了如何查询和解决DB2数据库的锁问题。通过db2list application和db2pd命令来定位应用句柄和事务，进而找到导致锁等待的SQL语句。此外，还提供了捕获锁等待、死锁的策略，如设置事件监控器来记录死锁事件，并给出了查看和处理锁问题的具体步骤。

查询数据库锁类问题的基本方法：
db2 list application |more
找到app. Handle
db2pd -db zbbb -applications
找到该app. Handle对应的L-AnchID和L-StmtUID。

db2pd -db zbbb -dynamic
通过L-AnchID和L-StmtUID找到该事务对应的sql

查找数据库中最早未提交事务的sql：

SELECT AI.APPL_STATUS as Status, AI.AGENT_ID as AGENT_ID, SUBSTR(AI.PRIMARY_AUTH_ID,1,10) AS "Authid", SUBSTR(AI.APPL_NAME,1,15) AS "ApplName", INT(AP.UOW_LOG_SPACE_USED/1024/1024) AS "LogUsed(M)", AP.APPL_IDLE_TIME, AP.APPL_CON_TIME AS "ConnectedSince" FROM SYSIBMADM.SNAPDB DB, SYSIBMADM.SNAPAPPL AP, SYSIBMADM.SNAPAPPL_INFO AI WHERE AI.AGENT_ID = DB.APPL_ID_OLDEST_XACT AND AI.AGENT_ID = AP.AGENT_ID

案例

1.
db2inst1@AMC_S_P550_1 $ db2diag -time 2015-12-09 -level error|more //该命令可以过滤出db2diag.log中某日的某类型的报告。
2015-12-10-19.41.08.118099+000 I44129965A496 LEVEL: Error
PID : 516512 TID : 1 PROC : db2bp
INSTANCE: db2inst1 NODE : 000
APPID : *LOCAL.db2inst1.161106112624
EDUID : 1
FUNCTION: <0>, <0>, <0>, probe:2137
MESSAGE : SQL0911N The current transaction has been rolled back because of a
deadlock or timeout. Reason code "68".
DATA #1 : String, 12 bytes
sqluexpm.SQC
DATA #2 : String, 44 bytes
Error from Import/Export or Load Processing.

2015-12-10-22.41.31.459297+000 I44264614A496 LEVEL: Error
PID : 516512 TID : 1 PROC : db2bp
INSTANCE: db2inst1 NODE : 000
APPID : *LOCAL.db2inst1.161106172233
EDUID : 1
FUNCTION: <0>, <0>, <0>, probe:2137
MESSAGE : SQL0911N The current transaction has been rolled back because of a
deadlock or timeout. Reason code "68".
DATA #1 : String, 12 bytes
sqluexpm.SQC
DATA #2 : String, 44 bytes
Error from Import/Export or Load Processing.

2015-12-10-22.50.35.013413+000 I44268751A496 LEVEL: Error
PID : 516512 TID : 1 PROC : db2bp
INSTANCE: db2inst1 NODE : 000
APPID : *LOCAL.db2inst1.161106173920
EDUID : 1
FUNCTION: <0>, <0>, <0>, probe:2137
MESSAGE : SQL0911N The current transaction has been rolled back because of a
deadlock or timeout. Reason code "68".
DATA #1 : String, 12 bytes
sqluexpm.SQC
DATA #2 : String, 44 bytes
Error from Import/Export or Load Processing.

2015-12-10-23.19.50.109560+000 I44281078A496 LEVEL: Error
PID : 516512 TID : 1 PROC : db2bp
INSTANCE: db2inst1 NODE : 000
APPID : *LOCAL.db2inst1.161106183816
EDUID : 1
FUNCTION: <0>, <0>, <0>, probe:2137
MESSAGE : SQL0911N The current transaction has been rolled back because of a
deadlock or timeout. Reason code "68".
DATA #1 : String, 12 bytes
sqluexpm.SQC
DATA #2 : String, 44 bytes
Error from Import/Export or Load Processing.

2015-12-11-00.16.17.828885+000 I44310965A496 LEVEL: Error
PID : 516512 TID : 1 PROC : db2bp
INSTANCE: db2inst1 NODE : 000
APPID : *LOCAL.db2inst1.161106202421
EDUID : 1
FUNCTION: <0>, <0>, <0>, probe:2137
MESSAGE : SQL0911N The current transaction has been rolled back because of a
deadlock or timeout. Reason code "68".
DATA #1 : String, 12 bytes
sqluexpm.SQC
DATA #2 : String, 44 bytes
Error from Import/Export or Load Processing.

2015-12-11-00.48.33.421210+000 I44323292A496 LEVEL: Error
PID : 516512 TID : 1 PROC : db2bp
INSTANCE: db2inst1 NODE : 000
APPID : *LOCAL.db2inst1.161106212140
EDUID : 1
FUNCTION: <0>, <0>, <0>, probe:2137
MESSAGE : SQL0911N The current transaction has been rolled back because of a
deadlock or timeout. Reason code "68".
DATA #1 : String, 12 bytes
sqluexpm.SQC
DATA #2 : String, 44 bytes
Error from Import/Export or Load Processing.

2.
db2 get snapshot for locks on zbbb|more //当锁超时或者是死锁发生时，查看当前锁的情况，锁的数量与持有者ip均可被看到

//脚本也可以使用sql语句的脚本，参考徐明伟db2书籍251页
//捕获锁等时间超过2min的应用，但是没有找到引起锁等待的语句，找引起锁等待的语句用db2pd
db2 "select 'Application - '||APPL_NAME||' is on lock wait for last 2 mins' from SYSIBMADM.LOCKWAITS where minute(current_timestamp - timestamp(LOCK_WAIT_START_TIME)) > 2"

db2pd -d zbbb -locks //也可以使用该命令查看锁的情况，此命令查看到锁的类型与数量，但是没有对应ip

3.
找出引起锁等的语句的方法：db2pd
db2pd -d zbbb -locks showlocks wait -tra -app -dyn > lockwait.txt //抓取结果参考徐明伟书籍252页
借助该命令可以抓取到持有锁的sql语句；UOW-Waiting：持有锁；

db2inst1@AMC_S_P550_1 $ more db2pd_lockwait.sh //也可借助脚本，定时抓取lock信息
. /home/database/.profile

db2 connect to zbbb user userdbs using userdbs

db2pd -db zbbb -locks wait showlocks -transactions -agents -applications -dynamic -file $HOME/bin/log/locksql/db2pd.out -repeat 5 40

echo "db2pd over!"

4.锁等与锁超时的捕获方法相同

//捕获死锁的方法
数据库上的查询、创建与删除事件监控器的方法：(此捕获方法无需重启数据库)
//查询数据库上的事件监控器的方法
[db2inst1@KONGSI ~]$ db2 "select evmonname, event_mon_state(evmonname) from syscat.eventmonitors"

EVMONNAME 2
-------------------------------------------------------------------------------------------------------------------------------- -----------
DB2DETAILDEADLOCK 1

1 record(s) selected.
//创建事件监控器，创建一个目录，用于保存事件监控结果
cd /home/db2inst1
mkdir deadlock
db2 connect to atrrdb user db2inst1 using db2inst1 //从172.20.66.4连接该库(该库在20.2.2上因为该库的codepage为819(支持中文)，而20.2.2的codepage为1386；)，连接端的codepage与数据库服务端的codepage不同时，会导致连接失败。

db2 "create event monitor db2detaildeadlock for deadlocks with details history write to '/home/db2inst1/db2inst1/NODE0000/SQL00001/db2event/db2detaildeadlock'" //deadlocks是一种可选项，还可以有statements，可能还有其他

//启动事件监控器
db2 set event monitor db2detaildeadlock state=0

//关闭事件监控器
db2 flush event monitor db2detaildeadlock
或者是 db2 set event monitor db2detaildeadlock state=1

//查看事件监控器的结果
db2evmon -path /home/db2inst1/db2inst1/NODE0000/SQL00001/db2event/db2detaildeadlock > deadlock.txt

[db2inst1@KONGSI ~]$

/home/db2inst1/db2inst1/NODE0000/SQL00001/db2event/db2detaildeadlock //event monitor事件监控器打开时，每当发生死锁事件，该目录下的文件中就会有记录。每隔5分钟生成一条记录，atrrdb有，但是zbbb没有

=================================================
查询数据库锁类问题的基本方法：
db2 list application |more
找到app. Handle
db2pd -db zbbb -applications
找到该app. Handle对应的L-AnchID和L-StmtUID。

db2pd -db zbbb -dynamic
通过L-AnchID和L-StmtUID找到该事务对应的sql

标签

#数据库 #运维