解决思路

1. 找到一直未提交的事务
2. 把这个事务杀掉

解决方案细节

1. 查找未提交的事务

mysql> select trx_id,trx_state from information_schema.innodb_trx;
+--------+-----------+
| trx_id | trx_state |
+--------+-----------+
| 101927 | RUNNING   |
+--------+-----------+
1 row in set (0.00 sec)

可以看到有一个事务没有提交，id为101927

2. 查找未提交事务的 session

mysql> show full processlist;
+----+-----------------+-----------------+--------+---------+--------+------------------------+-----------------------+
| Id | User            | Host            | db     | Command | Time   | State                  | Info                  |
+----+-----------------+-----------------+--------+---------+--------+------------------------+-----------------------+
|  5 | event_scheduler | localhost       | NULL   | Daemon  | 376984 | Waiting on empty queue | NULL                  |
|  9 | root            | localhost:12255 | db_seq | Sleep   |     53 |                        | NULL                  |
| 11 | root            | localhost:12733 | db_seq | Query   |      0 | init                   | show full processlist |
| 12 | root            | localhost:12764 | db_seq | Sleep   |     19 |                        | NULL                  |
| 13 | root            | localhost:12790 | NULL   | Sleep   |     27 |                        | NULL                  |
+----+-----------------+-----------------+--------+---------+--------+------------------------+-----------------------+

这里没有复现出来，如果一个事务卡很久它的 Command 会是 Query，假装这里12号session是卡着没提交的会话;

3. 杀死会话

mysql> kill 12;
Query OK, 0 rows affected (0.00 sec)

执行完在程序那边会抛个异常，表示事务提交失败，数据进行了回滚。
这样就不会卡着其他事务提交了

问题背景介绍

我同事上线了一个新的服务, 然后我又把数据库的字段改了, 阴差阳错导致了锁表, 事务又未提交, 新服务在疯狂抛异常

详细报错

2019-07-08 10:03:01.048 [SimpleAsyncTaskExecutor-1] INFO  o.s.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
2019-07-08 10:03:01.085 [SimpleAsyncTaskExecutor-1] ERROR o.s.a.i.SimpleAsyncUncaughtExceptionHandler - Unexpected error occurred invoking async method: public void com.kingseok.security.domain.AuthDomain.incrementLoginCount(com.kingseok.security.entity.User,com.kingseok.security.constant.OpenApiLoginSourceEnum)
org.springframework.dao.CannotAcquireLockException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
### The error may involve com.kingseok.security.mapper.UserMapper.updateByPrimaryKeySelective-Inline
### The error occurred while setting parameters
### SQL: update user_      SET login_count = ?,                       last_login_time = ?,                                                                 online_status = ?,                       login_source = ?      where id = ?
### 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:262)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy88.update(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
	at com.sun.proxy.$Proxy89.updateByPrimaryKeySelective(Unknown Source)
	at com.kingseok.security.domain.UserDomain.update(UserDomain.java:51)
	at com.kingseok.security.domain.AuthDomain.incrementLoginCount(AuthDomain.java:74)
	at com.kingseok.security.domain.AuthDomain$$FastClassBySpringCGLIB$$6a656ac4.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.interceptor.AsyncExecutionInterceptor.lambda$invoke$0(AsyncExecutionInterceptor.java:115)
	at java.util.concurrent.FutureTask.run$$$capture(FutureTask.java:266)
	at java.util.concurrent.FutureTask.run(FutureTask.java)
	at java.lang.Thread.run(Thread.java:748)
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.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:974)
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:391)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
	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.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
	at com.sun.proxy.$Proxy130.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)
	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:433)
	... 15 common frames omitted
2019-07-08 10:11:20.666 [http-nio-7070-exec-4] ERROR c.kingseok.security.controller.ExceptionController - nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30009ms.
### The error may exist in com/kingseok/security/mapper/UserMapper.xml
### The error may involve com.kingseok.security.mapper.UserMapper.selectByExample
### The error occurred while executing a query
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30009ms.

参考链接

• How to Avoid Lock wait timeout exceeded exception.?
• mysql提示 Lock wait timeout exceeded解决办法