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.<\/p>\n
Database version: 12.1.0.2, but the same behaviour was also present in 11.2.0.4.<\/p>\n
Very simple test schema set up:<\/p>\n
create table p (\n id number(10) primary key,\n v varchar2(10) not null\n) organization index;\n\ncreate table c (\n id number(10) primary key,\n p_id number(10) not null references p(id),\n v varchar2(10) not null\n);\n\ninsert into p values (1, '1');\ninsert into p values (2, '2');\ninsert into c values (1, 1, '1');\ninsert into c values (2, 1, '2');\ninsert into c values (3, 2, '3');\n\ncreate index cpid on c (p_id);\n<\/code><\/pre>\nNote, the foreign key is indexed.<\/p>\n
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.<\/p>\n
SQL> SELECT * FROM p WHERE id=1 FOR UPDATE;\n\n ID V \n---------- ----------\n 1 1 \n\nSQL> SELECT sys_context('userenv','sid') session_id from dual;\n\nSESSION_ID \n--------------------\n268 \n<\/code><\/pre>\nNow row id=1 is locked in the parent table p by session 268.
\nCould another session insert into table c when the the parent table row is locked?<\/p>\n
SQL> SELECT sys_context('userenv','sid') session_id from dual;\n\nSESSION_ID \n---------------------\n255 \n\nSQL> INSERT INTO c (id, p_id, v) VALUES (12, 2, 'not locked');\n\n1 row inserted.\n\nSQL> INSERT INTO c (id, p_id, v) VALUES (11, 1, 'locked');\n<\/code><\/pre>\nSo 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:<\/p>\n
SQL> select status, event, state, blocking_session from v$session where sid=255;\n\nSTATUS EVENT STATE BLOCKING_SESSION\n-------- ------------------------------------------------------------ ------------------- ----------------------------------\nACTIVE enq: TX - row lock contention WAITING 268\n<\/code><\/pre>\nSession doing the insert is blocked by the session who is holding a lock on the parent table row that the insert is referring to.
\nLets look at the locks both sessions are holding\/requesting:<\/p>\n
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\nfrom dba_locks where session_id in (255,268) and lock_type != 'AE'\norder by rbs, slot,seq\n\nSESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED BLOCKING_OTHERS RBS SLOT SEQ \n---------- --------------- --------------- --------------- --------------- ---------- ---------- --------\n 255 DML Row-X (SX) None Not Blocking 1 34910 0 \n 268 DML Row-X (SX) None Not Blocking 1 34910 0 \n 255 DML Row-X (SX) None Not Blocking 1 34912 0 \n 255 Transaction Exclusive None Not Blocking 2 23 3016 \n 255 Transaction None Share Not Blocking 9 3 2903 \n 268 Transaction Exclusive None Blocking 9 3 2903 \n\n 6 rows selected \n\nSQL> select o.object_type, o.object_name from v$locked_object lo join dba_objects o on o.object_id = lo.object_id\nwhere lo.xidusn=9 and lo.xidslot=3 and lo.xidsqn=2903;\n\nOBJECT_TYPE OBJECT_NAME \n--------------- ---------------\nTABLE P \n<\/code><\/pre>\nHere 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.
\nHere 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.
\nIt 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.<\/strong><\/p>\nLets try the same example, but now creating parent table as HEAP:<\/p>\n
create table pheap (\n id number(10) primary key,\n v varchar2(10) not null\n);\n\ncreate table cheap (\n id number(10) primary key,\n p_id number(10) not null references pheap(id),\n v varchar2(10) not null\n);\n\ninsert into pheap values (1, '1');\ninsert into pheap values (2, '2');\ninsert into cheap values (1, 1, '1');\ninsert into cheap values (2, 1, '2');\ninsert into cheap values (3, 2, '3');\n\ncreate index cheappid on cheap (p_id);\n<\/code><\/pre>\nLock the parent row in one session:<\/p>\n
SQL> SELECT * FROM pheap WHERE id=1 FOR UPDATE;\n ID V \n---------- ----------\n 1 1 \n\nSQL> SELECT sys_context('userenv','sid') session_id from dual;\n\nSESSION_ID \n--------------------\n3 \n<\/code><\/pre>\nAnd try to insert into child from another session:<\/p>\n
SQL> SELECT sys_context('userenv','sid') session_id from dual;\n\nSESSION_ID \n---------------\n12 \n\nSQL> INSERT INTO cheap (id, p_id, v) VALUES (12, 2, 'not locked');\n\n1 row inserted.\n\nSQL> INSERT INTO cheap (id, p_id, v) VALUES (11, 1, 'locked');\n\n1 row inserted.\n<\/code><\/pre>\nNo waiting whatsoever. Does anybody know why this difference in behaviour between IOT and HEAP tables?<\/p>\n
UPDATE 2016-06-26: After getting Jonathan Lewis<\/a> involved in Twitter, he solved this mystery very quickly \ud83d\ude42 Link to twitter conversation<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[4],"class_list":["post-317","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-oracle"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/317","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=317"}],"version-history":[{"count":8,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/317\/revisions"}],"predecessor-version":[{"id":326,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/317\/revisions\/326"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=317"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=317"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=317"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}