MySQL 事务原理分析

事务

• 前提：并发连接访问。
• 定义：事务是用户定义的一系列操作，这些操作要么都做，要么都不做，是一个不可分割的单位。
• 目的：事务将数据库从一种一致性状态转换为另一种一致性状态，保证系统始终处于一个完整且正确的状态。
• 组成：事务可由一条非常简单的 SQL 语句组成，也可以由一组复杂的SQL 语句组成。
• 特征：
  • 在数据库提交事务时，可以确保要么所有修改都已经保存，要么所有修改都不保存。
  • 事务是访问并更新数据库各种数据项的一个程序执行单元。
  • 在 MySQL innodb 下，单条 SQL 语句都具备事务，可以通过 set autocommit = 0，设置当前会话手动提交事务。
• 事务控制语句

  -- 开启事务
  START TRANSACTION | BEGIN
  -- 提交事务，并使得已对数据库做的所有修改持久化
  COMMIT
  -- 回滚事务，结束用户的事务，并撤销正在进行的所有未提交的修改
  ROLLBACK
  -- 创建一个保存点，一个事务可以有多个保存点
  SAVEPOINT identifier
  -- 删除一个保存点
  RELEASE SAVEPOINT identifier
  -- 事务回滚到保存点
  ROLLBACK TO [SAVEPOINT] identifier
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

---

ACID 特性

• 原子性（A）
  • 事务操作要么都做（提交），要么都不做（回滚），事务是访问并更新数据库各种数据项的一个程序执行单元，是一个不可分割的单位。
  • 通过 undo log 来实现回滚操作，undo log 记录事务的 DML 操作，当回滚时，回放事务 DML 操作的逆运算。
  • 在 MVCC 中，undo log 记录事务 DML 操作提交后产生的行数据版本信息。
• 一致性（C）
  • 一致性指事务将数据库从一种一致性状态转变为下一种一致性的状态，在事务执行前后，数据库完整性约束没有被破坏。
    • 例如：一个表的姓名有唯一约束，如果一个事务对姓名进行修改，但是在事务提交或事务回滚后，表中的姓名变得不唯一了，这样就破坏了一致性。
  • 逻辑上的一致性是可以被破坏的。（设置不同程度的隔离级别适当地破坏逻辑上的一致性）
  • 一致性由原子性、隔离性以及持久性共同来维护。
• 隔离性（I）
  • 隔离性表示各个事务之间相互影响的程度。
  • 目的：防止多个并发事务交叉执行导致数据不一致。
  • 通过设置不同程度的隔离级别，适当地破环逻辑上的一致性，从而提高性能。
  • 通过 MVCC 和 锁来实现。
    • MVCC：多版本并发控制，它不使用锁来限制读操作，从而实现高效并发读性能。
    • 锁用来处理并发 DML 操作，数据库中提供粒度锁的策略，针对表（聚簇索引 B+ 树）、页（聚簇索引 B+ 树叶子节点）、行（叶子节点当中某一段记录行）三种粒度加锁。
• 持久性（D）
  • 事务一旦完成，要将数据所做的变更记录下来，包括数据存储和多副本的网络备份。
  • 事务提交后，事务 DML 操作将会持久化（写入 redo log 磁盘文件：哪一个页、页偏移值、具体数据），即使发生宕机等故障，数据库也能将数据恢复。 redo log 记录的是物理日志，确保内存数据的安全。

---

隔离级别

• 目的：提升 MySQL 并发处理 SQL 语句的性能。
• ISO 和 ANIS SQL 标准制定了四种事务隔离级别的标准，MySQL innodb 默认支持的隔离级别是 repeatable read。
• read uncommitted（读未提交）
  • 读操作不做任何处理。
  • 写操作加 X 锁，写锁在事务提交或回滚后释放。
• read committed（读已提交）（RC）
  • 读操作使用 MVCC，读取最新版本的行数据。
  • 写操作加 X 锁。
• repeatable read（可重复读）（RR）
  • 读操作使用 MVCC，读取事务开始前版本的行数据。
  • 写操作加 X 锁。
• serializable（可串行化）
  • 读操作加 S 锁，所以事务都是串行化执行，此时隔离级别最严苛。
  • 写操作加 X 锁。

-- 设置隔离级别
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 或者采用下面的方式设置隔离级别
SET @@tx_isolation = 'REPEATABLE READ';
SET @@global.tx_isolation = 'REPEATABLE READ';
-- 查看全局隔离级别
SELECT @@global.tx_isolation;
-- 查看当前会话隔离级别
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

-- 手动给读操作加 S 锁
SELECT ... LOCK IN SHARE MODE;
-- 手动给读操作加 X 锁
SELECT ... FOR UPDATE;
-- 查看当前锁信息
SELECT * FROM information_schema.innodb_locks;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

---

不同隔离级别并发异常

• 脏读
  • 事务 A 读到事务 B 未提交的数据，也就是事务 A 读到脏数据。

• 不可重复读
  • 一个事务内两次读取同一个数据不一样。
  • 一般而言，不可重复读的问题是可以接受的，因为读到已经提交的数据，一般不会带来很大的问题，所以很多厂商（如 Oracle、SQL Server）默认隔离级别就是 read committed。

• 幻读
  • 一个事务内两次读取同一个范围内的记录得到的结果集不一样。 快照读和当前读不一致。
  • 在 repeatable read 隔离级别下通过读加锁解决。

---

MVCC

• MVCC 是一致性非锁定读，也就是读不加锁。
• 每一次开启事务的时候，MySQL 都会为其创建一个唯一的事务 id（长度为 64 位，并且一直递增）。
• read view
  • m_ids：创建 read view 时，已启动但未提交的事务 id 列表。
  • min_trx_id：创建 read view 时，已启动但未提交的最小事务 id。
  • max_trx_id：创建 read view 时，预分配给下一个未开启事务的 id。
  • creator_trx_id：创建该 read view 的事务 id。
• 聚簇索引记录的隐藏列
  • trx_id
    • 事务修改记录时，trx_id 记录该修改事务 id。
  • roll_pointer
    • 事务修改记录时，将旧记录写入 undo log，该指针指向旧版本记录。

• 事务可见性问题
  • 事务可以看到事务本身的修改。
  • 事务间的可见性
    • trx_id < min_trx_id：已提交，可见。
    • trx_id >= max_trx_id：未启动，不可见。
    • min_trx_id <= trx_id < max_trx_id
      • trx_id in m_ids：已启动但未提交，不可见。
      • trx_id not in m_ids：已提交，可见。
• 读已提交
  • 每次读取数据时，生成新的 read view。
• 可重复读
  • 启动事务时，生成新的 read view，一直使用直到事务提交。
• 快照读

  select * from table where ？
  1

• 当前读

  select * from table where ? lock in share mode # S 锁（读锁）
  select * from table where ? for update         # X 锁（写锁）
  
  insert into table values(...)
  update table set ? where ?
  delete from table where ?
  1
  2
  3
  4
  5
  6

---

锁

• 全局锁
  • 用于全库备份。

flush tables with read lock  # 整个数据库处于只读状态
unlock tables 
1
2

• 表级锁
  • 表锁

    lock tables 'table' [read/write]
    unlock tables
    1
    2

  • 元数据锁

    crud
    alter
    1
    2

  • 意向锁
    • 目的：告诉其他事务，此时该表正在被一个事务访问。
    • 作用：阻塞表级读写锁（全面扫描加锁），由于 innodb 支持的是行级别的锁，意向锁并不会阻塞除了全表扫描以外的任何请求。
    • 意向锁存储在表结构中。
    • 意向锁之间是互相兼容的，并且由数据库自动添加。
    • 当事务试图读或写某一条记录时，会先在表上加上意向锁，然后才在要操作的记录上加上读锁或写锁。这样判断表中是否有记录加锁就很简单了，只要看下表上是否有意向锁就可以了。
    • 分类
      • 意向共享锁（IS）：对一张表中某几行加的共享锁。
      • 意向排他锁（IX）：对一张表中某几行加的排他锁。
  • auto-inc 锁
    • 特殊表锁，实现自增约束，语句结束后释放锁（而非在事务结束时释放）。
• 行级锁
  • 记录锁（record lock）
    • 共享锁（S）
      • 事务读操作加的锁，对某一行加锁。
      • 在 serializable 隔离级别下，默认给读操作加共享锁。
      • 在 RR 隔离级别下，需手动加共享锁，可解决幻读问题。
      • 在 RC 隔离级别下，没必要加共享锁，采用的是 MVCC。
      • 在 read uncommitted 隔离级别下，既没有加锁也没有使用 MVCC。
    • 排他锁（X）
      • 事务删除或更新加的锁，对某一行加锁。
      • 在 4 种隔离级别下，都添加了排他锁，事务提交或事务回滚后释放锁。
  • 间隙锁（gap lock）
    • RR 隔离级别下，where 条件语句未命中时会自动添加间隙锁。
    • 防止其他事务在记录间隙插入新的记录，从而避免幻读现象。
    • 间隙锁会锁定一个范围，加锁区间为 (row1, row2)。
  • 临键锁（next-key lock）
    • 记录锁 + 间隙锁。
    • 加锁区间为 (row1, row2]。
• 查询
  • MVCC：undo log 实现历史版本记录。
  • S 锁：lock in share mode。
  • X 锁：for update。
  • 不做任何处理：read uncommitted 使用的策略。
• 删除、更新
  • 自动添加 X 锁。
• 插入
  • 使用插入意向锁（特殊的 gap 锁）和 X 锁。
    • 在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。
  • auto-inc lock：特殊表锁实现。

---

锁兼容

• 横向：表示已经持有的锁。
• 纵向：表示正在请求的锁。
• 一个事务已经获取了插入意向锁，对其他事务是没有任何影响的。
• 一个事务想要获取插入意向锁，如果有其他事务已经加了 gap lock 或 next-key lock 则会阻塞，这个是重点，死锁之源。

---

锁的对象

• 行级锁是针对表的索引加锁，索引包括聚簇索引和辅助索引。
• 表级锁是针对页或表进行加锁；
• 重点考虑 innodb 在 read committed 和 repeatable read 隔离级别下锁的情况。
• 聚簇索引，查询命中：

  UPDATE students SET score = 100 WHERE id = 15;
  1

• 聚簇索引，查询未命中：

   UPDATE students SET score = 100 WHERE id = 16;
  1

• 辅助唯一索引，查询命中：

  UPDATE students SET score = 100 WHERE no = 'S0003';
  1

• 辅助唯一索引，查询未命中：

   UPDATE students SET score = 100 WHERE no = 'S0008';
  1

• 辅助非唯一索引，查询命中：

  UPDATE students SET score = 100 WHERE name = 'Tom';
  1

• 辅助非唯一索引，查询未命中：

   UPDATE students SET score = 100 WHERE name = 'John';
  1

• 无索引：

  UPDATE students SET score = 100 WHERE score = 22;
  1

• 聚簇索引，范围查询：

  UPDATE students SET score = 100 WHERE id <= 20;
  1

• 辅助索引，范围查询：

  UPDATE students SET score = 100 WHERE age <= 23;
  1

• 修改索引值：

  UPDATE students SET name = 'John' WHERE id = 15;
  1

---

死锁

• 死锁原因：并发事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。
• 相反加锁顺序死锁
  • 不同表加锁顺序相反。
  • 相同表不同行加锁顺序相反。
    • 给辅助索引行加锁的时候，同时会给聚簇索引行加锁。
    • 使用外键索引时，给父表加锁，同时隐含给子表加锁。
  • 解决：调整加锁顺序。
• 锁冲突死锁
  • RR 隔离级别下，插入意向锁与 gap 锁冲突死锁。一个事务想要获取插入意向锁，如
    果有其他事务已经加了 gap lock 或 next-key lock 则会阻塞。
  • 解决：降低隔离级别至 RC。
• 如何避免死锁
  • 尽可能以相同顺序来访问索引记录和表。
  • 如果能确定幻读和不可重复读对应用影响不大，考虑将隔离级别降低为 RC。
  • 添加合理的索引，不走索引将会为每一行记录加锁，死锁概率非常大。
  • 尽量在一个事务中锁定所需要的所有资源，减小死锁概率。
  • 避免大事务，将大事务分拆成多个小事务，大事务占用资源多，耗时长，冲突概率变高。
  • 避免同一时间点运行多个对同一表进行读写的概率。
• 查看死锁
  • 系统表

    -- 开启标准监控
    CREATE TABLE innodb_monitor (a INT)
    ENGINE=INNODB;
    -- 关闭标准监控
    DROP TABLE innodb_monitor;
    -- 开启锁监控
    CREATE TABLE innodb_lock_monitor (a INT)
    ENGINE=INNODB;
    -- 关闭锁监控
    DROP TABLE innodb_lock_monitor
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

  • 系统参数

    -- 开启标准监控
    set GLOBAL innodb_status_output=ON;
    -- 关闭标准监控
    set GLOBAL innodb_status_output=OFF;
    -- 开启锁监控
    set GLOBAL innodb_status_output_locks=ON;
    -- 关闭锁监控
    set GLOBAL innodb_status_output_locks=OFF;
    -- 将死锁信息记录在错误日志中
    set GLOBAL innodb_print_all_deadlocks=ON;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

  • 命令

    -- 查看事务
    select * from information_schema.INNODB_TRX;
    -- 查看锁
    select * from information_schema.INNODB_LOCKS;
    -- 查看锁等待
    select * from information_schema.INNODB_LOCK_WAITS;
    1
    2
    3
    4
    5
    6

---