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, 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:
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.
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.
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.
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 ]
<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.
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.
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: