Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Deadlock problem

Re: Deadlock problem

From: Jared Still <jkstill_at_gmail.com>
Date: Mon, 8 May 2006 10:49:50 -0700
Message-ID: <bf46380605081049h565ac248qf404fa9d24ee4bf4@mail.gmail.com>


On 5/8/06, Alessandro Vercelli <alever_at_libero.it> wrote:

>

> 1. Is it correct an insert/update without a commit into a sql package? If
> yes, when are the inserted/updated data commited?

That really depends on the app.

eg. A stored procedure is used to update the data, the user has to push a button to do the commit, or another button to abandon the transaction and rollback.

Possible problems with that approach is that the user may start the transaction
and then go to lunch, leave N row locked.

This however does not cause a deadlock, just blocking, which is not really the same thing.

An ORA-4020 occurs when 2 sessions each hold a resource that the other session wants to lock. Oracle breaks the tie.

2. Would this the possible cause of the deadclock, as the table indexes
> could be locked by a large number of records inserted/updated?

You should probably look on MetaLink for documents related to ORA-4020. It has been written about exhaustively, and well documented. Search on asktom as well.

ORA-4020 is caused by inconsistently written SQL. Rather than try to explain it, it would be best if you just read what is already written about it.

3. Is this the correct way to get the choice of performing a rollback?

That really depends on the app requirements.

--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l
Received on Mon May 08 2006 - 12:49:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US