Each Transaction opens a new database conn when using HibernateTransactionManager

[elielgs]

Hello,

We´re using the Spring Framework as a manager layer for all transactions in our application.
When using HibernateTransactionManager, each opened transaction opens a new database connection in our database. Is there a way of making spring to use the same connection for each opened transaction or this is its default behavior ?

This behavior causes a high usage of datasource´s connections and as we need to provide a lot of transactions simultaneously, in some cases we can have problems with database´s locks. In the same way, we cannot increase the number of datasources created because we observed a degradation of our database performance.

The configuration of our environment is shown below:

Transaction Manager:
<bean id="transactionManager" class="org.springframework.orm.hibernate3.Hibernat eTransactionManager">
<property name="sessionFactory" ref="sessionFactory"/>
</bean>

Session Factory:

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotati on.AnnotationSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="configLocations">
<list>
<value>classpath:hibernate-pojo.cfg.xml</value>
</list>
</property>
</bean>

Datasource:

<datasources>
<local-tx-datasource>
<jndi-name>AAAAA01</jndi-name>
<connection-url>jdbcostgresql://localhost:5432/db_v1</connection-url>
<driver-class>org.postgresql.Driver</driver-class>
<user-name>postgres</user-name>
<password>123456</password>
<min-pool-size>1</min-pool-size>
<max-pool-size>100</max-pool-size>
<set-tx-query-timeout>10</set-tx-query-timeout>
<blocking-timeout-millis>50000</blocking-timeout-millis>
<new-connection-sql>select 1;</new-connection-sql>
<check-valid-connection-sql>select 1;</check-valid-connection-sql>
</local-tx-datasource>
</datasources>

We have the need of opening new transactions inside other openeded transactions and each time that is created a new one we observed that a new connection of our datasource is used. The code below shows how is done the new transaction invocation:

DefaultTransactionDefinition definition = new DefaultTransactionDefinition();
definition.setName("chain" + UUID.randomUUID());
definition.setPropagationBehavior(TransactionDefin ition.PROPAGATION_REQUIRES_NEW);
TransactionStatus status = transactionManager.getTransaction(definition); --> Uses a new Transaction (T1)
...
...
...
...

DefaultTransactionDefinition definition2 = new DefaultTransactionDefinition();
definition2.setName("chain" + UUID.randomUUID());
definition2.setPropagationBehavior(TransactionDefi nition.PROPAGATION_REQUIRES_NEW);
TransactionStatus status2 = transactionManager2.getTransaction(definition2); --> Uses a new Transaction (T2)
...
...
...

transactionManager.commit(status2); --> Releases T2 connection
transactionManager.commit(status); --> Releases T1 Connection

We tried to use another type of propagation behavior called called NESTED_TRANSACTIONS but hibernate doesn´t provide the use of NESTED_TRANSACTIONS. If we try to use this kind of configuration, we receive the following exception:

org.hibernate.TransactionException: nested transactions not supported
at org.hibernate.engine.transaction.spi.AbstractTrans actionImpl.begin(AbstractTransactionImpl.java:152)
at org.hibernate.internal.SessionImpl.beginTransactio n(SessionImpl.java:1363)
at br.com.dao.DAO.rollback(DAO.java:51)
at br.com.dao.ProfessorDAO.salvar(ProfessorDAO.java:1 7)
at br.com.controller.ControllerProf.salvar(Controller Prof.java:44)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknow n Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.el.parser.AstValue.invoke(AstValue.java :264)
at org.apache.el.MethodExpressionImpl.invoke(MethodEx pressionImpl.java:278)
at org.apache.jasper.el.JspMethodExpression.invoke(Js pMethodExpression.java:70)
at javax.faces.component.MethodBindingMethodExpressio nAdapter.invoke(MethodBindingMethodExpressionAdapt er.java:88)
at com.sun.faces.application.ActionListenerImpl.proce ssAction(ActionListenerImpl.java:102)
at javax.faces.component.UICommand.broadcast(UIComman d.java:315)
at javax.faces.component.UIViewRoot.broadcastEvents(U IViewRoot.java:794)
at javax.faces.component.UIViewRoot.processApplicatio n(UIViewRoot.java:1259)
at com.sun.faces.lifecycle.InvokeApplicationPhase.exe cute(InvokeApplicationPhase.java:81)
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:1 01)
at com.sun.faces.lifecycle.LifecycleImpl.execute(Life cycleImpl.java:118)
at javax.faces.webapp.FacesServlet.service(FacesServl et.java:593)
at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:225)
at org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.java:169)
at org.apache.catalina.authenticator.AuthenticatorBas e.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:168)
at org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:98)
at org.apache.catalina.valves.AccessLogValve.invoke(A ccessLogValve.java:927)
at org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.servic e(CoyoteAdapter.java:407)
at org.apache.coyote.http11.AbstractHttp11Processor.p rocess(AbstractHttp11Processor.java:999)
at org.apache.coyote.AbstractProtocol$AbstractConnect ionHandler.process(AbstractProtocol.java:565)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProce ssor.run(JIoEndpoint.java:309)
at java.util.concurrent.ThreadPoolExecutor.runWorker( Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run (Unknown Source)
at java.lang.Thread.run(Unknown Source)

We appreciate some help.

[Marten Deinum]

Please use [ code][/code ] tags when posting code/xml/stacktraces...

NESTED_TRANSACTIONS is something different as the name implies, I suggest some googling and reading.

The behavior you see is correct from what I can make from the little configuration and code you posted. You have multiple transaction managers and I assume also multiple sessionfactories. Each of those will open a session and each session has its own database connection.

[elielgs]

Dear Marten,

All the application uses the same session factory "AnnotationSessionFactoryBean" and each thread uses the same Session instance.

The problem is when in the same Thread using the same session instance, the application opens another connection instead using the same connection used in the first transaction. We tried using NESTED_TRANSACTION but when using hibernate this is not possible. Each created transaction has the type REQUIRES_NEW.

Are we doing anything wrong or is this a normal behavior ?

Tks in advance...

[Marten Deinum]

As mentioned NESTED_TRANSACTIONS is not what you think it is (and afaik it is only supported by Oracle)...

According to your code you are having multiple tx managers (judging from the name of the tx manager). But as mentioned the behavior is what you would expect, each transaction operates on its own session an each session is bound to a single connection. If you enable debug logging you will see logging which supports this.

Why do you need so many new transactions? For some reason it feels wrong...

[elielgs]

Marten,

The class which is injected the transactionManager is a singleton as the transactionManager itself.

We open one transaction inside another one because we need to garantee that the fisrt one only can be commited in case of success of the second one. In some cases these transactions are executed in a expressive amount of time because during its execution in many cases it´s necessary to access legacy systems and so on...

Looking by this point of view, are we doing anything wrong ? Or is this the behavior considering that we are using the same transactionmanager and the same session.

Tks,

[Marten Deinum]

We open one transaction inside another one because we need to garantee that the fisrt one only can be commited in case of success of the second one.

THen why do you need 2 transactions?! That is the point I don't really get. The either both fail or succeed so basically it is a single transaction... Maybe I'm looking at it from it wrong but that is what it looks like.

Looking by this point of view, are we doing anything wrong ? Or is this the behavior considering that we are using the same transactionmanager and the same session.

What you can do is set the connection release mode of hibernate to on_close that should reuse the connection for different transactions. The default is after_transaction which opens a new connection for each transaction.

Please post your hibernate.cfg.xml file (you left that out).

Also I still don't get your code

TransactionStatus status = transactionManager.getTransaction(definition); --> Uses a new Transaction (T1)

and

TransactionStatus status2 = transactionManager2.getTransaction(definition2); --> Uses a new Transaction (T2)

This appears as if you have multiple transaction managers tied to a single or multiple sessionfactories. I wonder why... If you only have a single session factory you also need a single transaction manager...