案例:死锁引起的应用挂起

今天，操作人员告知某系统无法进行刷卡操作，系统始终处于等待状态。至中午时分，状况愈演愈烈，只好先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

看来问题的根源已经找到了，剩下就是找出对应的程序并验证。