详述由ENQ：TX – ROW LOCK CONTENTION引起的全局死锁处理过程

墨墨导读：ENQ: TX - row lock contention等待事件，是Oracle数据库里最常见的等待事件之一，一般是由于不同的session更新或删除同一条记录、唯一索引有重复索引、位图索引同时被更新或同时并发的向位图索引字段上插入相同字段值等几种可能性，但是在11.2.0.3 到 11.2.0.4的数据库版本中，可能还会出现因为Oracle数据库bug而触发全局死锁。

问题描述

这种全局死锁的触发条件为：
1. 11.2.0.3 到 11.2.0.4的数据库版本
2. Table Statistics Gatherings（表统计信息收集）and High Execution of Update sys.col_usage$（大量执行更新sys.col_usage$表）

问题表现

1）在数据库的ash中，发现大量的enq：TX – row lock contention等待事件。
2）在数据库实例的alert日志中，出现以下类似报错代码：

Mon Apr 01 15:17:06 2019	
Global Enqueue Services Deadlock detected. More info in file	
 /u01/PROD/oracle/diag/rdbms/prod/PROD3/trace/PROD3_lmd0_123449.trc.	
Mon Apr 01 15:17:45 2019	
Incremental checkpoint up to RBA [0x12131.2f2c8.0], current log tail at RBA [0x12131.49f7c.0]	
Mon Apr 01 15:37:50 2019	
Incremental checkpoint up to RBA [0x12131.1fd21f.0], current log tail at RBA [0x12131.2157f5.0]	
Mon Apr 01 15:48:21 2019	
Global Enqueue Services Deadlock detected. More info in file	
 /u01/PROD/oracle/diag/rdbms/prod/PROD3/trace/PROD3_lmd0_123449.trc.	
Mon Apr 01 15:49:13 2019	
Global Enqueue Services Deadlock detected. More info in file	
 /u01/PROD/oracle/diag/rdbms/prod/PROD3/trace/PROD3_lmd0_123449.trc.

3）在trace报错的对应trace日志中，出现以下类似代码：

*** 2019-04-01 15:14:23.500	
kjddt2vb: valblk  [0.974f29] &gt; local ts [0.974f28]	
*** 2019-04-01 15:16:53.491	
·····&lt;省略部分内容&gt;	
----------enqueue 0x11e22ec4f8------------------------	
lock version     : 4303	
Owner inst       : 3	
grant_level      : KJUSEREX	
req_level        : KJUSEREX	
bast_level       : KJUSERNL	
notify_func      : (nil)	
resp             : 0x3a4082cd00	
procp            : 0x178995b0d0	
pid              : 123449	
proc version     : 0	
oprocp           : (nil)	
opid             : 123449	
group lock owner : 0x1724aeb350	
possible pid     : 123541	
xid              : 32000-0003-00000002	
dd_time          : 0.0 secs	
dd_count         : 0	
timeout          : 0.0 secs	
On_timer_q?      : N	
On_dd_q?         : N	
lock_state       : GRANTED	
ast_flag         : 0x0	
Open Options     : KJUSERDEADLOCK 	
Convert options  : KJUSERNOQUEUE KJUSERNODEADLOCKWAIT 	
History          : REM_AST &gt; REM_AST &gt; MSGSENT &gt; FREE &gt; REF_RES &gt; LOC_AST	
Msg_Seq          : 0x0	
res_seq          : 7168	
valblk           : 0x00000000000000002056dea617000000 . V	
user session for deadlock lock 0x11e22ec4f8	
  sid: 5251 ser: 1 audsid: 0 user: 0/SYS	
    flags: (0x51) USR/- flags_idl: (0x1) BSY/-/-/-/-/-	
    flags2: (0x40409) -/-/INC	
  pid: 50 O/S info: user: oraprod, term: UNKNOWN, ospid: 123541	
    image: oracle@ebsdb3 (SMON)	
current SQL:	
update sys.col_usage$ set   equality_preds    = equality_preds    + decode(bitand(:flag,1),0,0,1),   equijoin_preds    = equijoin_preds    + decode(bitand(:flag,2),0,0,1),   nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0,1),   range_preds       = range_preds       + decode(bitand(:flag,8),0,0,1),   like_preds        = like_preds        + decode(bitand(:flag,16),0,0,1),   null_preds        = null_preds        + decode(bitand(:flag,32),0,0,1),  timestamp = :time where obj# = :objn and intcol# = :coln	
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK	
possible owner[50.123541] on resource TX-2A6F0008-0003F4B6

故障解决

针对上述情况，根据Oracle官方文档Global Enqueue Services Deadlock Detected During Table Statistics Gatherings and High Execution of Update sys.col_usage$ (文档 ID 2347644.1)描述，建议应用数据库补丁19591060。

Apply Patch 19591060 which is merge patch for BUGS 16470836 and 17715578.
The fix for Bug 16470836 improves this greatly by only flushing the colusage/monitoring info for the gathered table.
The fix for Bug 17715578 further improves the performance of updating sys.mon_mods$.

扩展阅读