lazyloading error in container managed transactions (CMT)

[GoogleCodeExporter]

What version of the MyBatis are you using?
Mybatis 3.0.4

In Jboss environment it reports an error likes below, when the 
"lazyLoadingEnabled" set to "true"

16:33:11,800 INFO  [STDOUT] 16:33:11,798 ERROR [Connection] Error calling 
Connection.setAutoCommit:
java.sql.SQLException: You cannot set autocommit during a managed transaction!
        at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.setJdbcAutoCommit(BaseWrapperManagedConnection.java:520)
        at org.jboss.resource.adapter.jdbc.WrappedConnection.setAutoCommit(WrappedConnection.java:454)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:52)
        at $Proxy187.setAutoCommit(Unknown Source)
        at org.apache.ibatis.transaction.jdbc.JdbcTransaction.resetAutoCommit(JdbcTransaction.java:57)
        at org.apache.ibatis.transaction.jdbc.JdbcTransaction.close(JdbcTransaction.java:35)
        at org.apache.ibatis.executor.BaseExecutor.close(BaseExecutor.java:53)
        at org.apache.ibatis.executor.CachingExecutor.close(CachingExecutor.java:32)
        at org.apache.ibatis.executor.loader.ResultLoader.selectList(ResultLoader.java:75)
        at org.apache.ibatis.executor.loader.ResultLoader.loadResult(ResultLoader.java:49)
        at org.apache.ibatis.executor.loader.ResultLoaderMap$LoadPair.load(ResultLoaderMap.java:66)
        at org.apache.ibatis.executor.loader.ResultLoaderMap.load(ResultLoaderMap.java:35)
        at org.apache.ibatis.executor.loader.ResultObjectProxy$EnhancedResultObjectProxyImpl.intercept(ResultObjectProxy.java:121)
        at com.best.oasis.express.biz.system.model.SysSite$$EnhancerByCGLIB$$cdfa3a1e.getSuperiorSite(<generated>)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.dozer.util.ReflectionUtils.invoke(ReflectionUtils.java:270)
        at org.dozer.propertydescriptor.GetterSetterPropertyDescriptor.getDeepSrcFieldValue(GetterSetterPropertyDescriptor.java:116)
        at org.dozer.propertydescriptor.GetterSetterPropertyDescriptor.getPropertyValue(GetterSetterPropertyDescriptor.java:69)
        at org.dozer.fieldmap.FieldMap.getSrcFieldValue(FieldMap.java:82)
        at org.dozer.MappingProcessor.mapField(MappingProcessor.java:236)
        at org.dozer.MappingProcessor.map(MappingProcessor.java:218)
        at org.dozer.MappingProcessor.map(MappingProcessor.java:163)
        at org.dozer.MappingProcessor.map(MappingProcessor.java:119)
        at org.dozer.MappingProcessor.map(MappingProcessor.java:114)
        at org.dozer.DozerBeanMapper.map(DozerBeanMapper.java:92)

MyConfig

    <settings>
        <setting name="cacheEnabled" value="true" />
        <setting name="lazyLoadingEnabled" value="true" />
        <setting name="aggressiveLazyLoading" value="false" />
        <setting name="multipleResultSetsEnabled" value="true" />
        <setting name="useColumnLabel" value="true" />
        <setting name="autoMappingBehavior" value="PARTIAL" />
        <setting name="defaultExecutorType" value="REUSE" />
        <setting name="defaultStatementTimeout" value="25000" />
    </settings>

    <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
        <property name="jndiName" value="ds" />
    </bean>

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="configLocation" value="mybatis-config.xml" />
        <property name="dataSource" ref="dataSource" />
        <property name="transactionFactory">
            <bean class="org.apache.ibatis.transaction.managed.ManagedTransactionFactory" />
        </property>
    </bean>

    <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
        <constructor-arg index="0" ref="sqlSessionFactory" />
        <constructor-arg index="1" value="REUSE" />
    </bean>

SqlMapper：
    <resultMap id="sysSiteResultMap" type="SysSite">
        <id column="ID" property="id" jdbcType="DECIMAL" />
        <association property="superiorSite" javaType="SysSite"
            column="SUPERIOR_SITE_ID" select="SysSite.selectByPrimaryKey"></association>
    </resultMap>

when we're getting a Object "SysSite",the property "superiorSite" has not been 
set on(keep null), cause by the "lazyloading"

After call the method "getSuperiroSite()", mybatis gets Data by  CGLIB 

        com.best.oasis.express.biz.system.model.SysSite$$EnhancerByCGLIB$$cdfa3a1e.getSuperiorSite(<generated>)

it seems work like below:

    org.apache.ibatis.executor.loader.ResultLoader

    private List selectList() throws SQLException {
        Executor localExecutor = executor;
        if (localExecutor.isClosed()) {
          localExecutor = newExecutor();
        }
        try {
          return localExecutor.query(mappedStatement, parameterObject, RowBounds.DEFAULT, Executor.NO_RESULT_HANDLER);
        } finally {
          if (localExecutor != executor) {
            localExecutor.close(false);
          }
        }
      }

      private Executor newExecutor() throws SQLException {
        Environment environment = configuration.getEnvironment();
        if (environment == null)
          throw new ExecutorException("ResultLoader could not load lazily.  Environment was not configured.");
        DataSource ds = environment.getDataSource();
        if (ds == null) throw new ExecutorException("ResultLoader could not load lazily.  DataSource was not configured.");
        Connection conn = ds.getConnection();
        conn = wrapConnection(conn);
        Transaction tx = new JdbcTransaction(conn, false);
        return configuration.newExecutor(tx, ExecutorType.SIMPLE);
      }

here Transaction tx = new JdbcTransaction(conn, false);
when localExecutor.close(false); 
        at $Proxy187.setAutoCommit(Unknown Source)
        at org.apache.ibatis.transaction.jdbc.JdbcTransaction.resetAutoCommit(JdbcTransaction.java:57)
        at org.apache.ibatis.transaction.jdbc.JdbcTransaction.close(JdbcTransaction.java:35)
        at org.apache.ibatis.executor.BaseExecutor.close(BaseExecutor.java:53)

We got an error when tried to call the method "setAutoCommit"
org.jboss.resource.adapter.jdbc.WrappedConnection.setAutoCommit(WrappedConnectio
n.java:454)
    ERROR [Connection] Error calling Connection.setAutoCommit:
    java.sql.SQLException: You cannot set autocommit during a managed transaction!

the transaction is managed by jboss ,mybatis can't "setAutoCommit()"

So, we expected a result of calling "newExecutor()" with from the configuration 
,like it's been implemented in "DefaultSqlSessionFactory".
Or any other solution is fine as well.

    org.apache.ibatis.session.defaults.DefaultSqlSessionFactory 

    private SqlSession openSessionFromConnection(ExecutorType execType, Connection connection) {
        try {
          boolean autoCommit;
          try {
            autoCommit = connection.getAutoCommit();
          } catch (SQLException e) {
            // Failover to true, as most poor drivers
            // or databases won't support transactions
            autoCommit = true;
          }
          connection = wrapConnection(connection);
          final Environment environment = configuration.getEnvironment();
          final TransactionFactory transactionFactory = getTransactionFactoryFromEnvironment(environment);
          Transaction tx = transactionFactory.newTransaction(connection, autoCommit);
          Executor executor = configuration.newExecutor(tx, execType);
          return new DefaultSqlSession(configuration, executor, autoCommit);
        } catch (Exception e) {
          throw ExceptionFactory.wrapException("Error opening session.  Cause: " + e, e);
        } finally {
          ErrorContext.instance().reset();
        }
      }

Original issue reported on code.google.com by garth...@gmail.com on 12 Feb 2011 at 9:49

[GoogleCodeExporter]

If you are using CMT you should use the ManagedTransactionFactory instead of 
JdbcTransactionFactory.

Give it a try.

Original comment by eduardo.macarron on 12 Feb 2011 at 11:04

[GoogleCodeExporter]

i have use the ManagedTransactionFactory  
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="configLocation" value="mybatis-config.xml" />
        <property name="dataSource" ref="dataSource" />
        <property name="transactionFactory">
            <bean class="org.apache.ibatis.transaction.managed.ManagedTransactionFactory" />
        </property>
    </bean>

but in org.apache.ibatis.executor.loader.ResultLoader.selectList() 
mybatis use the JdbcTransaction eg. Transaction tx = new JdbcTransaction(conn, 
false);

Original comment by garth...@gmail.com on 12 Feb 2011 at 2:26

[GoogleCodeExporter]

You are right, the TransactionFactory should be read from Environment.

Original comment by eduardo.macarron on 12 Feb 2011 at 4:16

[GoogleCodeExporter]

Sorry I just read the stacktrace :) Thanks a lot for your detailed report.

Hope it is fixed in r3622. This is difficult to test so I just checked nothing 
gets broken. I have refreshed the 3.0.5-SNAPSHOT at download page (not in 
sonatype repo). Could you give it a try?

Original comment by eduardo.macarron on 12 Feb 2011 at 4:56

• Changed state: Fixed
• Added labels: Component-SqlMaps, Target-Release3.0.5

[GoogleCodeExporter]

i have try it in jboss 
it works well
thanks!

Original comment by garth...@gmail.com on 14 Feb 2011 at 7:10

[GoogleCodeExporter]

This fix is not sufficient for Spring-managed transactions when using 
mybatis-spring because the Executor's queries will only participate in the 
current transaction if the Connection is retrieved by calling 
org.springframework.jdbc.datasource.DataSourceUtils.getConnection(ds) instead 
of ds.getConnection(). Presumably mybatis-guice has the same problem. So one 
way this could be fixed is to define a ConnectionFactory interface (similar to 
the TransactionFactory that is in org.apache.ibatis.mapping.Environment) that 
would be implemented by mybatis-spring, mybatis-guice, and mybatis.

Here is a scenario that can reproduce this problem:
1. Delete row from table A.
2. Within the same transaction boundary, lazy load a collection from table A. 
Lazy loaded result will contain the deleted row but it should not.

Original comment by patrick....@3pillarglobal.com on 17 Mar 2011 at 7:58

[GoogleCodeExporter]

You are right Patrick. We already discussed that limitation in the dev list.

MyBatis transaction interface should also include the connection retrieval. So 
the full connection livecycle can be managed by a plugin. 

I don think this will be addressed in a short time. 

Anyway this should not be a common problem.

Original comment by eduardo.macarron on 17 Mar 2011 at 9:37

[GoogleCodeExporter]

I created Issue 279 for the Spring transaction bug.

Original comment by patrick....@3pillarglobal.com on 25 Mar 2011 at 4:19

[GoogleCodeExporter]

Original comment by eduardo.macarron on 3 Mar 2012 at 9:11

• Added labels: Version-Release3.x