数据库事务
#数据库 2023-04-09

事务的定义

事务是逻辑上的一组原子操作，即要么都执行，要么都不执行。在数据库中，START TRANSACTION; 和 COMMIT; 及其之间的所有语句被视为一个事务。

1
2
3
4
5
6
7
8
9
# 开启一个事务
START TRANSACTION;
# 多条 SQL 语句
SQL1,SQL2...
# 提交事务
COMMIT;

# 回滚
ROLLBACK;
flowchart LR
  start((开启事务)) --> sql(sql1 --> sql2 --> ... --> sqln) --sql全部执行成功--> commit((提交事务))
  sql --有一个sql执行失败--> rollback((回滚事务))

ACID

现代关系型数据库中的事务都有 ACID 的特性

  • 原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
  • 一致性（Consistency）：执行事务前后，数据保持一致。例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
  • 隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
  • 持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

但是，这四个特性不是平级关系。A、I、D 是手段，C 是目的。我们需要保证事务的原子性、隔离性、持久性之后，才能有事务的一致性。

flowchart TB
  Atomicity --> Consistency
  Isolation --> Consistency
  Durability --> Consistency

事务并发问题

在多个事务并发操作数据库时，如果没有有效的机制进行避免就会导致丢失修改（Lost to modify），脏读（Dirty Read），不可重复读（Unrepeatable Read），幻读（Phantom Read）。

丢失修改

  1. 事务 A 对数据进行了修改，然后事务 B 在事务 A 提交之前也对数据进行了修改；
  2. 事务 B 提交后，事务 A 的修改被覆盖。
事务 A 事务 B
UPDATE user SET age=21 WHERE id=1;
UPDATE user SET age=20 WHERE id=1;
COMMIT;
COMMIT; 
flowchart LR
  A((A)) --> age1A[age=10] --SET age=21--> age2[age=21] --COMMIT--> age3[age=10]
  B((B)) --> age1B[age=10]  --SET age=20--> age4[age=20] --COMMIT---> age5[age=20]

脏读

  1. 事务 A 对数据进行了修改，事务 B 读取了事务 A 修改后的数据；
  2. 由于某些原因，事务 A 并没有完成提交，回滚了操作，则事务 B 读取的数据就是脏数据。
事务 A 事务 B
UPDATE user SET age=21 WHERE id=1;
SELECT * FROM user WHERE id=1;
ROLLBACK; 
flowchart LR
  A((A)) --> age1[age=10] --SET age=21--> age2[age=21] --ROLLBACK--> age3[age=10]
  B((B)) ---> age4[age=21]

不可重复读

  1. 在事务 A 未结束时，事务 B 先读取了一次数据；
  2. 事务 A 修改了数据，事务 B 再次读取同一数据，两次读取的数据不一样。
事务 A 事务 B
SELECT * FROM user WHERE id=1;
UPDATE user SET age=21 WHERE id=1;
SELECT * FROM user WHERE id=1; 
flowchart LR
  A((A)) --> age1[age=10] --SET age=21--> age2[age=21]
  B((B)) --SELECT--> age3[age=10] --SELECT---> age4[age=21]

幻读

  1. 在事务 B 读取数据行数后，事务 A 插入数据；
  2. 事务 B 再次读取数据行数，发现有新的记录。
事务 A 事务 B
SELECT count(*) FROM user;
INSERT INTO user VALUES (1,2,3,4,5);
SELECT count(*) FROM user; 
flowchart LR
  A((A)) ----INSERT---> END((end))
  B((B)) --SELECT count(*)--> num2[num=10] --SELECT count(*)---> num3[num=11] --> END

不可重复读和幻读的区别

  • 不可重复读的重点在于内容修改或者记录减少；
  • 幻读的重点在于记录新增。

幻读本质上是不可重复读的特殊情况，单独区分幻读的原因是解决幻读和不可重复读的方案不一样。

执行 delete 和 update 操作的时候，可以直接对记录加锁。而执行 insert 操作的时候，由于记录锁（Record Lock）只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁（Gap Lock）。也就是说执行 insert 操作的时候需要依赖 Next-Key Lock（Record Lock+Gap Lock）来保证不出现幻读。

事务隔离

SQL 标准定义了四个隔离级别

  • 读取未提交（READ-UNCOMMITTED）：允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  • 读取已提交（READ-COMMITTED）：只能读取已经提交的事务所做的修改。
  • 可重复读（REPEATABLE-READ）：保证在事务中多次读取同一数据的结果是一样的。
  • 可串行化（SERIALIZABLE）：完全服从 ACID 的隔离级别。强制所有事务串行执行，这样事务之间就完全互不干扰。
隔离级别 脏读 不可重复读 幻读
READ-UNCOMMITTED √ √ √
READ-COMMITTED × √ √
REPEATABLE-READ × × √
SERIALIZABLE × × ×

如何实现隔离级别

由锁和 MVCC（Multiversion concurrency control）机制共同实现。

SERIALIZABLE 隔离级别是通过锁来实现的，READ-COMMITTED 和 REPEATABLE-READ 隔离级别是基于 MVCC 实现的。不过， SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读。

MySQL 的默认隔离级别

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重复读）。可以通过 SELECT @@tx_isolation; 命令来查看

参考资料