MySQL数据库进阶第五篇（锁）_mysql 行锁,间隙锁,临键锁

本篇博客深入详细地讨论了数据库锁的种类和使用场景。首先介绍了全局锁的含义和使用场景，如全库逻辑备份，但这将阻塞后续的写和更新操作。然后博客讨论了表级锁和元数据锁（MDL），它们在操作表和表结构时自动添加，以保持数据一致性。同时，博文也讲解了“意向锁”和行级锁的概念，解释了它们如何减少冲突和提高并发性能。最后，作者阐述了更加细粒度的“间隙锁”和“临键锁”，如何通过防止’幻读’来维护数据的一致性。本文涵盖了从更宏观的全局锁，到更微观的行级锁，甚至于间隙锁和临键锁的各种锁机制，为读者对数据库锁有了更全面深入的了解。

一、锁的概述

二、全局锁

对整个数据库实例加锁，加锁后整个实例处于只读状态，后续DML的写语句，DDL语句，更新操作的事务提交都将被阻塞

使用场景：全库逻辑备份（将数据库中的数据备份成一个SQL文件保存在磁盘中

若执行备份过程中，还有数据的修改和插入，则备份的数据无法保证数据的一致性和完整性。

例如表订单，表库存，表订单日志，逐个备份该表的过程（备份顺序：订单->库存->日志）中依然有下单的操作，即备份了订单 但库存已经减少等操作 会导致数据库的数据不一致

flush tables with read lock ; # 加全局锁
sudo mysqldump -h 127.0.0.1 -uroot -p123456 -d db01 > db01.sql # 数据备份 shell界面中执行
unlock tables; # 再登录到MySQL，释放锁

1
2
3
4

加锁后，DML和DDL无法操作，DQL查询数据　依然可以使用

数据库中加全局锁，是一个比较重的操作，存在以下问题：

如果在主库上备份，那么在备份期间都不能执行更新
如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟

在InnoDB引擎中，可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份
sudo mysqldump -single-transaction -h 127.0.0.1 -uroot -p123456 -d db01 > db01.sql

三、表级锁

表锁

表共享读锁(read lock)
表独占写锁(write lock)

lock tables 表名... read/write  # 加锁
unlock tables / 客户端断开连接  # 释放锁

1
2
3

四、元数据锁（meta data lock, MDL）

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上

主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与 DDL冲突（增删改 DDL：数据库 表 表字段 ，DML：表中数据），保证读写的正确性。

元数据就是表结构，元数据锁就是维护表结构一致性的锁

当对一张表进行增删改查的时候 DML语句，加MDL读锁(共享)
当对表结构进行变 更操作的时候 DDL语句，加MDL写锁(排他)

查看元数据锁的加锁情况：

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

五、意向锁

若两个线程操作一张表，线程A对表加了行锁/表锁，之后 线程B要对表加表锁，线程B需要先遍历表 查看表是否已经被加行锁 或者 表锁。

为了减少类似以上，DML执行时加的行锁与后来要加的表锁冲突的现象。InnoDB引入了意向锁，使表锁不用再检查每行是否被加锁，使用意向锁减少了表锁的检查。


意向共享锁(IS)：由语句select … lock in share mode添加

​ 与表锁共享锁 (read)兼容，与表锁排他锁(write)互斥

意向排他锁(IX)由insert、update、delete、select…for update添加 。

​ 与表锁共 享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥

通过SQL查看意向锁和行锁的加锁情况：select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;
1

六、行级锁

七、行锁（Record Lock）

行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在 RC、RR隔离级别下都支持

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁

排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁

排他锁：所有客户端都不能进行对表的任何操作

注意：

针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁
InnoDB的行锁是针对于索引加的锁，若不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁
1
2
3
4

八、间隙锁（Gap Lock）

锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事 务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

举例：

# -----------------------------------------客户端1-----------------------------------------
begin;
update usr set age = 40 where id = 5; 
# 在客户端1 update了一个不存在的id=5后，加上了间隙锁 (3,8)
commit;
# -----------------------------------------客户端2-----------------------------------------
begin;
INSERT INTO usr VALUES (7,'安拉', '17799998819', 'jd1h@126.com', '城市规划', 51,'2', '0', '2001-09-15 00:00:00');  # 阻塞，被间隙锁，直到客户端1 commit
commit;

1
2
3
4
5
6
7
8
9
10

九、临键锁（Next-Key Lock）

行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。 在RR隔离级别下支持。

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜 索和索引扫描，以防止幻读。

举例：

非唯一索引，为了其它事务向间隙中插入一条记录 出现幻读现象，因此会把（3，7）的间隙锁上，把3之前的这块间隙也锁住，3这行数据也锁住

注意：

索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。

索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。

索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止

举例：锁19这行的数据，邻键锁锁上（19，25）以及25这行记录，还有邻键锁锁上正无穷大 以及 (25,+∞) 的间隙

1
2
3
4
5
6
7
8
9
10

间隙锁锁间隙，不包含对应的数据记录，只锁定该数据记录之前的这部分间隙

邻键锁即包含当前的数据记录，也会锁定该数据记录之前的这部分间隙

间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会 阻止另一个事务在同一间隙上采用间隙锁

十、锁总结

全局锁：锁定数据库中的所有表（数据库备份

表级锁：每次操作锁住整张表
读锁/写锁 lock tables 表名… read/write; unlock tables;
元数据锁 自动加 DML语句 数据的增删改查时 MDL读锁，DDL语句表结构变更 MDL写锁
MDL读锁：
shared_read：select, select … lock in share mode,
shared_write：insert ,update, select … for update
MDL写锁：alter table
意向共享锁/意向排它锁 自动加
加意向共享锁：shared_read：select, select … lock in share mode,
加意向排它锁：shared_write：insert ,update, select … for update

行级锁：每次操作锁住对应的行数据。只针对索引，如果不是索引 升级成表锁
行锁：共享锁/排它锁
排它：insert,update,delete, select … for update
共享：select … lock in shared mode
select 不加任何锁
间隙锁和邻键锁：间隙锁锁住间隙，邻键锁锁数据和间隙。