Home -> Mailing Lists -> Oracle-L -> Locking behavior of compound triggers

Locking behavior of compound triggers

From: Ryan January <rjjanuary_at_multiservice.com>
Date: Wed, 12 Sep 2012 16:19:20 -0500
Message-ID: <5050FC58.80001_at_multiservice.com> 





Hello all,


We recently completed an application upgrade which has caused 
significant enq: TM contention during a number of batch processes. A 
part of this upgrade was a migration away from individual 
insert/update/delete triggers on key tables, moving to a common compound 
trigger.


I've isolated the cause and duplicated this behavior in the code at the 
bottom of this email, however I don't fully understand why it's 
occurring. All tests were performed on a single instance of a 3 node RAC 
11.2.0.3.3 DB on RHEL5.


 >>I have two tables:




parent

     parent_id: INT, PK
     name:      VARCHAR

child
     child_id:    INT, PK
     parent_id:   INT, FK TO PARENT.PARENT_ID





 >>with the following values:




SQL> select * from parent;



  PARENT_ID NAME


---------- ------------------------------
        100 hello
        200 world





SQL> select * from child;



   CHILD_ID  PARENT_ID



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

        101      100
        200      200





When a new parent record is created there is a high likelihood that the 
primary key value will be inserted null.  If the value is null, the 
trigger substitutes the next value from a sequence. The update and 
delete portions of the trigger I've found to be insignificant and are 
not included.




 >>as a test, I perform the following statements in separate sessions:




 >>SESSION1:



SQL> update child set child_id = 101 where child_id = 100;



1 row updated.



SQL> _at_mylocks;



        SID OBJECT_NAME OBJECT_TYPE STATUS    MODE_HELD

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


        267 CHILD       TABLE       Global    Row-X (SX)




 >>SESSION2:



SQL> update parent set name = 'testme' where 1 = 2; -- note that this is 
an UPDATE statement, and should always update no rows



(AT THIS POINT SESSION2 HANGS)




 >>SESSION1:



SQL> _at_mylocks;



        SID OBJECT_NAME OBJECT_TYPE STATUS    MODE_HELD

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

         52 CHILD       TABLE       Blocking    None
         52 CHILD       TABLE       Not Blocking None
         52 PARENT      TABLE       Global    Row-X (SX)
        267 CHILD       TABLE       Blocking    Row-X (SX)
        267 CHILD       TABLE       Not Blocking Row-X (SX)






 From what I've determined,  session2 is blocked due to an attempt to 
obtain a lock on the child table. This lock will cause a wait due to 
session1's uncommitted change.


Using a little "guess and check" I've determined this behavior is 
triggered by the existence of a "new.primary_key" reference in *any* 
portion of the compound trigger.  This includes one that is not 
executed. If we're requiring a lock I can understand the wait 
(considering the FK'd column in the child table is unindexed in this test.)
What I can't even begin to comprehend is why the lock needs to be 
obtained to begin with. In my example I am executing the code path for 
an update, and the reference requiring the lock is only executed on 
insert. This code also appeared to work well as individual triggers.  
This behavior only appears to manifest itself when using compound triggers.



Short term, we have added additional indexes to FK'd columns to reduce 
contention for some immediate relief.  The reason they were unindexed to 
begin with was that the PK of the parent table is almost never updated.  
As such we would likely never benefit from the index overhead.



Long term, I would like to understand why this lock occurs at all.  I 
don't feel that the index is a full solution since we're pushing our 
locks to the row level, rather than eliminating them completely.  If we 
run into an issue with a highly skewed parent_id value the contention 
could surface again.


If anyone could help explain why the locks are occurring, if it is to be 
expected, or if it's a bug I would greatly appreciate it. So far I've 
had 2 SR's open with Oracle and neither have helped us answer these 
questions.



Thank you,


Ryan






    
  
  •   test tables

    
   CREATE TABLE PARENT(

     PARENT_ID NUMBER,

     NAME VARCHAR2(30),

   constraint "PARENT_PK" PRIMARY KEY (PARENT_ID)
    );






   CREATE TABLE CHILD(


     CHILD_ID NUMBER,
     PARENT_ID NUMBER,




   CONSTRAINT CHILD_PK PRIMARY KEY (CHILD_ID),

   CONSTRAINT CHILD_PARENT_FK FOREIGN KEY (PARENT_ID) REFERENCES 

PARENT(PARENT_ID)



    );





    
  
  •   supporting seq for parent table

    
   CREATE SEQUENCE PARENT_SEQ START WITH 1 INCREMENT BY 1;

/

    
  
  •   create offending trigger

    
create or replace TRIGGER PARENT_CT
   FOR INSERT OR UPDATE OR DELETE ON "PARENT" COMPOUND TRIGGER

   BEFORE EACH ROW IS

   BEGIN

       IF  INSERTING THEN

           IF  :new.parent_id IS NULL THEN
               :new.parent_id := PARENT_SEQ.NEXTVAL;
           END IF;

       ELSIF   UPDATING THEN

           IF  :new.name = 'ryan' then
               :new.name := 'nayr';
           END IF;

       END IF;

   END BEFORE EACH ROW;

END PARENT_CT;

/


    





    
  
  •   insert dummy values

    
insert into parent (parent_id, name) values (100, 'hello');
insert into parent (parent_id, name) values (200, 'world');
insert into child (child_id, parent_id) values (100, 100);
insert into child (child_id, parent_id) values (200, 200);



    





This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email.





--
http://www.freelists.org/webpage/oracle-l


Received on Wed Sep 12 2012 - 16:19:20 CDT












Original text of this message