Yet another rule for un-indexed foreign keys

Un-indexed foreign keys are the number one reason for deadlocks in database.  Here is a scenario observed recently in a customer code base.

Here is the criteria for the test case.

• The child table should have more than one un-indexed foreign key to parent table.
• Primary key on child table should have *any* one of those un-indexed foreign key column on the leading edge of primary key definition. 
• A trigger has the *potential* to change a primary key column in parent.  (You can see from the trigger that this primary key will never *change*, but I don’t think optimizer is smart enough to deduce that, the moment the database see “:new.primary_key_col” as a potential assignment target, it assumes the worst)

Here is the test case to demonstrate that.

rajesh@ORA11G> set feedback off

rajesh@ORA11G> create table p(x1 int primary key,data varchar2(10));

rajesh@ORA11G> create table c(x int, x1 references p,x2 references p,

  2                     data varchar2(10),

  3                     constraint c_pk primary key(x2,x));

rajesh@ORA11G> insert into p values(1,'Data1');

rajesh@ORA11G> insert into p values(2,'Data2');

rajesh@ORA11G> commit;

rajesh@ORA11G> create or replace trigger p_trig

  2  before insert or update on P

  3  for each row

  4  declare

  5    k int;

  6  begin

  7    if 1=2 then

  8     :new.x1 := k ;

  9    end if;

 10  end;

 11  /

rajesh@ORA11G> set feedback on

rajesh@ORA11G>

rajesh@ORA11G> select * from p;

        X1 DATA

---------- ----------

         1 Data1

         2 Data2

2 rows selected.

rajesh@ORA11G> select * from c;

no rows selected

When we tried to update the parent table (a non-key column) which in-turn executed the trigger and looking at the code base  “:new.primary_key_column = k” primary key as assignment target which is bounded inside a False Fake statement  1=2 , will never be True in this case, so the primary key doesn’t change.

But optimizer and Pl/SQL compiler is not able to deduce that and places a Full table lock on the child table.

rajesh@ORA11G> update p set data ='XX' where x1=1;

1 row updated.

rajesh@ORA11G> select t2.object_name, t2.object_type, t1.locked_mode

  2  from v$locked_object t1,

  3       user_objects t2

  4  where t1.object_id = t2.object_id ;

OBJECT_NAME                    OBJECT_TYPE         LOCKED_MODE

------------------------------ ------------------- -----------

C                              TABLE                         3

P                              TABLE                         3

2 rows selected.

Remember lock_mode=3 is Row Exclusive Table lock (SX) is more restrictive than a share table lock. Only one transaction at a time can acquire an SX lock on a given table. An SX lock held by a transaction allows other transactions to query the table (except for SELECT ... FOR UPDATE) but not to update the table.

When a different transaction tries to place Full Table lock on child table will lead to dead lock.

rajesh@ORA11G> declare

  2     pragma autonomous_transaction;

  3  begin

  4     update p set data ='YY' where x1=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>

Is this a bug? To be honest, I don’t know. The resolution/workaround is of course, as per normal - add those indexes on foreign key columns.

Addendum:

o   This behavior is still reproducible on the latest version of oracle (12.1.0.2)

o   If primary key is not defined on the child table, then no deadlock.

o   If the primary key on the child table don’t include the foreign key column then no deadlock.  ( that is if primary key is defined on C(X) then no deadlock)

o   If the primary key on the child table don’t include the foreign key column in its leading edge then no deadlock ( that is if primary key is defined on C(X,X2) or C(X,X3) then no deadlock, instead if primary  key is defined as C(X2,X) or C(X3,X) then deadlock).