mysql数据库更新记录死锁，提示: Lock wait timeout exceeded； try restarting transaction

最新推荐文章于 2024-08-15 15:33:35 发布

qq_34787830

最新推荐文章于 2024-08-15 15:33:35 发布

阅读量840

点赞数

文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/qq_34787830/article/details/121758286

版权

先说解决办法：

我们可以通过到information_schema 中来进行查找被锁的语句。

解释： information_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。再简单点，这台MySQL服务器上，到底有哪些数据库、各个数据库有哪些表，每张表的字段类型是什么，各个数据库要什么权限才能访问，等等信息都保存在information_schema表里面。

我们可以用下面三张表来查原因：

innodb_trx 当前运行的所有事务
innodb_locks 当前出现的锁
innodb_lock_waits 锁等待的对应关系

如果数据库中有锁的话，我们可以使用这条语句来查看：

select * from information_schema.innodb_trx

aused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
   at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
   at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
   at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
   at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
   at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
   at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
   at sun.reflect.GeneratedMethodAccessor269.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
   at com.sun.proxy.$Proxy659.execute(Unknown Source)
   at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
   at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
   at sun.reflect.GeneratedMethodAccessor403.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
   at com.sun.proxy.$Proxy657.update(Unknown Source)
   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 sun.reflect.GeneratedMethodAccessor189.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
   at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:83)
   at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
   at com.sun.proxy.$Proxy656.update(Unknown Source)
   at sun.reflect.GeneratedMethodAccessor189.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
   at com.sun.proxy.$Proxy656.update(Unknown Source)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
   ... 161 more