Re: How to anticipate (Oracle) database locks.

There isn't really enough information here to give you an answer, as you first need to supply more detail of what is being locked and the lock type/state; and how the problem is being perceived/measured.

However, if I take a wild guess, based on the fact that your apparently trying to delete on "customer" entry and commit very quickly, perhaps your problem is the parent/child locking issue whereby tables like 'customer_payments', or 'orders_by_customers' are being locked for referential integrity tests whilst the customer is deleted.

If this is the case, then the standard solution is to create indexes representing the foreign key relationships.

Further details are in the Concepts manual - someone can probably quote the chapter
and page.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____Denmark__May 21-23rd
____Sweden___June
____Australia__June 28th (Canberra - CBO)
____Finland__September
____Norway___September


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK_(Manchester)_May x 2
____Estonia___June 4th - 6th
____Australia_June 18th - 20th (Perth)
____Australia_June 23rd - 25th (Melbourne)
____USA_(CA, TX)_August
____UK___September

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Jan Brunia" <j.j.brunia_at_speed.a2000.nl> wrote in message
news:HMRxa.409499$qm4.42554595_at_amsnews03.chello.com...


> Hi,

>

> We have the problem of encountering database locks in our

> Java program. De piece of code most probably causing

> the lock is shown below.

>

> Has anybody some advise concerning how to avoid the locks

> and when they happen how to handle them and remove them

> runtime?

>

> Many thanks,

>

> Jan

>

> --------------------------------------------------------------------

-


>     public void deleteByCustomerId(CustomerNr customerId)

>             throws MvpnException {

>         Database database = new Database(Bootstrap.getConfig(),

"billing");


>

>         try {

>             database.setAutoCommit(false);

>

>             PreparedStatement preparedStatement =

> database.prepareStatement(SQL_DELETE_VIRTUAL_MEMBERS);

>             preparedStatement.setString(1, customerId.toBilling());

>             preparedStatement.execute();

>

>             preparedStatement =

> database.prepareStatement(SQL_DELETE_SUBSCRIBERS);

>             preparedStatement.setString(1, customerId.toBilling());

>             preparedStatement.execute();

>

>             preparedStatement =

> database.prepareStatement(SQL_DELETE_CUSTOMER);

>             preparedStatement.setString(1, customerId.toBilling());

>             preparedStatement.execute();

>

>             database.commit();

>         } catch(SQLException e) {

>             try {

>                 database.rollback();

>             } catch(SQLException e1) {

>             } finally {

>                 log.error("sql error while storing parse result: ",

e);


>                 throw new MvpnException(e);

>             }

>         } finally {

>             database.close();

>         }

>     }

>

> --------------------------------------------------------------------

--------


>

>

>

>

>