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.