Written by: ilmarkerm

Category: Blog entry

Published: June 4, 2016

I hit this issue by accident, developers wanted to disable inserts to child table so they could perform some one-time maintenance operation, and this maintenance only affected one rown from the parent table (and all it’s children). I started wondering if there is any smaller level impact solution possible than taking a shared read lock on child table.

Database version: 12.1.0.2, but the same behaviour was also present in 11.2.0.4.

Very simple test schema set up:

create table p (
  id number(10) primary key,
  v varchar2(10) not null
) organization index;

create table c (
  id number(10) primary key,
  p_id number(10) not null references p(id),
  v varchar2(10) not null
);

insert into p values (1, '1');
insert into p values (2, '2');
insert into c values (1, 1, '1');
insert into c values (2, 1, '2');
insert into c values (3, 2, '3');

create index cpid on c (p_id);

Note, the foreign key is indexed.

I just had a thought that what will happen if I lock the parent table row first using SELECT FOR UPDATE in order to take the lock as low level as possible. What would happen then with inserts to the child table? Database needs to somehow protect that the parent row does not change/disappear while the child is being inserted.

SQL> SELECT * FROM p WHERE id=1 FOR UPDATE;

        ID V        
---------- ----------
         1 1         

SQL> SELECT sys_context('userenv','sid') session_id from dual;

SESSION_ID                                                                     
--------------------
268                                                                             

Now row id=1 is locked in the parent table p by session 268.
Could another session insert into table c when the the parent table row is locked?

SQL> SELECT sys_context('userenv','sid') session_id from dual;

SESSION_ID                                                                     
---------------------
255                                                                             

SQL> INSERT INTO c (id, p_id, v) VALUES (12, 2, 'not locked');

1 row inserted.

SQL> INSERT INTO c (id, p_id, v) VALUES (11, 1, 'locked');

So I could insert into the child table c a new row where p_id=2 (p.id=2 was not locked), but the second insert where p_id=1 (p.id=1 was locked by session 268 earlier) just hangs. Lets look why session 255 is hanging:

SQL> select status, event, state, blocking_session from v$session where sid=255;

STATUS   EVENT                                                        STATE                                 BLOCKING_SESSION
-------- ------------------------------------------------------------ ------------------- ----------------------------------
ACTIVE   enq: TX - row lock contention                                WAITING                                            268

Session doing the insert is blocked by the session who is holding a lock on the parent table row that the insert is referring to.
Lets look at the locks both sessions are holding/requesting:

SQL> select session_id, lock_type, mode_held, mode_requested, blocking_others, trunc(lock_id1/power(2,16)) rbs, bitand(lock_id1, to_number('ffff','xxxx'))+0 slot, lock_id2 seq
from dba_locks where session_id in (255,268) and lock_type != 'AE'
order by rbs, slot,seq

SESSION_ID LOCK_TYPE       MODE_HELD       MODE_REQUESTED  BLOCKING_OTHERS        RBS       SLOT SEQ    
---------- --------------- --------------- --------------- --------------- ---------- ---------- --------
       255 DML             Row-X (SX)      None            Not Blocking             1      34910 0       
       268 DML             Row-X (SX)      None            Not Blocking             1      34910 0       
       255 DML             Row-X (SX)      None            Not Blocking             1      34912 0       
       255 Transaction     Exclusive       None            Not Blocking             2         23 3016    
       255 Transaction     None            Share           Not Blocking             9          3 2903    
       268 Transaction     Exclusive       None            Blocking                 9          3 2903    

 6 rows selected 

SQL> select o.object_type, o.object_name from v$locked_object lo join dba_objects o on o.object_id = lo.object_id
where lo.xidusn=9 and lo.xidslot=3 and lo.xidsqn=2903;

OBJECT_TYPE     OBJECT_NAME   
--------------- ---------------
TABLE           P              

Here we see that session 268 is holding transaction (TX, row level) lock in Exclusive mode on table P and it is blocking session 255 that is requesting lock on the same row in Share mode.
Here I have to conclude, that when inserting a row to child table, Oracle also tries to get a Shared row lock on the parent table. Looks perfect for my use case and I announced victory in our internal DBA mailing list. But a few minutes later a colleque emailed me back, that it does not work. He recreated the setup (his own way, not using my scripts) and after locking the parent table row he was able to insert to the child table just fine.
It took some time to work out the differences in our setups and in the end I reduced the difference to a simple fact that I create index-organized tables by default, and he creates heap tables by default and that makes all the difference in this case. This only works when the parent table is index-organized.

Lets try the same example, but now creating parent table as HEAP:

create table pheap (
  id number(10) primary key,
  v varchar2(10) not null
);

create table cheap (
  id number(10) primary key,
  p_id number(10) not null references pheap(id),
  v varchar2(10) not null
);

insert into pheap values (1, '1');
insert into pheap values (2, '2');
insert into cheap values (1, 1, '1');
insert into cheap values (2, 1, '2');
insert into cheap values (3, 2, '3');

create index cheappid on cheap (p_id);

Lock the parent row in one session:

SQL> SELECT * FROM pheap WHERE id=1 FOR UPDATE;
        ID V        
---------- ----------
         1 1         

SQL> SELECT sys_context('userenv','sid') session_id from dual;

SESSION_ID                                                                                                                                                                                             
--------------------
3  

And try to insert into child from another session:

SQL> SELECT sys_context('userenv','sid') session_id from dual;

SESSION_ID    
---------------
12             

SQL> INSERT INTO cheap (id, p_id, v) VALUES (12, 2, 'not locked');

1 row inserted.

SQL> INSERT INTO cheap (id, p_id, v) VALUES (11, 1, 'locked');

1 row inserted.

No waiting whatsoever. Does anybody know why this difference in behaviour between IOT and HEAP tables?

UPDATE 2016-06-26: After getting Jonathan Lewis involved in Twitter, he solved this mystery very quickly 🙂 Link to twitter conversation