First the test on 11.2.0.4. The test case is same as the one used in previous post where table X is the parent table and Y is the child table which refers table X and foreign key column is not indexed. Tables were populated same as before. Two sessions were created using sqlplus and autocommit set to off.
Session one does an insert to the primary table
SQL> select sys_context('userenv','sid') sid from dual;
SID
----
32
SQL> insert into x values (11,21);
1 row created.Looking at the locks taken by session one (id : 32) it could be seen TM locks for both parent and child tables are there.SQL> select * from v$lock where sid=32; ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 0000000090D90E10 0000000090D90E68 32 AE 100 0 4 0 134 2 00007FFC99FE6E10 00007FFC99FE6E70 32 TM 72423 0 3 0 70 2 00007FFC99FE6E10 00007FFC99FE6E70 32 TM 72425 0 3 0 70 2 000000008DBA98F0 000000008DBA9968 32 TX 458766 5244 6 0 70 2 SQL> select object_name,object_id from user_objects where object_id in (72423,72425); OBJECT_NAME OBJECT_ID ----------- ---------- X 72423 Y 72425The ID1 column gives the object ids and querying the user object view shows that TM locks are taken for both parent (X) and child (Y) tables. The locking mode is 3 which is row-X (SX). 11.2 Oracle documentation also says "Inserts into the parent table do not acquire table locks on the child table" which is not clearly the case however this text has been changed in 12c documentation to state "Inserts into the parent table do not acquire blocking table locks that prevent DML on the child table".
Second session does an update of the parent table by changing a value in primary key column. This results in second session hanging and this is also stated in 11.2 documentation "When both of the following conditions are true, the database acquires a full table lock on the child table:
1.No index exists on the foreign key column of the child table.
2.A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table. Inserts into the parent table do not acquire table locks on the child table.". Condition 1 is true in this case but so far there has been only an insert to parent table and this has resulted in a TM lock even though documentation says otherwise. Second condition is true only for second session which updates a primary key.
SQL> select sys_context('userenv','sid') sid from dual;
SID
-----
149
SQL> update x set a = 12 where a = 7;The update hangs and querying locks for both session 32 and 149 gives the followingSQL> select * from v$lock where sid in (149,32); ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 0000000090D8EF40 0000000090D8EF98 149 AE 100 0 4 0 1027 2 0000000090D90E10 0000000090D90E68 32 AE 100 0 4 0 1029 2 00007FFC99FE6E10 00007FFC99FE6E70 32 TM 72423 0 3 0 965 2 00007FFC99FE6E10 00007FFC99FE6E70 32 TM 72425 0 3 0 965 1 00007FFC99FE6E10 00007FFC99FE6E70 149 TM 72423 0 3 0 58 2 00007FFC99FE6E10 00007FFC99FE6E70 149 TM 72425 0 0 4 58 0 000000008DBA98F0 000000008DBA9968 32 TX 458766 5244 6 0 965 2From the lock view it could be seen that second session (id :149) is also has locked (object id 72423 which table X, parent table) and trying to lock the child table same as session 32. However it's failing to get the lock on child table which it request with mode 4 but locked by session 32 with mode 3 and is not getting the lock. The block column shows that session 32 lock on object 72425 (child table Y) is the blocker.
DBA Waiters view give the holding and blocking session
SQL> select * from dba_waiters;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2
--------------- --------------- -------------------------- ---------- ---------- ---------- ----------
149 32 DML Row-X (SX) Share 72425 0and session wait shows the wait eventSQL> select sid,event from v$session_wait where sid in (32,149);
SID EVENT
---------- -------------------------
32 SQL*Net message to client
149 enq: TM - contention
Now the same test on 12c (12.1.0.2) both on EE and SE2. Session one does the insert
SQL> select sys_context('userenv','sid') sid from dual;
SID
-----
399
SQL> insert into x values (11,21);
1 row created.Looking the locks it could be seen the child table is locked in mode 2 (row-S (SS)), a shared mode which is different to behavior in 11.2SQL> select * from v$lock where sid=399; ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 000000013D4CBA98 000000013D4CBB10 399 AE 133 0 4 0 155 0 0 000000013731A468 000000013731A4E8 399 TX 393216 33536 6 0 89 0 0 00002B104C568A28 00002B104C568A90 399 TM 137537 0 3 0 89 0 0 00002B104C568A28 00002B104C568A90 399 TM 137539 0 2 0 89 0 0 SQL> select object_name,object_id from user_objects where object_id in (137537,137539); OBJECT_NAME OBJECT_ID ----------- ---------- X 137537 Y 137539Unlike the 11.2 environment in 12c the second session running the update statement will succeed.
SQL> select sys_context('userenv','sid') sid from dual;
SID
-----
769
SQL> update x set a = 12 where a = 7;
1 row updated.Looking at the lock view it shows that second session only has table locks on parent table and no locks on child tableSQL> select * from v$lock where sid in (769,399); ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 000000013D4CBA98 000000013D4CBB10 399 AE 133 0 4 0 426 0 0 000000013D4CCDC8 000000013D4CCE40 769 AE 133 0 4 0 423 0 0 00002B104C5679F0 00002B104C567A58 399 TM 137537 0 3 0 360 0 0 00002B104C5679F0 00002B104C567A58 399 TM 137539 0 2 0 360 0 0 00002B104C5679F0 00002B104C567A58 769 TM 137537 0 3 0 79 0 0 00000001373198C8 0000000137319948 769 TX 327692 33608 6 0 79 0 0 000000013731A468 000000013731A4E8 399 TX 393216 33536 6 0 360 0 0This a change in behavior on 12c and oracle documentation states "Inserts into the parent table do not acquire blocking table locks that prevent DML on the child table. In the case of inserts, the database acquires a lock on the child table that prevents structural changes, but not modifications of existing or newly added rows."
This change of locking behavior in 12c could result in removing some indexes on child tables if the only reason for creating them is to prevent locking situations similar to above that were observed in 11.2. However the other locking situations due to unindexed foreign key columns are still there.
