MySQL事务未提交导致整个表锁死

问题及说明：

当一个SQL事务执行完了，但未COMMIT，后面的SQL想要执行就是被锁，超时结束；报错信息如下：

mysql> ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

处理步骤：

该问题发生环境为MySQL 5.6，在MySQL 5.5版本后，information_schema 库中增加了三个关于锁的表，分别如下：

• innodb_trx：当前运行的所有事务

• innodb_locks：当前出现的锁

• innodb_lock_waits：锁等待的对应关系 
  该问题可以直接从这个几张表入手，找到了一直没有提交的只读事务，然后 kill thread id
  ，最后确认只读事物是否被干掉了就OK了。解决步骤如下：

  mysql> select * from information_schema.innodb_trx; mysql> SHOW FULL PROCESSLIST; mysql> kill 'thread id'; mysql> select * from information_schema.innodb_trx;

PS：如需要查看定位是哪条语句，可以在MySQL的binlog日志中查看根据id和时间定位查找语句。

MySQL事务知识点延伸：

1. 三个库的字段含义

1mysql > desc information_schema.innodb_locks;
2+-------------+---------------------+------+-----+---------+-------+
3| Field | Type | Null | Key | Default | Extra |
4+-------------+---------------------+------+-----+---------+-------+
5| lock_id | varchar(81) | NO | | | |#锁ID
6| lock_trx_id | varchar(18) | NO | | | |#拥有锁的事务ID
7| lock_mode | varchar(32) | NO | | | |#锁模式
8| lock_type | varchar(32) | NO | | | |#锁类型
9| lock_table | varchar(1024) | NO | | | |#被锁的表
10| lock_index | varchar(1024) | YES | | NULL | |#被锁的索引
11| lock_space | bigint(21) unsigned | YES | | NULL | |#被锁的表空间号
12| lock_page | bigint(21) unsigned | YES | | NULL | |#被锁的页号
13| lock_rec | bigint(21) unsigned | YES | | NULL | |#被锁的记录号
14| lock_data | varchar(8192) | YES | | NULL | |#被锁的数据
15+-------------+---------------------+------+-----+---------+-------+
1610 rows in set (0.00 sec)
17
18mysql> desc information_schema.innodb_lock_waits;
19+-------------------+-------------+------+-----+---------+-------+
20| Field | Type | Null | Key | Default | Extra |
21+-------------------+-------------+------+-----+---------+-------+
22| requesting_trx_id | varchar(18) | NO | | | |#请求锁的事务ID
23| requested_lock_id | varchar(81) | NO | | | |#请求锁的锁ID
24| blocking_trx_id | varchar(18) | NO | | | |#当前拥有锁的事务ID
25| blocking_lock_id | varchar(81) | NO | | | |#当前拥有锁的锁ID
26+-------------------+-------------+------+-----+---------+-------+
274 rows in set (0.00 sec)
28
29mysql> desc information_schema.innodb_trx;
30+----------------------------+---------------------+------+-----+---------------------+-------+
31| Field | Type | Null | Key | Default | Extra |
32+----------------------------+---------------------+------+-----+---------------------+-------+
33| trx_id | varchar(18) | NO | | | |#事务ID
34| trx_state | varchar(13) | NO | | | |#事务状态：
35| trx_started | datetime | NO | | 0000-00-00 00:00:00 | |#事务开始时间；
36| trx_requested_lock_id | varchar(81) | YES | | NULL | |#innodb_locks.lock_id
37| trx_wait_started | datetime | YES | | NULL | |#事务开始等待的时间
38| trx_weight | bigint(21) unsigned | NO | | 0 | |#
39| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |#事务线程ID
40| trx_query | varchar(1024) | YES | | NULL | |#具体SQL语句
41| trx_operation_state | varchar(64) | YES | | NULL | |#事务当前操作状态
42| trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |#事务中有多少个表被使用
43| trx_tables_locked | bigint(21) unsigned | NO | | 0 | |#事务拥有多少个锁
44| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#
45| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |#事务锁住的内存大小（B）
46| trx_rows_locked | bigint(21) unsigned | NO | | 0 | |#事务锁住的行数
47| trx_rows_modified | bigint(21) unsigned | NO | | 0 | |#事务更改的行数
48| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |#事务并发票数
49| trx_isolation_level | varchar(16) | NO | | | |#事务隔离级别
50| trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性检查
51| trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外键检查
52| trx_last_foreign_key_error | varchar(256) | YES | | NULL | |#最后的外键错误
53| trx_adaptive_hash_latched | int(1) | NO | | 0 | |#
54| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |#
55+----------------------------+---------------------+------+-----+---------------------+-------+
5622 rows in set (0.01 sec)转自：https://www.colabug.com/1912433.html

原文出处：https://www.cnblogs.com/leon0/p/10943711.html