记一次mysql执行DDL导致锁表

灰太狼 2023-02-21 01:53 63阅读 0赞

## 背景 ##

线上某数据库意外发现缺少索引，并且该表的数据量很少，只有几万条记录而已，因此很随意地尝试给该表添加索引。原本预期该表的记录很少，添加索引的耗时应该很短，结果却直接导致该表被锁，所有该表的增删改查操作全部阻塞，继而影响到了线上业务。

发现锁表后，执行`show processlist`发现大量线程阻塞，状态显示`Waiting for table metadata lock`。通过命令终止了DDL线程，该表恢复正常。mysql从5.6版本起支持`Online DDL`，理论上执行DDL语句不会阻塞诸如`INSERT`、`UPDATE`、`DELETE`这类DML操作。

事后排查发现，该表有个持续了3天未提交的事务，正是该事务导致DDL语句执行时锁表。

![在这里插入图片描述][20200628101554138.png]

![在这里插入图片描述][20200628101603177.png]

## 复现 ##

新建表

CREATE TABLE `lock_table` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `content` varchar(32) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

新建一个会话1，开启事务执行以下命令后不要提交事务

begin;
    select * from lock_table limit 1;

新建另一个会话2，执行DDL命令，发现DDL语句执行被阻塞

ALTER TABLE `lock_table` ADD INDEX content_index(content);

此时表被锁定，再新建一个会话3，执行查询语句发现该操作同样被阻塞住

select id from lock_table limit 1;

<table> 
 <thead> 
  <tr> 
   <th>-</th> 
   <th>会话1</th> 
   <th>会话2</th> 
   <th>会话3</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>步骤1</td> 
   <td><code>begin;</code></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>步骤2</td> 
   <td><code>select * from lock_table limit 1;</code></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>步骤3</td> 
   <td>-</td> 
   <td><code>ALTER TABLE lock_table ADD INDEX content_index(content);</code></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>步骤4</td> 
   <td>-</td> 
   <td>-</td> 
   <td><code>select * from lock_table limit 1;</code></td> 
  </tr> 
 </tbody> 
</table>

## 原理 ##

#### DDL上锁流程 ####

[MySQL Online DDL的改进与应用][MySQL Online DDL]

1.  prepare阶段：尝试获取MDL排他锁，禁止其他线程读写；
2.  ddl执行阶段：降级成MDL共享锁，允许其他线程读取；
3.  commit阶段：升级成MDL排他锁，禁止其他线程读写；
4.  finish阶段：释放MDL锁；

#### DDL导致锁表的原因 ####

> To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table’s structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

mysql官方文档[metadata-locking][]一节中指出，为了确保事务可序列化，mysql不允许一个会话对在另一会话中未完成的显式或隐式启动的事务中使用的表执行DDL语句。服务器通过获取事务中使用的表上的元数据锁并将这些锁的释放推迟到事务结束之前来实现。表上的元数据锁可防止更改表的结构。这种锁定方法的含义是，一个会话中事务正在使用的表在事务结束之前不能被其他会话在DDL语句中使用。

mysql对申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。由上可知当事务一旦申请到MDL锁后，直到事务执行完才会将锁释放，当长事物或未提交的事务未提交完成时，执行DDL语句会等待MDL排他锁而阻塞，继而阻塞该表的后续其他操作。

#### 观察MDL锁 ####

MySQL5.7中的`performance_schea`库下新增了一张表`metadata_locks`，可以很方便地查看MDL锁的状态。默认情况下mysql未启动该表，可以通过以下两种方式打开该功能：

1.  临时启用(mysql实例重启后，恢复默认值)

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';

1.  永久启用

修改配置文件，添加以下参数

[mysqld]
    performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

为了能方便查看到事务以及DDL执行过程中MDL锁的状态，需要延长DDL执行时长，否则会由于DDL执行过快而难以观察MDL锁的状态。可以通过如下语句向测试表中添加200万条测试记录。

delimiter  //
    CREATE PROCEDURE init_lock_table() 
    BEGIN 
    	DECLARE index_id INT;
    	SET index_id = 0;
    	START TRANSACTION; 
    	WHILE index_id < 2000000 DO
    		insert into lock_table(content) values (CONCAT('content',index_id));
    		SET index_id = index_id + 1;
    	END WHILE;
    	COMMIT; 
    END //
    delimiter ;
    call init_lock_table();

1.  事务获取共享锁

开启会话1，启用事务执行以下语句，获取MDL共享锁：

begin;
    select id from lock_table limit 1;

查看MDL锁状态，可见348线程（会话1）获取到测试表的共享锁。

1.  DDL语句获取排它锁

开启会话2，执行DDL语句，尝试获取MDL排他锁：

ALTER TABLE `lock_table` ADD INDEX content_index(content);

查看MDL锁状态，可见349线程（会话2）获取到测试表的共享锁，并且在等待测试表的排它锁。由于DML锁已经被会话1的事务占有，会话2只能等待会话1事务结束后释放DML锁，因而该锁状态为`PENDING`。

1.  DDL持有锁降级成共享锁

会话1执行语句`commit;`提交事务，令会话1释放MDL共享锁。此时可见会话2获取MDL排它锁成功，然后DDL的排他锁降级为共享锁：

当DDL持有的MDL锁降级为共享锁后，不再阻塞该表的其他操作，此时会话1再开启事务获取MDL共享锁不会阻塞，锁状态如下，从而实现`Online DDL`。

1.  DDL持有锁升级为排他锁

当DDL执行完成时，会再次尝试获取MDL排他锁，如果此时348线程（会话1）的事务未完成而持有MDL共享锁时，349线程（会话2）的DDL操作等待MDL锁继而再一次0阻塞该表的其他操作。

## 解决方案 ##

解除正在锁表的状态有两种方法：

#### 方法一 ####

终止DDL语句，选择业务低峰期或其他时间段执行。

1.  查询是否锁表

show OPEN TABLES where in_use > 0;

1.  查询进程  
    （如果账号有SUPER权限，则可以看到所有线程，否则，只能看到当前用户的线程）

show processlist;

或者

select * from information_schema.processlist where COMMAND != 'Sleep';

1.  杀死进程

kill 进程ID;

示例：

查询到DDL会话的进程ID为286，然后通过`kill`命令杀死该进程终止会话，解决阻塞问题。

![在这里插入图片描述][20200628101629109.png]

kill 286;

#### 方法二 ####

杀死阻塞DDL语句的会话，但并不建议这么操作，因为无法判断该会话执行了什么操作，武断地终止该会话会造成无法预估的业务异常。

1.  查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

1.  查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

1.  查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

1.  杀死进程

kill 进程ID;

示例：

查询到阻塞DDL操作的长事务会话的进程ID为285，然后通过`kill`命令杀死该进程终止会话，解决阻塞问题。

![在这里插入图片描述][20200628101641584.png]

kill 285;

#### 方法三 ####

执行DDL语句前，先通过`lock_wait_timeout`设置好锁超时时间，避免长时间的DML锁等待。当DDL语句等待MDL锁超时时，自动终止当前会话，避免长时间等待锁继而阻塞其他线程。

示例：

-- 设置当前会话等待锁超过5秒后，自动终止DDL
    SET lock_wait_timeout=5;
    ALTER TABLE `lock_table` ADD INDEX content_index2(content);

当等待DML锁超时后，DDL终止并且抛出错误：`1205 - Lock wait timeout exceeded; try restarting transaction`。

## 总结 ##

1.  谨慎使用长事务，业务代码中的耗时操作尽量不要在事务中执行；
2.  执行DDL语句前先查看当前活跃事务，防止有未提交事务或者长事务存在；
3.  DDL语句导致锁表后，先杀掉DDL语句进程，终止锁库减小影响范围，再然后排查问题；

[20200628101554138.png]: https://img-blog.csdnimg.cn/20200628101554138.png
[20200628101603177.png]: https://img-blog.csdnimg.cn/20200628101603177.png
[MySQL Online DDL]: https://www.cnblogs.com/xinysu/p/6732646.html
[metadata-locking]: https://dev.mysql.com/doc/refman/5.7/en/metadata-locking.html
[20200628101629109.png]: https://img-blog.csdnimg.cn/20200628101629109.png
[20200628101641584.png]: https://img-blog.csdnimg.cn/20200628101641584.png