RE: Questions for Oracle list

> -----Original Message-----
> From: John Dunn [SMTP:john.dunn_at_sefas.co.uk]
> Sent: Tuesday, February 11, 2003 12:04 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Questions for Oracle list
>
>
> > Question 1)
> >
> > I've got a update trigger on a table that updates a record in another
> > table.
> >
> > i.e.
> >
> > CREATE OR REPLACE TRIGGER table1_trigger_update
> > BEFORE UPDATE ON table1
> > FOR EACH ROW
> > BEGIN
> >
> > UPDATE
> > Table2
> > SET
> > Table2.version = Table2.version + 1
> > WHERE
> > Table2.job_id = :new.job_id;
> >
> > END;
> >
> > The question is - at what stage does Oracle attempt to get a lock in the
> > table referenced in the trigger?

        [Sony] when you try to update table1 but your procedure will lock table2 first.
> Does Oracle attempt to lock the Table2
> > row at the same time as locking the Table1 row

        [Sony] it is not in the same time, oracle will lock table2 first then table1.
> , or does it attempt to lock
> > when the COMMIT is finally done?

        [Sony] when you do COMMIT at the last of your procedure, oracle will release your table1 and table2.
> >
> > Question 2)
> >
> > When there is a deadlock situation is it the bit of SQL that starts the
> > deadlock that gets the exception? This is the way I thought it worked,
> but
> > I'm sure I've seen different bits of code getting the exception in
> > identical circumstances.

        [Sony] COMMIT is the answer and deadlock would not be happen.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: John Dunn
> INET: john.dunn_at_sefas.co.uk
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sony kristanto
  INET: Sony_at_polyfincanggih.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).