MySQL报错：1205 Lock wait timeout exceeded； try restarting transaction处理

错误

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
接口响应时间超长，报警日志中出现Lock wait timeout exceeded; try restarting transaction的错误

原因

mysql数据库采用InnoDB模式，一旦数据库锁超过innodb_lock_wait_timeout参数设置的锁等待的时间(默认50s)就会报错。

一个事务在等待另一个事务释放锁，另一个事务持有了一个资源的资源，导致另这个事务一直等待并超时。并且通过trx_started可发现，持有锁的事务是之前开启的，一直没释放。

解决：

方法一

1. 查看事物表，找出被锁线程的id

   select * from information_schema.innodb_trx; -- 找到了那个一直没有提交的只读事务 
   

   
2. 根据id，kill掉被锁住的线程

   kill trx_mysql_thread_id; -- 对应的线程
   

   

方法二

1. 执行MySQL命令：SHOW FULL PROCESSLIST; 找到被锁住的线程ID
   
2. 根据id，kill掉被锁住的线程：kill 64

---

其他排查命令：

-- 排查事务的SQL
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_query LIKE '%表名%';
-- KILL 掉执行的线程

-- 查看事务  当前运行的所有事务
select * from information_schema.INNODB_TRX;
--查看锁   当前出现的锁
select * from information_schema.INNODB_LOCKS;
--查看锁等待   锁等待的对应关系
select * from information_schema.INNODB_LOCK_WAITS;
# 当前线程详情
show full processlist;

# 查询全局等待事务锁超时时间
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout'
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+

## 查看当前被使用的表
show OPEN TABLES where In_use > 0;
-- SHOW OPEN TABLES：列举在表缓存中当前被打开的非TEMPORARY表。
-- In_use：表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用
 

SELECT
    trx_id AS  `事务ID`,
    trx_state AS `事务状态`,
    trx_requested_lock_id  AS  `事务需要等待的资源`,
    trx_wait_started    AS  `事务开始等待时间`,
    trx_tables_in_use AS `事务使用表`,
    trx_tables_locked AS `事务拥有锁`,
    trx_rows_locked  AS `事务锁定行`,
    trx_rows_modified  AS `事务更改行`
FROM
    information_schema.innodb_trx ;
 
SELECT
    lock_id         AS  `锁ID`,
    lock_trx_id    AS  `拥有锁的事务ID`,
    lock_mode      AS  `锁模式    `,
    lock_type      AS  `锁类型`,
    lock_table         AS  `被锁的表`,
    lock_index         AS  `被锁的索引`,
    lock_space         AS  `被锁的表空间号`,
    lock_page      AS  `被锁的页号`,
    lock_rec       AS  `被锁的记录号`,
    lock_data      AS  `被锁的数据`
FROM
    information_schema.innodb_locks;
 
 
SELECT
  requesting_trx_id   AS  `请求锁的事务ID`,
  requested_lock_id   AS  `请求锁的锁ID`,
  blocking_trx_id     AS  `当前拥有锁的事务ID`,
  blocking_lock_id    AS  `当前拥有锁的锁ID`
FROM
  innodb_lock_waits

• 修改事务锁超时时间*

  set innodb_lock_wait_timeout=100;
  set global innodb_lock_wait_timeout=100;
  

  或者修改配置文件

  # my.ini文件：
  innodb_lock_wait_timeout = 100
  

MySQL 5.5 – innodb_lock_wait 锁 等待

在5.5中，information_schema 库中增加了三个关于锁的表（MEMORY引擎）；

  innodb_trx ## 当前运行的所有事务
  innodb_locks ## 当前出现的锁
  innodb_lock_waits ## 锁等待的对应关系

看一下表结构

desc information_schema .innodb_locks;
  +-------------+---------------------+------+-----+---------+-------+
  | Field | Type | Null | Key | Default | Extra |
  +-------------+---------------------+------+-----+---------+-------+
  | lock_id | varchar(81) | NO | | | |#锁ID
  | lock_trx_id | varchar(18) | NO | | | |#拥有锁的事务ID
  | lock_mode | varchar(32) | NO | | | |#锁模式
  | lock_type | varchar(32) | NO | | | |#锁类型
  | lock_table | varchar(1024) | NO | | | |#被锁的表
  | lock_index | varchar(1024) | YES | | NULL | |#被锁的索引
  | lock_space | bigint(21) unsigned | YES | | NULL | |#被锁的表空间号
  | lock_page | bigint(21) unsigned | YES | | NULL | |#被锁的页号
  | lock_rec | bigint(21) unsigned | YES | | NULL | |#被锁的记录号
  | lock_data | varchar(8192) | YES | | NULL | |#被锁的数据
  +-------------+---------------------+------+-----+---------+-------+
desc information_schema .innodb_lock_waits;
  +-------------------+-------------+------+-----+---------+-------+
  | Field | Type | Null | Key | Default | Extra |
  +-------------------+-------------+------+-----+---------+-------+
  | requesting_trx_id | varchar(18) | NO | | | |#请求锁的事务ID
  | requested_lock_id | varchar(81) | NO | | | |#请求锁的锁ID
  | blocking_trx_id   | varchar(18) | NO | | | |#当前拥有锁的事务ID
  | blocking_lock_id  | varchar(81) | NO | | | |#当前拥有锁的锁ID
  +-------------------+-------------+------+-----+---------+-------+
desc information_schema .innodb_trx ;
  +----------------------------+---------------------+------+-----+---------------------+-------+
  | Field | Type | Null | Key | Default | Extra |
  +----------------------------+---------------------+------+-----+---------------------+-------+
  | trx_id | varchar(18) | NO | | | |#事务ID
  | trx_state | varchar(13) | NO | | | |#事务状态：
  | trx_started | datetime | NO | | 0000-00-00 00:00:00 | |#事务开始时间；
  | trx_requested_lock_id | varchar(81) | YES | | NULL | |#innodb_locks.lock_id
  | trx_wait_started | datetime | YES | | NULL | |#事务开始等待的时间
  | trx_weight | bigint(21) unsigned | NO | | 0 | |#
  | trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |#事务线程ID
  | trx_query | varchar(1024) | YES | | NULL | |#具体SQL语句
  | trx_operation_state | varchar(64) | YES | | NULL | |#事务当前操作状态
  | trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |#事务中有多少个表被使用
  | trx_tables_locked | bigint(21) unsigned | NO | | 0 | |#事务拥有多少个锁
  | trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#
  | trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |#事务锁住的内存大小（B）
  | trx_rows_locked | bigint(21) unsigned | NO | | 0 | |#事务锁住的行数
  | trx_rows_modified | bigint(21) unsigned | NO | | 0 | |#事务更改的行数
  | trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |#事务并发票数
  | trx_isolation_level | varchar(16) | NO | | | |#事务隔离级别
  | trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性检查
  | trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外键检查
  | trx_last_foreign_key_error | varchar(256) | YES | | NULL | |#最后的外键错误
  | trx_adaptive_hash_latched | int(1) | NO | | 0 | |#
  | trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |#
  +----------------------------+---------------------+------+-----+---------------------+-------
select * from information_schema.innodb_lock_waits G