There
has been subtle but significant change introduced with Oracle 11g with regard
to the manner in which locks held in relation to policing foreign key
constraints. The following has been tested with 10.2.0.5 , 11.2.0.4 and
12.1.0.2 database.
I
am going to create a table that has two foreign key constraints pointing to two
different parents and populate them with few rows.
rajesh@ORA10G> set feedback off
rajesh@ORA10G> create table p1 (x int primary key,y
varchar2(10));
rajesh@ORA10G> create table p2 (x int primary key,y
varchar2(10));
rajesh@ORA10G> create table c(x int primary key,id1 int
references p1,
2 id2 int references p2) ;
rajesh@ORA10G> insert into p1 values(1,'Text1');
rajesh@ORA10G> insert into p1 values(2,'Text2');
rajesh@ORA10G> insert into p2 values(1,'Text1');
rajesh@ORA10G> insert into p2 values(2,'Text2');
rajesh@ORA10G> insert into c values(1,1,1);
rajesh@ORA10G> insert into c values(2,1,1);
rajesh@ORA10G> commit;
rajesh@ORA10G> set feedback on
rajesh@ORA10G>
rajesh@ORA10G> select * from p1;
X Y
---------- ----------
1 Text1
2 Text2
2 rows selected.
rajesh@ORA10G> select * from p2;
X Y
---------- ----------
1 Text1
2 Text2
2 rows selected.
rajesh@ORA10G> select * from C;
X ID1 ID2
---------- ---------- ----------
1 1 1
2 1 1
2 rows selected.
rajesh@ORA10G>
When
running the following insert on table P1
rajesh@ORA10G> insert into p1 values(3,'Text3');
1 row created.
check
in the V$lock view will show that transaction holds a TM (DML enqueuer) lock in
mode=2 on the child table “C” due to
FK relationship between these tables.
rajesh@ORA10G> select t2.object_name,t1.lmode,t1.type,
2 t3.name,t3.id1_tag,t3.id2_tag,description
3 from v$lock t1,
4 user_objects t2,
5 v$lock_type t3
6 where sid = userenv('sid')
7 and t1.id1 = t2.object_id
8 and t1.type = t3.type;
OBJEC LMODE TY
NAME ID1_TAG ID2_TAG DESCRIPTION
----- ---------- -- ---------- ---------- --------------------
--------------------
P1 3 TM
DML object # table/partition Synchronizes accesse
s to an object
C 2
TM DML object # table/partition Synchronizes accesse
s to an object
2 rows selected.
rajesh@ORA10G>
If
another session/transaction were to either say delete a row or update the PK
from the other parent table, then.
rajesh@ORA10G>
rajesh@ORA10G> declare
2 pragma autonomous_transaction;
3 begin
4 delete from p2 where x =2;
5 rollback;
6 end;
7 /
PL/SQL procedure successfully completed.
It
will succeed with no problem, for when it temporarily requires a TM shared lock
(mode=4), it can successfully grab it as the concurrent lock (mode=2) does not
prevent this from occurring. It requires access to this shared lock (mode=4) to
ensure that no transactions currently impacting table “C” that could
potentially violate the constraint following the DML operations on the parent
table P2.
However,
repeating the insert on 11g database we hit a subtle difference. When running
the insert statement again in the P1 table.
rajesh@ORA11G> insert into p1 values(3,'Text3');
1 row created.
rajesh@ORA11G> select t2.object_name,t1.lmode,t1.type,
2 t3.name,t3.id1_tag,t3.id2_tag,description
3 from v$lock t1,
4 user_objects t2,
5 v$lock_type t3
6 where sid = userenv('sid')
7 and t1.id1 = t2.object_id
8 and t1.type = t3.type;
OBJEC LMODE TY
NAME ID1_TAG ID2_TAG DESCRIPTION
----- ---------- -- ---------- ---------- --------------------
--------------------
C 3
TM DML object # table/partition Synchronizes accesse
s to an object
P1 3 TM
DML object # table/partition Synchronizes accesse
s to an object
2 rows selected.
rajesh@ORA11G>
A
check in v$lock view will now show the transaction hold a TM (DML Enqueue) lock
in lmode=3 on the child table “C”. This is a “higher” level lock mode which has
the following consequence on the other session/transaction now attempting to
either delete/ update on PK in the parent table P2.
rajesh@ORA11G>
rajesh@ORA11G> declare
2 pragma autonomous_transaction;
3 begin
4 delete from p2 where x =2;
5 rollback;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for
resource
ORA-06512: at line 4
rajesh@ORA11G>
This
transaction is now deadlocked, as it has to wait for the other transaction/session
to release the DML Enqueue LMODE 3 before it can grab the required TM (mode=4)
shared table lock it is requesting. This is precisely the issue we hit with
somewhat poorly written application trying to perform something akin to above
series of updates from within two different sessions.
This
change was introduced by Oracle to eliminate an ORA-600 issue that could occur
when deleting a row from a table with a PK while rebuilding an associated FK
index that references the PK. It addresses bug 5909305.
However,
introducing a more restricted level of lock in this manner has the side effect
of increasing the likelihood of hitting deadlock scenarios (like the one
described above). The “fix” in this case (in Oracle 11g database) would be to
have index in place on FK columns.
However,
this bug got fixed in 12c.
rajesh@ORA12C> insert into p1 values(3,'Text3');
1 row created.
rajesh@ORA12C>
rajesh@ORA12C> select t2.object_name,t1.lmode,t1.type,
2 t3.name,t3.id1_tag,t3.id2_tag,description
3 from v$lock t1,
4 user_objects t2,
5 v$lock_type t3
6 where sid = userenv('sid')
7 and t1.id1 = t2.object_id
8 and t1.type = t3.type;
OBJEC LMODE TY
NAME ID1_TAG
ID2_TAG DESCRIPTION
----- ---------- -- ---------- ---------- --------------------
--------------------
C 2
TM DML object # table/partition Synchronizes accesse
s to an object
P1 3 TM
DML object # table/partition Synchronizes accesse
s to an object
2 rows selected.
rajesh@ORA12C>
rajesh@ORA12C> declare
2 pragma autonomous_transaction;
3 begin
4 delete from p2 where x =2;
5 rollback;
6 end;
7 /
PL/SQL procedure successfully completed.
rajesh@ORA12C>
No comments:
Post a Comment