今天,操作人员告知某系统无法进行刷卡操作,系统始终处于等待状态。至中午时分,状况愈演愈烈,只好先kill session部分阻塞用户,以便缓解状况。通过gv$lock视图查询可知主要锁定在p_kaoqin等相关表。查询对应语句多为insert,甚至包括SELECT SYSDATE FROM DUAL,有点诡异。通过v$session_event查看用户等待事件,存在大量的enq:TM contention。
TM锁定的对象是表,考虑是否由于外建索引失效造成。查看相关表的约束条件,并无外建约束。看来不是外建的原因。
查看alert文件,发现大量死锁提示:
GES: Potential blocker (pid=2146686) on resource TM-002E3D8B-00000000;
enqueue info in file /oracle/app/admin/xxxxx/bdump/xxxxx2_lmd0_98684.trc and DIAG trace file
GES: Potential blocker (pid=2183274) on resource TM-002E3D93-00000000;
enqueue info in file /oracle/app/admin/xxxxx/bdump/xxxxx2_lmd0_98684.trc and DIAG trace file
GES: Potential blocker (pid=860534) on resource TM-002E3D93-00000000;
enqueue info in file /oracle/app/admin/xxxxx/bdump/xxxxx2_lmd0_98684.trc and DIAG trace file
GES: Potential blocker (pid=1589864) on resource TM-002E3D93-00000000;
enqueue info in file /oracle/app/admin/xxxxx/bdump/xxxxx2_lmd0_98684.trc and DIAG trace file
GES: Potential blocker (pid=1675346) on resource TM-002E3D93-00000000;
enqueue info in file /oracle/app/admin/xxxxx/bdump/xxxxx2_lmd0_98684.trc and DIAG trace file
GES: Potential blocker (pid=1712332) on resource TM-002E3D93-00000000;
enqueue info in file /oracle/app/admin/xxxxx/bdump/xxxxx2_lmd0_98684.trc and DIAG trace file
通过TM-XXXXXXXX-XXXXXXXX转换为数据库对象,正是p_kaoqin等表。查看trace文件,查找sql关键字,
synca inc 4 lvl 58471 from 0 rcvd (my inc,lvl: 4,58470) (4/0.36.0)
ftd received from node 0 (4/0.37.0)
all ftds received
synca inc 4 lvl 58472 from 0 rcvd (my inc,lvl: 4,58471) (4/0.38.0)
*** 2013-11-08 10:15:55.091
End DRM(1832) for pkey transfer request(s) from 0
*** 2013-11-08 10:17:43.053
user session for deadlock lock 7000003c1357218
pid=107 serial=5367 audsid=463747602 user: 268/xxxx
O/S info: user: test, term: unknown, ospid: , machine: xxxx-test3
program: JDBC Thin Client
client info: 172.16.1.193
application name: JDBC Thin Client, hash value=0
Current SQL Statement:
lock table P_KaoQin in exclusive mode
user session for deadlock lock 7000003c33578a8
pid=427 serial=15207 audsid=463728039 user: 268/xxxx
O/S info: user: Guest, term: unknown, ospid: , machine: PC-201102091043
program: JDBC Thin Client
client info: 172.30.122.100
application name: JDBC Thin Client, hash value=0
Current SQL Statement:
UPDATE P_Task SET pt_KaigFlg=:1, pt_TinggFlg=:2, pt_TinggTime=:3, pt_TinggTime_sg=:4, pt_Tinggyy=:5, pt_TinggDepartID=:6, pt_TinggUserCod=:7 WHERE pt_DepartID = :8 AND pt_ID = :9
Global blockers dump start:---------------------------------
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x2e3d93][0x0],[TM]
----------resource 0x700000385358ea0----------------------
resname : [0x2e3d93][0x0],[TM]
Local node : 1
dir_node : 1
master_node : 1
hv idx : 114
hv last r.inc : 4
current inc : 4
hv status : 0
hv master : 1
open options : dd cached
grant_bits : KJUSERNL KJUSERCW
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 2 0 1 0 0 0
val_state : KJUSERVS_VALUE
valblk : 0x01000000000000000000000000000000 .
lock table p_kaoqin in exclusive mode
看来问题的根源已经找到了,剩下就是找出对应的程序并验证。