Tomcat halts when inserting Blobs in oracle

[Henrique Ordine]

Hi everyone,

I have a transaction which includes inserting 5 records into a table, which has a Blob column. I'm trying to insert pdf files in that column.

The tables are in an oracle database. I'm using tomcat 6 as app server and Hibernate as persistence framework.

When i run my application, the system halts when i try to commit the transaction. I tried commiting the transaction after inserting each record and the transaction commits after the first one and halts on the second commit.

I've checked rollback segment size and that doesn't seem to be the problem.

When i run the same transaction in PL/SQL, connecting with the same oracle user, the transaction runs and commits in less than one second.

Could anyone please help me with this? Why does the system halt when i call Transaction.commit()?

[Ray Johnson]

Can you post the code snippet of your bean having blob and the place where you are setting value to it and inserting/updating before commit?

[Henrique Ordine]

Ray, firstly, thanks for your interest in helping.
My beans are of type Anexo. This class has the property anexoBlob of type Blob which maps to the Blob column. I create the blob by calling Hibernate.createBlob(byte[]).

Here's the code where i try to commit after every insert:

Session oracleSes = sessionFactory.openSession();
oracleSes.setFlushMode(FlushMode.COMMIT);
oracleSes.beginTransaction();
 
Anexo certidaoRegistro = new Anexo();
Anexo titulacao = new Anexo();
Anexo aviso = new Anexo();
oracleSes.save(certidaoRegistro);
oracleSes.getTransaction().commit();
oracleSes.beginTransaction();
oracleSes.save(titulacao);
oracleSes.getTransaction().commit(); // system halts here

This is what hibernate print's on console:

Hibernate: select seq_anexo.nextval from dual
PK: 237
Hibernate: insert into anexo (ANE_CONTEUDO, ANE_DESCRICAO, ANE_EXTENSAO, ANE_ID) values (?, ?, ?, ?)
Hibernate: update anexo set ANE_CONTEUDO=?, ANE_DESCRICAO=?, ANE_EXTENSAO=? where ANE_ID=?
Hibernate: select seq_anexo.nextval from dual
PK: 238
Hibernate: insert into anexo (ANE_CONTEUDO, ANE_DESCRICAO, ANE_EXTENSAO, ANE_ID) values (?, ?, ?, ?)
Hibernate: update anexo set ANE_CONTEUDO=?, ANE_DESCRICAO=?, ANE_EXTENSAO=? where ANE_ID=?
Hibernate: update anexo set ANE_CONTEUDO=?, ANE_DESCRICAO=?, ANE_EXTENSAO=? where ANE_ID=?

Note that on the second insert, it tries to update the Anexo table twice.

Thanks again.

[Dee Brown]

I have what appears to be the exact same problem that you have documented here. I have the 'double update' condition when the failure is present. Would it be possible for you to tell me what the fix for the problem was? I would be very grateful if you are able to.

Thanks in advance..db

[Henrique Ordine]

Dee,

I don't remember the details of the solution.
The problem was something to do with a bug in an oracle driver when executing batch updates in a blob column.
The work around was to set a hibernate config property related to batch updates (I don't remember which one) to 0 or 1 (not sure which either). I think this property was part of the connection pool setting.
This would make hibernate send updates to oracle as they were executed, instead of waiting for the number of updates set for that property to be reached. There could be a performance drawback. This property defaults to 5 or 10, not sure.

hope this helps.

[Paul Sturrock]

Which Oracle driver are you using? There have historically been bugs in the various drivers handing of binary data. It could be the updating to the latest 11g driver will also fix this.
[ November 10, 2008: Message edited by: Paul Sturrock ]

[Dee Brown]

The 'fix':

<property name = "hibernate.jdbc.batch_size" value="0" />

Thanks for the tip.

As an FYI, I had originally tried the 11g driver and switched to the 10g driver when "a hang" was first encountered. Since I did not view the SQL log at that time (I am new to Hibernate), I will likely try the 11g driver again.

Thanks

db

[Chinmay Bajikar]

Hi db,

I have a similar problem with Blobs.
The thing is that it has started failing recently.
My dev env has Tomcat & I never encountered a failure there.
Also this was working fine in prod till now & it has started freaking out
since the weekend.I suppose it is because of the extra yer-end load on the system.

One thing that I am doing different is a
Single Insert.
And a commit after that.

Also my hibernate.jdbc.batch_size is not set to 0.

Here is the stacktrace from prod,

java.net.SocketInputStream.socketRead0(Native Method)
java.net.SocketInputStream.read(SocketInputStream.java:129)
oracle.net.ns.Packet.receive(Unknown Source)
oracle.net.ns.DataPacket.receive(Unknown Source)
oracle.net.ns.NetInputStream.getNextPacket(Unknown Source)
oracle.net.ns.NetInputStream.read(Unknown Source)
oracle.net.ns.NetInputStream.read(Unknown Source)
oracle.net.ns.NetInputStream.read(Unknown Source)
oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1099)
oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1070)
oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:478)
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10622)
weblogic.jdbc.wrapper.PreparedStatement.executeBatch(PreparedStatement.java:157)
org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)
org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:606)

[Dee Brown]

If you read this thread you will see that a signature of the indicated issue is that hibernate will issue two update() calls even though a single record is being persisted. To confirm the above use the following to enable tracing:

log4j.logger.org.hibernate.SQL=DEBUG, A1
log4j.additivity.org.hibernate.SQL=false