Re: How to anticipate (Oracle) database locks.

Hello,

The lock I'm referring to is actually a deadlock according the database experts
I talk to. It seems that a table is locked and until it's released another program can
do an access.

I did some searching on the web and I encountered the term pessimistic access.

I have some more information about the issue. This is it. Doe sthis make any sense
to you? Hope it does.

Kind regards,

Jan

> *** SESSION ID:(65.22699) 2003-04-03 11:41:23.264
> DEADLOCK DETECTED
> Current SQL statement for this session:
> DELETE FROM ben.Customer_import WHERE customer_nr = :1
> The following deadlock is not an ORACLE error. It is a
> deadlock due to user error in the design of an application
> or from issuing incorrect ad-hoc SQL. The following
> information may aid in determining the deadlock:
> Deadlock graph:
> ---------Blocker(s)--------
> ---------Waiter(s)---------
> Resource Name process session holds waits process session holds
> waits
> TM-00043504-00000000 76 65 SX SSX 89 86 SX
> SSX
> TM-00043504-00000000 89 86 SX SSX 76 65 SX
> SSX
> session 65: DID 0001-004C-00000002 session 86: DID 0001-0059-00000002
> session 86: DID 0001-0059-00000002 session 65: DID 0001-004C-00000002
> Rows waited on:
> Session 86: no row
> Session 65: no row
> ===================================================
>
> *** 2003-04-03 11:41:14.627
> *** SESSION ID:(57.6318) 2003-04-03 11:41:12.684
> DEADLOCK DETECTED
> Current SQL statement for this session:
> DELETE FROM ben.Subscriber_import WHERE customer_nr = :1
> The following deadlock is not an ORACLE error. It is a
> deadlock due to user error in the design of an application
> or from issuing incorrect ad-hoc SQL. The following
> information may aid in determining the deadlock:
> Deadlock graph:
> ---------Blocker(s)--------
> ---------Waiter(s)---------
> Resource Name process session holds waits process session holds
> waits
> TM-00043506-00000000 48 57 SX 76 65 SX
> SSX
> TM-00043504-00000000 76 65 SSX 48 57
> SX
> session 57: DID 0001-0030-00000002 session 65: DID 0001-004C-00000002
> session 65: DID 0001-004C-00000002 session 57: DID 0001-0030-00000002
> Rows waited on:
> Session 65: no row
> Session 57: no row
> ===================================================
>
> *** 2003-04-01 15:43:20.350
> *** SESSION ID:(18.63988) 2003-04-01 15:43:20.324
> DEADLOCK DETECTED
> Current SQL statement for this session:
> DELETE FROM ben.Subscriber_import WHERE customer_nr = :1
> The following deadlock is not an ORACLE error. It is a
> deadlock due to user error in the design of an application
> or from issuing incorrect ad-hoc SQL. The following
> information may aid in determining the deadlock:
> Deadlock graph:
> ---------Blocker(s)--------
> ---------Waiter(s)---------
> Resource Name process session holds waits process session holds
> waits
> TM-00043506-00000000 49 18 SX 50 91 SX
> SSX
> TM-00043504-00000000 50 91 SSX 49 18
> SX
> session 18: DID 0001-0031-00000002 session 91: DID 0001-0032-00000002
> session 91: DID 0001-0032-00000002 session 18: DID 0001-0031-00000002
> Rows waited on:
> Session 91: no row
> Session 18: no row
> ===================================================
> *** 2003-04-01 13:35:52.059
> *** SESSION ID:(88.48627) 2003-04-01 13:35:52.027
> DEADLOCK DETECTED
> Current SQL statement for this session:
> DELETE FROM ben.Subscriber_import WHERE customer_nr = :1
> The following deadlock is not an ORACLE error. It is a
> deadlock due to user error in the design of an application
> or from issuing incorrect ad-hoc SQL. The following
> information may aid in determining the deadlock:
> Deadlock graph:
> ---------Blocker(s)--------
> ---------Waiter(s)---------
> Resource Name process session holds waits process session holds
> waits
> TM-00043506-00000000 14 88 SX 55 90 SX
> SSX
> TM-00043504-00000000 55 90 SSX 14 88
> SX
> session 88: DID 0001-000E-00000002 session 90: DID 0001-0037-00000002
> session 90: DID 0001-0037-00000002 session 88: DID 0001-000E-00000002
> Rows waited on:
> Session 90: no row
> Session 88: no row
> ===================================================
>

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

--


>

>

>

>

>