Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Ali.

Asked: November 02, 2016 - 12:19 pm UTC

Last updated: November 04, 2016 - 9:47 am UTC

Version: Oracle 11 g

Viewed 10K+ times! This question is

You Asked

Hello team,

I want to discuss an Issue of Locking occur on Insert in my Table

Issue is that I have a Table A with Primary Key on Column 1, foreign key on Nullable Column 2 with out any Index.

Table B is having foreign key of Table A P.key with Normal Index and Table B does not have any Primary/Unique Key in it.


On Insert in Table A Row level locking occur. I can figure out the Foreign key Column 2 without an Index is the root cause, Is it correct ?


Thanks

and Chris said...

Deleting from the child table doesn't prevent inserts on the parent. You can see this with the following test case:

Setup:

create table t1 (
  x int primary key
);
create table t2 (
  y int references t1 (x)
);

insert into t1 values (1);
insert into t2 values (1);
commit;


Session 1:

SQL> delete t2;

1 row deleted.


Session 2:

SQL> insert into t1 values (2);

1 row created.


As you can see, both statements completed. Checking in a third session you can see there's no blocking:

SQL> select l1.sid, ' IS BLOCKING ', l2.sid
  2  from   v$lock l1, v$lock l2
  3  where  l1.block =1 and l2.request > 0
  4  and    l1.id1=l2.id1
  5  and    l1.id2=l2.id2;

no rows selected


The most likely reason your inserts are blocked is because you have multiple sessions trying to insert the same PK value.

Back in session 1:

SQL> rollback;

Rollback complete.

SQL> insert into t1 values (2);


At this point session one is stuck, waiting for session 2 to commit/rollback:

SQL> select l1.sid, ' IS BLOCKING ', l2.sid
  2  from   v$lock l1, v$lock l2
  3  where  l1.block =1 and l2.request > 0
  4  and    l1.id1=l2.id1
  5  and    l1.id2=l2.id2;

       SID 'ISBLOCKING'         SID
---------- ------------- ----------
       138  IS BLOCKING          49


If this doesn't explain your situation, then please provide us a test case showing how your insert is blocked!

Is this answer out of date? If it is, please let us know via a Comment