Re: Mutating triggers & autonomous transactions

From: <roelof.streekstra_at_gmail.com>
Date: Wed, 22 Apr 2009 19:41:32 -0700 (PDT)
Message-ID: <a5b3e952-39b0-41d0-8c10-6c0e81ca17da_at_k38g2000yqh.googlegroups.com>



On Apr 22, 8:56 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Apr 22, 3:23 pm, roelof.streeks..._at_gmail.com wrote:
>
>
>
> > On Apr 22, 4:16 pm, Shakespeare <what..._at_xs4all.nl> wrote:
>
> > > roelof.streeks..._at_gmail.com schreef:
>
> > > > Hi all,
>
> > > > I have an application that uses the pragma autonomous transactions
> > > > inside trigger code to prevent the mutating triggers problem.
> > > > I do not think you can use pragma autonomous transactions do this.
> > > > Because an autonomous transactions is a new transaction with a new
> > > > SCN.
> > > > To me oracle will execute the blow code like this:
>
> > > > Step 1 Create new orderline get SCN
> > > > Step 2 trigger fires
> > > > Step 3 inside trigger call function
> > > > Step 4 function is autonomous ei get second SCN
>
> > > > In my opinion time has elapsed between step 1 and step 4 so some other
> > > > Process Could have gotten in between in those steps and change the
> > > > data
> > > > in the underlying tables.
>
> > > > I think if you have this problem of mutating tables you solve it by
> > > > creating
> > > > Three triggers on the underlying table.
> > > >    Trigger one is a before statement trigger to cleans out a temp
> > > >    Table that stores the changed records of the table you are
> > > > changing.
> > > >    Trigger Two is a after row trigger that fills up the temp table
> > > >    With change records.
> > > >    Trigger three is a after statement trigger that does the work of
> > > >    Calculating the total price of the order by using the temp table
> > > >    and the underlying transactional table.
>
> > > > If you do the three trigger approach you never have two SCN’s so you
> > > > do
> > > > not have any problems with your read consistency ever.
>
> > > > Is my reasoning true/valid?
>
> > > > This approach of three triggers a learned longtime ago when oracle was
> > > > still in the 7 series.
>
> > > > Thanks Roelof
>
> > > > PS I think that triggers should only be used for auditing and for
> > > > implementing certain kind of check constraints.
>
> > > > Current Code pattern is like this:
> > > >     table Order_header ( id, cust#, TotalOrderAmount)
> > > >     table Order_line (id,order_header_id,product_name,price)
> > > >     trigger on T1 before row on delete, update, insert called T1_BRDUI
> > > >     function CalcTotalOrderAmount( p_order_header_id)
>
> > > > trigger body T1_BRDUI
> > > > begin
> > > > ....
> > > >    UPDATE Order_header SET TotalOrderAmount=CalcTotalOrderAmount( NVL
> > > > (:new.order_header_id,:old.order_header_id)
> > > > WHERE id=NVL(:new.order_header_id,:old.order_header_id);
> > > > ...
> > > > end;
>
> > > > Function code:
> > > > FUNCTION CalcTotalOrderAmount( p_ order_header_id IN NUMBER)
> > > >   RETURN NUMBER
> > > > IS
> > > >   RETVAL   NUMBER;
> > > >   PRAGMA AUTONOMOUS_TRANSACTION;
> > > > BEGIN
> > > >   SELECT SUM(price)
> > > >     INTO RETVAL
> > > >     FROM Order_line
> > > >    WHERE order_header_id = p_order_header_id;
>
> > > >   RETURN retval;
> > > > END CalcTotalOrderAmount;
>
> > > > My solution would be
>
> > > I think you're right about this.
>
> > > But it looks like something is missing at the end of your post.
> > > If this is all about a total order amount, then this can be done more
> > > simply. Just add the amount of an inserted row to the total; you don't
> > > have to recalc the total every time. So you don't need to sum the detail
> > > records to calculate the total. So you won't have a mutating table
> > > problem at all.
> > > Same can be done with updates and deletes. It's all a matter of good
> > > book keeping.
>
> > > An other option might be (not sure about that): lock the order header
> > > row before updating and calculating the total of the details. A second
> > > process can not get this lock so will not fire in the period you mentioned.
>
> > > Shakespeare
>
> > I understand what you say here but my question is can you use
> >    autonomous transactions
> > in triggers to prevent the mutating table problem.
>
> Search for the word mutating at asktom.oracle.com, and read his
> books.  And look for "red flags" here:  http://tkyte.blogspot.com/2008/05/another-of-day.html
>
> jg
> --
> _at_home.com is bogus.http://blog.wired.com/27bstroke6/2009/04/pins.html

Thanks Joel,

Thanks for pointing me to the blog entry again. I read it a while ago but then it when to /dev/null :( because I had to do to much other stuff.
But it definitely looks like that my assumption of two different SCN's is correct because an autonomous transaction create another session as well. The problem I have know is convincing folks that they have a bug in there code. Because when they functionally tested it (single user mode no concurrency) it worked well.

Most developers do not understand oracles read consistency model very well :(. In this code base they have done the autonomous transaction trick over and over again.

Thanks roelof. Received on Wed Apr 22 2009 - 21:41:32 CDT

Original text of this message