Lock wait timeout exceeded； try restarting transaction异常排查

原创于 2025-08-22 09:12:13 发布 · 176 阅读

CC 4.0 BY-SA版权

文章标签：

报错信息

2025-08-21 18:07:29,347 ERROR [Telecom-Diversion-Test-Result-update-Thread] c.j.j.e.t.TDTTaskResultUpdateExecutor [TDTTaskResultUpdateExecutor.java : 116] [TDT][UPDATE]执行任务时发生异常
org.springframework.dao.CannotAcquireLockException:
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
### The error may exist in class path resource [mapper/TelecomUserDiversionChannelReportDetailMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: update talbe_name         set report_status = ?         where channel_id = ?         and ext = ?         and sign = ?
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:267)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
	at com.sun.proxy.$Proxy71.update(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:288)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:67)
	at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:145)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
	at com.sun.proxy.$Proxy148.updateReportStatusByChannelIdAndExtAndSign(Unknown Source)
	at com.jjxt.job.executor.service.impl.TelecomUserDiversionChannelReportDetailServiceImpl.updateReportStatusByChannelIdAndExt(TelecomUserDiversionChannelReportDetailServiceImpl.java:94)
	at com.jjxt.job.executor.thread.TDTTaskResultUpdateExecutor.selectReportStatusAndUpdate(TDTTaskResultUpdateExecutor.java:230)
	at com.jjxt.job.executor.thread.TDTTaskResultUpdateExecutor.run(TDTTaskResultUpdateExecutor.java:103)
Caused 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.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:371)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3461)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3459)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
	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:194)
	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:427)
	... 9 common frames omitted

问题描述

可以肯定的是这个报错是MySQL InnoDB 行锁等待超时，发生在 UPDATE 操作上。

排查方向

表更新是否太快，且表更新where语句没有索引，导致扫描更多行加更多锁，甚至表锁。
排查是否有其他事务事务没有释放锁导致这里的更新操作获取锁超时。

问题排查

表的问题排查

查看是否是表索引问题导致。
发现没有索引，开始加唯一索引，尝试解决问题。
加索引的时候发现执行很慢，等了5分钟没执行完，这时候怀疑是否是表锁了。

锁问题排查

1.说明索引创建被阻塞了，不是在“慢慢建”，而是在“等锁”。

SELECT * FROM information_schema.INNODB_TRX ORDER BY trx_started DESC\G;

执行结果：

                    trx_id: 10648548704
                 trx_state: RUNNING
               trx_started: 2025-08-21 15:14:29
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 50187
       trx_mysql_thread_id: 1484756
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 50185
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0