MySQL应用报错：java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
开发反馈，某业务系统插入一条记录的时候，日志报错，插入失败：
1 2 3 4 | ### Error updating database. Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction### The error may involve defaultParameterMap### The error occurred while setting parameters### SQL: INSERT INTO ... |
登录 mysql，使用 show processlist 查看没有发现相关会话的存在。然后使用 show engine innodb status 也没有最近的死锁信息。
至此，可以猜测，因为变量 innodb_lock_wait_timeout 的缘故，插入失败的会话已经结束。
以下是变量 innodb_lock_wait_timeout 的用途说明：
innodb 事务等待行锁的时间，单位是秒，等待超过这个时间后就会放弃。默认是 50 秒。尝试访问被另一个 InnoDB 事务锁定的行的事务在发出以下错误之前最多要等待这么多秒才能对该行进行写访问：
1 | ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
发生锁等待超时时，将回滚当前语句（而不是整个事务）。要使整个事务回滚，请使用 --innodb-rollback-on-timeout 选项启动服务器。另请参见第 14.21.4 节“ InnoDB 错误处理”。对于高度交互式应用程序或 OLTP 系统你可能需要降低该参数的值，给用户反馈或者将更新到队列中供以后处理。可以为长时间运行的后端操作（例如，数据仓库中等待其他大型插入或更新操作完成的转换步骤）增加此值。
innodb_lock_wait_timeout 仅适用于 InnoDB 行锁。MySQL 表锁不会在 InnoDB 内部发生，并且此超时不适用于等待表锁。
锁等待超时值不适用于死锁，因为 InnoDB 会立即检测到它们并回滚其中一个死锁的事务。请参见第 14.7.5.2 节“死锁检测和回滚”。
innodb_lock_wait_timeout 可以用 SET GLOBAL 或 SET SESSION 语句运行时设置。更改 GLOBAL 设置需要足够的特权来设置全局系统变量（请参见第 5.1.8.1 节“系统变量特权”），并影响随后连接的所有客户端的操作。任何客户端都可以更改 innodb_lock_wait_timeout 的 SESSION 设置，这仅影响该客户端。
顺便查看了一下数据库中其它事务：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | >SELECT * FROM information_schema.INNODB_TRX\G*************************** 1. row *************************** trx_id: 5877143864 trx_state: RUNNING trx_started: 2019-11-02 16:43:24 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 0 trx_mysql_thread_id: 10010454 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 0 trx_lock_memory_bytes: 360 trx_rows_locked: 0 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 9996 trx_is_read_only: 0trx_autocommit_non_locking: 0 |
这里看到，事务 5877143864 状态是 running，但是 trx_query 却为 null。光从这里看不到执行的 sql，也不知道具体在哪个对象上加了锁。
通过 show engine innodb status 也只是能看到该事务，但是看不到该事务的详细信息。
这里需要明白的是，如果一个会话 (连接) 里面有未提交事务，然后不做任何操作，那么这个线程处于 Sleep 状态。这也是为何通过 show processlist 查看，对应的线程也是处于 sleep 状态的原因。
通过以下查询，看看该会话最后执行的 sql 是什么内容：
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT a.sql_text, c.id, d.trx_started FROM PERFORMANCE_SCHEMA.events_statements_current a JOIN PERFORMANCE_SCHEMA.threads b ON a.thread_id = b.thread_id JOIN information_schema.PROCESSLIST c ON b.processlist_id = c.id JOIN information_schema.innodb_trx d ON c.id = d.trx_mysql_thread_id WHERE c.id = 10010454 ORDER BY d.trx_started\G; |
查出的结果语句也是一个插入语句，与报错的语句居然是相同功能的语句。那就正好可以分析一下，上面应用为何报错了。
原因其实也很简单，就是该会话执行插入后，事务没有及时提交。导致后续的插入在插入唯一键时候阻塞了 (这里涉及到跟开发沟通才了解的业务逻辑，本身设计存在问题以及唯一键问题)。