SELF DEADLOCK PROCESS WAITS ON ''Cursor: Pin S Wait On X'' with SQL_TRACE ena...

刘泽宇

SYMPTOMS
System hangs intermittently.


Problem is resolved by killing blocking process
Blocking process is waiting for an eXclusive cursor pin mutex and is holding a Share:
  waiting for 'cursor: pin S wait on X'
Process holds and requests the same mutex within the same session
e.g.: (extract from Systemstate / Processtate of process in question)

Wait section shows a wait in progress for 'cursor: pin S wait on X'


waiting for 'cursor: pin S wait on X' blocking sess=0x(nil) seq=55457
wait_time=0 seconds since wait started=0
idn=a116531d, value=6100000000, where|sleeps=20023f5fe


The mutex identifier is : idn=a116531d
So, searching for 'a116531d' in the rest of the process trace finds:

This shows an 'oper EXCL' :


KGX Atomic Operation Log 0x1c0fa9c78
Mutex 0x17d972b18(97, 0) idn 6d616e62a116531d oper EXCL

LIBRARY OBJECT HANDLE: handle=1ba7cf5b8 mtx=0x1ba7cf6e8(1) cdp=1
name=SELECT * FROM TABLE(MISC_UTILS.SPLIT(:B1 , :B2 ))
hash=8820cb5f8485ab3582d173dfa116531d timestamp=11-06-2006 14:03:51


and this shows an 'oper GET_SHRD ' for the same mutex in the same process:



KGX Atomic Operation Log 0x1c0fa9bd0
Mutex 0x17d972b18(97, 0) idn 414e532ea116531d oper GET_SHRD

LIBRARY OBJECT HANDLE: handle=1ba7cf5b8 mtx=0x1ba7cf6e8(1) cdp=1
name=SELECT * FROM TABLE(MISC_UTILS.SPLIT(:B1 , :B2 ))
hash=8820cb5f8485ab3582d173dfa116531d timestamp=11-06-2006 14:03:51


So the single process holds both oper EXCL and oper GET_SHRD on the same mutex.

Top of the stack for this process is the following:


semtimedop sskgpwwait skgpwwait kslwaitns kskthbwt kslwait
kksLockWait kgxWait kgxSharedExamine kxsGetRuntimeLock kksfbc kxstcls
kksCloseCursor opicca opiclo opicls opiodr rpidrus
skgmstack rpidru rpiswu2 rpidrv psddr0 psdcls
CAUSE
It is possible that SQL_TRACE is the cause of the problem.

From Bug:5653007 The stack contains routines that are specific to tracing. (kxstcls)
SOLUTION
1. Disable SQL_TRACE
or
2. If you must keep SQL_TRACE enabled, suggest applying patch 5485914. You will need to check for available backport for your platform.