MySQL 锁机制与加锁实操

锁这块是我在备份和并发里最容易踩坑的地方。按加锁的范围分三层来看：全局锁、表级锁、行级锁，这份笔记也就照这个顺序记，每一层都开两个会话实际跑一遍才记得住。

一、全局锁

备份数据库的时候先加全局锁，备份完再解锁。加锁之后其他客户端只能读、不能写。

# 全局锁
flush tables with read lock;

# 备份操作
mysqldump -uroot -p123321 [备份数据库名] > [备份数据库文件]

1. 完整备份流程（要开两个会话）

# 完整备份流程
# 本地安装端口不是默认的3306需要 -P 指定（非容器内，容器内是3306）
# 不是在mysql内执行就需要先连接后进入执行
# 或者一直在shell（如果是电脑本地安装）用类似docker exec方式（建议）


# 1. 进入mysql环境加全局读锁（当前会话）
docker exec -it mysql01 mysql -uroot -p123321

flush tables with read lock;

# 2. 执行备份（另开终端）
# 不能在mysql内执行，没有dump命令
# 在容器环境shell执行，自动安装了命令

# 进入容器内部执行（交互式）
docker exec -it mysql01 bash
mysqldump -uroot -p123321 mydb > /tmp/mydb.sql

# 然后从容器目录复制到宿主机，因为没有挂载 /tmp 这个目录
docker cp mysql01:/tmp/mydb.sql /opt/mysql/backup/

# 解除锁
unlock tables;

# 3. 在mysql环境释放锁（原会话）
docker exec mysql01 mysql -uroot -p123321 -e "unlock tables;"



# 注意点
# ① flush tables with read lock; 后当前会话不能退出，否则锁自动释放
# ② mysqldump 备份需在另一个终端执行
# ③ 生产环境建议使用 --single-transaction（innodb 热备份）替代全局锁

2. 全局锁的问题，以及更推荐的备份方式

全局锁其实是个比较重的操作：

1. 在主库上备份，备份期间执行不了更新，业务基本就得停摆
2. 在从库上备份，备份期间从库没法执行主库同步过来的二进制日志（binlog），会导致主从延迟

所以 InnoDB 引擎里可以在备份时加上 --single-transaction，做不加锁的一致性备份：

# 若不使用全局锁，innodb 引擎可使用事务快照备份，避免锁表
# --single-transaction 备份开始时拍一张照片，备份过程中只读照片，不管别人怎么改
# --quick 就是备份时一点一点读数据，不会一次性把所有数据塞进内存
# 两个一起用是不影响业务和内存的常见手法

# 但 docker exec 是直接进入容器内部执行命令，不需要指定 -P
docker exec mysql01 mysqldump -uroot -p123321 --single-transaction --quick test > /tmp/test.sql


root@VM-0-12-ubuntu:~# docker exec mysql01 mysqldump -uroot -p123321 --single-transaction --quick test > /tmp/test.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 

root@VM-0-12-ubuntu:~# ls -alh /tmp/test.sql
-rw-r--r-- 1 root root 2.2K Aug 24 16:14 /tmp/test.sql

3. 为什么备份文件直接落到了宿主机上（而不是容器里）

这个我一开始没想通，后来拆开看就明白了：docker exec 把命令分成了两部分。

我执行的是：

docker exec mysql01 mysqldump ... --single-transaction --quick test > /tmp/test.sql

拆开就是两段：

第一部分（在容器内执行）：
docker exec mysql01 mysqldump ... --single-transaction --quick test

第二部分（在宿主机执行）：
> /tmp/test.sql

> 这个重定向不是被传进容器里执行的，而是由宿主机当前的 shell 处理的（命令本来就是在 Linux 上敲的，当然交给当前 shell）。docker exec 只是把 mysqldump 的输出送到宿主机 shell 的标准输出，宿主机 shell 再把这份输出写进宿主机的 /tmp/test.sql，所以备份文件理所当然地出现在宿主机的 Linux 上。

二、表级锁

下面演示的都是 InnoDB 引擎的表级锁，至少要开两个会话才能看出锁的效果。

1. 表锁（主动加）

• 表共享锁（read）：阻塞其余客户端写，不阻塞任意客户端读
• 表独占锁（write）：其余客户端不能读也不能写，自己能读能写

# 语法
# 加锁
lock tables 表名 read/write;
# 释放锁(关闭客户端也行)
unlock tables;

2. 元数据锁 MDL（被动加）

MDL 主要针对表结构，不针对数据。

它的设计目的：防止 DML（数据操作）与 DDL（结构变更）同时进行时产生数据不一致。

对表增删改查会自动加 MDL 读锁，对表结构做 alter 变更会加 MDL 写锁。读锁之间兼容，写锁和写锁、读锁都互斥。

加表锁的时候也会自动加 MDL。

查数据 select 不开启事务，执行完了锁就没了；但如果开启了事务，必须 commit 才能解锁。这又一次体现了事务的重要性。

顺便区分一下 MDL 和表锁：

拿 score 表来试，先看一眼表里的数据：

# 语法

# 会话 1 中执行
begin;
select * from score;

# 会话 2 
begin;
select * from score;
update score set math = 69 where id = 1;
# 这些操作都成功了，因为不针对表结构操作，只对数据操作了
# 结束，两个会话 commit

# 开启两个会话的事务
# 会话 1 触发 MDL读锁
begin;
select * from score;

# 会话 2，尝试修改表结构
begin;
alter table score add column java int;

执行后发现阻塞了：

因为 alter 的 MDL 锁与其余 MDL 锁都互斥，所以被挡住了。

MDL 记录在一张系统表里，可以这样查：

# 查看元数据锁（MDL）
# MDL记录在一张系统表中

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

select 
    object_type,      -- 被锁住的是什么东西（是表？还是整个库？还是全局？）
    object_schema,    -- 属于哪个数据库
    object_name,      -- 被锁住的对象叫什么名字（表名/库名）
    lock_type,        -- 加的是什么锁（读锁/写锁/其他）
    lock_duration     -- 这把锁要持续多久（事务结束释放/语句结束释放/手动释放）
from performance_schema.metadata_locks;

注意要在产生 MDL 锁的时候查才看得到：

3. 意向锁

执行 update 的时候，InnoDB 会对扫描到的所有索引记录加行锁，不只是最终匹配的那几行；如果扫了全表，那就相当于所有行都上了锁（机制上还是行锁，效果接近表锁）。

意向锁主要解决 InnoDB 引擎中行锁和表锁的冲突问题，前提是开启了事务。

-- 假设 score 表有 100 万行，id 是主键

-- 示例1：走主键索引，只锁一行
update score set math = 100 where id = 1;  -- 只锁 id=1 这一行

-- 示例2：走普通索引，锁索引记录 + 主键记录（回表）
-- 假设 name 有索引
update score set math = 100 where name = '张三';  -- 锁 name 索引 + 主键索引

-- 示例3：不走索引，全表扫描
-- 假设 math 没有索引
update score set math = 100 where math < 60;  -- 扫描全表，所有行都加行锁

# 总之就是锁住扫过的索引

特殊情况：已经有行锁的时候不能直接加表锁，加表锁之前还得逐行判断有没有被锁，性能非常低，这就是行锁和表锁的冲突。

意向锁就是来解决这个问题的：行锁出现的时候，InnoDB 会给表也加一把意向锁，这时候再加表锁，只要检查意向锁的情况就行，不用去扫每一行。如果意向锁和当前表锁兼容就不冲突，反之会阻塞，直到行锁和意向锁被释放（commit）。

• 意向共享锁（IS）：对某表某行加共享行锁，同时自动加 IS 表锁，由 select ... lock in share mode 添加
• 意向排他锁（IX）：对某表某行加排他行锁，同时自动加 IX 表锁，由 insert、update、delete 和 select ... for update 添加

其中只有 select 需要 for update 手动触发行锁和表锁，其余三个（增删改）都是自动加 X 行锁和 IX 表锁。总之，增删改是自动锁，读操作是手动锁。

把上面这张图的意思写下来就是：意向共享锁（IS）与表锁共享锁（read）兼容，与表锁排它锁（write）互斥；意向排他锁（IX）与表锁共享锁（read）及排它锁（write）都互斥，但意向锁之间不会互斥。

结论：只有意向共享锁和表共享锁兼容，所以加表锁肯定是加共享锁。

查看意向锁和行锁的加锁情况（MySQL 8.0 推荐）：

# 查看意向锁及行锁加锁情况（MySQL 8.0 推荐）

select 
    object_schema,
    object_name,
    index_name,
    lock_type,
    lock_mode,
    lock_data
from performance_schema.data_locks;

select 
    object_schema,   -- 数据库名称（锁对象所属的库）
    object_name,     -- 表名称（锁对象所属的表）
    index_name,      -- 索引名称（锁加在哪个索引上；NULL 表示表锁，PRIMARY 表示主键索引）
    lock_type,       -- 锁类型：TABLE（表锁）/ RECORD（行锁）
    lock_mode,       -- 锁模式（具体锁类型，如 IS/IX/S/X/S_GAP/X_GAP/INSERT_INTENTION 等）
    lock_data        -- 锁数据（行锁时显示被锁住的主键值；表锁时为 NULL）
from performance_schema.data_locks;

意向共享锁：

# 会话 1 加行锁的共享锁，同时给表加上意向共享锁
begin;
select * from score where id = 1 lock in share mode;

# 查一下锁的情况
select 
    object_schema,
    object_name,
    index_name,
    lock_type,
    lock_mode,
    lock_data
from performance_schema.data_locks;
# score 成功加了意向共享行锁和表锁

record 是行锁，table 是表锁。

# 为score添加表共享锁（写锁会卡住）
lock tables score read;
# lock tables score read; 加的是 服务层（Server层）的表锁
# 而 performance_schema.data_locks只显示 InnoDB 存储引擎层的锁信息
# 所以无法查到表共享锁

意向排他锁：

# 添加排行、表锁
# 会话 1 查看
update score set math = 66 where id = 1;

# 看锁情况
# 此时加表锁会卡死，因为排他锁和表锁互斥

加了意向锁，为什么还特地加表锁？ 我一开始也绕进去了。意向锁是表锁的一种，但它并不真正"锁住整个表"，它只表达"这个表里有行被锁了"这个事实；真正锁住整个表的是表锁的 X/S。意向锁只是逻辑上存在，用来给表锁判断是否冲突的，本质还是行锁和表锁的冲突。

所以不是"为了加意向锁而去加表锁"，而是为了能安全地加表锁，才必须有意向锁。

三、行级锁

1. 行锁

行级锁每次操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最低，并发度最高，用在 InnoDB 存储引擎里。

InnoDB 的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。行级锁主要分三类：

行锁有两种类型：

• 共享锁（S）：共享锁之间兼容，和排他锁互斥
• 排他锁（X）：第一个事务获取某行数据的排他锁，其他事务就不能获取这行数据的共享锁和排他锁了

总结：只有 S 可以和 S 兼容。

常见操作各自加什么行锁：

演示之前先补两条默认规则：

#  查看 意向锁及行锁 加锁情况（MySQL 8.0）

select 
    object_schema,
    object_name,
    index_name,
    lock_type,
    lock_mode,
    lock_data
from performance_schema.data_locks;

#  字段含义

object_schema : 数据库名
object_name   : 表名
index_name    : 锁使用的索引名（NULL=表锁，PRIMARY=主键索引，其他=二级索引名）
lock_type     : 锁粒度（TABLE=表锁，RECORD=行锁）
lock_mode     : 锁模式（IS/IX/S/X/S_GAP/X_GAP/INSERT_INTENTION 等）
lock_data     : 锁定的具体行数据（行锁时为主键值，表锁/间隙锁时为 NULL）

# 手动添加select行锁
begin;
select * from score where id = 1 lock in share mode;

# 查看锁的情况

可以看到隐式主键（这张表没有索引，MySQL 自己生成了一个主键索引）GEN_CLUST_INDEX，查询 * 扫了所有行，就把所有行都锁上了（S）。

# 在会话 2 执行相同语句验证共享锁兼容
begin;
select * from score where id = 1 lock in share mode;

# 查看锁的情况

锁了两次表（IS），提交事务之后再查这张表，会发现相同的锁少了一批。

这里把 2.3 之前的笔记插回来再看一眼：

排他锁的互斥情况这里就不演示了。

2. 间隙锁与临键锁

上面这张图上的三条规则值得单独记一下：

1. 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁
2. 索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁
3. 索引上的范围查询（唯一索引）——会访问到不满足条件的第一个值为止

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

情况 1 示例：

左边事务提交之后，右边的 update 就会执行成功。

提交右侧事务后查询数据：成功插入 id=7 的数据。

情况 2 示例：

临键锁（Next-Key Lock） = 行锁（Record Lock） + 间隙锁（Gap Lock）

上图中索引不唯一，id=18 的前后间隙都可能被插入 id=18，为了防止查询 id=18 时出现幻读，需要锁住间隙以及 id=18 这一行。

上图锁住的区间：(16,29)。

情况 3 示例：

记得先提交之前的事务。

四、小结

间隙锁的唯一目的：防止其他事务插入间隙造成幻读。间隙锁可以共存，一个事务的间隙锁不会阻止另一个事务在同样的间隙上锁。

临键锁（Next-Key Lock） = 行锁（Record Lock） + 间隙锁（Gap Lock）

主要理解到为什么要加锁就行，具体原因可以不会。