mysql 锁超时: Lock wait timeout exceeded;

错误信息：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)就会报错。

> SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+

1 通过语句修改参数

set innodb_lock_wait_timeout=100;
set global innodb_lock_wait_timeout=100;

2 修改配置文件参数项目

# my.ini文件：
innodb_lock_wait_timeout = 100

注意：

修改参数需要慎重。全局更改，等待时间加长，容易使等待事务增多导致堆积问题。

推荐：

• 对于耗时任务，进行合理拆分，减少等待时间。
• 找到缩表的业务，对业务代码进行分析，优化。从根本解决问题。

相关信息表

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

--查看事务
select * from information_schema.INNODB_TRX;

--查看锁
select * from information_schema.INNODB_LOCKS;

--查看锁等待
select * from information_schema.INNODB_LOCK_WAITS;

INNODB_TRX 表列信息详解：

trx_id：

唯一事务id号，只读事务和非锁事务是不会创建id的。

TRX_WEIGHT：

事务的高度，代表修改的行数（不一定准确）和被事务锁住的行数。为了解决死锁，innodb会选择一个高度最小的事务来当做牺牲品进行回滚。已经被更改的非交易型表的事务权重比其他事务高，即使改变的行和锁住的行比其他事务低。

TRX_STATE：

事务的执行状态，值一般分为：RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.

TRX_STARTED：

事务的开始时间

TRX_REQUESTED_LOCK_ID:

如果trx_state是lockwait,显示事务当前等待锁的id，不是则为空。想要获取锁的信息，根据该lock_id，以innodb_locks表中lock_id列匹配条件进行查询，获取相关信息。

TRX_WAIT_STARTED：

如果trx_state是lockwait,该值代表事务开始等待锁的时间；否则为空。

TRX_MYSQL_THREAD_ID：

mysql线程id。想要获取该线程的信息，根据该thread_id，以INFORMATION_SCHEMA.PROCESSLIST表的id列为匹配条件进行查询。

TRX_QUERY：

事务正在执行的sql语句。

TRX_OPERATION_STATE：

事务当前的操作状态，没有则为空。

TRX_TABLES_IN_USE：

事务在处理当前sql语句使用innodb引擎表的数量。

TRX_TABLES_LOCKED：

当前sql语句有行锁的innodb表的数量。（因为只是行锁，不是表锁，表仍然可以被多个事务读和写）

TRX_LOCK_STRUCTS：

事务保留锁的数量。

TRX_LOCK_MEMORY_BYTES：

在内存中事务索结构占得空间大小。

TRX_ROWS_LOCKED：

事务行锁最准确的数量。这个值可能包括对于事务在物理上存在，实际不可见的删除标记的行。

TRX_ROWS_MODIFIED：

事务修改和插入的行数

TRX_CONCURRENCY_TICKETS：

该值代表当前事务在被清掉之前可以多少工作，由 innodb_concurrency_tickets系统变量值指定。

TRX_ISOLATION_LEVEL：

事务隔离等级。

TRX_UNIQUE_CHECKS：

当前事务唯一性检查启用还是禁用。当批量数据导入时，这个参数是关闭的。

TRX_FOREIGN_KEY_CHECKS：

当前事务的外键坚持是启用还是禁用。当批量数据导入时，这个参数是关闭的。

TRX_LAST_FOREIGN_KEY_ERROR：

最新一个外键错误信息，没有则为空。

TRX_ADAPTIVE_HASH_LATCHED：

自适应哈希索引是否被当前事务阻塞。当自适应哈希索引查找系统分区，一个单独的事务不会阻塞全部的自适应hash索引。自适应hash索引分区通过 innodb_adaptive_hash_index_parts参数控制，默认值为8。

TRX_ADAPTIVE_HASH_TIMEOUT：

是否为了自适应hash索引立即放弃查询锁，或者通过调用mysql函数保留它。当没有自适应hash索引冲突，该值为0并且语句保持锁直到结束。在冲突过程中，该值被计数为0，每句查询完之后立即释放门闩。当自适应hash索引查询系统被分区（由 innodb_adaptive_hash_index_parts参数控制），值保持为0。

TRX_IS_READ_ONLY：
值为1表示事务是read only。

TRX_AUTOCOMMIT_NON_LOCKING：

值为1表示事务是一个select语句，该语句没有使用for update或者shared mode锁，并且执行开启了autocommit，因此事务只包含一个语句。当TRX_AUTOCOMMIT_NON_LOCKING和TRX_IS_READ_ONLY同时为1，innodb通过降低事务开销和改变表数据库来优化事务。

> desc information_schema.INNODB_LOCKS;
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| lock_id     | varchar(81)         | NO   |     |         |       |
| lock_trx_id | varchar(18)         | NO   |     |         |       |
| 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>  |       |
+-------------+---------------------+------+-----+---------+-------+

INNODB_LOCKS表列信息详解：

LOCK_ID
一个唯一的锁ID号，内部为 InnoDB。
LOCK_TRX_ID
持有锁的交易的ID
LOCK_MODE
如何请求锁定。允许锁定模式描述符 S，X， IS，IX， GAP，AUTO_INC，和 UNKNOWN。锁定模式描述符可以组合使用以识别特定的锁定模式。
LOCK_TYPE
锁的类型
LOCK_TABLE
已锁定或包含锁定记录的表的名称
LOCK_INDEX
索引的名称，如果LOCK_TYPE是 RECORD; 否则NULL
LOCK_SPACE
锁定记录的表空间ID，如果 LOCK_TYPE是RECORD; 否则NULL
LOCK_PAGE
锁定记录的页码，如果 LOCK_TYPE是RECORD; 否则NULL。
LOCK_REC
页面内锁定记录的堆号，如果 LOCK_TYPE是RECORD; 否则NULL。
LOCK_DATA
与锁相关的数据（如果有）。如果 LOCK_TYPE是RECORD，是锁定的记录的主键值，否则NULL。此列包含锁定行中主键列的值，格式为有效的SQL字符串。如果没有主键，LOCK_DATA则是唯一的InnoDB内部行ID号。如果对键值或范围高于索引中的最大值的间隙锁定，则LOCK_DATA 报告supremum pseudo-record。当包含锁定记录的页面不在缓冲池中时（如果在保持锁定时将其分页到磁盘），InnoDB不从磁盘获取页面，以避免不必要的磁盘操作。相反， LOCK_DATA设置为 NULL。

> desc information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------+------+-----+---------+-------+
| Field             | Type        | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| requesting_trx_id | varchar(18) | NO   |     |         |       |
| requested_lock_id | varchar(81) | NO   |     |         |       |
| blocking_trx_id   | varchar(18) | NO   |     |         |       |
| blocking_lock_id  | varchar(81) | NO   |     |         |       |
+-------------------+-------------+------+-----+---------+-------+

INNODB_LOCK_WAITS表列信息详解：

REQUESTING_TRX_ID
请求（阻止）事务的ID。
REQUESTED_LOCK_ID
事务正在等待的锁的ID。
BLOCKING_TRX_ID
阻止事务的ID。
BLOCKING_LOCK_ID
由阻止另一个事务继续进行的事务所持有的锁的ID