ORA-2049 reported on Distributed Txn affecting 2 Tables with a Foreign Key Relationship in a RAC environment

Last updated on MAY 07, 2017

Applies to:

Symptoms

ORA-2049 can be observed when there are modifications to two or more tables in a distributed
transactions where :

- there is a foreign key relationship between the tables and under the circumstances of this particular issue :
  - there was an insert into tableA on node 1 ; table A has a pk
  - there was an insert into tableB on node 2 in the same txn ; tableB has a foreign key reference to the
    primary key in tableA
- a locking situation is observed where a branch may appear to be blocking waiting on event :
  'enq: TX - row lock contention'

One of the sessions may be blocked trying to lock an index block with an execution stack context as
follows :

ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1900<-sspuser()+112<-__sighandler()<-semtimedop()+10
<-skgpwwait()+160<-ksliwat()+1865<-kslwaitctx()+163<-kjusuc()+3600<-ksipgetctxi()+1759
<-ksqcmi()+20904<-ksqgtlctx()+3810<-ksqgelctx()+561<-ktuGetTxForXid()+131<-ktcwit1()+291
<-ktbgtl0()+1192<-kdiexi()+9228<-kxccexi()+543<-kxccresIndex()+490<-kxccres()+612
<-qesltcHandleRefConstraints()+420<-qesltcAfterRowProcessing()+385
<-__PGOSF803_qerltcNoKdtBufferedInsRowCBK()+383<-qerltcSingleRowLoad()+279<-qerltcFetch()+380
<-qerstFetch()+321<-insexe()+682<-opiexe()+5531<-kpoal8()+2231<-opiodr()+910<-ttcpip()+2289
<-opitsk()+1670<-opiino()+966<-opiodr()+910<-opidrv()+570<-sou2o()+103<-opimai_real()+133
<-ssthrdmain()+252<-main()+201<-__libc_start_main()+244<-_start()+36

kdiexi() - indicates index access and we are unable to perform the operation on the index ;
ksliwat() - indicates a waiting situation

Changes

Clusterwide global transactions are now being used.

Cause