1. 场景描述
1.1. 问题
Lock wait timeout exceeded; try restarting transaction
1.2. 原因
InnoDB在锁等待超时过期时报告此错误。等待时间过长的语句被回滚（而不是整个事务）。如果SQL语句需要等待其他事务完成的时间更长，则可以增加 innodb_lock_wait_timeout 配置选项的值；如果太多长时间运行的事务导致锁定问题并降低繁忙系统上的并发性，则可以减少该选项的值。
2.innodb_lock_wait_timeout
2.1. 参数介绍
innodb_lock_wait_timeout是InnoDB事务在放弃前等待行锁的时间（秒）。innodb_lock_wait_timeout默认值为20秒。当有试图访问被另一行锁定的行的事务InnoDB事务在发出以下错误：
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction当发生锁等待超时时，将回滚当前语句 (而不是整个事务)。要回滚整个事务，请使用innodb_rollback_on_timeout 开启值为：ON
对于高度交互式的应用程序或 OLTP 系统，您可能会降低该值，以便快速显示用户反馈，或者将更新放入队列中以便稍后处理。对于长时间运行的后端操作，例如等待其他大型插入或更新操作完成的数据仓库中的转换步骤，您可能会增加此值。innodb_lock_wait_timeout 仅适用于 InnoDB 行锁。MySQL 表锁不会发生在 InnoDB 内部，并且此超时不适用于等待表锁。锁等待超时值不适用于死锁，因为 InnoDB 会立即检测到它们，并回滚其中一个死锁事务。innodb_lock_wait_timeout 可以在运行时使用 SET GLOBAL 或 SET SESSION 语句设置。更改全局设置需要超级权限，并影响随后连接的所有客户端的操作。任何客户端都可以更改 innodb_lock_wait_timeout 超时的会话设置，这只影响该客户端。
2.2. 参数设置与查询
MariaDB>
SET GLOBAL innodb_lock_wait_timeout=600;设置该值后，仅对新创建的会话生效，已连接的会话仍然使用修改之前的值。
2.3. 关键的四张表
- INNODB_TRX：当前运行的所有事务
提供有关当前正在内部执行的每个事务的信息 InnoDB，包括事务是否在等待锁定，事务何时启动以及事务正在执行的SQL语句（如果有） - INNODB_LOCKS：当前出现的锁
提供有关InnoDB 事务已请求但尚未获取的每个锁的信息，以及事务持有的阻止另一个事务的每个锁。 - INNODB_LOCK_WAITS：锁等待对应的关系
包含每个被阻止InnoDB 事务的一个或多个行，指示它已请求的锁以及阻止该请求的任何锁。 - processlist：当前进程信息
3. 插件 metadata_lock_info
-
安装方法：
INSTALL SONAME 'metadata_lock_info'; -
作用：METADATA_LOCK_INFO 插件在 INFORMATION_SCHEMA 数据库中创建 METADATA_LOCK_INFO 表。 此表显示活动的元数据锁。 如果没有活动的元数据锁，该表将为空。
-
用法：
SELECT * FROM information_schema.metadata_lock_info; -
关于lock_mode的解释
| 锁名称 | 锁类型------------ | 说明 | 适用语句 |
|---|---|---|---|
| MDL_INTENTION_EXCLUSIVE | 共享锁 | 意向锁，锁住一个范围 | 任何语句都会获取MDL意向锁，然后再获取更高级别的MDL锁。 |
| MDL_SHARED | 共享锁 | 共享锁，表示只访问表结构 | |
| MDL_SHARED_HIGH_PRIO | 共享锁 | 共享锁，只访问表结构 | show create table 等，只访问INFORMATION_SCHEMA的语句 |
| MDL_SHARED_READ | 共享锁 | 访问表结构并且读表数据 | select语句，LOCK TABLE ... READ |
| MDL_SHARED_WRITE | 共享锁 | 访问表结构并且写表数据 | SELECT ... FOR UPDATE，DML语句 |
| MDL_SHARED_UPGRADABLE | 共享锁 | 可升级锁，访问表结构并且读写表数据 | Alter语句中间过程会使用 |
| MDL_SHARED_NO_WRITE | 共享锁 | 可升级锁，访问表结构并且读写表数据，并且禁止其它事务写。 | Alter语句中间过程会使用 |
| MDL_SHARED_NO_READ_WRITE | 共享锁 | 可升级锁，访问表结构并且读写表数据，并且禁止其它事务读写。 | LOCK TABLES ... WRITE |
| MDL_EXCLUSIVE | 写锁 | 禁止其它事务读写。 | CREATE/DROP/RENAME TABLE等DDL语句。 |
4. 分析过程
4.1. 查询数据库运行情况
MariaDB>
show engine innodb status\G
具体含义：
4.2. 查询当前正在执行的事务的sql
SELECT * FROM information_schema.INNODB_TRX;
4.3. 查询当前出现的锁
SELECT * FROM information_schema.INNODB_LOCKS x ;
这里的锁仅仅是阻塞后呈现的锁，并不包含事务中更新过的表且未提交的锁。具体的锁还应在 metadata_lock_info 中查看
4.4. 查看所有元数据锁
SELECT * FROM information_schema.metadata_lock_info;
对于同个表出现在两个会话当中，且 lock_mode 为 MDL_SHARED_WRITE 模式时说明一定有阻塞与等待。
4.5. 查看未提交的事务(3秒未操作的事务)
未提交的事务也即正在运行的事务，包含了阻塞的事务与被阻塞的事务。
SELECT
p.id AS conn_id, -- mysql内部线程的唯一标识,connection_id可以查询,可以 kill线程号
t.trx_id AS trx_id, -- 事务的ID
t.trx_state AS trx_state, -- 事务的状态
p.user AS login_user, -- 启动这个线程的用户
p.host AS login_host, -- 发送请求的客户端的 IP 和 端口号
p.db AS database_name, -- 该线程连接的是哪个数据库
p.time AS trx_sleep_seconds, -- 这个状态持续的时间，单位是秒
t.trx_started AS trx_started, -- 事务开始时间
t.trx_wait_started AS trx_wait_started, -- 事务开始等待的时间
t.trx_isolation_level AS trx_isolation_level, -- 事务隔离级别
t.trx_tables_in_use AS trx_tables_in_use, -- 事务中有多少个表被使用
t.trx_tables_locked AS trx_tables_locked, -- 事务拥有多少个锁
t.trx_rows_locked AS trx_rows_locked, -- 事务锁住的行数
t.trx_rows_modified AS trx_rows_modified, -- 事务更改的行数
t.trx_state AS trx_state, -- 事务状态
t.trx_query AS trx_query, -- 具体SQL语句
t.trx_lock_memory_bytes AS trx_lock_memory_bytes, -- 事务锁住的内存大小,单位为Byte
p.COMMAND AS process_state, -- 此刻该线程正在执行的命令
(SELECT GROUP_CONCAT(REPLACE(REPLACE(REPLACE(t1.sql_text,'\n',' '),'\r',' '),'\t',' ') SEPARATOR ';')
FROM performance_schema.events_statements_history t1
INNER JOIN performance_schema.threads t2 ON t1.thread_id = t2.thread_id
WHERE t2.processlist_id = p.id
) AS trx_sql_text
FROM information_schema.innodb_trx t
INNER JOIN information_schema.processlist p ON t.trx_mysql_thread_id = p.id
WHERE P.time > 3
/* AND t.trx_state = 'RUNNING'
AND p.command = 'Sleep' */
;
4.6. 查询阻塞事务与被阻塞事务的关系
SELECT p.host AS 阻塞方主机,
p.user AS 阻塞方用户,
b.trx_id AS 阻塞方事务id,
b.trx_mysql_thread_id AS 阻塞方线程号, -- 与show processlist中的ID值相对应
b.trx_state AS 阻塞方的状态,
b.trx_query AS 阻塞方查询,
l.lock_mode AS 阻塞方的锁模式,
l.lock_type AS "阻塞方的锁类型(表锁还是行锁)",
l.lock_table AS 阻塞方锁住的表,
l.lock_index AS 阻塞方锁住的索引,
l.lock_space AS 阻塞方锁对象的space_id,
l.lock_page AS 阻塞方事务锁定页的数量,
l.lock_rec AS 阻塞方事务锁定行的数量,
l.lock_data AS 阻塞方事务锁定记录的主键值,
IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' 秒'), 0) AS 阻塞方事务空闲的时间,
p2.host AS 被阻塞方host,
p2.user AS 被阻塞方用户,
r.trx_id AS 被阻塞方事务id,
r.trx_mysql_thread_id AS 被阻塞方线程号, -- 与show processlist中的ID值相对应
TIMESTAMPDIFF(SECOND,
r.trx_wait_started,
CURRENT_TIMESTAMP
) AS 等待时间,
r.trx_state AS 被阻塞方的状态,
r.trx_query AS 被阻塞的查询,
m.lock_mode AS 被阻塞方的锁模式,
m.lock_type AS "被阻塞方的锁类型(表锁还是行锁)",
m.lock_index AS 被阻塞方锁住的索引,
m.lock_space AS 被阻塞方锁对象的space_id,
m.lock_page AS 被阻塞方事务锁定页的数量,
m.lock_rec AS 被阻塞方事务锁定行的数量,
m.lock_data AS 被阻塞方事务锁定记录的主键值
FROM information_schema.INNODB_LOCK_WAITS w -- 锁等待的对应关系表
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id -- 当前运行的所有事务,阻塞方
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id -- 当前运行的所有事务,被阻塞方
INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id AND l.lock_trx_id=b.trx_id -- 当前出现的锁,阻塞方
INNER JOIN information_schema.INNODB_LOCKS m ON m.lock_id=w.requested_lock_id AND m.lock_trx_id=r.trx_id -- 当前出现的锁,被阻塞方
INNER JOIN information_schema.PROCESSLIST p ON p.ID = b.trx_mysql_thread_id -- 线程信息,阻塞方
INNER JOIN information_schema.PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id -- 线程信息,被阻塞方
ORDER BY 等待时间 DESC;
阻塞方的状态一般为 RUNNING ，而被阻塞方的状态为 LOCK WAIT 。
这里不要太天真的认为第二个SQL语句能够获取所有场景下的阻塞源头SQL语句，实际业务场景，会话可能在执行一个存储过程或复杂的业务，有可能它执行完阻塞源头SQL后，继续在执行其它SQL语句，此时，你抓取的是这个连接会话最后执行的SQL语句。
4.7. 查询阻塞的源头
SELECT distinct
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread_id,
SUBSTRING(p. HOST, 1, INSTR(p. HOST, ':') - 1) AS blocking_host,
SUBSTRING(p. HOST, INSTR(p. HOST, ':') + 1) AS blocking_port,
IF(p.COMMAND = 'Sleep', p.TIME, 0) AS idel_in_trx,
b.trx_query AS blocking_query,
r.trx_id AS waiting_trx_id,
r.trx_mysql_thread_id AS waiting_thread,
TIMESTAMPDIFF(SECOND,
r.trx_wait_started,
CURRENT_TIMESTAMP) AS wait_time,
r.trx_query AS waiting_query,
l.lock_table AS waiting_table_lock
FROM information_schema.INNODB_LOCKS l
LEFT JOIN information_schema.INNODB_LOCK_WAITS w ON w.requested_lock_id = l.lock_id
LEFT JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
LEFT JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
LEFT JOIN information_schema.PROCESSLIST p ON p.ID = b.trx_mysql_thread_id
JOIN (SELECT blocking_trx_id -- 查找最源头的trx_id
FROM information_schema.INNODB_LOCK_WAITS ilw
WHERE blocking_trx_id NOT IN (SELECT requesting_trx_id
FROM information_schema.INNODB_LOCK_WAITS)) c
ON c.blocking_trx_id = b.trx_id
ORDER BY wait_time DESC;
- 根据线程号在数据库服务器上找到相关的线程信息，并通过 metadata_lock_info 表中查询到在该线程上被锁的表有哪些。
SELECT * FROM information_schema.processlist WHERE id = ?; SELECT * FROM information_schema.metadata_lock_info x WHERE x.thread_id = ?;
- 根据阻塞方的IP地址与端口号，我们在客户端查询到执行程序的进程号和运行的软件。
# Linux操作系统 netstat -nlatp | grep port ps -eaf | grep pid
Windows操作系统
netstat -ano | findstr "port"
tasklist | findstr "pid"
#### 4.8. 中止阻塞的线程
```sql
MariaDB>
kill thread_id;5. 总结
5.1. 如何优化与避免MDL锁
MDL锁一旦发生会对业务造成极大影响，因为后续所有对该表的访问都会被阻塞，造成连接积压。我们日常要尽量避免MDL锁的发生，下面给出几点优化建议可供参考：
- 开启metadata_locks表记录MDL锁。
- 设置参数lock_wait_timeout为较小值，使被阻塞端主动停止。
- 设置参数innodb_rollback_on_timeout为ON，使
- 规范使用事务，及时提交事务，避免使用大事务。
- 增强监控告警，及时发现MDL锁。
- DDL操作及备份操作放在业务低峰期执行。
- 少用工具开启事务进行查询，图形化工具要及时关闭。
5.2. 更深入的分析锁
6.测试的脚本
DROP TABLE IF EXISTS tb01;
DROP TABLE IF EXISTS tb02;
DROP TABLE IF EXISTS tb03;
CREATE TABLE tb01 (
order_id NUMERIC(11) NOT NULL,
order_time datetime DEFAULT NULL,
remark varchar(64),
CONSTRAINT pk_tb01 PRIMARY KEY (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE tb02 (
order_id NUMERIC(11) NOT NULL,
order_time datetime DEFAULT NULL,
remark varchar(64),
CONSTRAINT pk_tb02 PRIMARY KEY (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE tb03 (
order_id NUMERIC(11) NOT NULL,
order_time datetime DEFAULT NULL,
remark varchar(64),
CONSTRAINT pk_tb02 PRIMARY KEY (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO tb01(order_id,order_time)
VALUES (11,now()),(12,now());
SELECT * FROM tb01;
INSERT INTO tb02(order_id,order_time)
VALUES (21,now()),(22,now());
SELECT * FROM tb02;
INSERT INTO tb03(order_id,order_time)
VALUES (31,now()),(32,now());
SELECT * FROM tb03;
-- SESSION1:
START TRANSACTION;
SET @dt_time1 = STR_TO_DATE('2022-01-01 01:01:01','%Y-%m-%d');
SET @dt_time2 = str_to_date('2022-12-31 23:59:59','%Y-%m-%d');
UPDATE tb01
SET order_time = @dt_time1,
remark = 'session1-update-tb01-11'
WHERE order_id = 11;
SELECT * FROM tb01 WHERE tb01.order_id = 11;
UPDATE tb02
SET order_time = @dt_time2,
remark = 'session1-update-tb02-21'
WHERE order_id = 21;
SELECT connection_id();
ROLLBACK;
-- SESSION2:
START TRANSACTION;
SET @dt_time1 = STR_TO_DATE('2022-01-01 01:01:01','%Y-%m-%d');
SET @dt_time2 = str_to_date('2022-12-31 23:59:59','%Y-%m-%d');
UPDATE tb03
SET order_time = @dt_time1,
remark = 'session2-update-tb03-31'
WHERE order_id = 31;
UPDATE tb01
SET order_time = @dt_time2,
remark = 'session2-update-tb01-11'
WHERE order_id = 11;
SELECT * FROM tb01 WHERE tb01.order_id = 11;
SELECT connection_id();
ROLLBACK;