项目中遇到的死锁问题 Lock wait timeout exceeded try restarting transaction / 开普饭

最近项目中频繁出现 Lock wait timeout exceeded; try restarting transaction这个错误，把我们弄得痛苦不堪啊，为了解决问题，上网上找好多资料，终于把问题复现了。具体操作步骤如下（我使用的mysql工具是 navicat）：

第一步：开启sql命令页面

第二步：输入start transaction;开启一个事务

第三步：输入update语句 UPDATE TABLENAME set time=null where id=29163;（切记不要提交（commit；））

第四步：书写测试类

public class StTest implements Runnable{

private static ApplicationContext applicationContext=null;

private static AtomicInteger a=new AtomicInteger(0);

static {

applicationContext=new ClassPathXmlApplicationContext("classpath:application.xml");

}

public static void main(String[] args) throws Exception {

ExecutorService executorService = Executors.newFixedThreadPool(10);

for (int i = 0; i <10 ; i++) {

executorService.execute(new aaaa());

}

if(!executorService.isTerminated()){

executorService.shutdown();

}

@Override

public void run() {

XXXService bean = applicationContext.getBean(XXXService.class);

XXXBeand xx= null;

try {

xx= bean.byId(254213);

} catch (Exception e) {

e.printStackTrace();

}

long aaa=System.currentTimeMillis();

for (int i = 0; i < 10; i++) {

try {

bean.update(xx);

} catch (Exception e) {

e.printStackTrace();

}

System.out.println(System.currentTimeMillis()-aaa);

}

运行程序并且运行下面3个sql查看结果

SELECT * FROM information_schema.INNODB_TRX;

select * from information_schema.innodb_lock_waits;

select * from information_schema.innodb_locks;

　　得到以下运行结果。我们发现有其他几个的 trx_state 状态是LOCK WAIT，得到我们要复现的结果

第五步：等待错误出现，在我们等待一段时间之后会发现程序报错

Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)

at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:995)

at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493)

at sun.reflect.GeneratedMethodAccessor21.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:55)

at com.sun.proxy.$Proxy17.execute(Unknown Source)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)

at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)

at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:100)

at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)

at sun.reflect.GeneratedMethodAccessor35.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59)

at com.sun.proxy.$Proxy15.update(Unknown Source)

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148)

at sun.reflect.GeneratedMethodAccessor34.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:354)

... 20 more

从上面得到的结果，可以知道这个错误是在一个事务没有提交的时候，其他事务也操作相同对象导致的，那么找到了问题的原因，我们就可以针对这种情况进行修改了。

这是本次我遇到的错误，分享给大家一下，以避免程序中遇到这样的情况。

项目中遇到的死锁问题 Lock wait timeout exceeded try restarting transaction

相关推荐