MySQL解除死锁

报错日志：Lock wait timeout exceeded; try restarting transaction

【治标方法】事务信息查询

通过脚本：SELECT * FROM information schema.innodb_trx
查询未提交事务，査到没有提交的只读事务(trx_state="LOCKWAIT")，
找到对应线程，执行kill命令，其中线程id为表中的trx_mysqlthread_id字段。


mysql> SELECT * FROM information_schema.innodb_trx;
+-----------------+-----------+---------------------+-------------------------------------------+---------------------+------------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+-----------------+
| trx_id          | trx_state | trx_started         | trx_requested_lock_id                     | trx_wait_started    | trx_weight | trx_mysql_thread_id | trx_query                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | 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 | trx_is_read_only | trx_autocommit_non_locking | trx_schedule_weight | trx_waited_time |
+-----------------+-----------+---------------------+-------------------------------------------+---------------------+------------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+-----------------+
|        83488431 | LOCK WAIT | 2025-12-02 15:20:33 | 140673625892520:1228:4:10:140667004024864 | 2025-12-02 15:20:33 |          2 |            31233061 | UPDATE cs_xx_kpzd SET create_by = 'jdd_admin_user', create_time = '2025-11-21 11:14:55', update_by = 'jdd_admin_user', update_time = '2025-12-02 15:20:33.777', sys_org_code = 'A03A01A01A23A01', tenant_id = 1001, company_id = '1982703852667277314', zddm = '91411224MA460BGYXG', zdmc = '卢氏县清河加油站', zdlx = 'sd', fwqid = '', kpms = 'fxtd', tdyw = '01', fplxdm = '01,02', nsrdm = '91411224MA460BGYXG', nsrsbh = '91411224MA460BGYXG', nsrmc = '卢氏县清河加油站', xfyh = '河南卢氏农村商业银行股份有限公司潘河支行', xfyhzh = '09408001100000041', xfdz = '卢氏县潘河乡清河村S250省道北方矿业对面', xfdh = '13939887301', dzswjzh = '13939887301', dzswjmm = 'Aa111111', dljs = '1', sjh = '13290813447', dljsmm = '1Y/Zm6qktq+cWCrav4t/sw==', dlsj = '2025-12-02 15:20:33.776', zxzt = '在线', dzswjpzid = '1991706544500641794', jybs = '0' WHERE cs_xx_kpzd.tenant_id = 1001 AND id = '1991706863229997057' | starting index read |                 1 |                 1 |                2 |                  1128 |               1 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |                   1 |              21 |
|        83360270 | RUNNING   | 2025-12-02 11:59:43 | NULL                                      | NULL                |          3 |            31250291 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | NULL                |                 0 |                 1 |                2 |                  1128 |               1 |                 1 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 | NULL                |               0 |
| 422148602666008 | RUNNING   | 2025-12-02 10:52:40 | NULL                                      | NULL                |          0 |            31250495 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | NULL                |                 0 |                 0 |                0 |                  1128 |               0 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 | NULL                |               0 |
| 422148602605624 | RUNNING   | 2025-12-02 09:44:32 | NULL                                      | NULL                |          0 |            31229753 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | NULL                |                 0 |                 0 |                0 |                  1128 |               0 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 | NULL                |               0 |
| 422148602632552 | RUNNING   | 2025-12-02 11:59:10 | NULL                                      | NULL                |          0 |            30913938 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | NULL                |                 0 |                 0 |                0 |                  1128 |               0 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 | NULL                |               0 |
+-----------------+-----------+---------------------+-------------------------------------------+---------------------+------------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+-----------------+
5 rows in set (0.07 sec)

kill trx_mysqlthread_id  =》kill 31233061;


【治标方法】如果杀掉线程依然不能解决，可以查找执行线程耗时比较久的任务，kill掉

SELECT * from information_schema.`PROCESSLIST` WHERE Time > 1000 AND USER = 'xxx' ORDER BY TIME desc;

kill 线程ID