Announcement Announcement Module
Collapse
No announcement yet.
problem with spring transaction management with multi-threading Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • problem with spring transaction management with multi-threading

    Hi

    I need to perform some db operations in a multi-thread enviroment. For each thread, the task is the same as below:

    1. Check if a shared table exists. If not, create it, else, truncate it so that each thread will use its own data;
    2. Insert some date into this shared table;
    3. Call a stored proc to retrieve some data.

    I'm using JdbcTemplate to perform these db operations via a connection pool. Meanwhile, I'm using org.springframework.jdbc.datasource.DataSourceTran sactionManager to declare that the task each thread is to be carrying out to be transactional (propagation="REQUIRED" isolation="SERIALIZABLE").

    However, this seems not working. I still run into cases when threads try to create an existing table, or they see data inserted into the shared table by other threads.

    Could someone give my some hint here?

    Many thanks.

  • #2
    If the service is kicking off the threads then it is running under the same transaction so that is why they are seeing the inserted data. You might want the threads to be spawned off before it gets to the service, so they run in their own transaction, I dont know maybe that will work.

    Comment


    • #3
      this may be related to how database handles concurrent connections and how JdbcTemplate handles multi-statement transactions. It seems to me that even I declared the whole service method to be transactional, the JdbcTemplate still handles each sql statement in its own transaction.

      If this guess is not correct, please someone tell me.

      Comment


      • #4
        I guess you are right.

        I am encountering the same issue. I apply spring data source transaction manager annotation to handle the transaction. The job runs in a thread pool with multiple callable tasks. The business requirement in my system is, if one thread's job fails, all threads' jobs need to be rollback. However, only the one fails is rollback, but the others are still committed into the DB.

        It seems the each thread has its own transaction no matter how I define propagation behavior. (I define REQUIRED in both the overall caller method and the each DAO related update method by using JdbcTemplate to perform db operations)

        How to handle the transactional management in a thread pooling environment by using Spring Jframework DBC DataSourseTransactionManager?

        Please help. Thanks a lot.

        Comment


        • #5
          Originally posted by sunflowerr View Post
          I guess you are right.

          I am encountering the same issue. I apply spring data source transaction manager annotation to handle the transaction. The job runs in a thread pool with multiple callable tasks. The business requirement in my system is, if one thread's job fails, all threads' jobs need to be rollback. However, only the one fails is rollback, but the others are still committed into the DB.

          It seems the each thread has its own transaction no matter how I define propagation behavior. (I define REQUIRED in both the overall caller method and the each DAO related update method by using JdbcTemplate to perform db operations)

          How to handle the transactional management in a thread pooling environment by using Spring Jframework DBC DataSourseTransactionManager?

          Please help. Thanks a lot.

          If you use JDBC transaction, spring will bind your transaction with thread. Under spring's implementation it uses lots ThreadLocal. So if you want to manage your transaction in multi thread env it will be some difficult. Maybe you need change's its transaction strategy.


          Another problem, thread's run() method will not throw exception, so maybe you need to customize your UncaughtExceptionHandler for threads, at least it should know the exist of transaction manager and so on.

          My guess is like this, you first start your transaction before all threads start, then maybe you need to pass those transaction env to every thread like jdbc connection(I think you can't use spring's JDBCTemplate anymore). then do your database task in thread. if some exception occurs your UncaughtExceptionHandler will detect it and rollback transaction and stop all thread.

          You have lots work to do, like join threads, share transaction env like jdbc connection, deal exception when one thread fail...

          Just my understanding, I didn't manage transaction with multi threads, too difficult for developers...

          Comment


          • #6
            Originally posted by javeer View Post
            My guess is like this, you first start your transaction before all threads start, then maybe you need to pass those transaction env to every thread like jdbc connection(I think you can't use spring's JDBCTemplate anymore). then do your database task in thread. if some exception occurs your UncaughtExceptionHandler will detect it and rollback transaction and stop all thread.
            Hii,,

            If you do database operations using the jdbc connection (i mean using DataSource and prgramatically by creating & using Connection), then you need to take some extra things as well ( like commiting to DataBase, Connection close/release ) . As default when its complete execution of Query it will commit the changes to back-end. Then you may need to put defaultAutoCommit=false and programatically commit on successful execution.

            But again i dont think once committed to Database will get rollback on exception of any other thread.

            So i suggest to use JDBCTemplate, But i also observed that JDBCTemplate is not working in thread environment (I tried with small demo application). Its not doing rollback. I guess there must be some way to use it in thread environment.

            I hope any expert will come up with some solution/trick for such scenario.

            You can put your code snipptes here, So any expert will guide you.

            Best Luck,,,,
            Last edited by ronak; May 28th, 2010, 05:19 AM.

            Comment


            • #7
              Originally posted by javeer View Post
              If you use JDBC transaction, spring will bind your transaction with thread. Under spring's implementation it uses lots ThreadLocal. So if you want to manage your transaction in multi thread env it will be some difficult. Maybe you need change's its transaction strategy.


              Another problem, thread's run() method will not throw exception, so maybe you need to customize your UncaughtExceptionHandler for threads, at least it should know the exist of transaction manager and so on.

              My guess is like this, you first start your transaction before all threads start, then maybe you need to pass those transaction env to every thread like jdbc connection(I think you can't use spring's JDBCTemplate anymore). then do your database task in thread. if some exception occurs your UncaughtExceptionHandler will detect it and rollback transaction and stop all thread.

              You have lots work to do, like join threads, share transaction env like jdbc connection, deal exception when one thread fail...

              Just my understanding, I didn't manage transaction with multi threads, too difficult for developers...


              Thanks very much for the answer. It definitely helps. As you said, I guess I need to change the strategy of handling transaction in a multi-threading environment. I'll do some test to see what I can get by using the new way.

              Comment


              • #8
                Sorry about the English ability.

                Correct me if I am wrong.
                Using Spring JDBCTemplate, each thread obtains its own database connection, the transaction associated to a thread attaches to this particular connection, so it cannot be shared by other threads. If a thread's job fails, only this thread db job can be rollback, but not other threads jobs.

                Based on the above scenario, if I pass a Connection object as parameter in the overall caller method, and each thread share the same db connection, and apply with programmatic rollback or commit way to handle the transaction. Is it feasible? Is it correct that now all threads stick to only one transaction?
                Last edited by sunflowerr; May 28th, 2010, 03:12 PM.

                Comment


                • #9
                  Originally posted by ronak View Post
                  Hii,,

                  If you do database operations using the jdbc connection (i mean using DataSource and prgramatically by creating & using Connection), then you need to take some extra things as well ( like commiting to DataBase, Connection close/release ) . As default when its complete execution of Query it will commit the changes to back-end. Then you may need to put defaultAutoCommit=false and programatically commit on successful execution.

                  But again i dont think once committed to Database will get rollback on exception of any other thread.

                  So i suggest to use JDBCTemplate, But i also observed that JDBCTemplate is not working in thread environment (I tried with small demo application). Its not doing rollback. I guess there must be some way to use it in thread environment.

                  I hope any expert will come up with some solution/trick for such scenario.

                  You can put your code snipptes here, So any expert will guide you.

                  Best Luck,,,,

                  Thanks a lot.
                  I need find out another solution to make all threads shared within same transaction scope.
                  Anybody is more than welcome to discuss how to make it work in multi-threading env.
                  Last edited by sunflowerr; May 28th, 2010, 07:47 PM.

                  Comment


                  • #10
                    Originally posted by sunflowerr View Post
                    Sorry about the English ability.

                    Correct me if I am wrong.
                    Using Spring JDBCTemplate, each thread obtains its own database connection, the transaction associated to a thread attaches to this particular connection, so it cannot be shared by other threads. If a thread's job fails, only this thread db job can be rollback, but not other threads jobs.

                    Based on the above scenario, if I pass a Connection object as parameter in the overall caller method, and each thread share the same db connection, and apply with programmatic rollback or commit way to handle the transaction. Is it feasible? Is it correct that now all threads stick to only one transaction?
                    Just did a test. It works by passing a Connection object to each thread.

                    Forget to use the Spring Transaction management for JDBCTemplate. use the plain JDBC connection transaction handling by setAutoCommit(false) when initiate the Connection object, then pass this connection object as a parameter for each Thread task. If one thread fails, the exception thrown in a thread will be caught in the overall caller method, and rollback all in the catch block, otherwise commit all.

                    I cannot make it work if use Spring Transaction management.

                    Comment


                    • #11
                      Anyone can help me to handle the transaction in a multi-threading env by using Spring Transaction Management?

                      Comment


                      • #12
                        Originally posted by sunflowerr View Post
                        Anyone can help me to handle the transaction in a multi-threading env by using Spring Transaction Management?
                        post your code, configuration and what you are trying to do...

                        Comment


                        • #13
                          Originally posted by bdangubic View Post
                          post your code, configuration and what you are trying to do...
                          sorry I go back here late.

                          If use JdbcTemplate to manipulate database, my understanding is even within the same thread, when call getJdbcTemplate().update(), a new db connection from the connection pool will be used to update the db. Hence, if in a multi-threading env, how to make all threads' jobs rollback if one thread's job fails by using Spring transaction management?

                          I wrote a small demo, below is the code. The code only rolls back the one that is failed. I need to find a way to handle rollback all threads' job if one thread job fails.

                          Thanks a million.
                          Code:
                          <beans xmlns="http://www.springframework.org/schema/beans"
                              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                              xmlns:tx="http://www.springframework.org/schema/tx"
                              xmlns:aop="http://www.springframework.org/schema/aop"
                              xsi:schemaLocation="http://www.springframework.org/schema/beans
                                  http://www.springframework.org/schem...-beans-2.5.xsd
                                  http://www.springframework.org/schema/tx
                                  http://www.springframework.org/schem...ing-tx-2.5.xsd
                                  http://www.springframework.org/schema/aop
                                  http://www.springframework.org/schema/aop/spring-aop-2.5.xsd">
                          
                              <tx:annotation-driven />
                              
                          	<bean id="dataSource"
                          		class="org.apache.commons.dbcp.BasicDataSource">
                          		<property name="driverClassName">
                          			<value>com.mysql.jdbc.Driver</value>
                          		</property>
                          		<property name="url">
                          			<value>jdbc:mysql://localhost:3306/spring</value>
                          		</property>
                          		<property name="username">
                          			<value>user</value>
                          		</property>
                          		<property name="password">
                          			<value>password</value>
                          		</property>
                          	</bean>
                          	
                              <bean id="transactionManager"
                                  class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
                                  <property name="dataSource" ref="dataSource" />
                              </bean>
                              
                              <bean id="bookShop" 
                              	class="mytest.bookshop.JdbcBookShop" >
                              	<property name="dataSource" ref="dataSource" />
                              </bean>
                              
                              
                              <bean id="bookShopUtil" 
                              	class="mytest.bookshop.BookShopRunUtil" >
                              	<property name="bookShop" ref="bookShop" />
                              </bean>
                              
                          </beans>

                          Code:
                          public class JdbcBookShop extends JdbcDaoSupport {
                          	@Transactional(propagation = Propagation.REQUIRED)
                          	public void updateBookInfo( final String actionName )  throws RuntimeException {
                          		String isbn = "0001";
                          		String userName = "rose";
                          		int price = 11;
                          		
                          		try {
                          			if (actionName.equalsIgnoreCase("BOOK")) {
                          				getJdbcTemplate().update(
                          		                "UPDATE  BOOK  SET PRICE = 2000 " +
                          		                "WHERE ISBN = ?",
                          		                new Object[] { isbn });
                          			}
                          			else if (actionName.equalsIgnoreCase("ACCOUNT")){
                          				 getJdbcTemplate().update(
                          			                "UPDATE ACCOUNT SET BALANCE = BALANCE - ? " +
                          			                "WHERE USERNAME = ?",
                          			                new Object[] { price, userName });
                          				 
                          				 //SQLException here, bad column name
                          				 getJdbcTemplate().update(
                          			                "UPDATE BOOK_STOCK SET STOCKK = STOCK - 1 " +  
                          			                "WHERE ISBN = ?",
                          			                new Object[] { isbn });
                          			}
                          			
                          		}
                          		catch (RuntimeException e) {
                          			System.out.println(e.getMessage());
                          			throw e;
                          		}
                          			
                          	}
                          
                          
                          public class BookShopRunUtil {
                          	......
                              
                              @Transactional(propagation = Propagation.REQUIRED)
                          	public void runThreadPool() {
                                          //create thread pool, and generate two threads
                          		ThreadPoolExecutor threadPoolExecutor = new ThreadPoolExecutor(5, 5, 6L,TimeUnit.SECONDS, new LinkedBlockingQueue(5));
                          		Callable callable = null;
                          		Collection collection = new ArrayList();
                          		try {
                          			callable = new UnitTask("ACCOUNT", bookShop);
                          			collection.add(callable);
                          			callable = new UnitTask("BOOK", bookShop);
                          			collection.add(callable);
                          	
                          			List<Future<Boolean>> returnList = (List<Future<Boolean>>)threadPoolExecutor.invokeAll(collection);
                          			boolean success = true;
                          			for( int i = 0; i < returnList.size(); i++ ) {
                          				if( returnList.get(i).get().booleanValue() == false ) {
                          					success = false;
                          					break;
                          				}
                          			}
                          			if( !success ) {
                          				throw new Exception("RuntimeException occurs in Main");
                          			}
                          		}
                          		catch (InterruptedException ex) {
                          			....
                          		}
                          		catch (Exception ex) {
                          			System.err.println(ex.getMessage());
                          			throw ex;
                          		}
                          	}
                          
                          }
                          
                          public class UnitTask implements Callable{
                          	private String actionTable;
                          	private BookShop bookShop;
                          	
                          	public UnitTask(String actionTable, BookShop bookShop){
                          		this.actionTable= actionTable;
                          		this.bookShop = bookShop;
                          	}
                          	
                          	public Boolean call() { 	
                          		String threadName = Thread.currentThread().getName();
                          		Boolean returnVal;
                          		
                          		try{ 
                          			bookShop.updateBookInfo(actionTable);
                          			returnVal =  true;
                          		}
                          		catch(RuntimeException e){
                          			System.out.println(threadName + e.getMessage());
                          			returnVal = false;
                          		}
                          		return returnVal;
                          	}
                          
                          }
                          Last edited by sunflowerr; Jun 4th, 2010, 01:11 PM.

                          Comment


                          • #14
                            Me Too!Need Your Help!

                            Comment


                            • #15
                              Anyone can help? please?

                              Comment

                              Working...
                              X