Re: JDBC insert query to Oracle sometimes hangs

On 21 May 2004 08:51:01 -0700, sisian_at_mail.ru (Anton Mironenko) wrote:

>There is a thread which performs multiple inserts into one table.
>Sometimes this thread hangs, and hangs forever on
>preparedStatement.executeBatch():
>
>Thread dump shows the following:
>
>"SdrWriterThread-0" prio=1 tid=0x60fa7e78 nid=0x100b runnable
>[6ccd1000..6ccd18e8]
> at java.net.SocketInputStream.socketRead0(Native Method)
> at java.net.SocketInputStream.read(SocketInputStream.java:129)
> at oracle.net.ns.Packet.receive(Unknown Source)
> at oracle.net.ns.NetInputStream.getNextPacket(Unknown Source)
> at oracle.net.ns.NetInputStream.read(Unknown Source)
> at oracle.net.ns.NetInputStream.read(Unknown Source)
> at oracle.net.ns.NetInputStream.read(Unknown Source)
> at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:718)
> at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:690)
> at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:373)
> at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
> at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:822)
> - locked <0x48cc1520> (a oracle.jdbc.ttc7.TTC7Protocol)
> at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1446)
> at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1371)
> at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:2883)
> - locked <0x491e8db0> (a
>oracle.jdbc.driver.OraclePreparedStatement)
>
>select from v$session and v$locked_object shows that this session is
>ACTIVE and object corresponding inserted table is locked.
>
>Attempt to kill this session using ALTER SYSTEM KILL SESSION doesn't
>help, this query cause only "ORA-00031: session marked for kill", but
>thread is still in read from socket state.
>
>It happens in JDK 1.4.2_02, Oracle 8.1.7 and Oracle 9.2.0

Looks like the second INSERT is waiting for the first to release the LOCK. Are these multiple INSERTS in multiple Oracle sessions? That would explain it. You will need to change your approach.

--
Sybrand Bakker, Senior Oracle DBA