Hi everyone,

I have a transaction which includes adding 5 records in a table, which includes a Blob (pdf file) as one of its columns.

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

When i run my application, the system halts when i try to commit the transaction.

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()?

Hello,

Your post didn't give a lot of details about what you mean by "system halts at commit" so this solution may or may not apply.

We also stored PDF files in a BLOB column. We found that if we were trying to insert large BLOBs or multiple BLOBs, the database session would hang. It would never call commit, and from the database's perspective, the session was hanging open but not busy (no SQL was being executed). From the Java side, I'd have to restart the web app to kill the connection off.

I enabled uber-logging on the database tier and was able to see that the final log statement issued before the connection hung was
"DEBUG org.hibernate.jdbc.AbstractBatcher - Executing batch size: 2".
Armed with the knowledge that batching might be the issue, I was able to do much better google searches and turned up other users reporting the same error.

I investigated batching, and here's what I learned about it: Starting with Oracle 8, Oracle's JDBC driver gave users the ability to "batch" database requests within a single transaction. If the Java code detected that multiple statements would be issued inside a single transaction, the JDBC driver would batch them up until a batch limit was reached, and then would send all the statements as single group to the database. The default seems to be set to 10 statements in a batch. This does have a performance improvement if you are doing many statements in a transaction, especially in a high-throughput system.

Unfortunately, this feature seems to be buggy with respect to BLOBs and CLOBs ever since it was introduced in version 8.

So what was happening is that we would issue a handful of update statements in a single transaction including several for each of the BLOBs. Oracle's batching would 'hold' them until the batch limit of 10 was reached, or the transaction commit was issued, ... and then just hang, never to come back to life again.

Our solution was to disable JDBC batching and force each statement to be sent to the server one at a time - this can be done via the Hibernate configuration like so:
<property name="hibernate.jdbc.batch_size">0</property>

Hope this helps,
Tara

Tara, it worked!
Your post was very informative and clear.
Thank you so much.
You have a beautiful name, by the way. :)