mysql异常解决方案 - Lock wait timeout exceeded异常

最新推荐文章于 2023-10-24 21:45:22 发布
最新推荐文章于 2023-10-24 21:45:22 发布
阅读量7.9k 收藏 15
点赞数 3
分类专栏： 数据库 文章标签： hibernate 数据库
原文链接：https://juejin.cn/post/6917605106582028302
版权

问题

  • 最近线上业务在运行过程中，遇到如下异常：
2021-01-13 10:25:20.838 WARN [http-nio-7010-exec-104][SqlExceptionHelper.java:127] - SQL Error: 1205, SQLState: 41000
2021-01-13 10:25:20.839 INFO [http-nio-7010-exec-104][AbstractBatchImpl.java:193] - HHH000010: On release of batch it still contained JDBC statements
2021-01-13 10:25:20.839 ERROR [http-nio-7010-exec-104][SqlExceptionHelper.java:129] - Lock wait timeout exceeded; try restarting transaction
Stack trace:
org.springframework.dao.CannotAcquireLockException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.LockTimeoutException: could not execute statement
...
Caused by: org.hibernate.exception.LockTimeoutException: could not execute statement
	at org.hibernate.dialect.MySQLDialect$3.convert(MySQLDialect.java:520)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:207)
	at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45)
	at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3134)
	at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:3013)
	at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3393)
	at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:145)
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:582)
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:456)
	at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:337)
	at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
	at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1282)
	at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:465)
	at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:2963)
	at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2339)
	at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:485)
	at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:147)
	at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$100(JdbcResourceLocalTransactionCoordinatorImpl.java:38)
	at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:231)
	at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:65)
	at org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:61)
	at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:517)
	... 181 more
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2843)
	at com.mysql.jdbc.LoadBalancedMySQLConnection.execSQL(LoadBalancedMySQLConnection.java:166)
	at sun.reflect.GeneratedMethodAccessor366.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.mysql.jdbc.LoadBalancingConnectionProxy.invoke(LoadBalancingConnectionProxy.java:651)
	at com.mysql.jdbc.LoadBalancingConnectionProxy.invoke(LoadBalancingConnectionProxy.java:556)
	at com.sun.proxy.$Proxy212.execSQL(Unknown Source)
	at com.mysql.fabric.jdbc.FabricMySQLConnectionProxy.execSQL(FabricMySQLConnectionProxy.java:961)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2085)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2337)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2265)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2249)
	at sun.reflect.GeneratedMethodAccessor369.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
	at com.sun.proxy.$Proxy465.executeUpdate(Unknown Source)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
	... 201 more
  • 业务场景：发布某些特定的资源，发现一直在等待，直到超时抛出异常

问题定位过程

  • 从异常堆栈来看，问题的根源在于Lock wait timeout exceeded; try restarting transaction异常。从异常信息来看，应该是：mysql锁超时了
  • 刚开始的时候，认为是大资源导致操作的sql较多，从而达到设置的最大超时时间。尝试在测试、预生产模拟该类大资源，但数据能正常审核。通过sql语句(如下)检查各环境innodb_lock_wait_timeout的值，发现都是50s。结合pinpoint上的调用链可发现，整个事务的执行时间大概4s左右，但事务一直没提交，直到超时。所以基本上可以排除掉该业务本身导致的超时
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
  • 排除了业务本身的超时可能性后，基本上可以确认是两个事务之间，锁等待导致的超时。因为锁等待是mysql抛出的异常，第一反应是mysql是否提供了锁等待的相关详情用于辅助定位问题。经过百度、google发现mysql的information_schema确实有几个表（innodb_trx、innodb_locks、innodb_lock_waits）来存储锁的相关信息可用于定位问题
#  锁等待的对应关系
select * from information_schema.innodb_lock_waits;
# 当前出现的锁
select * from information_schema.innodb_locks;
# 当前运行的所有事务
select * from information_schema.innodb_trx;
# 当前线程详情
show full processlist;
  • 经过与DBA的配合,在生产复现了该问题,拿到了相关数据。通过对数据的分析可以看到，确实是一个事务在等待另一个事务释放锁，另一个事务持有了一个资源的资源，导致另一个事务一直等待到超时。并且通过trx_started可发现，持有锁的事务是一天前开启的，还没释放。

  • 从上面的数据分析来看，是：昨天开启的一个事务导致的，该事务对应的线程数为20154145，该线程id对应的info为null，user为业务正常使用的账号。考虑到业务上都是使用@Transactional注解来声明事务,按理说,spring应该会正常管理事务进行commit和rollback。所以定位到此，还是没找到根本原因

  • 想着如果是某个业务导致的，服务重启之后，mysql连接断掉之后，应该就会正常。因此，对服务进行了重启
  • 重启之后，发现该问题还是存在，资源还是无法发布。因此重新找DBA拿了相关数据。分析数据可以发现，持有锁的事务id变了，原先的那个事务不见了，但新启了一个事务，该事务持有了锁，导致了后续业务无法正常拿到锁。并且，该事务的开始时间基本上在服务重启的一小会。所以应该是存在什么业务会去开启一个新事务，并且一直不commit
  • 结合业务场景，想了很久还是无法想到有什么业务会导致这种情况。因此只能让DBA先把持有锁的事务kill掉
  • 这时候出现了一个很神奇的事情，当DBA把线程kill掉之后，有个MQ发送了消费异常的告警消息。至此造成锁等待超时的元凶算是找到了
  • 进一步分析这个MQ的消费可以发现：该MQ会开启一个事务，在事务中，会取到被影响到的所有资源（大约1w多），然后for循环对相关资源进行联动操作。所以造成了事务开启后，一直无法提交

mysql锁相关表详解

innodb_lock_waits表

requesting_trx_id：请求事务的ID
requested_lock_id：请求事务的锁ID。可以和 INNODB_LOCKS 表 JOIN
blocking_trx_id：阻塞事务的 ID
blocking_lock_id：阻塞事务的锁ID。可以和 INNODB_LOCKS 表 JOIN

innodb_locks表

lock_id：锁id
lock_trx_id：拥有锁的事务ID。可以和 INNODB_TRX 表 JOIN 得到事务的详细信息
lock_mode：锁的模式。有如下锁类型：行级锁包括：S、X、IS、IX，分别代表：共享锁、排它锁、意向共享锁、意向排它锁。表级锁包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁。
lock_type：锁的类型。RECORD 代表行级锁，TABLE 代表表级锁。
lock_table：被锁定的或者包含锁定记录的表的名称。
lock_index：当lock_table='RECORD' 时，表示索引的名称；否则为 NULL。
lock_space：当lock_table='RECORD' 时，表示锁定行的表空间ID；否则为 NULL。
lock_page：当lock_table='RECORD' 时，表示锁定行的页号；否则为 NULL。
lock_rec：当lock_table='RECORD'时，表示一堆页面中锁定行的数量，亦即被锁定的记录号；否则为 NULL。
lock_data：当lock_table='RECORD' 时，表示锁定行的主键；否则为NULL

innodb_trx表

trx_id：事务ID
trx_state：事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING
trx_started：事务开始时间
trx_requested_lock_id：事务当前正在等待锁的标识，可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息
trx_wait_started：事务开始等待的时间
trx_weight：事务的权重
trx_mysql_thread_id：事务线程 ID，可以和 PROCESSLIST 表 JOIN。
trx_query：事务正在执行的 SQL 语句。
trx_operation_state：事务当前操作状态。
trx_tables_in_use：当前事务执行的 SQL 中使用的表的个数。
trx_tables_locked：当前执行 SQL 的行锁数量。
trx_lock_structs：事务保留的锁数量。
trx_lock_memory_bytes：事务锁住的内存大小，单位为 BYTES。
trx_rows_locked：事务锁住的记录数。包含标记为 DELETED，并且已经保存到磁盘但对事务不可见的行。
trx_rows_modified：事务更改的行数。
trx_concurrency_tickets：事务并发票数。
trx_isolation_level：当前事务的隔离级别。
trx_unique_checks：是否打开唯一性检查的标识。
trx_foreign_key_checks：是否打开外键检查的标识。
trx_last_foreign_key_error：最后一次的外键错误信息。
trx_adaptive_hash_latched：自适应散列索引是否被当前事务锁住的标识。
trx_adaptive_hash_timeout：是否立刻放弃为自适应散列索引搜索 LATCH 的标识

processlist表

Id：连接标识符。可通过kill id将该线程kill掉
User：访问的用户
Host：发出该语句的客户端的主机名。格式：host_name:client_port
db：线程的默认数据库。如果未选择任何数据库，则为null
Command: 线程代表客户端执行的命令类型。如果会话处于空闲状态，则为Sleep。该列的值对应于客户端/服务器协议的 命令和 状态变量
Time：线程处于其当前状态的时间（以秒为单位）
State：指示线程正在执行的操作，事件或状态
Info：线程正在执行的语句。如果线程不执行任何语句，则为null

结论

  • 开发业务的时候要注意：尽量不要有长事务，长事务很容易造成锁竞争
  • 遇到 Lock wait timeout exceeded 异常时，可结合 innodb_lock_waits（锁等待的对应关系）、innodb_locks（当前出现的锁）、innodb_trx（当前运行的所有事务）、show full processlist（当前线程详情）进行分析及处理。具体步骤如下：
    • select * from information_schema.innodb_lock_waits;找出相互等待的事务
      • 重点关注：blocking_trx_id(阻塞事务的id)
    • select * from information_schema.innodb_locks;查看各事务锁的相关数据
      • 重点关注：lock_trx_id(阻塞事务的id)、lock_table(锁住的表)、lock_data(锁定行的主键)
    • select * from information_schema.innodb_trx;获取当前正在运行的事务详情
      • 重点关注：trx_id(事务id)、trx_state(事务状态)、trx_started(事务开始时间)、trx_mysql_thread_id(对应的线程id)、trx_query(执行的语句)
    • show full processlist;获取当前各线程详情，辅助定位问题
    • 识别出造成锁等待的事务对应的线程id后，用kill id;命令将对应的线程kill掉即可

参考链接

【异常解决】MySQL数据库：Lock wait timeout exceeded； try restarting transaction问题解析及解决方案
DreamDlee的博客
04-20 1632
因为使用的数据库为 MySQL，而 member_level_record 表的引擎是 InnoDB 表类型，此时会出现锁等待的情况，在出现锁等待时，会根据参数 innodb_lock_wait_timeout（默认50s）的配置，判断是否需要进行 timeout 的操作，如果等待时间超过了设置的时间就会报错。也就是说，这台 MySQL 服务器上，到底有哪些数据库、各个数据库有哪些表，每张表的字段类型是什么，各个数据库要什么权限才能访问等等信息都保存在 information_schema 库里面。
Mysql更新数据时出现 Lock wait timeout exceeded 问题处理步骤
最新发布
码觉客的博客
02-26 977
当遇到 “Lock wait timeout exceeded;try restarting transaction” 错误时，通常是由于数据库事务长时间等待锁资源超时导致。，需通过诊断工具定位阻塞源，及时终止异常事务，并结合事务优化、索引调整等措施长期规避。临时调整超时参数仅作为应急手段，优化数据库设计才是根本解决方案。增大锁等待超时时间（临时缓解，需重启事务生效）。确认阻塞事务后，终止其进程（需谨慎操作）。首先确定哪个事务持有锁并阻塞了其他操作。
Mysql错误: Lock wait timeout exceeded 解决办法
weixin_30872671的博客
04-10 383
一、临时解决办法： 执行mysql命令:show full processlist; 然后找出插入语句的系统id 执行mysql命令:kill id 或 首先,查看数据库的进程信息: show full processlist; /*结果略,主要看id列的值*/ 再查看事物表: SELECT * FROM information_schema.INNODB_TRX\G;/...
MySQL事务锁问题-Lock wait timeout exceeded
weixin_33882452的博客
08-08 1166
问题现象：   接口响应时间超长，耗时几十秒才返回错误提示，后台日志中出现Lock wait timeout exceeded; try restarting transaction的错误 问题场景：   1、在同一事务内先后对同一条数据进行插入和更新操作；   2、多台服务器操作同一数据库；   3、瞬时出现高并发现象； 问题原因：  ...
Lock wait timeout exceeded解决方案
程序员精进之路
12-13 3万+
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction 基本原因：后提交的事务等待前面处理的事务释放锁，但是在等待的时候超过了mysql的锁等待时间，就会引发这个异常。 1.通过到information_schema中来进行查找被锁的语句 information_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。再简单点，这台MySQL服务器上，到底有哪.
Lock wait timeout exceeded； try restarting transaction问题解析
热门推荐
zcm036200的博客
12-01 13万+
目录 一、异常发现 二、异常定位 1、锁表语句确认 2、实际场景排查 三、解决思路 1、本次解决方式 2、其他场景解决思路扩展 1、【治标方法】innodb_lock_wait_timeout锁定等待时间改大 2、【治标方法】事务信息查询 3、【治标方法】如果杀掉线程依然不能解决，可以查找执行线程耗时比较久的任务，kill掉 4、【根本解决方法！】找到锁表的事务，分析锁表原因，进行优化 一、...
mysql Lock wait timeout exceeded； try restarting transaction
一个喜欢诗和远方的程序媛
05-12 3684
也就是遇到了mysql死锁，等待资源，事务锁的问题。可能原因：意外处理没有关闭连接，导致连接过多、或是要更新的表的锁在其它线程手里、系统异常导致事务未提交，再次请求相同记录等等。当中trx_mysql_thread_id为事务线程的id，參照show full processlist命令中的线程信息查看。innodb_lock_waits表则由performance_schema.data_lock_waits表代替。综上大体可以清楚的找到等待的事务即没有获取锁的事务，进一步调整业务逻辑代码。
从报错到溯源-Lock wait timeout exceeded...
qq_42985101的博客
07-14 371
这个项目是个小项目，用户比较少，但是用户在这么少的情况下，还能出现锁等待超时的问题，由于这个项目还是部署在内网的，导致每次解决问题都只能去客户那边，难受。废话不多说，直接开讲。 首先这个报这个错的原因是：假设存在事务A和事务B，事务A一直在占用着某个资源，并且这个资源被上了锁，但是事务B也想要这个资源，那么事务B就等着事务A执行完，好把锁释放掉，把这个资源让给事务B，但是这事务A执行的时间太长了，导致事务B等待的时间也就太长了，等待时间超过了MySQL中默认的锁等待时间（50s），于是就报了这个错。 看
Mysql 异常：Lock wait timeout exceeded； try restarting transaction的解决办法
Hello World
01-18 1万+
问题现象   接口响应时间超长，耗时几十秒才返回错误提示，后台日志中出现Lock wait timeout exceeded; try restarting transaction的错误 <-- java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
mysql 报错：error: 1205 Lock wait timeout exceeded； try restarting transaction
oToyix的博客
04-18 1480
error: 1205 Lock wait timeout exceeded; try restarting transaction
Lock wait timeout exceeded
eagle89的专栏
10-19 1028
在做修改语句的时候，数据库抛出Lock wait timeout exceeded; try restarting transaction这样的异常，提示错误的意思很明显，是因为这条语句被锁住了，所以释放这个锁。 解决方案: 1.通过sql： SELECT * FROM information_schema.INNODB_TRX 来查看当前所运行的所有事务，通过查询表，发现有一条事务记录一直处于RUNNING的状态 2.再次通过sql：SELECT * FROM information_...
mysql lock wait_Mysql错误: Lock wait timeout exceeded 解决办法
weixin_36006862的博客
01-18 6402
一、临时解决办法：执行mysql命令:show full processlist;然后找出插入语句的系统id执行mysql命令:kill id或首先,查看数据库的进程信息:show full processlist;/*结果略,主要看id列的值*/再查看事物表:SELECT * FROM information_schema.INNODB_TRX\G;/*结果略,注意结果中的trx_mysql_t...
MySQL:关于Lock wait timeout exceeded; try restarting transaction
熊诗言的博客
07-27 7641
1、锁等待超时。是当前事务在等待其它事务释放锁资源造成的。可以找出锁资源竞争的表和语句，优化你的SQL，创建索引等，如果还是不行，可以适当减少并发线程数。 2、你的事务在等待给某个表加锁时超时了，估计是表正被另的进程锁住一直没有释放。 可以用 SHOW INNODB STATUS/G; 看一下锁的情况。 3、搜索解决 之道 在管理 节点的[ndbd default] 区加： Transacti...
Lock wait timeout exceeded mysql锁表和解决方案
启迪思维，精进技术
10-24 7395
mysql 锁表和查询超时解决办法
java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
chuncui2576的博客
12-29 508
在开发测试过程中发现出了这个问题： RECHARGE：业务，数据: {"platformUserNo":"RPD_1512632756191","amount":6000,"rechargeWay":"SWIFT","bankcode":"ICBK","payCompany":"LANMAO...
'Lock wait timeout exceeded; try restarting transaction'问题
weixin_34314962的博客
10-14 98
OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')   原因很简单，太多错误，意外处理没有关闭连接，导致连接过多。
mysql 1205 -Lock wait timeout exceeded; try restarting transaction 如何解决
08-18
当MySQL遇到"Lock wait timeout exceeded; try restarting transaction"错误时，这通常意味着某个事务正在等待锁定资源的时间超过了服务器设置的默认锁超时时间。以下是几种可能的解决方案： 1. **检查并调整锁超时时间**： - 登录到MySQL数据库，通过命令行或配置文件(`my.cnf`或`.my.ini`)修改`innodb_lock_wait_timeout`参数。例如，将其增加到更长的值，如60秒、180秒等。 ``` [mysqld] innodb_lock_wait_timeout = 180 ``` 2. **优化查询**： - 确保查询是高效的，避免长时间阻塞其他事务。尽量减少嵌套事务，及时释放不需要的资源。 - 对于频繁使用的索引进行优化，确保表被正确地分区和索引。 3. **手动中断并重启事务**： - 如果你知道可以取消当前事务的操作，尝试使用`ROLLBACK`或`COMMIT`来结束事务。 - 或者，如果是程序运行的问题，考虑在代码中处理这种情况，比如设置重试机制，如果发生此错误则重新尝试操作，但需注意不要无限循环。 4. **排查死锁**： - 使用`SHOW ENGINE INNODB STATUS`命令查看是否有死锁事件。如果有死锁，可能需要手动解决或调整事务顺序。 5. **服务器硬件升级**： - 如果经常遇到这个错误，可能是由于服务器性能不足导致的，考虑提升内存或CPU来改善事务处理能力。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值