Change in Table Locking (TM) Behavior When Foreign Key Columns are Unindexed in 12.1.0.2

One of the most common cause of Table locks related wait enq: TM - contention is unindexed foreign key columns. There are two previous post related to this which areTM enq: Locking and Invisible Indexes and TM enq: Locks. One of the scenarios examined in these previous posts was where one session inserting to parent table and another session updating the primary key column or deleting a row on the parent table. In this scenario the second session would hang, observed both in 11.2.0.3 and 11.2.0.4. However this is no longer the case in 12c, tested on 12.1.0.2. This post look the change in this behavior.
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               72425

The 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 following

SQL>  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          2

From 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          0

and session wait shows the wait event

SQL> 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.2

SQL> 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               137539

Unlike 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 table

SQL> 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          0

This 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.

SQL Sinppet : Inter Instance Lockings

Identify the inter instance locks in a RAC environment.

Select (Select locking.sid||','||se.serial# From Gv$session Se Where Se.Sid=Locking.Sid And Se.Inst_Id = Locking.Inst_Id) As Locking_Session_serial,
Locking.Type As holding_lock_Type,
Locking.Inst_Id As Lock_Instance,
Decode (Locking.Lmode,0,'None',1,'Null',2,'Row-S',3, 'Row-X',4,'Share',5,'S/Row',6,'Exclusive') Locking_Mode,
(Select waiting.sid||','||se.serial# From Gv$session Se Where Se.Sid=Waiting.Sid And Se.Inst_Id = Waiting.Inst_Id) As Waiting_Session,
Waiting.Type As Wait_Lock_Type,
Waiting.Inst_Id As Wait_Instance,
DECODE(waiting.REQUEST,0,'None',1,'Null',2,'Row-S',3, 'Row-X',4,'Share',5,'S/Row',6,'Exclusive') REQUEST_mode,
(Select Prev_Sql_Id||' @ '||Machine From Gv$session Se Where Se.Sid=Locking.Sid And Se.Inst_Id = Locking.Inst_Id) Locking_Sql_And_Machine,
(Select Sql_Id||' @ '||Machine From Gv$session Se Where Se.Sid=Waiting.Sid And Se.Inst_Id = Waiting.Inst_Id) Waiting_Sql_and_machine,
Case When Locking.Type='TM' Then
(select object_name from dba_objects where object_id=locking.id1)
end as TM_lock_Object
FROM Gv$lock Locking,
  Gv$lock Waiting
WHERE Locking.Id1   = Waiting.Id1
And Locking.Id2     = Waiting.Id2
And Locking.Block   > 0
And Waiting.Request >0;

Above gives sid,serial# from locking and waiting sessions (makes it easy to kill them), client machine names of the session in the lock situation as well as sql ids to identify the sql involved in the lock and instance ids.

There's also gv$global_blocked_locks view which gives global locks.

SELECT g.inst_id INST,
  s.sid,
  g.type,
  s.username,
  s.serial#,
  s.process,
  DECODE (LMODE,0,'None',1,'Null',2,'Row-S',3, 'Row-X',4,'Share',5,'S/Row',6,'Exclusive') LMODE,
  DECODE(REQUEST,0,'None',1,'Null',2,'Row-S',3, 'Row-X',4,'Share',5,'S/Row',6,'Exclusive') REQUEST
FROM gv$global_blocked_locks g,
  gv$session s
WHERE g.sid   = s.sid
And G.Inst_Id = S.Inst_Id
ORDER BY state;

To test this create the tables listed in TM Enq post.
From instance 1 run

update y set c = 8 where d=10;

and from instance 2 run

update x set a = 12 where a = 7;

second session will hang. From a third session run the first SQL above to identify the locks and objects involved.



Kill blocking sessions across all instances

Select (Select 'alter system kill session '''||locking.sid||','||se.serial#||',@'||locking.inst_id||''';' 
From Gv$session Se Where Se.Sid=Locking.Sid And Se.Inst_Id = Locking.Inst_Id) As kill_stmt
FROM Gv$lock Locking,
  Gv$lock Waiting
WHERE Locking.Id1   = Waiting.Id1
And Locking.Id2     = Waiting.Id2
And Locking.Block   > 0
And Waiting.Request >0;


KILL_STMT
--------------------------------------------------
alter system kill session '762,397,@2';
alter system kill session '16,8893,@1';

Invisible Indexes and TM enq: Locks

Indexes could be created to prevent locking situations when foreign key columns are un-indexed.
Introducing the index could alter the execution plans and resulting plans may not be desirable.
In 11g indexes could be made invisible thus preventing the optimizer from using them (unless use invisible indexes set to true).
But this would defeat the purpose if the indexes were created to prevent the foreign key columns related locking situations.

create table x (a number primary key, b number);
create table y (c number,d number, foreign key (c) references x(a));

begin
for i in 1 .. 10
loop
insert into x values (i, i + 10);
end loop;
end;
/

begin
for i in 1 .. 5
loop
insert into y values (i, i + 5);
end loop;
end;
/

commit;

Create two sessions with autocommit off and run on session 1

insert into x values (11,21);

on session 2

update x set a = 12 where a = 7;

In this case session 2 will hang.

Now create an index on y.c

create index aidx on y(c);

Run the same scenario as above and this time both sessions will get executed without hanging.

Make the index invisible

alter index aidx invisible;

Run the above two session scenario and second session will start to hang.

Even though it is said, invisible indexes are maintained during DML, it appears invisible indexes cannot be used to prevent such locking situations.

If the index is made visible from the first session while the second session still hanging, second session will come out of the hang and proceed as normal.

This was tested on 11gR2 (11.2.0.1.3)

SQL Sinppet : Foreign Key Columns not Inside an Index

--select 'create index '||child_table||'_aidx on '||child_table||'('||child_table_column||') Tablespace Tbxindexes Online Nologging Compute Statistics;' from (
SELECT p.table_name   AS parent_table,
  pc.column_name      AS parent_table_column,
  C.Table_Name        AS Child_Table,
  Cc.Column_Name      AS Child_Table_Column ,
  C.R_Constraint_Name As Parent_Constraint_Name,
  C.Constraint_Name   As Child_Constraint_Name,
  cc.position
FROM user_constraints p,
  user_constraints c,
  user_cons_columns pc,
  user_cons_columns cc
WHERE p.constraint_name = c.r_constraint_name
AND p.constraint_name   = pc.constraint_name
AND pc.position         = cc.position
And C.Constraint_Name   = Cc.Constraint_Name
And C.Table_Name        =  '<<child_table_name_here>>'
--And C.Table_Name   like  '<<child_table_name_here>>_%'
And Not Exists (Select 1 from user_ind_columns Inds
Where Inds.Table_Name = C.Table_Name
And Inds.Column_Name=Cc.Column_Name
And Inds.Column_Position = Cc.Position) 
 Order By Parent_Table,child_table,child_constraint_name, Position;
--);

Useful in situations where foreign keys need to be indexed.

SQL Sinppet : Parent/Child tables

SQL to find out foreign key relationships between tables

select p.table_name as parent_table,
pc.column_name as parent_table_column,
C.Table_Name As Child_Table,
Cc.Column_Name As Child_Table_Column , 
C.R_Constraint_Name As Parent_Constraint_Name,
c.constraint_name as child_constraint_name
from user_constraints p, user_constraints c,
user_cons_columns pc, user_cons_columns cc
where p.constraint_name = c.r_constraint_name
and p.constraint_name = pc.constraint_name
and pc.position = cc.position
and c.constraint_name = cc.constraint_name
and c.table_name = 'table name';

TM enq: Locking

If an index does not exists on a foreign key column then,
when an update happens on that foreign key column the referenced column on the parent table is locked. No update will be possible on this column. Other columns in the parent table are not affected.

create table x (a number primary key, b number);
create table y (c number,d number, foreign key (c) references x(a));

begin
for i in 1 .. 10
loop
insert into x values (i, i + 10);
end loop;
end;
/

begin
for i in 1 .. 5
loop
insert into y values (i, i + 5);
end loop;
end;
/

session 1
update y set c = 8 where d=10;

1 row updated.

session 2
update x set a = 12 where a = 7;

hangs.....

query dba_waiters from session 1
select lock_type,mode_held,mode_requested,lock_id1 from dba_waiters;

LOCK_ MODE_HELD MODE_REQUE LOCK_ID1
----- ---------- ---------- ----------
DML Row-X (SX) Share 227738

lock_id1 gives the object id of the child table which has the lock

select object_name from user_objects where object_id=227738;
object_name
-----------
Y

quering v$lock it is possible to find the parent table as well.

There's also a insert, update/delete combination which will leave one session hanging.

session 1
insert into x values (11,21);

session 2
update x set a = 12 where a = 7;

or

delete from x where b = 18;

hangs....

query dba_waiters from session 1
select lock_type,mode_held,mode_requested,lock_id1 from dba_waiters;

LOCK_ MODE_HELD MODE_REQUE LOCK_ID1
----- ---------- ---------- ----------
DML Row-X (SX) Share 227738

select object_name from user_objects where object_id=227738;
object_name
-----------
Y

running the delete or update statement first and insert statement second doesn't cause this blocking.