HenryKang's Docs

MySQL事务与并发控制和锁

16分钟阅读

Overview

了解事务的 ACID 特性、隔离级别、并发控制、锁……

事务

ACID 特性

  • 原子性 (atomicity)：整个事务中的操作要么都做，要么都不做。
  • 一致性 (consistency)：事务不能改变数据库的一致性，如事务中的操作打破了定义的约束条件，这时候就得回滚。
  • 隔离性 (isolation)：事务之间相互隔离，事务未提交之前，其更改对其他事务都不可见。
  • 持久性 (durability)：事物一旦提交，其结果就是永久性的，即使崩溃也要能够恢复。

隔离级别

  • 事务并发时的数据不一致性问题：
    • 脏读：读到了其他事务已修改但尚未提交的数据。
    • 不可重复读：一个事务中多次读取相同记录，发现内容不一致，因为另一个事务进行了修改操作并已提交。
    • 幻读：一个事务中多次读取的数据条数不一致，另一个事务进行了增删改操作。
  • 为了解决上述问题，提出的隔离级别：
    • 读未提交(Read Uncommitted)：一个事务可以看到其他事务已修改但尚未提交的数据。
    • 读已提交(Read Committed，RC)：一个事务只能看到其他事务已经提交的数据。
    • 可重复读(Repeatable Read，RR)：一个事务在执行过程中看到的数据，总是和它启动时看到的一样。
    • 可串行化(Serializable)：读写都要加锁，所有操作均可以转化为一个串行化序列，避免并行访问。

注意：隔离级别越高，并发度越低，MySQL 默认为可重复读。

隔离级别脏读不可重复读幻读
读未提交√√√
读已提交×√√
可重复读××√
可串行化×××

注意 SQL 标准中的 RR 级别允许幻读，而 InnoDB 通过 next-key lock 解决了幻读。

并发控制

事务的隔离级别是为了解决数据的不一致性问题，是通过加锁的手段实现的，但如果只是单纯的加锁，如加读写锁使其可串行化避免并行访问，业务方面那当然不会有问题。只是加锁的同时我们还要考虑其并发性，我们知道读读是不冲突的，而读写、写写都会冲突，如果有一个办法能够保证一致性读的同时还能够进行写操作 (即一致性非锁定读)，那是极好的。

这时候就有了 MySQL 的多版本并发控制(Multiple Version Concurrent Control，MVCC)。

多版本并发控制 (MVCC)

MVCC 的目的就是实现一致性非锁定读，这就需要为每个事务维护一个 一致性快照(consistent snapshot)，这个快照当然不是物理上真实存在的，而是通过给每行记录加上一个 版本号 再配合 undo log 实现的。

undo log：当记录更新时，都会在 undo log 中记录一条回滚操作，即更新的逆操作，通过这个逆操作我们可以使记录回滚到之前的一个状态。当系统内没有比某条 undo log 更早的快照时，这条 undo log 就可以删除。

MVCC 是针对 RC、RR 两个隔离级别而言的，区别就在于 RR 下一致性快照是在事务开启时创建的，RC 下事务中的每一条语句都会开启一个一致性快照，达到读已提交的效果。

MySQL 通过 MVCC 解决 RR 下的不可重复读，但是为什么 RC 也要使用 MVCC，因为 MVCC 不只解决了重复读问题，还提供了一致性非锁定读。

下面针对 MySQL 的默认隔离级别 RR 对 MVCC 进行讨论：

InnoDB 会按照严格的递增顺序为每个事务分配一个唯一的事务 IDtrx_id；表中每一行记录都附加了一个版本号row_id，记录的是事务 ID，指示该行数据的当前版本是被哪一个事务更新过的。

历史版本数据并不会真实的保存，而是需要时通过 undo log 进行还原，undo log 除了记录更新操作对应的逆操作之外，还记录了 trx_id 以指示这个更新是哪个事务做的。

在一个事务开启的瞬间，InnoDB 为该事务创建了一个数组，用来记录此刻系统内正在活动的事务 ID。这个数组中的最大值 (即当前事务 ID) 和最小值将表中的记录按照其 row_id 分为了三个部分：

  • 小于最小值的部分，都是已提交的事务更新过的数据，则当前事务可以看到。
  • 中间部分：
    • 若记录的 row_id 在当前事务的数组中，说明当前事务开启的瞬间，row_id 指向的事务还没提交，当前事务看不到。
    • 若记录的 row_id 不在当前事务的数组中，说明当前事务开启的瞬间，row_id 指向的事务已经提交，当前事务可以看到。
    • 注意：此处就是 RR 和 RC 的区别，对于 RC 只要提交了都能看到，而对于 RR，只要当前事务开启的瞬间，没提交的事务即便后来已经提交，当前事务也看不见。
  • 大于最大值的部分，都是未来事务提交过的数据，当前事务看不到。

利用这个机制，查询时根据版本号判断某记录是不是当前事务能够看到的，若能看到，则返回该记录，若看不到，则使用 undo log 回滚到第一个碰到的、当前事务能看到的记录，再将其返回。

RR下某事务的查询过程

注意：

  • 对于默认的查询操作，会判断版本号，查询可见的版本。而对于更新操作，例如 update set a=a+1 where id = 1，将采用 当前读 操作，即获取最新版本的数据，如果对于 a 字段还去根据 undo log 查询历史可见版本，那么就会丢失未来事务做的更改。
  • 使用默认的查询称为一致性非锁定读，即没有加读锁。而使用 select ... for update 添加排他锁 (Exclusive Lock, X Lock)、select ... lock in share mode 添加共享锁 (Shared Lock, S Lock) 则会使用一致性锁定读，即正常的加读、写锁，保证了读取的是最新版本同时也不能让后面的事务进行更新操作。
    • 锁定读不会阻塞后续的默认查询操作，即使用 MVCC 保证一致性非锁定读；
    • X 锁会阻塞后续的 S、X 锁，S 锁之会阻塞后续的 X 锁；

如何解决幻读

来自MySQL实战45讲

start transaction VS begin

前者执行后就会开启事务，后者执行到第一条语句前才会开启事务。

如上图所示，当其他事务在当前事务上一次查询时返回的结果集范围内部区域进行了增删改操作，都可能会导致幻读。（注意这里讨论的是 RR 级别下的当前读，因为默认读操作使用 MVCC，RC 级别下不会加间隙锁）

间隙锁(Gap Lock)：给两条记录之间的间隙添加锁，锁住特定的范围，防止其他事务在间隙中插入新数据。间隙锁之间、间隙锁和行锁之间都不会冲突，间隙锁只和往这个间隙之中插入的操作发生冲突。

间隙锁 + 行锁称为 Next-Key Lock，是一个左开右闭的区间，区间的 “右闭” 端点对应的正是对该位置数据行的行锁，间隙锁防止了数据的新增操作，结合行锁防止了删改操作，两者共同作用解决了幻读问题。

Next-Key Lock 加锁机制如下：

  • 凡是使用当前读访问过的数据都会加行锁和间隙锁。
  • 当进行等值查询时，有两个优化点：
    • 当使用唯一索引时，降级为行锁；
    • 当使用普通索引时，遇到的第一个不满足等值条件的记录对应的锁会降级为间隙锁，即不对该记录添加行锁。
CREATE TABLE products (
    id INT PRIMARY KEY, -- 唯一索引
    category_id INT,    -- 普通索引
    name VARCHAR(10),
    INDEX idx_category (category_id) -- 为 category_id 创建普通索引
);
 
-- 初始数据
INSERT INTO products VALUES
(10, 100, 'A'),
(20, 100, 'B'),
(30, 200, 'C'),
(40, 300, 'D'),
(50, 300, 'E');

考虑有如上所示的表和数据，对于唯一索引、普通索引上的等值和范围查询，会有如下行为。

  • 唯一索引
    • 等值查询：降级为行锁，因为唯一性约束保证不会有其他事务插入；
    • 范围查询：无降级，例如 where id > 30，会锁定 (30, 40]、(40, 50]、(50, +∞)；
  • 普通索引
    • 等值查询：遇到第一个不满足等值条件的记录时，降级为间隙锁，即最后一个区间为左开右开区间。例如 where category_id = 100，会锁定 (-∞, 100]、(100, 200)；
    • 范围查询：无降级，例如 where category_id > 100，会锁定  (100, 200]、(200, 300] 和 (300, +∞)；
  • 无索引：导致全表扫描，每一行和每一个间隙都会加锁，相当于表锁。

锁

锁的目的是解决并发访问下的数据一致性问题；锁的持有者是事务；锁定的对象是表、索引；锁信息保存在内存中，包含当前事务 ID、锁类型、索引、数据记录标识 (页号 + 偏移量) 等。

  • 全局锁：对整个数据库实例加锁，典型的应用场景是数据库备份。
    • 使用命令：flush tables with read lock
  • 表级锁包含：
    • 表锁：lock tables ... read/write 是由 Server 层提供的。
    • 元数据锁(meta data lock，MDL)：也由 Server 层提供，分读、写锁，不需要显式使用，当访问一个表时就会自动加上读锁，对表结构更改时会添加写锁，阻塞后续的读写操作；一定注意大数据量表不要在线上直接修改表结构。
    • 意向锁(Intention Lock)：意向共享锁 IS，意向排它锁 IX，当申请相应的行级锁 S 或 X 时，必须先持有相应的意向锁，由 InnoDB 自动完成。意向锁只会和表级锁冲突，它的意义是，当申请表级锁时，快速判断当前表上有无行级锁，避免去扫描锁列表信息。
  • 行级锁：
    • 行锁：锁定单行数据，区分读写锁。
      • 读锁：select ... for share 或 select ... lock in share mod；
      • 写锁：增删改或 select ... for update 时触发；
    • 间隙锁：锁定一个范围，间隙锁和其他间隙锁、行锁不互斥，只会阻塞插入这个间隙的操作。
    • Next-Key Lock：即行锁 + 间隙锁。

Innodb 什么时候会使用表锁？

  • 非索引字段作为查询条件而引起的全表操作；
  • 索引失效而引起的全表操作；
  • 更新表结构、多表复杂的联合查询的时候。

加锁的方式：

两段锁协议：将事务加锁和解锁分为锁的扩张和收缩两阶段，一旦开始收缩就不能再申请锁。若并发执行的事务遵守两段锁协议，则这些事务的调度都将是可串行化的，但仍有可能发生死锁。

事务开始后就处于加锁阶段，一直到执行 ROLLBACK 或 COMMIT 之前都是加锁阶段，ROLLBACK 和 COMMIT 使事务进入解锁阶段。给我们的启示是：将最有可能冲突的锁最后申请，减少加锁的时间，提高并发度。

一个简单的死锁：

/*
A 使用 S 锁，锁住一条记录；
B 尝试删除该条记录，则 B 尝试加的 X 锁会阻塞在 A 的 S 锁上；
A 尝试删除该记录，会阻塞在 B 的 X 锁上；
造成死锁。
*/
A: select * from t where id = 1 lock in share mode;
B: delete from t where id = 1;
A: delete from t where id = 1;

使用 SHOW ENGINE INNODB STATUS 命令，搜索 LATEST DETECTED DEADLOCK 部分可以查看最近一次死锁信息。

关系图谱

反向链接