Hi,

I'm using Spring+Hibernate in my application. The database used is Informix.
I'm testing concurrency and I want my application to wait for a while when
a query try to access a row that is locked. This time can be set in informix
whith 'SET LOCK MODE TO WAIT'. But I'm looking for a method in hibernate
or spring to do the same.

I tried setting a timeout in the transaction, but it did not work.

...
@Transactional(readOnly = false, propagation=Propagation.REQUIRES_NEW, isolation=Isolation.READ_COMMITTED, timeout=20)
...
getHibernateTemplate().getSessionFactory().getCurrentSession().getTransaction().setTimeout(11);
...

Could anyone help me?
Thanks.

Hi,

one of the simplest ways to do this is, doing it through your connection pool configuration.
Most connection pool implementation allow to define SQL-statements which have to be executed before a
connection get borrowed to hibernate.
For example if you are using C3PO you can define:

<property name="hibernate.c3p0.testConnectionOnCheckout" value="true"/>
<property name="hibernate.c3p0.preferredTestQuery" value="SET LOCK MODE TO WAIT;select * from systables;"/>

N.B.: On C3PO the validation query must return at least one result (on other connection pool it may be enough, that it dont raises an exception)
As "SET LOCK MODE TO WAIT" probably does not return anything,
you probably have to define a second query like 'select * from systables;' or any query which without effort is returning a result.

Which connection pool are you using?
You could also check if your Informix JDBC-driver provides an apposite parameter in the connection url.

Hi, "pb00067"

Thanks a lot for your response,
I'm using a connection pool defined in Weblogic. I can set the 'lock mode to wait'
when I get the conection in Weblogic as an initial query. The problem is the next: there are some querys
which I want to wait more time for them than others. So, for this case I need to set the 'lock mode to wait' at the
level of the query. I could do this using a native query and setting the "lock mode to wait" before
executing my concrete query, and then when the query finishes I can set the "lock mode to wait" to it's default
value. But I would prefer not to use native querys, do you know any other solution?

Thanks in advance.

As with Informix it is not possible to specify the wait timeout within the select-statement itself,
I see not other way than toggling the wait-timeouts by using native querys.