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

数据和云 2019-08-21

865

墨墨导读：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] > local ts [0.974f28]
*** 2019-04-01 15:16:53.491
·····<省略部分内容>
----------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 > REM_AST > MSGSENT > FREE > REF_RES > 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$.

扩展阅读