Yes we have a commit in the procedure and we rollback if an exception is raised. That was one of the first things I looked at. It would have been nice if that was the only problem.
- Babette -----Original Message----- Ryan Sent: Tuesday, September 09, 2003 12:29 AM To: Multiple recipients of list ORACLE-L transaction are you committing in the procedure with the autonomous transaction? do you have an exception block with a rollback? ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, September 09, 2003 12:09 AM > We are getting a strange deadlock problem and I am having trouble > understanding the cause and action. Metalink has not been too useful for > this one. > > We have a procedure eg INS_REC that does an insert into table ABC. The > procedure uses a "pragma autonomous transaction". > > We have a package eg DO_SET_OF_WORK that selects from several tables and > then based on the information selected will call the above procedure, along > with several other procedures that will do inserts or deletes (on DIFFERENT > tables). > > The package does opens a cursor to process all records selected. > > When we use "pragma autonomous transaction" AND commit in the procedure > called, the package dies with an ORA-0060. When the "pragma autonomous > transaction" is removed from the procedure, the package does not get a > deadlock. > > There is only ONE user using this set of tables (in a separate schema from > other users on the system) at the time this is occuring. > > I am puzzled. Another DBA suggested this may be related to FREELISTS, so I > increased them for both the table and indexes that were being reported as > deadlocking but it did not make any difference. > > Anyone have any other suggestions ? > > Thanks > Babette > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Babette Turner-Underwood > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: Ryan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Babette Turner-Underwood INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
