Mysql为什么需要锁

日常工作中锁的出现是应对共享资源竞争，将资源锁住，防止出现资源随意更改而不符合预期。对数据库而言，数据便是共享的资源，Mysql提供了锁机制，也是为了应对不同场景提供数据安全的一种保障。本篇主要介绍全局锁与表级锁。

Mysql锁分类

全局锁

锁住数据库中所有表

表级锁

锁住数据库中某张表

行级锁

锁住数据库某张表的某一行数据

Mysql锁实现

数据库演示版本 select version(); // 5.7.38
使用默认引擎 InnoDB

-- 表结构与数据
CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

INSERT INTO `test`.`users` (`name`, `age`) VALUES ('zhangsan', '21');
INSERT INTO `test`.`users` (`name`, `age`) VALUES ('lisi', '22');
INSERT INTO `test`.`users` (`name`, `age`) VALUES ('wangwu', '23');
INSERT INTO `test`.`users` (`name`, `age`) VALUES ('zhaoliu', '24');
INSERT INTO `test`.`users` (`name`, `age`) VALUES ('haha', '25');
INSERT INTO `test`.`users` (`name`, `age`) VALUES ('hehe', '21');
INSERT INTO `test`.`users` (`name`, `age`) VALUES ('heihei', '26');
INSERT INTO `test`.`users` (`name`, `age`) VALUES ('oo', '26');

全局锁

当数据库加上全局锁时，只允许数据的读，而不允许DML（插入、更新和删除），也不允许DDL（alter更新表结构等）。一般用于Mysql的数据备份场景（可以使用mysqldump命令）。

如果不加锁备份，可能出现多个关联表因为先后备份导致数据的不一致。

在InnoDB引擎中，关于mysqldump备份，可以不通过加锁的方式备份，添加参数即可 --signle-transaction，前提是引擎需要支持事务。

命令
添加全局锁：flush tables with read lock;
释放全局锁：unlock tables; / 断开连接

演示
模拟2个数据库连接会话

1、会话1加锁：

flush tables with read lock;

2、会话2进行查询和更新：
image-1671244319366

读成功，更新阻塞

3、会话1解锁

unlock tables;

4、会话2阻塞的sql立即执行成功

-- 控制台
mysql> update users set age = 1 where id = 1;


Query OK, 1 row affected (1 min 48.61 sec)
Rows matched: 1  Changed: 1  Warnings: 0

扩展
设置全库只读的另一个方式：set global readonly = true;
两者之间区别：
1、set global…方式一般用于做只读库的判断，可能有其它影响面
2、set global…在客户端断开连接时仍然有效，而FTWRL命令则会在客户端断开连接时自动释放。

表级锁

表级锁一种是表锁；一种是元数据锁（MDL，metadata lock）

表锁

1、表共享读锁（表读锁）
2、表独占写锁（表写锁）

命令
加锁：lock tables 表名 read/write;
解锁：unlock tables; / 客户端断开连接

演示
有两个客户端连接会话
1、会话1加users表的读锁;

lock tables users read;

2、会话2进行读/更新数据
image-1671244319366

读成功，更新阻塞

3、会话1本身进行读/更新数据
image-1671244543746

读成功，更新提示失败(包括更新索引)

4、会话1释放锁，会话2阻塞结束，执行成功

unlock tables;

结论
如果一个连接增加了表读锁，那么它本身只能读不能写；其它连接也只能读不能写。
如果一个连接增加表写锁，那么它本身既能读也能写；其它连接即不能读也不能写。
表锁一般用于全量更新数据，为了防止因其它行数导致更新变慢出现长事务，可以使用表级锁来提高效率。

元数据锁

元数据锁是系统自动控制，无须显示使用，释放是在语句所在事物提交后自动释放。MDL（metadata lock）的作用是，为了避免DML与DDL的冲突，保证读写的正确性。

当对一个表做增删改查操作的时候，加MDL读锁；
当对表结构/索引做变更的时候，加MDL写锁。
读锁之间不互斥，读写与写写锁之间互斥（读写互斥，指的是一个表被加了MDL读锁，便不能再加写锁；同理写写锁互斥）。

想象这样一个场景，当一个连接在进行表遍历的时候，此时有另一个连接改变了表结构，删了一列，查询结果便会对不上，便有了MDL的机制。

对应SQL 锁类型 说明
lock tables xxx read/write shared_read_only、shared_no_read_write
select、select … lock in share mode shared_read 与shared_write兼容、与exclusive互斥
insert、update、delete、select…for update shared_write 与shared_read兼容、与exclusive互斥
alter table… exclusive 与其它都互斥

shared_read_only(sro)：持有者读取表结构和数据
shared_no_read_write(snw)：持有者读取表结构和读写数据，阻塞其它线程对表数据的读写
shared_read(sr)：访问表结构并且读表数据
shared_write(sw)：访问表结构并且写表数据
exclusive：修改表结构和表索引

演示
查看元数据锁的语句：

select object_type, object_schema, object_name, lock_type, lock_duration from performance_schema.metadata_locks;

-- 如果返回Empty set，需要通过下面语句开启元数据锁记录（只是开启记录，不开启记录也会有锁机制）
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

启动3个连接会话

1、会话1开启事务，并执行查询

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users where id = 1;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |    1 |
+----+----------+------+
1 row in set (0.01 sec)

此时查询元数据锁记录：
image-1671260133113

2、会话2，开启事务，修改表结构
image-1671259057682

此时查询元数据锁记录：
image-1671260433927

表上多加了shared_upgradable共享升级锁，这个和Mysql的online DDL特性有关，后续涉及到再展开。

3、会话1，再次执行查询正常

4、会话3，是最后开启的，执行查询被阻塞，因为在会话2后面，会话2导致后面的dml语句无法申请MDL读锁，元数据锁处于pendding状态。

此时查询元数据锁记录：
image-1671260527304

5、手动结束会话2，会话3查询正常了，会话2对应元数据锁释放。

结论
执行dml语句后会加MDL锁，此时执行ddl会导致与MDL锁互斥。对于alter语句的执行，一定要避免其前有长事物的出现，因为它可能导致alter阻塞，然后加MDL写锁(pendding状态)，进而阻塞其后面的读写语句，导致表不可读写，进而可能导致数据库资源耗尽。

意向锁

意向锁表示事务有意向对表的某些行加锁。

属于表级锁的一种，为了避免加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得添加表锁时不用遍历每一行确认是否有行锁，提高加表级锁效率(lock tables … read/write)。

分类
1、意向共享锁（IS）
添加：select … lock in share mode
2、意向排他锁（IX）
添加：insert、update、delete、select…for update。

互斥关系
1、意向共享锁（IS）：与表共享锁兼容，与表排他锁互斥
2、意向排他锁（IX）：与表共享锁及表排他锁都互斥
3、意向锁之间不互斥
4、意向锁不会与行级共享/排他锁互斥

具体互斥关系可见：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

总结

1、避免出现长事务，如果有长事务存在，执行ddl语句会非常危险，因为ddl可能因为mdl写锁，导致后续的读写语句均阻塞。
2、关于行记锁会在下篇重点介绍。