死锁诊断：Lck0 Self-Deadlock Causes Processes Hang on Row Cache Lock

APPLIES TO:

Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.2 [Release 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS

• Alert log may report the following message:
  

  WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=XX

   

• The blocker process are waiting for "row cache lock":
  
  
  

  Oracle session identified by:

  {
  instance: 1 (XXX.XXX1)
  os id: 126502
  process id: 104, oracle@XXXX
  session id: 1781
  session serial #: 32774
  pdb id: 21 (XXX_DEV_PDB)
  }
  which is waiting for 'row cache lock' with wait info: <<<<< the blocker process was blocked on row cache lock too.
  {
  p1: 'cache id'=0x8
  p2: 'mode'=0x0
  p3: 'request'=0x5
  time in wait: 0.757977 sec
  heur. time in wait: 11 min 45 sec
  timeout after: 2.242023 sec
  wait id: 3836
  blocking: 2 sessions
  wait history:
  * time between current wait and wait #1: 0.000038 sec
  1. event: 'row cache lock'
  time waited: 3.000962 sec
  wait id: 3835 p1: 'cache id'=0x8 <<<<< the row cache is about dc_objects.
  p2: 'mode'=0x0
  p3: 'request'=0x5
  * time between wait #1 and #2: 0.000029 sec

    
  
  
•  lck0 process shows a self-deadlock on a row cache lock
  

  PENDING_Q:
  lp 0x3c1a63c068 gl KJUSERNL rl KJUSEREX rp 0x3b0f277ad8 [0xec4a8cec][0xd1a6683e],[QI][ext 0x15,0x0]
  master 2 pid 16116 rseq 4 mseq 0 bast armed
  history NONE > NONE > NONE > NONE > REF_RES > GR2CVT
  convert opt KJUSERNODEADLOCKWAIT KJUSERNODEADLOCKBLOCK KJUSERTRCCANCEL
  ......

  GRANTED_Q:
  ......
  lp 0x3b0b43cb40 gl KJUSEREX rp 0x3b0f277ad8 [0xec4a8cec][0xd1a6683e],[QI][ext 0x15,0x0]
  master 2 pid 16116 rseq 4 mseq 0 bast fired 5050 sec ago
  history NONE > NONE > REF_RES > LOC_AST > LOC_AST > LOC_BAST
  open opt KJUSERPROCESS_OWNED KJUSERCLIENTLOCK sec since mv2grQ 5050

    
• Following functions may be in the row cache file:
  

  <-skgpwwait<-ksliwat<-kslwaitctx<-kqrigt<-kqrLockAndPinPo<-kqrpre1<-kqrpre<-kpdbUpdateMDL<-1<-opiex<-opiosq0<-opiosq<-opiodr<-rpidrus

    
  

The same lock is on both pending Q and granted Q, it's a self-deadlock  on lck0 process.

CHANGES

 none.

CAUSE

This matches following the bug:

Bug 23254511 : UTLRP GETTING STUCK FOR A LONG TIME which was closed as duplicate to following bug

Bug 20613079 : LNX64-12.2-CDB: PQ HIT ORA-600:[KSIPGETCTXIA:PDBIDOUTOFRANGE]

SOLUTION

 Apply patch 20613079