Oracle Database Discussions

Hi,

I have a strange locking issue where a CREATE INDEX ONLINE is waiting for a transaction that did only an inserted a row into the parent table.

Wait is on TX mode=4 (not TM locks) and I've currently reproduced it only on 11.2.0.3 and 11.2.0.4

Easy to reproduce on SCOTT schema:

Session 1:

SQL> set time on

14:54:58 SQL> insert into SCOTT.DEPT(deptno,dname) values (50,'test');

1 row created.

Session 2;

14:55:24 SQL> create index test on SCOTT.EMP (ename) ONLINE;

this is waiting on 'enq: TX - row lock contention':

SQL> select sid,chain_signature from v$wait_chains where blocker_is_valid='TRUE'

       SID CHAIN_SIGNATURE                                                      

---------- ----------------------------------------------------------------------

       603 'SQL*Net message from client'<='enq: TX - row lock contention'

waiting to acquire transaction lock in mode 4:

SQL> select * from gv$lock where sid=603

   INST_ID ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

---------- ---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

         1 00000006CDA05EB8 00000006CDA05F10        603 AE       352577          0          4          0        348          2

         1 00000006CDA06078 00000006CDA060D0        603 DL       779505          0          3          0        308          2

         1 00000006CD9FABE0 00000006CD9FAC38        603 DL       779505          0          3          0        308          2

         1 00007F28001F5028 00007F28001F5088        603 TM       779505          0          2          0        308          2

         1 00007F28001F5028 00007F28001F5088        603 TM       779510          0          4          0        308          2

         1 00000006CD9FA240 00000006CD9FA298        603 OD       779505          0          4          0        308          2

         1 00000006B4F26790 00000006B4F26808        603 TX       589847     711499          6          0        308          2

         1 00000006CDA0ED68 00000006CDA0EDC0        603 TX       655377     996169          0          4        308          0

I've traced 10046 and 10704:

broadcast message=29231196672 obj#=779514 tim=1447251053634045

ksqgtl *** TX-000f001e-000ad97c mode=4 flags=0x10001 timeout=21474836 ***

ksqgtl: xcb=0x6bb322528, ktcdix=2147483647, topxcb=0x6bb322528

        ktcipt(topxcb)=0x0

ksucti: init session DID from txn DID:

ksqgtl:

        ksqlkdid: 0001-003F-0000AFC4

*** ksudidTrace: ksqgtl

        ktcmydid(): 0001-003F-0000AFC4

        ksusesdi:   0001-003F-0000AFC3

        ksusetxn:   0001-003F-0000AFC4

ksqcmi: TX,f001e,ad97c mode=4 timeout=21474836

*** 2015-11-11 15:11:36.566

WAIT #140700792578680: nam='enq: TX - row lock contention' ela= 42932082 name|mode=1415053316 usn<<16 | slot=983070 sequence=711036 obj#=779514 tim=1447251096566283

And there's nothing in V$SESSION current file/block/row. current obj# is EMP. current sql_id is the CREATE INDEX.

If anyone has an idea about:

- why creating an index online has to wait for transactions that modified only parent table

- how to investigate further

- can or cannot reproduce in other versions

Thanks in advance,

Franck.