Embarrassment: Query timeout & table locking

jtblair · **Joined:** Mon Jul 18, 2005 10:15 am **Posts:** 17

Hibernate version: 3.0.5

Background:
Running Hibernate within WebSphere with container (WebSphere) managed transaction and everything works greatly -- stateless session bean + dao + hibernate -- ...until one day there comes a new requirement which translates to a long standing query (10-20 min). In this case the transaction gets time out after a pre-defined period (e.g. 2 min) and the query locks the tables resulting in other transactions not being able to update them.

Questions:
a) how I can make the query not lock the tables?
b) how can I solve the timeout issue? OR how can I remove the transaction context from the query?

I appreciate any suggestions but please don't tell me 'get rid of WebSphere'.

JB

Code:

<property name="connection.datasource">java:comp/env/jdbc/jndiMyDB</property>      
<property name="hibernate.transaction.factory_class">org.hibernate.transaction.CMTTransactionFactory</property> 
<property name="hibernate.transaction.manager_lookup_class"> org.hibernate.transaction.WebSphereExtendedJTATransactionLookup</property>
<property name="hibernate.transaction.auto_close_session">true</property>
<property name="hibernate.transaction.flush_before_completion">true</property>
<property name="hibernate.connection.release_mode">auto</property>

max · **Posted:** Fri Jan 13, 2006 4:53 pm

not webspheres fault - the locking is controlled by your database.

LaLiLuna · **Posted:** Sat Jan 14, 2006 5:53 pm

Some ideas
a)
specify no transaction for your EJB method this is possible.
b)
verify if your query does not run into a deadlock situation with other queries.
c)
I am not sure if it possible to setTimeout within a EJBQuery. You may try this or alternatively use a SQL statement and bypass EJB.
With a statement you may use setQueryTimeout.
d)
check for your database if you can change locking policy for your query. In this case you will probably need a native SQL query.
e)
Post more Information about the query and the database you use.

Regards Sebastian

jtblair · **Joined:** Mon Jul 18, 2005 10:15 am **Posts:** 17

Thanks for the suggestions.

a) For my configuration (WebSphere manages the TX), specifying EJB as no TX will cause problems for Hibernate as it expects a TX. Is it a feature of Hibernate or a bug?

b) For my long running query, deadlock with other database activities is not an issue, at lease for the sake of my testing.

c) Setting a timeout withint a EJB is not easy with CMT - container managed transaction - which is my configuration.
c1) With EJBs, some beans can be specigied as CMT while others BMT.
c2) Is it possible as well with Hibernate to have some TX managed by container and some by my code? Do I have to use two (similar) config files hibernate.cfg.xml's in order to achieve this?

e) DB=DB2 V8.1

Thanks again,
JB