Re: How to anticipate (Oracle) database locks.

Jan Brunia wrote:

> 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();
> }
> }
>
> ----------------------------------------------------------------------------

Nothing you posted is capable of locking anything.

If locking is taking place it is taking place in the procedures you are executing and if they are locking anything they are poorly written.

If you respond ... isolate the specific procedure doing the locking by looking at the appropriate v_$ views and include your Oracle version.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)