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、关于行记锁会在下篇重点介绍。