Announcement Announcement Module
Collapse
No announcement yet.
Data commiited even after readonly=true, please help!! Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Data commiited even after readonly=true, please help!!

    Hi All,
    I am currently developing a Spring MVC application.I have configured a JDBC TransactionManager and I am doing declarative transaction management using AOP XML.However, even if I configure the method to run on a read-only=true, it still commits the transaction.

    Database : Oracle 10g

    My database-config.xml

    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
    	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xsi:schemaLocation="
    http://www.springframework.org/schema/beans
    http://www.springframework.org/schem...ring-beans.xsd
    http://www.springframework.org/schema/tx
    http://www.springframework.org/schema/tx/spring-tx.xsd
    http://www.springframework.org/schema/aop
    http://www.springframework.org/schema/aop/spring-aop.xsd">
    
    
    	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    		destroy-method="close">
    		<property name="driverClassName" value="${driver}" />
    		<property name="url" value="${url}" />
    		<property name="username" value="${username}" />
    		<property name="password" value="${password}" />
    		<property name="defaultAutoCommit" value="false" />
    	</bean>
    
    	<bean id="txManager"
    		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    		<property name="dataSource" ref="dataSource" />
    	</bean>
    
    	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    		<property name="dataSource" ref="dataSource" />
    		<property name="mapperLocations" value="classpath:com/mybatis/mappers/*.xml" />
    	</bean>
    
    
    	<!--
    		the transactional advice (what 'happens'; see the <aop:advisor/> bean
    		below)
    	-->
    	<tx:advice id="txAdvice" transaction-manager="txManager">
    		<!-- the transactional semantics... -->
    		<tx:attributes>
    			<!-- all methods starting with 'get' are read-only -->
    			<tx:method name="get*" read-only="true" />
    			<!-- other methods use the default transaction settings (see below) -->
    			<tx:method name="*" read-only="true" rollback-for="RuntimeException"/>
    		</tx:attributes>
    	</tx:advice>
    
    	<!--
    		ensure that the above transactional advice runs for any execution of
    		an operation defined by the FooService interface
    	-->
    	<aop:config>
    		<aop:pointcut id="fooServiceOperation"
    			expression="execution(* com.service.EmployeeService.*(..))" />
    		<aop:advisor advice-ref="txAdvice" pointcut-ref="fooServiceOperation" />
    	</aop:config>
    
    
    </beans>
    My controller


    Code:
    package com.service;
    
    import java.util.List;
    
    import com.mybatis.dao.EmployeeMapperInterface;
    import com.spring.model.Employee;
    
    public class EmployeeService implements EmployeeBaseService{
    
    	EmployeeMapperInterface employeeMapper;
    
    	public EmployeeMapperInterface getEmployeeMapper() {
    		return employeeMapper;
    	}
    
    	public void setEmployeeMapper(EmployeeMapperInterface employeeMapper) {
    		this.employeeMapper = employeeMapper;
    	}
    
    	@Override
    	public Employee getEmployeeById(long empId){
    		//retrieve from database
    		List empList = employeeMapper.getEmployeeWithId(empId);
    		if(empList != null && empList.size()>0){
    			return (Employee) empList.get(0);	
    		}
    		return null;
    
    	}
    
    
    	
    
    @Override
    	public long saveEmployee(Employee employee){
    		long empId = 0l;
    		if(employee.getEmpId()==0){
    			empId  = new Long( employeeMapper.insertEmployee(employee));
    		}else{
    			 employeeMapper.updateEmployee(employee);
    			 empId  =  employee.getEmpId();
    		}
    		try {
    			System.out.println("gonna sleep");
    			Thread.sleep(10);
    			
    		} catch (InterruptedException e) {
    
    			e.printStackTrace();
    		}
    		return empId;
    	}
    How do I prevent the auto commit.I have also noticed that even if I don't put any transaction management code, the code still commits. Please help. Note, the transaction advice is however,invoked as when I put a no-rollback-for for RuntimeException and then do a 1/0, it correctly commits the data and rolls back if I put the same as rollback-for.
    I have also tried out the query timeout by putting the thread on sleep, even that doesn't work, but I figure that timeout might be for an actual query, so thats fine.
    Please help!!

  • #2
    I am facing with the same issue
    basically I am using cached values (hibernate + ehCache) and it was quite strange to see in the profile that every time it gets connection from the pool it
    1 valdiates that connection is still alive with validation query (SELECT 1)
    2 sets autocimmit to false
    3 commits
    4 sets autocommit to true

    and on dev machine every step takes additional 10-12 ms

    I tuned up 1, 2 and 4
    so now validation query is run periodically and all connections I create in my pool have autocommit=false by default
    so the only problem left is commit during transactions marked as readonly

    is any way to get rid of it?

    PS I have a call where I need to get about 15-20 cached entities, so commit on each one would give 15*12ms
    PSS theoretically I can work around it by putting everything to one transaction... but I do not see the reason of commiting read-only transactions

    Comment


    • #3
      readonly is nothing. It is a hint which might be interpreted by the JDBC provider. It enforces nothing.

      Also your transaction code is flawed as you should never put a try/catch and swallow the exception. The tx management needs to see the exception to enable rollback of the transaction.

      Comment


      • #4
        Originally posted by Marten Deinum View Post
        readonly is nothing. It is a hint which might be interpreted by the JDBC provider. It enforces nothing.

        Also your transaction code is flawed as you should never put a try/catch and swallow the exception. The tx management needs to see the exception to enable rollback of the transaction.
        Thanks Marten. Yes, I know I should have thrown the exception, but ,is there a way to run a query which will not auto commit the transaction? Right now, if I run the update method inside and outside the context of a tx., the effect is the same. Should it be like that?


        READ_COMMITTED is supposedly the default isolation level and will suit my purpose quite well. Only if I could just run a query and not see it as being committed. I am using Mybatis as my persistence f/w btw.
        Last edited by ani_anirban; May 7th, 2012, 02:51 AM.

        Comment


        • #5
          If there is a transaction then there is a commit regardless of the fact it it is readonly (because that is only a hint nothing more, nothing less). If you don't want a commit don't use a transaction, there is no such thing as a read-only transaction, there is only a transaction.

          Comment


          • #6
            Originally posted by Marten Deinum View Post
            If there is a transaction then there is a commit regardless of the fact it it is readonly (because that is only a hint nothing more, nothing less). If you don't want a commit don't use a transaction, there is no such thing as a read-only transaction, there is only a transaction.
            Well, I enabled logging and got the following responses :
            #Without TX advice
            [07/05/12 04:33:34:034 SGT] DEBUG support.HandlerMethodInvoker: Invoking request handler method: public java.lang.String com.spring.controller.EmployeeController.saveEmplo yee(com.spring.model.Employee,org.springframework. validation.BindingResult) throws java.lang.Exception
            [07/05/12 04:33:34:034 SGT] DEBUG spring.SqlSessionUtils: Creating a new SqlSession
            [07/05/12 04:33:34:034 SGT] DEBUG spring.SqlSessionUtils: SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSessi on@eced18] was not registered for synchronization because synchronization is not active
            [07/05/12 04:33:34:034 SGT] DEBUG datasource.DataSourceUtils: Fetching JDBC Connection from DataSource
            [07/05/12 04:33:34:034 SGT] DEBUG transaction.SpringManagedTransaction: JDBC Connection [jdbc:oracle:thin:@*.*, Oracle JDBC driver] will not be managed by Spring

            [07/05/12 04:33:34:034 SGT] DEBUG EmployeeMapperInterface.updateEmployee: ooo Using Connection [jdbc:oracle:thin:@*.*, Oracle JDBC driver]
            [07/05/12 04:33:34:034 SGT] DEBUG EmployeeMapperInterface.updateEmployee: ==> Preparing: update employee set firstname= ?,lastname =?, emailid = ? where empId = ?
            [07/05/12 04:33:34:034 SGT] DEBUG EmployeeMapperInterface.updateEmployee: ==> Parameters: a(String), Bsed(String), cdef(String), 92178039(Long)
            [07/05/12 04:33:34:034 SGT] DEBUG transaction.SpringManagedTransaction: Committing JDBC Connection [jdbc:oracle:thin:@*.*, Oracle JDBC driver]
            [07/05/12 04:33:34:034 SGT] DEBUG spring.SqlSessionUtils: Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSessi on@eced18]
            [07/05/12 04:33:34:034 SGT] DEBUG datasource.DataSourceUtils: Returning JDBC Connection to DataSource
            [07/05/12 04:33:34:034 SGT] DEBUG support.DefaultListableBeanFactory: Invoking afterPropertiesSet() on bean with name 'redirect:employeeHome.html?empId=92178039'
            [07/05/12 04:33:34:034 SGT] DEBUG servlet.DispatcherServlet: Rendering view [org.springframework.web.servlet.view.RedirectView: name 'redirect:employeeHome.html?empId=92178039'; URL [employeeHome.html?empId=92178039]] in DispatcherServlet with name 'springProj'



            #With TX Advice.[07/05/12 04:38:43:043 SGT] DEBUG support.HandlerMethodInvoker: Invoking request handler method: public java.lang.String com.spring.controller.EmployeeController.saveEmplo yee(com.spring.model.Employee,org.springframework. validation.BindingResult) throws java.lang.Exception
            [07/05/12 04:38:43:043 SGT] DEBUG datasource.DataSourceTransactionManager: Creating new transaction with name [com.service.EmployeeService.saveEmployee]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,-RuntimeException
            [07/05/12 04:38:43:043 SGT] DEBUG datasource.DataSourceTransactionManager: Acquired Connection [jdbc:oracle:thin:@*.*, Oracle JDBC driver] for JDBC transaction
            [07/05/12 04:38:43:043 SGT] DEBUG spring.SqlSessionUtils: Creating a new SqlSession
            [07/05/12 04:38:43:043 SGT] DEBUG spring.SqlSessionUtils: Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSessi on@2423ad]
            [07/05/12 04:38:43:043 SGT] DEBUG transaction.SpringManagedTransaction: JDBC Connection [jdbc:oracle:thin:@*.*, Oracle JDBC driver] will be managed by Spring
            [07/05/12 04:38:43:043 SGT] DEBUG EmployeeMapperInterface.updateEmployee: ooo Using Connection [jdbc:oracle:thin:@*.*, Oracle JDBC driver]
            [07/05/12 04:38:43:043 SGT] DEBUG EmployeeMapperInterface.updateEmployee: ==> Preparing: update employee set firstname= ?,lastname =?, emailid = ? where empId = ?
            [07/05/12 04:38:43:043 SGT] DEBUG EmployeeMapperInterface.updateEmployee: ==> Parameters: a(String), B(String), cd(String), 92178039(Long)
            [07/05/12 04:38:43:043 SGT] DEBUG spring.SqlSessionUtils: Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSessi on@2423ad]
            [07/05/12 04:38:43:043 SGT] DEBUG spring.SqlSessionUtils: Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSessi on@2423ad]
            [07/05/12 04:38:43:043 SGT] DEBUG datasource.DataSourceTransactionManager: Initiating transaction commit
            [07/05/12 04:38:43:043 SGT] DEBUG datasource.DataSourceTransactionManager: Committing JDBC transaction on Connection [jdbc:oracle:thin:@*.*, Oracle JDBC driver]
            [07/05/12 04:38:43:043 SGT] DEBUG spring.SqlSessionUtils: Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSessi on@2423ad]
            [07/05/12 04:38:43:043 SGT] DEBUG datasource.DataSourceTransactionManager: Releasing JDBC Connection [jdbc:oracle:thin:@*.*, Oracle JDBC driver] after transaction
            [07/05/12 04:38:43:043 SGT] DEBUG datasource.DataSourceUtils: Returning JDBC Connection to DataSource
            [07/05/12 04:38:43:043 SGT] DEBUG servlet.DispatcherServlet: Rendering view [org.springframework.web.servlet.view.RedirectView: name 'redirect:employeeHome.html?empId=92178039'; URL [employeeHome.html?empId=92178039]] in DispatcherServlet with name 'springProj'
            [07/05/12 04:38:43:043 SGT] DEBUG servlet.DispatcherServlet: Successfully completed request

            Its interesting because, its clear that a transaction is started when the SqlSession is retrieved and then finally its committed, even though no commit was issued from the application.

            Comment


            • #7
              Please use [ code][/code ] tags when posting stacktraces, that leaves them more readable!

              Not sure why the mybatis integration stuff (which isn't written by spring btw!) wants to have a transaction and manage it by itself. As you can see from the logging it isn't spring that is starting a transaction now but the MyBatis integration stuff is managing the transaction now. (What is it what you are using I see both ibatis and mybatis stuff passing in your logging?!).

              Comment


              • #8
                My apologies for not enabling the code tags
                Am using mybatis-spring-1.1.0-bundle.

                For the non-tx managed code, I see that the tx. is managed by SpringManagedTransaction which is class on mybatis-spring-1.1.0.jar but its managed by the Mybatis team.

                For further clarity down the road :
                for a Spring managed tx:
                Code:
                [07/05/12 04:38:43:043 SGT] DEBUG spring.SqlSessionUtils: Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSessi on@2423ad]
                [07/05/12 04:38:43:043 SGT] DEBUG datasource.DataSourceTransactionManager: Initiating transaction commit
                [07/05/12 04:38:43:043 SGT] DEBUG datasource.DataSourceTransactionManager: Committing JDBC transaction on Connection [jdbc:oracle:thin:@*.*, Oracle JDBC driver]
                [07/05/12 04:38:43:043 SGT] DEBUG spring.SqlSessionUtils: Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSessi on@2423ad]
                [07/05/12 04:38:43:043 SGT] DEBUG datasource.DataSourceTransactionManager: Releasing JDBC Connection [jdbc:oracle:thin:@*.*, Oracle JDBC driver] after transaction
                for a non-spring managed tx:
                Code:
                [07/05/12 04:33:34:034 SGT] DEBUG transaction.SpringManagedTransaction: Committing JDBC Connection [jdbc:oracle:thin:@*.*, Oracle JDBC driver]
                [07/05/12 04:33:34:034 SGT] DEBUG spring.SqlSessionUtils: Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSessi on@eced18]
                [07/05/12 04:33:34:034 SGT] DEBUG datasource.DataSourceUtils: Returning JDBC Connection to DataSource

                Code:
                /**
                 * {@code SpringManagedTransaction} handles the lifecycle of a JDBC connection.
                 * It retrieves a connection from Spring's transaction manager and returns it back to it
                 * when it is no longer needed.
                 * <p>
                 * If Spring's transaction handling is active it will no-op all commit/rollback/close calls
                 * assuming that the Spring transaction manager will do the job.
                 * <p>
                 * If it is not it will behave like {@code JdbcTransaction}.
                public class SpringManagedTransaction implements Transaction {
                
                /**
                   * {@inheritDoc}
                   */
                  public void commit() throws SQLException {
                    if (this.connection != null && !this.isConnectionTransactional && !this.autoCommit) {
                      if (this.logger.isDebugEnabled()) {
                        this.logger.debug("Committing JDBC Connection [" + this.connection + "]");
                      }
                      this.connection.commit();
                    }
                  }

                Comment


                • #9
                  MyBatis stores the SqlSession as a synchronization in SpringTxManager but does not commit/rollback the JDBC connection. That is done by DataSourceTransaction manager.

                  I would try this with JDBCTemplate to discard a MyBatis integration issue. But I suspect that readOnly=true does nothing,

                  Comment

                  Working...
                  X