"org.postgresql.xa.PGXAException: Connection is busy with another transaction" appearing from time to time

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

"org.postgresql.xa.PGXAException: Connection is busy with another transaction" appearing from time to time

Dennis Brakhane-2
Hi.

In one of our applications, we see the following exception appearing
from time to time in the live system (I've replaced the project name
with xxx):

Cause: java.sql.SQLException: error enlisting a JdbcConnectionHandle
of a JdbcPooledConnection from datasource user2 in state ACCESSIBLE
wrapping org.postgresql.xa.PGXAConnection@676c81d8 on Pooled
connection wrapping physical connection
org.postgresql.jdbc4.Jdbc4Connection@20dbbeeb
    at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:185)
    at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:104)
    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:566)
    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:541)
    at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:106)
    at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientImpl.java:83)
    at xxx.common.integration.persistence.ibatis.IbatisDAOUtil.wrappedQueryForObject(IbatisDAOUtil.java:192)
    ... 93 more
Caused by: java.sql.SQLException: error enlisting a
JdbcConnectionHandle of a JdbcPooledConnection from datasource user2
in state ACCESSIBLE wrapping org.postgresql.xa.PGXAConnection@676c81d8
on Pooled connection wrapping physical connection
org.postgresql.jdbc4.Jdbc4Connection@20dbbeeb
    at bitronix.tm.resource.jdbc.JdbcConnectionHandle.enlistResource(JdbcConnectionHandle.java:61)
    at bitronix.tm.resource.jdbc.JdbcConnectionHandle.prepareStatement(JdbcConnectionHandle.java:200)
    at sun.reflect.GeneratedMethodAccessor63.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:225)
    at $Proxy5.prepareStatement(Unknown Source)
    at com.ibatis.sqlmap.engine.execution.SqlExecutor.prepareStatement(SqlExecutor.java:494)
    at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:176)
    at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:205)
    at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
    ... 99 more
Caused by: bitronix.tm.internal.BitronixSystemException: cannot enlist
an XAResourceHolderState with uniqueName=user2
XAResource=org.postgresql.xa.PGXAConnection@676c81d8 with XID a
Bitronix XID [7061722D6D75632D6170702D31320000011EB84BBD7002AC5DF7 :
7061722D6D75632D6170702D31320000011EB84BBD7202AC5DF9],
error=XAER_PROTO
    at bitronix.tm.BitronixTransaction.enlistResource(BitronixTransaction.java:83)
    at bitronix.tm.resource.common.TransactionContextHelper.enlist(TransactionContextHelper.java:267)
    at bitronix.tm.resource.common.TransactionContextHelper.enlistInCurrentTransaction(TransactionContextHelper.java:49)
    at bitronix.tm.resource.jdbc.JdbcConnectionHandle.enlistResource(JdbcConnectionHandle.java:59)
    ... 109 more
Caused by: org.postgresql.xa.PGXAException: Connection is busy with
another transaction
    at org.postgresql.xa.PGXAConnection.start(PGXAConnection.java:134)
    at bitronix.tm.internal.XAResourceHolderState.start(XAResourceHolderState.java:179)
    at bitronix.tm.internal.XAResourceManager.enlist(XAResourceManager.java:89)
    at bitronix.tm.BitronixTransaction.enlistResource(BitronixTransaction.java:76)
    ... 112 more

As of now, I wasn't able to reproduce it on my developer system, but
this exception is occuring rather often; and apparently at the same
point (unfortunately exactly when a new users tries to register). So
far, I do not know whether this is a result of a misconfiguration, a
BTM bug or a PostgreSQL JDBC bug.

The PGXAException is thrown by Postgresqls JDBC Driver (version at the
bottom of this mail) whenever start() is called for the second time on
a XAConnection.

About our configuration:
We use iBATIS in connection with Spring and PostgreSQL, as well as
aspectj for annotation-driven transaction support. The BTM Wiki has
examples how to configure it for Spring and for iBATIS, but not for
both, so we ended up with a configuration like the following (i've
modified/anonymized it a little bit):


    <bean id="btmConfig"
class="bitronix.tm.TransactionManagerServices"
factory-method="getConfiguration" >
        <property name="serverId" value="${xxx.btm.serverId}"/>
        <property name="asynchronous2Pc" value="${xxx.btm.asynchronous2Pc}"/>
        <property name="logPart1Filename" value="${xxx.btm.logPart1Filename}"/>
        <property name="logPart2Filename" value="${xxx.btm.logPart2Filename}"/>
    </bean>

      <bean id="bitronixTransactionManager"
class="bitronix.tm.TransactionManagerServices"
          factory-method="getTransactionManager"
          depends-on="btmConfig,
          userDataSource,
          .. other DataSources ...
          "
          destroy-method="shutdown"/>

    <!-- all the DelegationTransactionManagerInterceptor does is
override the begin(), commit() and rollback()
           methods to notify registered listeners whenever a
transaction was commited or rolled back. All calls are
           delegated to the normal transaction manager -->

    <bean id="transactionManagerInterceptor"
class="xxx.transactionsupport.DelegatingTransactionManagerInterceptor">
        <property name="transactionManager" ref="bitronixTransactionManager"/>
    </bean>

    <bean id="transactionManager"
class="org.springframework.transaction.jta.JtaTransactionManager">
        <property name="transactionManager"
ref="transactionManagerInterceptor"/>
        <property name="userTransaction" ref="transactionManagerInterceptor"/>
    </bean>


The iBATIS SqlMappers are configured like this:

    <bean id="abstractDataSource"
class="bitronix.tm.resource.jdbc.PoolingDataSource" init-method="init"
destroy-method="close" abstract="true">
        <property name="className" value="${xxx.JDBC.XADriver}"/> <!--
set to org.postgresql.xa.PGXADataSource -->
        <property name="testQuery" value="${xxx.JDBC.TestQuery}"/>
        <property name="preparedStatementCacheSize"
value="${xxx.JDBC.PreparedStatementCacheSize}"/>
        <property name="allowLocalTransactions" value="true"/>
        <property name="driverProperties">
            <props>
                <prop key="user">${xxx.JDBC.Username}</prop>
                <prop key="password">${xxx.JDBC.Password}</prop>
            </props>
        </property>
    </bean>


    <bean id="userDataSource" parent="abstractDataSource">
        <property name="uniqueName" value="user2"/>
        <property name="maxPoolSize" value="${xxx.JDBC.User.MaxPoolSize}"/>
        <property name="driverProperties">
            <props merge="true">
                <prop key="databaseName">${xxx.JDBC.User.Database}</prop>
                <prop key="serverName">${xxx.JDBC.User.Host}</prop>
                <prop key="portNumber">${xxx.JDBC.User.Port}</prop>
            </props>
        </property>
    </bean>

    <bean id="userSqlMap"
                class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
                <property name="configLocation" value="${xxx.userSqlmap.path}" />
        <property name="dataSource" ref="userDataSource" />
    </bean>

All SqlMapConfigs look like this:

  <settings
    useStatementNamespaces="true"
    lazyLoadingEnabled="false"
    cacheModelsEnabled="true"
  />



One important thing is that some DataSources point to the same
database, for example, userDataSource and - say - searchDataSource
could both define a DataSource for the "users"-Database.
Also, although most of the sqlMappers use their separate dataSource
(which may point to the same DB, see previous sentence), some
sqlMappers share the same(!) dataSource. Could this be a problem?

The exception seems to appear at the first query for a page request
(the POST request to save the registration data), and that request is
executed within a transaction. The query itself is a simple SELECT.

Does anybody have an idea what is going wrong, and/or how I can
reproduce/fix it?

Any help is greatly appreciated.

Greetings,
  Dennis

PS: The versions used are:

BTM 1.3.1 (the fixed bug in 1.3.2 does not appear to be related to
what I'm seeing here)
Postgresql JDBC: postgresql-8.3-604.jdbc4.jar
ibatis 2.3.0.677 (I know it's outdated, but unfortunately I cannot
upgrade right now)
Spring 2.5.5
AspectJ 1.6.0

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: "org.postgresql.xa.PGXAException: Connection is busy with another transaction" appearing from time to time

Ludovic Orban
Administrator
This most certainly is an issue in PostgreSQL due to the rather strict limitations of their XA implementation.

Two things you could try that could help:

 1) set allowLocalTransactions to false
 2) disable prepared statement cache

Obviously those are educated guesses which is the best you can do as long as you do not know how to reproduce the issue.