mybatis笔记：Lock wait timeout exceeded； try restarting transaction

最新推荐文章于 2024-08-15 16:10:18 发布

Hars、Wlgb

最新推荐文章于 2024-08-15 16:10:18 发布

阅读量784

点赞数 1

分类专栏： mybatis 编程所遇小bug 文章标签：单元测试

本文链接：https://blog.csdn.net/Harswlgb/article/details/107785007

版权

编程所遇小bug 同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

mybatis

2 篇文章 0 订阅

订阅专栏

今天在学习中单元测试的时候出现了这个错误

org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
### The error may involve com.yc.dao.OptionMapper.addOptions-Inline
### The error occurred while setting parameters
### SQL: insert into tb_options (vid,opname,views,usid) values           (?,?,0,null)    ,    (?,?,0,null)    ,    (?,?,0,null)    ,    (?,?,0,null)
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
	at com.sun.proxy.$Proxy12.addOptions(Unknown Source)
	at com.yc.test.VoteDAOTest.testAdd(VoteDAOTest.java:33)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:115)
	at org.junit.vintage.engine.execution.RunnerExecutor.execute(RunnerExecutor.java:40)
	at java.util.stream.ForEachOps$ForEachOp$OfRef.accept(Unknown Source)
	at java.util.stream.ReferencePipeline$3$1.accept(Unknown Source)
	at java.util.Iterator.forEachRemaining(Unknown Source)
	at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Unknown Source)
	at java.util.stream.AbstractPipeline.copyInto(Unknown Source)
	at java.util.stream.AbstractPipeline.wrapAndCopyInto(Unknown Source)
	at java.util.stream.ForEachOps$ForEachOp.evaluateSequential(Unknown Source)
	at java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(Unknown Source)
	at java.util.stream.AbstractPipeline.evaluate(Unknown Source)
	at java.util.stream.ReferencePipeline.forEach(Unknown Source)
	at org.junit.vintage.engine.VintageTestEngine.executeAllChildren(VintageTestEngine.java:80)
	at org.junit.vintage.engine.VintageTestEngine.execute(VintageTestEngine.java:71)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:229)
	at org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$6(DefaultLauncher.java:197)
	at org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:211)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:191)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:137)
	at org.eclipse.jdt.internal.junit5.runner.JUnit5TestReference.run(JUnit5TestReference.java:89)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:41)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:542)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:770)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:464)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:210)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:952)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
	at com.sun.proxy.$Proxy13.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
	... 48 more

这是我测试类的代码

package com.yc.test;

import java.util.Arrays;
import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.yc.bean.Vote;
import com.yc.dao.OptionMapper;
import com.yc.dao.VoteMapper;
import com.yc.util.DbUtil;
import com.yc.util.VoteUtil;

public class VoteDAOTest {
	DbUtil db = new DbUtil();
	@Test
	public void testAdd() throws Exception{
		
		SqlSession session = db.getSession();
		VoteMapper mapper = db.getMapper(VoteMapper.class);
		OptionMapper optionMapper = db.getMapper(OptionMapper.class);
		Vote vote = new Vote();
		vote.setEndDate("2020-09-01");
		vote.setStartDate("2020-08-20");
		vote.setVtype(1);
		vote.setVname("开始三期项目2");
		vote.setVid(VoteUtil.genVid());
		String [] options = new String[]{"4月上旬","4月中旬","4月下旬","5月上旬"};
		List<String> ops = Arrays.asList(options);

		int i = mapper.add(vote);
		int j = optionMapper.addOptions(ops, vote.getVid());

		if (i > 0 && j > 0) {
			session.commit();
		}

		db.closeSession(session);
		// dao.add(vote,options);
		
	}
}

后来发现原因是我的dbutil中，每一个会话都是单独创建关闭的
这是dbutil的代码

package com.yc.util;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class DbUtil {
	// static SqlSession session;

	public SqlSessionFactory getSqlSessionFactory() throws IOException {
		String config = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(config);
		SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
		return factory;
	}

	public SqlSession getSession(boolean autoCommit) throws IOException {
		SqlSessionFactory factory = getSqlSessionFactory();
		SqlSession session = factory.openSession(autoCommit);
		return session;
	}

	public SqlSession getSession() throws IOException {
		SqlSessionFactory factory = getSqlSessionFactory();
		SqlSession session = factory.openSession();
		return session;
	}

	/**
	 * 获取接口代理对象
	 * 
	 * @param <T>
	 * @param c
	 * @return
	 * @throws IOException
	 */
	public <T> T getMapper(Class<T> c) throws IOException {
		SqlSession session = getSession();
		T t = session.getMapper(c);
		return t;
	}

	public <T> T getMapper(Class<T> c, boolean autoCommit) throws IOException {
		SqlSession session = getSession(autoCommit);
		T t = session.getMapper(c);
		return t;
	}
	/**
	 * 关闭会话
	 */
	public void closeSession(SqlSession session) {
		if (null != session) {
			session.close();
		}
	}
}

解决方案是吧测试类中的会话共同使用也就是session.getMapper()而不是用db.getMapper()

以下是正确的测试类代码

package com.yc.test;

import java.util.Arrays;
import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.yc.bean.Vote;
import com.yc.dao.OptionMapper;
import com.yc.dao.VoteMapper;
import com.yc.util.DbUtil;
import com.yc.util.VoteUtil;

public class VoteDAOTest {
	DbUtil db = new DbUtil();
	@Test
	public void testAdd() throws Exception{
		
		SqlSession session = db.getSession();
		VoteMapper mapper = session.getMapper(VoteMapper.class);
		OptionMapper optionMapper = session.getMapper(OptionMapper.class);
		Vote vote = new Vote();
		vote.setEndDate("2020-09-01");
		vote.setStartDate("2020-08-20");
		vote.setVtype(1);
		vote.setVname("开始三期项目2");
		vote.setVid(VoteUtil.genVid());
		String [] options = new String[]{"4月上旬","4月中旬","4月下旬","5月上旬"};
		List<String> ops = Arrays.asList(options);

		int i = mapper.add(vote);
		int j = optionMapper.addOptions(ops, vote.getVid());

		if (i > 0 && j > 0) {
			session.commit();
		}

		db.closeSession(session);
		// dao.add(vote,options);
		
	}
}