Hi guys

I met a strange problem when i using Hibernate with DB2 8.1.

In one transaction, I'm trying to insert several records into a table,
the steps of inserting one is like the following:
1. Execute 'an insert query' from hibernate (like saving a object)
2. AuditInterceptor was executed automatially which do a query like 'select max(display_id) from table'. And the selected value was set to the object to be saved.
3. The insert query executed

The problem is when the second record comes to select max(display_id) .... That table was locked and every operation related that table hangs up.

I'll post the real SQL generated by Hibernate later, when I found them again :|

Thanks in advance,
Tele

From the look of this, it seems you are trying to manually set the primary key? This may be related to the fact that the transaction has not committed yet and/or the session has not flushed.

Are you selecting from the same table you are inserting to?

If you are using this to create your primary key, perhaps check what <generator> you are using in your mapping.

-G

Thanks for you reply, Brannor.

Actually I'm not trying to create primary keys but to create something looks like primary keys for some reason.


I aslo thought this might be related with the uncommitted transaction, so I tried the same thing manually on a table using JDBC and primitive SQL


seems it won't halt for the second select statement.

My guess would be that this is related to isolation levels of the transactions.

How is the "one transaction" within which you are trying to do this processing handled? Through Hibernate? Through JTA?

How about on the JDBC connection when you attempted through straight JDBC? And in DB2's sql tool when you tried through that?

Thanks steve,

Sorry it's all my fault. Actually my colleague acquired another session in AuditInterceptor.
He said that operating the same session that uses the interceptor like
will leads to serious error. So his solution is use another session for select.
(Can someone tell me whether this is TRUE or FALSE as using the same session object in AuditInterceptor as the session object called AuditInterceptor)

Here's some difference between MYSQL and DB2. When we use MYSQL, no such kind error, but when we switched DB2, selecting a table when another connection is inserting into the same table will make the selecting suspends.

Now the problem has been fixed by using a JDBC connection session.connection() and some primitive JDBC operations.

Thanks again for Brannor and steve. *_*)/~

Tele