Interesting difference in foreign key locking behavior between heap and index organized tables
- 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