Skip to Main Content
  • Questions

  • New again rules of unindexed foreign keys

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Charles.

Asked: January 14, 2016 - 5:44 am UTC

Last updated: January 30, 2016 - 5:26 am UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

I'm finding that this much authored rule "You do not update the parent table’s unique/primary key value, either purposely or by accident (via a tool)." and other variants in the Oracle documents are not holding true as of 11.2.0.4. I have an unindexed child FK to a parent and simple updates to non-key columns like a monetary value or date on the parent are generating child deadlocks TM-SX-SSX in the Deadlock Graph (1552169.1). The real nasty part is that these tables are VPD, partitioned, sub-partitioned, and segregated across many clients and client A processes are deadlocking with client B. Indexes would fix it as would "ALTER SYSTEM SET "_fix_control" = '5909305:OFF'" based on initial testing. Small test cases of identical DDL and a small set of data do not generate the same blocking issue. We're scaling out rapidly to multiple clients and if the rules have really changed to index all foreign keys or die we need to make some serious adjustments.

Is it really suppose to be just parent updates on the key that cause the child locks?

and Connor said...

From the Concepts guide:

"When both of the following conditions are true, the database acquires a full table lock on the child table:
- No index exists on the foreign key column of the child table.
- A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table.
"

Now, all well and good for me to be quoting the doc :-) but I've not seen any evidence on the Support site, or via Google that suggests there's a major bug in this anywhere currently. However, note that locking behaviour *did* change in a subtle fashion in 11g. See

https://richardfoote.wordpress.com/2010/11/10/oracle11g-new-locking-modes-when-policing-fk-constraints-a-wolf-at-the-door/

for some good info on that.

Similarly, there is the outstanding issue, "Bug 14350611 - SX lock acquired for foreign key when SS should do (Doc ID 14350611.8)" which I assume you've already seen due to your reference to 5909305. That one is fixed in 12c.

Hope this helps.

Rating

  (7 ratings)

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

Comments

Special case for PK update

A reader, January 27, 2016 - 1:48 pm UTC

It turns out that a before insert or update trigger contained

IF :new.fiscal_year_id IS NULL
THEN
:new.fiscal_year_id := v_cfy_id; --current year captured earlier
END IF;

Where fiscal_year_id is part of the PK. It doesn't matter if the code is reachable. It will lock if any of the PK columns are set. The branch never evaluates in our case of an UPDATE because it is existing data in a NOT NULL column. It even locks with this logic.

IF 1 = 2
THEN
:new.fiscal_year_id := v_cfy_id;
END IF;

It locks if it is only in the IF INSERTING section of the trigger and you issue an UPDATE.
Connor McDonald
January 28, 2016 - 3:18 am UTC

That's very interesting. Any chance you could post a complete test case - I'll ask around internally about it.

Special case for PK update

Rajeshwaran, Jeyabal, January 28, 2016 - 6:53 am UTC

It locks if it is only in the IF INSERTING section of the trigger and you issue an UPDATE.

Tried this on 11.2.0.4, but dont see the issue(Full Table lock on Child table). missing something here?

rajesh@ORA11G> set feedback off
rajesh@ORA11G> create table t1(x1 int ,x2 int,y int,
  2     constraint t1_pk primary key(x1,x2));
rajesh@ORA11G> create table t2(x1 int,x2 int,y int,
  2     constraint t2_fk foreign key(x1,x2)
  3     references t1(x1,x2));
rajesh@ORA11G> insert into t1(x1,x2,y) values(1,1,1);
rajesh@ORA11G> insert into t2(x1,x2,y) values(1,1,1);
rajesh@ORA11G> insert into t2(x1,x2,y) values(1,1,2);
rajesh@ORA11G> insert into t1(x1,x2,y) values(2,2,1);
rajesh@ORA11G> commit;
rajesh@ORA11G> set feedback on
rajesh@ORA11G> select * from t1;

        X1         X2          Y
---------- ---------- ----------
         1          1          1
         2          2          1

2 rows selected.

rajesh@ORA11G> select * from t2;

        X1         X2          Y
---------- ---------- ----------
         1          1          1
         1          1          2

2 rows selected.

rajesh@ORA11G>
rajesh@ORA11G> create or replace trigger t1_trig
  2  before insert or update on t1
  3  for each row
  4  declare
  5     l_value int := 55;
  6  begin
  7     if inserting then
  8             if :new.x1 is null then
  9                     :new.x1 := l_value;
 10             end if;
 11     end if;
 12  end;
 13  /

Trigger created.

rajesh@ORA11G> update t1 set y = 55 where x1 = 2;

1 row updated.

rajesh@ORA11G> select * from t1;

        X1         X2          Y
---------- ---------- ----------
         1          1          1
         2          2         55

2 rows selected.

rajesh@ORA11G> declare
  2     pragma autonomous_transaction;
  3  begin
  4     delete from t2
  5     where x1=1 and y = 1;
  6     rollback;
  7  end;
  8  /

PL/SQL procedure successfully completed.

rajesh@ORA11G>

A reader, January 28, 2016 - 4:57 pm UTC

I could never get the small test cases to block. 
-- Assume: Users fklock, t1, t2. 

SET DEFINE OFF;

DROP TABLE fklock.fj13 CASCADE CONSTRAINTS PURGE;
DROP TABLE fklock.fe05 CASCADE CONSTRAINTS PURGE;

CREATE TABLE fklock.fe05
(
   wkccd                VARCHAR2 (2) NOT NULL,
   cowke                VARCHAR2 (3) NOT NULL,
   joser                VARCHAR2 (5) NOT NULL,
   kopcd                VARCHAR2 (1) NOT NULL,
   kowkt                VARCHAR2 (2) NOT NULL,
   r1ldate              DATE NOT NULL,
   r1luser              VARCHAR2 (8),
   r1crusr              VARCHAR2 (8),
   r1crdte              DATE NOT NULL,
   koesd                DATE,
   evnbr                VARCHAR2 (4),
   koarc                VARCHAR2 (2),
   shnbrk               VARCHAR2 (2),
   wcnbrk               VARCHAR2 (4),
   kossd                DATE,
   koscd                DATE,
   koccd                VARCHAR2 (1),
   kopri                VARCHAR2 (1),
   kottl                VARCHAR2 (25),
   kofma                VARCHAR2 (1),
   sched                VARCHAR2 (2),
   kowsc                VARCHAR2 (1),
   koclc                VARCHAR2 (1),
   kosid                DATE,
   korec                VARCHAR2 (3),
   kosyc                VARCHAR2 (6),
   koerv                VARCHAR2 (2),
   koarv                VARCHAR2 (2),
   koacc                VARCHAR2 (1),
   r3dydur              VARCHAR2 (3),
   kolcd                DATE,
   kotby                VARCHAR2 (8),
   znmgr                VARCHAR2 (4),
   kotfl                NUMBER (4),
   kompn                NUMBER (2),
   koerd                DATE,
   koard                DATE,
   koasd                DATE,
   koacd                DATE,
   korsd                DATE,
   korcd                DATE,
   kocld                DATE,
   kojci                VARCHAR2 (1),
   korsr                VARCHAR2 (2),
   korsc                VARCHAR2 (3),
   komsn                VARCHAR2 (4),
   kowmc                VARCHAR2 (1),
   kowic                VARCHAR2 (1),
   kofcc                VARCHAR2 (1),
   konrs                NUMBER (2),
   konrc                NUMBER (2),
   kopcp                NUMBER (3),
   kecmo                NUMBER (12, 2),
   keomo                NUMBER (12, 2),
   kedmi                NUMBER (12, 2),
   kodcl                DATE,
   kecoo                NUMBER (10, 2),
   kecco                NUMBER (10, 2),
   keooo                NUMBER (10, 2),
   keoco                NUMBER (10, 2),
   activity_sa_id       NUMBER NOT NULL,
   fiscal_year_id       NUMBER (4) NOT NULL,
   created_user_sa_id   NUMBER (9),
   lastmod_user_sa_id   NUMBER (9)
)
TABLESPACE users
PARTITION BY LIST (fiscal_year_id)
   SUBPARTITION BY LIST (activity_sa_id)
   (PARTITION
       fy15
       VALUES (2015)
       (
          SUBPARTITION fe05jrmc15 VALUES (8),
          SUBPARTITION fe05nnsy15 VALUES (3),
          SUBPARTITION fe05marmc15 VALUES (5),
          SUBPARTITION fe05phnsy15 VALUES (1),
          SUBPARTITION fe05pnsy15 VALUES (2),
          SUBPARTITION fe05psnsy15 VALUES (4),
          SUBPARTITION fe05sermc15 VALUES (6),
          SUBPARTITION fe05swrmc15 VALUES (7)),
    PARTITION
       fy16
       VALUES (2016)
       (
          SUBPARTITION fe05jrmc16 VALUES (8),
          SUBPARTITION fe05nnsy16 VALUES (3),
          SUBPARTITION fe05marmc16 VALUES (5),
          SUBPARTITION fe05phnsy16 VALUES (1),
          SUBPARTITION fe05pnsy16 VALUES (2),
          SUBPARTITION fe05psnsy16 VALUES (4),
          SUBPARTITION fe05sermc16 VALUES (6),
          SUBPARTITION fe05swrmc16 VALUES (7)))
ROWDEPENDENCIES;

CREATE TABLE fklock.fj13
(
   wkccdi               VARCHAR2 (2) NOT NULL,
   cowkei               VARCHAR2 (3) NOT NULL,
   joseri               VARCHAR2 (5) NOT NULL,
   wkccdf               VARCHAR2 (2) NOT NULL,
   cowkef               VARCHAR2 (3) NOT NULL,
   joserf               VARCHAR2 (5) NOT NULL,
   kolbp                NUMBER (4, 1) NOT NULL,
   komlp                NUMBER (4, 1) NOT NULL,
   r1ldate              DATE NOT NULL,
   r1luser              VARCHAR2 (8),
   r1crusr              VARCHAR2 (8),
   r1crdte              DATE NOT NULL,
   kopcd                VARCHAR2 (1),
   kowkt                VARCHAR2 (2),
   shnbr                VARCHAR2 (2),
   wcnbr                VARCHAR2 (4),
   activity_sa_id       NUMBER NOT NULL,
   fiscal_year_id       NUMBER (4) NOT NULL,
   created_user_sa_id   NUMBER (9),
   lastmod_user_sa_id   NUMBER (9)
)
TABLESPACE users
PARTITION BY LIST (fiscal_year_id)
   SUBPARTITION BY LIST (activity_sa_id)
   (PARTITION
       fy15
       VALUES (2015)
       (
          SUBPARTITION fj13jrmc15 VALUES (8),
          SUBPARTITION fj13nnsy15 VALUES (3),
          SUBPARTITION fj13marmc15 VALUES (5),
          SUBPARTITION fj13phnsy15 VALUES (1),
          SUBPARTITION fj13pnsy15 VALUES (2),
          SUBPARTITION fj13psnsy15 VALUES (4),
          SUBPARTITION fj13sermc15 VALUES (6),
          SUBPARTITION fj13swrmc15 VALUES (7)),
    PARTITION
       fy16
       VALUES (2016)
       (
          SUBPARTITION fj13jrmc16 VALUES (8),
          SUBPARTITION fj13nnsy16 VALUES (3),
          SUBPARTITION fj13marmc16 VALUES (5),
          SUBPARTITION fj13phnsy16 VALUES (1),
          SUBPARTITION fj13pnsy16 VALUES (2),
          SUBPARTITION fj13psnsy16 VALUES (4),
          SUBPARTITION fj13sermc16 VALUES (6),
          SUBPARTITION fj13swrmc16 VALUES (7)))
ROWDEPENDENCIES;

CREATE UNIQUE INDEX fklock.fe05_pk
   ON fklock.fe05 (activity_sa_id,
                   fiscal_year_id,
                   wkccd,
                   cowke,
                   joser,
                   kopcd,
                   kowkt)
   LOCAL;

CREATE INDEX fklock.fj1301_ndx
   ON fklock.fj13 (activity_sa_id,
                   fiscal_year_id,
                   wkccdf,
                   cowkef,
                   joserf,
                   wkccdi,
                   cowkei,
                   joseri)
   LOCAL;

CREATE UNIQUE INDEX fklock.fj13_uk
   ON fklock.fj13 (activity_sa_id,
                   fiscal_year_id,
                   wkccdi,
                   cowkei,
                   joseri,
                   kopcd,
                   kowkt,
                   wkccdf,
                   cowkef,
                   joserf,
                   shnbr,
                   wcnbr)
   LOCAL;

CREATE OR REPLACE FUNCTION fklock.vpd_act_predicate (
   pvar_schema_nm   IN VARCHAR2,
   pvar_object_nm   IN VARCHAR2)
   RETURN VARCHAR2
AS
   /*
   ||===================================================================
   || Description:
   ||
   || Pre:
   ||
   || Post:
   ||
   || Modification History:
   ||===================================================================
   */
   lvar_where_clause   VARCHAR2 (4000) := NULL;
BEGIN
   -- if NOT 'Multi-UIC' mode
   IF USER = 'T1'
   THEN
      lvar_where_clause := 'activity_sa_id = 5';
   -- else, 'Multi-UIC' mode
   ELSE
      lvar_where_clause := 'activity_sa_id = 8';
   END IF;

   RETURN lvar_where_clause;
END vpd_act_predicate;
/

BEGIN
   sys.DBMS_RLS.add_policy (
      object_schema     => 'FKLOCK',
      object_name       => 'FE05',
      policy_name       => 'ACTIVITY_ACCESS_POLICY',
      function_schema   => 'FKLOCK',
      policy_function   => 'VPD_ACT_PREDICATE',
      statement_types   => 'SELECT,INSERT,UPDATE,DELETE',
      policy_type       => DBMS_RLS.dynamic,
      long_predicate    => FALSE,
      update_check      => TRUE,
      static_policy     => FALSE,
      enable            => TRUE);
END;
/

BEGIN
   sys.DBMS_RLS.add_policy (
      object_schema     => 'FKLOCK',
      object_name       => 'FJ13',
      policy_name       => 'ACTIVITY_ACCESS_POLICY',
      function_schema   => 'FKLOCK',
      policy_function   => 'VPD_ACT_PREDICATE',
      statement_types   => 'SELECT,INSERT,UPDATE,DELETE',
      policy_type       => DBMS_RLS.dynamic,
      long_predicate    => FALSE,
      update_check      => TRUE,
      static_policy     => FALSE,
      enable            => TRUE);
END;
/

ALTER TABLE fklock.fe05 ADD (
  CONSTRAINT fe05_pk
  PRIMARY KEY
  (activity_sa_id, fiscal_year_id, wkccd, cowke, joser, kopcd, kowkt)
  USING INDEX LOCAL
  ENABLE VALIDATE);

ALTER TABLE fklock.fj13 ADD (
  CONSTRAINT fj13_uk
  UNIQUE (activity_sa_id, fiscal_year_id, wkccdi, cowkei, joseri, kopcd, kowkt, wkccdf, cowkef, joserf, shnbr, wcnbr)
  USING INDEX LOCAL
  ENABLE VALIDATE);

ALTER TABLE fklock.fj13 ADD (
  CONSTRAINT fj1303_fk
  FOREIGN KEY (activity_sa_id, fiscal_year_id, wkccdi, cowkei, joseri, kopcd, kowkt)
  REFERENCES fklock.fe05 (activity_sa_id,fiscal_year_id,wkccd,cowke,joser,kopcd,kowkt)
  ENABLE VALIDATE);

ALTER TABLE fklock.fj13 ADD (
  CONSTRAINT fj1304_fk
  FOREIGN KEY (activity_sa_id, fiscal_year_id, wkccdf, cowkef, joserf, kopcd, kowkt)
  REFERENCES fklock.fe05 (activity_sa_id,fiscal_year_id,wkccd,cowke,joser,kopcd,kowkt)
  ENABLE VALIDATE);

GRANT SELECT, UPDATE ON fklock.fe05 TO t1;

GRANT SELECT, UPDATE ON fklock.fj13 TO t1;

GRANT SELECT, UPDATE ON fklock.fe05 TO t2;

GRANT SELECT, UPDATE ON fklock.fj13 TO t2;

INSERT INTO fklock.fe05 (wkccd,
                         cowke,
                         joser,
                         kopcd,
                         kowkt,
                         r1ldate,
                         r1luser,
                         r1crusr,
                         r1crdte,
                         koesd,
                         evnbr,
                         koarc,
                         shnbrk,
                         wcnbrk,
                         kossd,
                         koscd,
                         koccd,
                         kopri,
                         kottl,
                         kofma,
                         sched,
                         kowsc,
                         koclc,
                         kosid,
                         korec,
                         kosyc,
                         koerv,
                         koarv,
                         koacc,
                         r3dydur,
                         kolcd,
                         kotby,
                         znmgr,
                         kotfl,
                         kompn,
                         koerd,
                         koard,
                         koasd,
                         koacd,
                         korsd,
                         korcd,
                         kocld,
                         kojci,
                         korsr,
                         korsc,
                         komsn,
                         kowmc,
                         kowic,
                         kofcc,
                         konrs,
                         konrc,
                         kopcp,
                         kecmo,
                         keomo,
                         kedmi,
                         kodcl,
                         kecoo,
                         kecco,
                         keooo,
                         keoco,
                         activity_sa_id,
                         fiscal_year_id,
                         created_user_sa_id,
                         lastmod_user_sa_id)
     VALUES ('16',
             '67D',
             '51111',
             'A',
             '01',
             TO_DATE ('01/20/2016 04:55:25', 'MM/DD/YYYY HH24:MI:SS'),
             NULL,
             NULL,
             TO_DATE ('02/12/2015 14:29:45', 'MM/DD/YYYY HH24:MI:SS'),
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             '0',
             '0',
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             TO_DATE ('01/20/2016 04:55:25', 'MM/DD/YYYY HH24:MI:SS'),
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             0,
             NULL,
             NULL,
             NULL,
             8,
             2015,
             1996,
             NULL);

INSERT INTO fklock.fe05 (wkccd,
                         cowke,
                         joser,
                         kopcd,
                         kowkt,
                         r1ldate,
                         r1luser,
                         r1crusr,
                         r1crdte,
                         koesd,
                         evnbr,
                         koarc,
                         shnbrk,
                         wcnbrk,
                         kossd,
                         koscd,
                         koccd,
                         kopri,
                         kottl,
                         kofma,
                         sched,
                         kowsc,
                         koclc,
                         kosid,
                         korec,
                         kosyc,
                         koerv,
                         koarv,
                         koacc,
                         r3dydur,
                         kolcd,
                         kotby,
                         znmgr,
                         kotfl,
                         kompn,
                         koerd,
                         koard,
                         koasd,
                         koacd,
                         korsd,
                         korcd,
                         kocld,
                         kojci,
                         korsr,
                         korsc,
                         komsn,
                         kowmc,
                         kowic,
                         kofcc,
                         konrs,
                         konrc,
                         kopcp,
                         kecmo,
                         keomo,
                         kedmi,
                         kodcl,
                         kecoo,
                         kecco,
                         keooo,
                         keoco,
                         activity_sa_id,
                         fiscal_year_id,
                         created_user_sa_id,
                         lastmod_user_sa_id)
     VALUES ('38',
             '67D',
             '51101',
             'A',
             '01',
             TO_DATE ('02/12/2015 14:22:41', 'MM/DD/YYYY HH24:MI:SS'),
             NULL,
             NULL,
             TO_DATE ('02/12/2015 12:51:52', 'MM/DD/YYYY HH24:MI:SS'),
             TO_DATE ('02/12/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
             NULL,
             NULL,
             '72',
             'WHR1',
             NULL,
             NULL,
             NULL,
             NULL,
             '#1A GTM BI',
             'F',
             NULL,
             '0',
             '0',
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             0,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             8,
             2015,
             1996,
             1996);

INSERT INTO fklock.fe05 (wkccd,
                         cowke,
                         joser,
                         kopcd,
                         kowkt,
                         r1ldate,
                         r1luser,
                         r1crusr,
                         r1crdte,
                         koesd,
                         evnbr,
                         koarc,
                         shnbrk,
                         wcnbrk,
                         kossd,
                         koscd,
                         koccd,
                         kopri,
                         kottl,
                         kofma,
                         sched,
                         kowsc,
                         koclc,
                         kosid,
                         korec,
                         kosyc,
                         koerv,
                         koarv,
                         koacc,
                         r3dydur,
                         kolcd,
                         kotby,
                         znmgr,
                         kotfl,
                         kompn,
                         koerd,
                         koard,
                         koasd,
                         koacd,
                         korsd,
                         korcd,
                         kocld,
                         kojci,
                         korsr,
                         korsc,
                         komsn,
                         kowmc,
                         kowic,
                         kofcc,
                         konrs,
                         konrc,
                         kopcp,
                         kecmo,
                         keomo,
                         kedmi,
                         kodcl,
                         kecoo,
                         kecco,
                         keooo,
                         keoco,
                         activity_sa_id,
                         fiscal_year_id,
                         created_user_sa_id,
                         lastmod_user_sa_id)
     VALUES ('38',
             'TN1',
             'NSS01',
             'A',
             '02',
             TO_DATE ('01/12/2015 10:52:02', 'MM/DD/YYYY HH24:MI:SS'),
             NULL,
             NULL,
             TO_DATE ('01/12/2015 10:18:27', 'MM/DD/YYYY HH24:MI:SS'),
             TO_DATE ('01/12/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
             NULL,
             NULL,
             '13',
             'QB1Q',
             NULL,
             NULL,
             NULL,
             NULL,
             'SHIP LVL R',
             'F',
             NULL,
             '0',
             '0',
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             0,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             5,
             2015,
             3172,
             3172);

INSERT INTO fklock.fe05 (wkccd,
                         cowke,
                         joser,
                         kopcd,
                         kowkt,
                         r1ldate,
                         r1luser,
                         r1crusr,
                         r1crdte,
                         koesd,
                         evnbr,
                         koarc,
                         shnbrk,
                         wcnbrk,
                         kossd,
                         koscd,
                         koccd,
                         kopri,
                         kottl,
                         kofma,
                         sched,
                         kowsc,
                         koclc,
                         kosid,
                         korec,
                         kosyc,
                         koerv,
                         koarv,
                         koacc,
                         r3dydur,
                         kolcd,
                         kotby,
                         znmgr,
                         kotfl,
                         kompn,
                         koerd,
                         koard,
                         koasd,
                         koacd,
                         korsd,
                         korcd,
                         kocld,
                         kojci,
                         korsr,
                         korsc,
                         komsn,
                         kowmc,
                         kowic,
                         kofcc,
                         konrs,
                         konrc,
                         kopcp,
                         kecmo,
                         keomo,
                         kedmi,
                         kodcl,
                         kecoo,
                         kecco,
                         keooo,
                         keoco,
                         activity_sa_id,
                         fiscal_year_id,
                         created_user_sa_id,
                         lastmod_user_sa_id)
     VALUES ('16',
             'TN1',
             'NSS34',
             'A',
             '02',
             TO_DATE ('01/20/2016 04:55:58', 'MM/DD/YYYY HH24:MI:SS'),
             NULL,
             NULL,
             TO_DATE ('01/12/2015 10:18:28', 'MM/DD/YYYY HH24:MI:SS'),
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             '0',
             '0',
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             TO_DATE ('01/20/2016 04:55:58', 'MM/DD/YYYY HH24:MI:SS'),
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             0,
             NULL,
             NULL,
             NULL,
             5,
             2015,
             3172,
             NULL);

COMMIT;

INSERT INTO fklock.fj13 (wkccdi,
                         cowkei,
                         joseri,
                         wkccdf,
                         cowkef,
                         joserf,
                         kolbp,
                         komlp,
                         r1ldate,
                         r1luser,
                         r1crusr,
                         r1crdte,
                         kopcd,
                         kowkt,
                         shnbr,
                         wcnbr,
                         activity_sa_id,
                         fiscal_year_id,
                         created_user_sa_id,
                         lastmod_user_sa_id)
     VALUES ('38',
             '67D',
             '51101',
             '16',
             '67D',
             '51111',
             100,
             100,
             TO_DATE ('02/12/2015 14:52:31', 'MM/DD/YYYY HH24:MI:SS'),
             NULL,
             NULL,
             TO_DATE ('02/12/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
             'A',
             '01',
             NULL,
             NULL,
             8,
             2015,
             1996,
             2623);

INSERT INTO fklock.fj13 (wkccdi,
                         cowkei,
                         joseri,
                         wkccdf,
                         cowkef,
                         joserf,
                         kolbp,
                         komlp,
                         r1ldate,
                         r1luser,
                         r1crusr,
                         r1crdte,
                         kopcd,
                         kowkt,
                         shnbr,
                         wcnbr,
                         activity_sa_id,
                         fiscal_year_id,
                         created_user_sa_id,
                         lastmod_user_sa_id)
     VALUES ('38',
             'TN1',
             'NSS01',
             '16',
             'TN1',
             'NSS34',
             100,
             100,
             TO_DATE ('01/29/2015 13:15:11', 'MM/DD/YYYY HH24:MI:SS'),
             NULL,
             NULL,
             TO_DATE ('01/29/2015 13:15:11', 'MM/DD/YYYY HH24:MI:SS'),
             'A',
             '02',
             NULL,
             NULL,
             5,
             2015,
             3172,
             3172);

COMMIT;

CREATE OR REPLACE TRIGGER fklock.fe05_cu
   BEFORE INSERT OR UPDATE
   ON fklock.fe05
   FOR EACH ROW
DECLARE
   v_user_sa_id   NUMBER := 0;
   v_cfy_id       NUMBER (4) := 2016;
BEGIN
   SELECT SYS_CONTEXT ('APP_USER_CTX', 'USER') INTO v_user_sa_id FROM DUAL;

   IF 1 = 2
   THEN
      :new.fiscal_year_id := v_cfy_id;
   END IF;

   :new.r1ldate := CURRENT_DATE;
   :new.lastmod_user_sa_id := v_user_sa_id;

   IF INSERTING
   THEN
      :new.r1crdte := CURRENT_DATE;
      :new.created_user_sa_id := v_user_sa_id;
   END IF;
END;
/

/*
2. SQLPLUS t1 and run without commit
UPDATE fklock.fe05
   SET koacd = CURRENT_DATE, kecoo = 0
 WHERE     fiscal_year_id = 2015
       AND wkccd = '16'
       AND cowke = '67D'
       AND joser = '51111'
       AND kopcd = 'A'
       AND kowkt = '01';

3.  SQLPLUS t2 and run...it is blocked.
UPDATE fklock.fe05  
   SET koacd = CURRENT_DATE, kecoo = 0
 WHERE     fiscal_year_id = 2015
       AND wkccd = '16'
       AND cowke = 'TN1'
       AND joser = 'NSS34'
       AND kopcd = 'A'
       AND kowkt = '02';
*/

Chris Saxon
January 29, 2016 - 12:17 pm UTC

Well this has been a fascinating investigation ! And thanks for the test case - that was awesome.

Here's a reproducible test case of smaller scale, which also shows that we can rule out partitioning, VPD, and multiple users.

drop table PAR cascade constraints purge;
drop table CHD cascade constraints purge;

create table PAR ( a int, f int , w int, data varchar2(10));
alter table PAR add constraint PAR_PK primary key ( a,f,w)  using index;

insert into PAR values (8, 2015, 16, 'x' );
insert into PAR values (8, 2015, 38, 'x' );
insert into PAR values (5, 2015, 38, 'x' );
insert into PAR values (5, 2015, 16, 'x' );

commit;

create table CHD ( a int, f int, w1 int, w2 int, h int, data varchar2(10) );

alter table CHD add constraint CHD_PK primary key (a,f,w1,h);
alter table CHD add constraint CHD_FK1 foreign key (a,f,w1) references par(a,f,w);
alter table CHD add constraint CHD_FK2 foreign key (a,f,w2) references par(a,f,w);


insert into CHD values (8,2015,38,16,100,'x');
insert into CHD values (8,2015,38,16,101,'x');
commit;

create or replace 
trigger PAR_TRG 
before insert or update on PAR
for each row
declare
  x int;
begin
  if 1=2 then
     :new.f := x;
  end if;
end;
/

Session 1:
update PAR set data = 'z' where f = 2015 and w = 16 and a = 8;

Session 2:
update PAR set data = 'z' where f = 2015 and w = 16 and a = 5;


If 'create index' commands are commented out, then we get the block. If they are present, then the update proceeds as expected.

The *combination* of things that appears to be required to create this situation appears to be:

1) 2 foreign keys back to the parent (if you leave out either one, it doesnt block), which probably means the general case, ">1" foreign keys back to the parent.

2) the trigger has the *potential* to change a primary key column in the parent.

You can see from the trigger (just like yours) that this primary key change will actually never transpire, but I dont think we're smart enough to deduce that. The moment we see ":new.prim_key_col" as a potential assignment target, we're assuming the worst. For example, if I switch it around ( x := :new.pk ) then all is well.

Is this a bug ? To be honest, I dont know - I'm guessing its more a "better to be safe than sorry" process we're following. The resolution/workaround is of course, as per normal - add those indexes.


No Deadlock/blocking in 11.2.0.4

Rajeshwaran, Jeyabal, January 29, 2016 - 12:55 pm UTC

Connor,

Since this question was raised under 11.2.0.4 category, i tested your script as such in 11g database - but i dont received any Deadlock/blocking issue.

rajesh@ORA11G> set feedback off
rajesh@ORA11G> drop table PAR cascade constraints purge;
rajesh@ORA11G> drop table CHD cascade constraints purge;
rajesh@ORA11G> create table PAR ( a int, f int , data varchar2(10));
rajesh@ORA11G> alter table PAR add constraint PAR_PK primary key ( a,f)  using index;
rajesh@ORA11G> insert into PAR values (1,1,'x');
rajesh@ORA11G> insert into PAR values (1,2,'y');
rajesh@ORA11G> insert into PAR values (3,2,'x');
rajesh@ORA11G> insert into PAR values (3,3,'y');
rajesh@ORA11G> commit;
rajesh@ORA11G> create table CHD ( a int, f int, g int, h int, data varchar2(10) , f2 int);
rajesh@ORA11G> alter table CHD add constraint CHD_FK1 foreign key (a,f) references par(a,f);
rajesh@ORA11G> alter table CHD add constraint CHD_FK2 foreign key (a,f2) references par(a,f);
rajesh@ORA11G> insert into CHD values (1,1,10,10,'a',2);
rajesh@ORA11G> insert into CHD values (3,2,20,20,'b',3);
rajesh@ORA11G> commit;
rajesh@ORA11G> create or replace
  2  trigger PAR_TRG
  3  before insert or update on PAR
  4  for each row
  5  declare
  6    x int;
  7  begin
  8    if 1=2 then
  9       :new.f := x;
 10    end if;
 11  end;
 12  /
rajesh@ORA11G> set feedback on
rajesh@ORA11G>
rajesh@ORA11G> select * from par ;

         A          F DATA
---------- ---------- ----------
         1          1 x
         1          2 y
         3          2 x
         3          3 y

4 rows selected.

rajesh@ORA11G> select * from chd ;

         A          F          G          H DATA               F2
---------- ---------- ---------- ---------- ---------- ----------
         1          1         10         10 a                   2
         3          2         20         20 b                   3

2 rows selected.

rajesh@ORA11G> update PAR set data = 'z' where a = 1 and f = 1;

1 row updated.

rajesh@ORA11G> declare
  2     pragma autonomous_transaction;
  3  begin
  4     update PAR set data = 'z' where a = 1 and f = 2;
  5     rollback;
  6  end;
  7  /

PL/SQL procedure successfully completed.

rajesh@ORA11G> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

5 rows selected.

rajesh@ORA11G>

No Deadlock/blocking in 11.2.0.4

Rajeshwaran, Jeyabal, January 29, 2016 - 1:04 pm UTC

Well, you guys now changed the DDL and DML in the above "Original" followup.
Connor McDonald
January 30, 2016 - 5:24 am UTC

That was my pebkac - I cut/pasted the wrong demo script.

Dont work in case of non-composite PK/FK ?

Rajeshwaran, Jeyabal, January 29, 2016 - 1:31 pm UTC

Connor - Don't work in case of non-composite PK/FK ?

rajesh@ORA11G>
rajesh@ORA11G> set feedback off
rajesh@ORA11G> create table p(x1 int primary key,data varchar2(10));
rajesh@ORA11G> create table c(x1 references p,x2 references p,data varchar2(10));
rajesh@ORA11G> insert into p values(1,'Data1');
rajesh@ORA11G> insert into p values(2,'Data2');
rajesh@ORA11G> insert into p values(3,'Data3');
rajesh@ORA11G> insert into p values(4,'Data4');
rajesh@ORA11G> insert into c values(1,2,'Child1');
rajesh@ORA11G> insert into c values(3,4,'Child1');
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> update p set data ='XX' where x1=1;

1 row updated.

rajesh@ORA11G> declare
  2     pragma autonomous_transaction;
  3  begin
  4     update p set data ='XX' where x1=2;
  5     rollback;
  6  end;
  7  /

PL/SQL procedure successfully completed.

rajesh@ORA11G>

Connor McDonald
January 30, 2016 - 5:26 am UTC

Yes, it certainly seems to be a specific set of circumstances.

Dont work in case of non-composite PK/FK ?

Rajeshwaran, Jeyabal, January 29, 2016 - 2:06 pm UTC

I think, i founded it now. In addition to "above" specified conditions, these should be added up

In case of "Deadlock" to kick, the child table should have
a) more than one un-indexed FK should be available.
b) PK should be defined and it should inherit any one of the un-indexed FK columns to it. (say in below case,
(i) if PK defined only on C(X) then no-deadlock,
(ii) if PK defined as C(X1,X) or C(X2,X) then deadlock occurs - specifically un-indexed FK should be on leading edge of PK definition
(iii) in case if PK defined as C(X,X1) or C(X,X2) then no-deadlock - that is un-indexed FK on non-leading edge of PK definition

rajesh@ORA11G> set feedback off
rajesh@ORA11G> drop table C purge;
rajesh@ORA11G> drop table P purge;
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> update p set data ='XX' where x1=1;

1 row updated.

rajesh@ORA11G> declare
  2     pragma autonomous_transaction;
  3  begin
  4     update p set data ='XX' 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> rollback;

Rollback complete.

rajesh@ORA11G>
rajesh@ORA11G>


Even on 12c(12.1.0.2) this is reproducible.

rajesh@ORA12C> set feedback off
rajesh@ORA12C> drop table C purge;
rajesh@ORA12C> drop table P purge;
rajesh@ORA12C> create table p(x1 int primary key,data varchar2(10));
rajesh@ORA12C> create table c(x int, x1 references p,x2 references p,
  2                                     data varchar2(10),
  3                                     constraint c_pk primary key(x2,x));
rajesh@ORA12C> insert into p values(1,'Data1');
rajesh@ORA12C> insert into p values(2,'Data2');
rajesh@ORA12C> commit;
rajesh@ORA12C> 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@ORA12C> set feedback on
rajesh@ORA12C>
rajesh@ORA12C> update p set data ='XX' where x1=1;

1 row updated.

rajesh@ORA12C> declare
  2     pragma autonomous_transaction;
  3  begin
  4     update p set data ='XX' 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@ORA12C> rollback;

Rollback complete.

rajesh@ORA12C>
rajesh@ORA12C>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.