Re: Deadlock problem

  • From: "Jared Still" <jkstill@xxxxxxxxx>
  • To: alever@xxxxxxxxx
  • Date: Mon, 8 May 2006 10:52:57 -0700

That should teach me to check the error message before replying.

I can never keep straight which is which.

(ORA-60 vs. ORA-4020)

It's an ORA-60, as Mark has stated.

Different error number, same advice.

On 5/8/06, Jared Still <jkstill@xxxxxxxxx> wrote:

On 5/8/06, Alessandro Vercelli <alever@xxxxxxxxx> 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

Possible problems with that approach is that the user may start the
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

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

Other related posts: