生产环境MYSQL常见锁表场景

前言

锁表是我们在生产环境十分常见的问题之一，解决问题前需要先了解锁表产生的原因以找到解决方案，并制定方案以预防锁表，本文接下来会分别模拟元数据锁表（MDL锁）、行锁升级为表锁、死锁、**显示锁表
**四种锁表情形。

准备工作

• 安装了MYSQL和MYSQL客户端的带可视化界面的终端系统（windows、mac、可视化ubuntu等）。
• 分别打开三个MYSQL客户端A、B、C（会话C可以替换为navicat等可视化连接工具操作）。
• 创建测试表

-- 创建测试数据库
CREATE DATABASE IF NOT EXISTS locktest;
USE locktest;-- 创建测试表
CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50),value INT
);-- 插入一些测试数据
INSERT INTO test_table (name, value) VALUES 
('测试1', 100),
('测试2', 200),
('测试3', 300),
('测试4', 400),
('测试5', 500);

元数据锁表（MDL锁）

说明

当前进程执行DDL语句时需要获取MDL锁，若此时其他进程正在执行事务且包含当前表的操作语句（读或写），则DDL语句会被阻塞，进入等待锁的状态。

模拟流程

• 会话A执行：

TART TRANSACTION;
SELECT * FROM test_table;
# 暂时不执行COMMIT

• 会话B执行：

ALTER TABLE test_table ADD COLUMN new_col INT;

• 此时会话B会被阻塞，会话C（navicat）查看进程情况，执行：

SHOW PROCESSLIST;

• navicat上查询结果如下：
  

• 可以看到ID=24的进程正在等待元数据锁。这时会话A执行提交：

# 会话A提交事务
commit;

• 会话A提交事务后释放锁，会话B获取锁执行成功。

总结

这种锁表情形，常见于项目新版本上线时，开发人员A需要执行修改表tableA字段的SQL脚本时，此时开发人员B正在启动事务并查询tableA的数据，导致开发人员A的SQL脚本执行阻塞；或者此时线上项目正在执行包含表tableA操作的事务，该事务需要执行很长时间。

解决方案

行锁升级为表锁

说明

对于大数量的表进行查询时，通常会增加索引以提高查询效率。但如果在DML语句执行期间，如果索引使用不当导致索引失效时，行锁可能会升级为表锁（多行锁）。

模拟流程

• 给测试表增加索引

create index idx_name on test_table(name);

• 会话A执行：

START TRANSACTION;
UPDATE test_table SET value = 777 WHERE name LIKE '%测试%';
-- 不执行COMMIT

• 会话B执行：

UPDATE test_table SET value = 888 WHERE name = '测试2';

• 此处会话B语句阻塞，会话C（navicat）查看进程：

# 会话C执行
SHOW PROCESSLIST;

• 长时间等待后，会话B提示等待锁超时：
  
• 同样会话A执行commit，语句执行成功后恢复正常

# 会话A提交事务
commit;

总结

在InnoDB引擎中，锁是加在索引上的，当修改语句的索引失效时，innoDB会对扫描到的所有索引记录都加锁（多行锁），直到这条查询语句执行结束。在此期间，其他的进程如果需要修改其中一个被扫描到的索引对应的行记录时，就会因为没有得到行级锁而被阻塞。

解决方案

预防索引失效，索引失效常见情况：

死锁

说明

死锁是线上环境的经典问题，死锁形成的本质就是多进程之间形成了资源等待环，并且无法凭借自身能力解锁。

模拟流程

• 事务A执行：

START TRANSACTION;
UPDATE test_table SET value = 100 WHERE id = 1;

• 事务B执行：

START TRANSACTION;
UPDATE test_table SET value = 200 WHERE id = 2;

• 事务A再执行：

UPDATE test_table SET value = 300 WHERE id = 2;

• 事务B再执行：

UPDATE test_table SET value = 400 WHERE id = 1;

• 此时其中一个会话会提示死锁，并且innoDB已强制回滚并终止了其中一个事务：
  

总结

解决方案

• 检查相关事务的逻辑，尽量保证多个事务的获取锁资源顺序是相同的，比如都是先获取A锁，再获取B锁，最后获取C锁。又或者让获取多个锁的流程作为一个原子操作。

显式锁表

说明

手动执行sql语句LOCK TABLE锁表

模拟流程

• 会话A执行：

LOCK TABLES test_table WRITE;
-- 现在表被锁定，只有会话A可以读写

• 会话B常识查询或修改表，会被阻塞：

SELECT * FROM test_table;
-- 或
UPDATE test_table SET value = 500 WHERE id = 1;

• 会话A释放锁，会话B的语句能继续执行：

UNLOCK TABLES;

总结

解决方案

找到手动锁表的会话，进行手动释放锁即可。

常见观察锁表的SQL语句

-- 查看当前进程列表，观察被阻塞的查询
SHOW PROCESSLIST;-- 查看InnoDB事务和锁信息
SHOW ENGINE INNODB STATUS;-- 查看当前事务
SELECT * FROM information_schema.innodb_trx;-- 查看当前锁
SELECT * FROM performance_schema.data_locks;-- 查看锁等待
SELECT * FROM performance_schema.data_lock_waits;