解决事务未完成提交，其它db连接查询不到的问题

一、前提:

使用spring注解方式声明事务；

隔离级别默认采用数据库的事务隔离级别，我当前的db隔离级别为REPEATABLE-READ；

采用spring TaskExecutor启动异步任务

二、解决思路：

原来是db insert和db query操作在同一个事务里面顺序执行。改为db query放到一个线程里面异步执行，且先等待1000ms再作查询操作。

三、查询mysql事务隔离级别

SELECT @@global.tx_isolation; 

SELECT @@session.tx_isolation; 

SELECT @@tx_isolation;

四、配置

配置spring线程池

	<bean id="taskExecutor" class="org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor"> 
	    <!-- corePoolSize 同时运行的线程数量 -->    
	    <property name="corePoolSize" value="100" />     
	    
	    <!-- maxPoolSize+queueCapacity 添加的最多线程数量 -->  
	    <property name="maxPoolSize" value="300" />     
	    <property name="queueCapacity" value="300" />  
	</bean>
spring事务开启配置 
	<bean id="txManager"
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource" />
	</bean>
	
	<tx:annotation-driven transaction-manager="txManager"/>
五、代码
1、mvc controller
package com.hcb.mc.controller;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.task.TaskExecutor;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseStatus;

import com.hcb.mc.service.UserAtomicService;


@Controller
@RequestMapping(value = "/db-test")
public class DBTestController {

	private static Logger LOG = LoggerFactory.getLogger(DBTestController.class);
	
	@Resource 
	UserAtomicService userAtomicService;
	
	@Resource
	private TaskExecutor taskExecutor;
	
    /**
     * 测试并发事务访问同时读写，读不完整问题
     * http://localhost:8080/mfy-platform/db-test/dao-test
     * @param request
     * @param response
     */
    @RequestMapping(value="/dao-test", method =RequestMethod.GET)
	@ResponseStatus(HttpStatus.NO_CONTENT)
    public void daoTest(HttpServletRequest request,HttpServletResponse response){
    	
    	this.fixConcurrentFeedbackProblem();
    	
    	LOG.info("dao-test execute ok.");
    }
    
	public void fixConcurrentFeedbackProblem() {
		for (int i = 0; i < 2; i++) {
			taskExecutor.execute(new UserServiceTask("Message" + i));
			
		}
	}

	private class UserServiceTask implements Runnable { 
		private String message;

		public UserServiceTask(String message) {
			this.message = message;
		}

		public void run() {
			//模拟并发反馈问题
			//查询不到数据库事务未完成提交的记录
			//userAtomicService.testConcurrentFeedbackProblem(message);
			
			//解决并发反馈问题
			userAtomicService.testFixConcurrentFeedbackProblem(message);
			try {
				Thread.sleep(500);
			} catch (InterruptedException e) {
				e.printStackTrace();
			}
		}
	}
	
}
2、service类
package com.hcb.mc.service;

import javax.annotation.Resource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.task.TaskExecutor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.hcb.mc.controller.DBTestController;
import com.hcb.mc.vo.User;
import com.hcb.mybatis.UserDao;

@Service
public class UserAtomicService {

	private static Logger LOG = LoggerFactory.getLogger(DBTestController.class);
	
	@Resource 
	UserDao userDao;
	
	@Resource
	private TaskExecutor taskExecutor;
	
	/**
	 * 插入数据
	 * @param name
	 */
	private void insert(String name) { 
    	User toAddUser = new User(); 
    	toAddUser.setName("newInsertName"); 
    	userDao.insert(toAddUser);
    	LOG.info("userDao.insert execute." );
	}
	
	
	/**
	 * 查询数据
	 * @param name
	 */
	private void query(String name) {
    	LOG.info(name + " --------userDao.countAll():" + userDao.countAll());
	}
	
	/**
	 * 模拟并发反馈问题
	 * 查询不到数据库事务未完成提交的记录
	 * @param name
	 */
	@Transactional
	public void testConcurrentFeedbackProblem(String name) { 
		this.insert(name); 
		this.query(name);
	}
	
	/**
	 * 解决并发反馈问题
	 * @param name
	 */
	@Transactional
	public void testFixConcurrentFeedbackProblem(final String name) {
		this.insert(name); 
		taskExecutor.execute(new Thread() {
			public void run() {
				try {
					Thread.sleep(500);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
				
				query(name);
			}
		});
	}
	
}