前言
锁表是我们在生产环境十分常见的问题之一，解决问题前需要先了解锁表产生的原因以找到解决方案，并制定方案以预防锁表，本文接下来会分别模拟元数据锁表（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;
