locks

Lock by reference

Posted on Updated on

As you may know, not indexing foreign key can have a huge impact on lock behavior and hurt database scalability (Not only with Oracle).

But there is also some rules of thumbs (coming from Asktom) when non indexing the foreign key is considered as reasonable.

So, when do you NOT necessary need to index a foreign key ?

In general when the following conditions are met:

o you do NOT delete from the parent table. (especially with delete cascade — it is a
double whammy)

o you do NOT update the parent tables unique/primary key value.

o you do NOT join from the PARENT to the CHILD (like DEPT->EMP).

This was verified on one of the application i am working on, until reference partitioning comes into play with extreme side effects.

Here is a demo of what can happen when foreign keys are not indexed and you start to use reference partitioning :

First create test1 table:

CREATE TABLE test1
 (
 id number,
 PERIOD_STOP DATE
 )
 PARTITION BY RANGE (PERIOD_STOP)
 (
 PARTITION P2015 VALUES LESS THAN (TO_DATE('12/31/2015 23:01', 'MM/DD/YYYY HH24:MI')),
 PARTITION P2016 VALUES LESS THAN (TO_DATE('12/31/2016 23:01', 'MM/DD/YYYY HH24:MI')),
 PARTITION AA_PMAX VALUES LESS THAN (MAXVALUE)
 );

enable row movement on test1 and create the primary key :

alter table test1 enable row movement;

ALTER TABLE test1 ADD (
 CONSTRAINT PK_test1
 PRIMARY KEY
 (ID))
 ;

now create table test2 partitioned by reference on test1 :

CREATE TABLE test2
 (
 id number not null,
 CONSTRAINT fk_test1
 FOREIGN KEY (ID)
 REFERENCES test1 (ID)
 ENABLE VALIDATE
 )
 PARTITION BY REFERENCE (fk_test1) enable row movement;

Important Remark: We don’t create an index on fk_test1 and we don’t populate any child in test2 table

now let’s insert some data in test1 :

insert into test1 values(1,sysdate);
insert into test1 values(2,sysdate+200);
commit;

now let’s enable 10046 and 10704 traces in order to trace oracle locks:

alter session set events '10046 trace name context forever, level 4 : 10704 trace name context forever, level 3';

update table test1 to force a row migration :

update test1 set period_stop=sysdate + 200 where id =1;

Check what you have in terms of locks (for more info on that, please check Franck Pachot blog post ):

First list objects involved in this transaction:

select object_id , to_char(object_id,'0XXXXXXX') , object_name,subobject_name,object_type from all_objects where object_name like 'TEST%';
OBJECT_ID  TO_CHAR(OBJECT_ID,'0XXXXXXX OBJECT_NAME  SUBOBJECT_NAME       OBJECT_TYPE
---------- -----------------------     ------------ -------------------- -------------------
 83166     000144DE                    TEST1        AA_PMAX              TABLE PARTITION
 83164     000144DC                    TEST1        P2015                TABLE PARTITION
 83165     000144DD                    TEST1        P2016                TABLE PARTITION
 83163     000144DB                    TEST1        TABLE
 83171     000144E3                    TEST2        AA_PMAX              TABLE PARTITION
 83169     000144E1                    TEST2        P2015                TABLE PARTITION
 83170     000144E2                    TEST2        P2016                TABLE PARTITION
 83168     000144E0                    TEST2        TABLE

we have mostly what is described in Franck’s post except that a reference partition is created and involved in row movement.

The lock sequences is the following

first oracle locks table test1 is TM Row exclusive mode :

ksqgtl *** TM-000144DB-00000000-00000000-00000000 mode=3 flags=0x400 timeout=21474836 ***

then test2 table is locked is share mode (mode 4) once again due to the non-indexed foreign key :

ksqgtl *** TM-000144E0-00000000-00000000-00000000 mode=4 flags=0x400 timeout=21474836 ***

this lock on test2 is released just afterward:

ksqrcl: TM-000144E0-00000000-00000000-00000000

then partition P2015 on test1 is locked in RX mode :

ksqgtl *** TM-000144DC-00000000-00000000-00000000 mode=3 flags=0x400 timeout=21474836 ***

then a TX lock is acquired for locking the updated row :

ksqgtl *** TX-00010002-00000449-00000000-00000000 mode=6 flags=0x401 timeout=0 ***

then partition P2016 on test1 is locked (RX) in order to move row :

ksqgtl *** TM-000144DD-00000000-00000000-00000000 mode=3 flags=0x400 timeout=21474836 ***

and once again share lock is acquired and release immediately on test2.

5 locks for one operation but wait that’s not all :

Checking oracle 10046 trace oracle executes the following recursive statement:

update /*+ opt_param('_and_pruning_enabled', 'false') */ "SYS"."TEST2" partition (dataobj_to_partition( "SYS"."TEST1" , 83164))
 move to partition (dataobj_to_partition( "SYS"."TEST1" , 83165)) set "ID" = "ID" where "ID" = 1;

That makes senses oracle instructs to move row on table test2 that are dependent from table test1 (id=1) coming from partition p2015 (object_id=83164) to partition p2016 (object_id=83165).

So if we check the 10074 trace again we can see that table test2 is locked on TM mode 3:

ksqgtl *** TM-000144E0-00000000-00000000-00000000 mode=3 flags=0x400 timeout=21474836 ***

So now if we update test1 in another session,event without any row migration,

please remember that updating test1 with non-indexed key leads to a share lock on test2 :

update test1 set period_stop=period_stop where id=2;

as it’s not possible to acquire a share lock when a TM lock Row-X mode is already present.

This update is locked until the first update is commited or rollbacked.

As opposite if you create an index on test2 you will see the following lock sequence:

first oracle locks table TEST1 is TM Row exclusive mode :

ksqgtl *** TM-000144DB-00000000-00000000-00000000 mode=3 flags=0x400 timeout=21474836 ***

then table TEST2 is TM Row exclusive mode :

ksqgtl *** TM-000144E0-00000000-00000000-00000000 mode=3 flags=0x400 timeout=21474836 ***

test1 partition P2015 in Row exclusive mode :

ksqgtl *** TM-000144DC-00000000-00000000-00000000 mode=3 flags=0x400 timeout=21474836 ***

lock the update row :

ksqgtl *** TX-00010002-00000449-00000000-00000000 mode=6 flags=0x401 timeout=0 ***

and last test1 partition P2016 in Row exclusive mode :

ksqgtl *** TM-000144DD-00000000-00000000-00000000 mode=3 flags=0x400 timeout=21474836 ***
no more share lock and no more recursive statement as we don’t have any child in test2 table.

Advertisements