MySQL 锁与事务

参考资料
CS-Notes 数据库原理
MySQL 加锁处理分析

事务

什么是数据库事务

数据库事务是并发控制的基本单位。所谓事务就是一个操作序列，要么都执行，要么都不执行，是一个不可分割的工作单位。ACID 是事务需要满足四个特性，包含原子性、一致性、隔离性和持久性。

事务并发可能存在的问题

先来看一下在没有隔离的时候，事务间 (这里以 T1 和 T2 为例) 都可能会发生哪些相互影响。
1) 脏读。按照执行时间 T1 和 T2 执行如下操作：

T1 start 
T1 read colA = 50 
T2 start
T2 read colA = 50 
T2 update colA = colA(50) - 5 = 45 
T1 read colA = 45
T2 rollback colA = 50

事务 T1 读到事务 T2 中间过程的数据，该数据最后被回滚，此时事务 T1 读到的 colA = 45 就是脏读。脏读是事务可以读到其它事务未提交的数据导致的。
2) 丢失修改。按照执行时间 T1 和 T2 执行如下操作：

T1 start 
T1 read colA = 50 
T2 start
T2 read colA = 50 
T1 update colA = colA(50) - 1 = 49 
T2 update colA = colA(50) - 5 = 45 
T1 commit colA = 49 
T2 commit colA = 45

可以看到 T2 覆盖了 T1 的修改，T1 事务的修改丢失了。
3）不可重复读。按照执行时间 T1 和 T2 执行如下操作：

T1 start 
T1 read colA = 50 
T2 start
T2 read colA = 50 
T2 update colA = colA(50) - 5 = 45 
T2 commit colA = 45
T1 read colA = 45

事务 T1 前后两次读取同一份数据，得到不同的结果，事务 T1 可能会发生错乱。对比脏读，不可重复读读到的是另外的事务已提交的结果。
4）幻读。按照执行时间 T1 和 T2 执行如下操作：

T1 start 
T1 count(*) = 1000 
T2 start
T2 insert 100 new records
T2 commit
T1 count(*) = 1100

事务 T1 前后两次对同一份数据进行计数，得到不同的结果。
幻读和不可重复读的差别主要是：不可重复读是事务 update 和 delete 操作相互影响；幻读是事务 insert 操作的相互影响。
为什么把 delete 操作的相互影响定义为可能导致不可重复读，而 insert 操作相互影响可能导致幻读呢？这是因为解决不可重复读可以通过使用行锁就能解决，而幻读则需要表锁或其它技术去解决。
行锁和表锁都是悲观锁。 使用锁可以解决脏读、丢失修改、不可重复读和幻读问题。但由于锁会导致并发性能下降，并且获取锁和释放锁本身耗费资源也比较多，数据库往往结合锁和其它技术来隔离事务，以期在解决事务问题的同时，尽可能提高性能。

事务隔离级别

为了避免上述事务间的并发问题，mysql 提供了四种隔离级别。
1) 读未提交，该隔离级别实际上就是事务不隔离，可能会出现脏读、不可重复读和幻读现象。
2) 读已提交，该隔离级别下，当前事务不能读其它事务未提交的数据，因此不会出现脏读现象，但不可重复和幻读现象仍会发生。大多数数据例如 Sql Server 和 Oracle 的默认隔离级别就是该级别。
3) 可重复读，该隔离级别下，当前事务提交前，其它事务不能 update 或 delete 当前事务已读取的数据（当前事务持有这些数据的共享锁），因此不会出现脏读和不可重复读现象。在隔离级别下，默认是没有解决幻读问题的。MySql 的默认隔离级别就是该级别。另外，在该隔离级别下，MySQL 使用间隙锁解决幻读问题，并借助 MVCC 提高并发性能。
4) 序列化，对同一数据的事务操作被强制排序，只有前一个事务提交完毕后才开始下一个事务，即牺牲了事务的并发性。一般不会使用该隔离级别。

数据库的锁

MySQL 存储引擎与锁

1. 不同的存储引擎各自实现其锁机制。MyISAM 不支持事务，只提供表级锁，有读锁定、写锁定两种类型。
2. InnoDB 存储引擎采用行级锁和表级锁共存的方式，数据库的锁帮助存储引擎实现事务的隔离性，不同的锁实现的隔离级别不同。行锁和表锁即可以是共享锁（S），也可以是排他锁（X）。
   另外除了对行或表的锁定，InnoDB 还有表级锁定的意向锁，即意向共享锁和意向排他锁作为行锁的辅助工具。事务开启后，当前事务进行数据库查询、更新、新增、删除等操作时，数据库引擎会要求携带对应锁信息。事务要获取某些行的共享锁，则必须先获得表的意向共享锁( SELECT column FROM table ... LOCK IN SHARE MODE;)；事务要获取某些行的排他锁，则必须先获得表的意向排他锁(SELECT column FROM table ... FOR UPDATE;)。意向锁可以提高锁冲突时的检查效率。
   行锁具体分为记录锁、间隙锁（gap lock）、临键锁(next-key lock)，以上三种行锁都是排他锁，都需要加锁在索引列上的，如果加锁操作没有索引列对象，那么该锁会退化为表锁。
   记录锁锁住索引对应的记录，记录锁加锁操作必须作用在唯一索引列或主键列上，否则将会变成临键锁。
   间隙锁锁定的是索引范围内的记录，可以是多个不连续的索引范围，事务隔离级别是 Repeatable Read 时自动开启，其它隔离级别下会失效。
   临键锁是记录锁和间隙锁的组合，临键锁在索引具有唯一性时（例如主键索引），将会降级为记录锁，以增加并发性。
   MySQL 中结合锁和 MVCC 解决幻读，MVCC 是一种乐观锁，乐观锁大多基于数据版本记录实现，MVCC 亦即多版本并发控制。InnoDB 的事务操作尽可能在保证事务满足隔离要求的前提上，锁定尽可能少的数据，提高并发性能。MVCC 是行锁的变种，是一种乐观锁，是 InnoDB 用来减少锁的使用，减小锁消耗的资源开支的一种技术手段。
3. 间隙锁
   MySQL的锁机制 - 记录锁、间隙锁、临键锁
   间隙锁时封锁索引之间的间隔，包括第一条索引之前的范围和最后一条索引之后的范围，防止其它事务在间隙中插入数据。为了便于说明，给出如下示例数据。
   1) 表 student

对于 name 列来说，其间隙为 (-infinity, ‘Mary’), (‘Mary’, ‘Lily’), (‘Lily’, ‘Tom’), (‘Tom’, +infinity)
对于 student_id 列来说，其间隙为 (-infinity, 100001), (100001, 100004), (100004, 100005), (100005, 100009), (100009, +infinity)
2) 表上的索引

show index from student;

即在表 student 上有三个索引列，分别是主键索引，列 student_id 上的唯一索引和在 name 列上的非唯一辅助索引。
在 RR 隔离级别下，在下面的情况下会产生间隙锁。
1) 使用非唯一索引作为等值或范围筛选条件对数据库表进行写操作，例如

 update student set age=13 where name='Tom';  
 -- 记录锁锁住 name='Tom' 的记录；间隙锁锁住 ('Lily', 'Tom'), ('Tom', +infinity)
 update student set age=13 where name like 'T%';
 -- 记录锁锁住 name like 'T%' 的所有记录；间隙锁锁住 ('Lily', 'Tom'), ('Tom', +infinity)

2) 使用多个唯一索引作为筛选条件对数据库表进行写操作，例如

 delete from student where id=2 and student_id=100004;

3) 使用唯一索引作为范围筛选条件，或使用唯一索引作为等值筛选条件，但该值不存在于表中对数据库表进行写操作，例如

 update student set age=13 where student_id between 100001 and 100004;
 -- 间隙锁锁住 student_id (100001, 100004), (100004, 100005) 这两个区间；记录锁锁住 100001 和 100004 这两条记录
 update student set age=13 where student_id=100011;
 -- 间隙锁锁住 student_id (100009, +infinity) 这个区间

4) 只使用非索引列作为筛选条件（几乎是表锁）

InnoDB 存储引擎的隔离级别与锁

接下来讨论一下，InnoDB 各个隔离级别下是怎么使用锁来实现事务隔离的。
首先明确一点，数据库开始事务时，并不知道需要对哪些数据加锁，所以数据库采用两段锁协议，即执行到具体的事务操作时，才对相关数据加锁（加锁阶段），随着事务过程中执行不同的 SQL，不同的 SQL 需要锁的数据不同，锁的数量也会增加；事务结束即 commit 或 rollback 时，进入解锁阶段。

1. 读未提交
   事务操作不使用锁
2. 读已提交
   事务执行过程中，普通读操作不加锁，借助 MVCC 技术来实现事务隔离；事务的修改和删除需要对相应的数据加记录锁（X 锁）
3. 可重复读
   事务执行过程中，普通读操作不加锁，借助 MVCC 技术来实现事务隔离；
   对于事务的修改和删除操作，参见间隙锁部分的内容
   注意：
   1) 在 RC 级别下，每次读都会生成一个新的快照，所以每次快照读到的都是当前其它事务提交了的最新版本；在 RR 级别下，快照读借助 MVCC 和 undo log 来实现，会在事务中第一次 SELECT 语句执行时生成快照，只有在本事务中对数据进行更改时才会更新快照（其它事务的提交不会引发快照的更新）;
   2) 区别于普通读，SELECT column FROM table ... LOCK IN SHARE MODE; 会获得数据的读锁（S 锁）； SELECT column FROM table ... FOR UPDATE; 会获得数据的写锁（X 锁）

InnoDB RR 隔离级别如何解决幻读

InnoDB 中多版本并发控制 (MVCC) 的实现方式

MySQL 的 InnoDB 中，每一行数据除了用户数据外，数据库会自动维护一些额外的字段，和 MVCC 相关的有 DATA_TRX_ID 和 DATA_ROLL_PTR，前者是最近更新该行的 Transaction ID，每开启一个事务，事务 ID 每次增加 1，每个事务拿到的 ID 都不一样；后者是 Undo Log 数据指针，支持了事务回滚操作，同时也支持了多个事务之间并发读的版本冲突问题。
MySQL 中 MVCC 的实现并不是完全按照 MVCC 的定义来实现的。MVCC 定义中要求数据库中存在记录的多个版本，除了记录的创建版本（DATA_TRX_ID），还需要保存记录的删除版本。MySQL 中使用 undo log 实现了了数据库表中记录的多版本。

下面是一个例子，来说明事务相关列的数据更迭情况。
1) 表 student

2) 表上的索引

show index from student;

即在表 student 上有两个索引列，分别是主键索引和在 name 列上的非唯一辅助索引。
3）在表上的事务操作的数据变化情况

-- T1
T1 start 
update student set age = 14 where id = 1;
update student set name = 'Lucy' where id = 1;
T1 commit

事务开始后，undo 日志中先拷贝一条

的记录，我们假定该日志的位置指针为 p1。
接下来将 student 表中 id = 1 的记录的 age 字段更新为 14，并更新 DATA_TRX_ID 和 DATA_ROLL_PTR 字段，此时 student 表中的数据如下

然后，undo 日志中拷贝该记录

最终 commit 后，表 student 中数据为

多个事务并行操作某行数据（只能有一个事务是修改操作，其它都是读操作），该新版本记录的 DATA_TRX_ID 是生成该记录的事务 ID 和和日志位置（Undo log，指向生成该记录时基于的数据版本)。某条 Undo log 的事务 ID 小于当前所有活动事务的版本号时，说明该条 Undo log 已经没用了，会被删除避免 Undo log 无限增长。
MVCC 最大的好处是读不加锁，读写不冲突，极大增加了系统的并发性能。MVCC 的读操作分为快照读和当前读。

RR 隔离级别快照读

快照读又称为一致性非锁定读，在 RR 隔离级别下，它读取的是事务开始时的数据快照。假设在上面 T1 事务开始后，提交前，我们开启了另外的事务 T2 和 T3，注意 T2 是读操作，T3 是写操作。

-- T3
T3 start
insert into student(id, name, age) values(4, 'Lily', 12);
T3 commit

T2 和 T3 的执行时序。

-- T2 
-- T1 start
T2 start
select name, age from student where id = 1; --(a)
select count(1) from student where id > 2;  --(b)
-- T1 commit 
-- T3 start
-- T3 commit
select name, age from student where id = 1; --(c)
select count(1) from student where id > 2;  --(d)
T2 commit

(a) 和（c）处读到的数据都是 name=’Mary’, age=13, (b) 和 (d) 读到的数据数都是 1，不会读到 T2、T3事务提交的改动。
其原因是 T2 开始时，生成一个 ReadView，这个 ReadView 保存了当前系统中还有哪些活跃的读写事务（T1，T2）。
1) (a）处进行查询时，id=1 的记录 DATA_TRX_ID 字段值如果是 T0，T0 < min(T1, T2)，则直接取该记录 name 和 age 字段返回；id=1 的记录 DATA_TRX_ID 字段值如果是 T1，T1 存在于活跃事务列表中，但是 T1 不是当前事务（T2），因此该记录不可见，但是可以从该记录 Undo 日志开始回溯查找，直到找到满足要求的版本即 T0 版本，返回 name=’Mary’, age=13。
2) (b) 处查询比较简单，只会计算 id>2 的记录，只有一条。
3) (c) 处查询和 (a) 处一样，只是此时 T1 已经提交，但由于 T2 ReadView 的活跃事务集中仍然包含 T1，undo 日志的 purge 进程不会删除 T1 的 undo 日志，因此仍能通过 undo 日志找到相应的版本数据。
4) (d) 处查询会自动过滤掉事务版本号大于当前事务 ReadView 的活跃事务集最大事务的版本，由于 T3 > max(T1, T2)，id=4 的记录（该记录已由 T3 插入完成）会被忽略，因此计数仍然只有一条。
可以发现，在 MVCC 的帮助下和 undo 日志，普通读操作不会对数据加锁，也不等待其它事务对数据进行操作时释放锁。
RR 级别下的快照读和 RC 级别的快照读会差异，RR 级别下，同一个事务只有在第一次 select 时会生成快照，后续 select 都使用这个快照，这也是上面 T1 提交后，T2仍不能看到 T1 提交的原因。RC 级别下，每次 select 都会更新快照，因此是可以看到其它事务已提交的数据的。 MySQL事务隔离级别和MVCC

RR 隔离级别当前读

数据库的写操作都是先读原数据，然后进行写操作。当前读针对 insert, udpate, delete, select … for update 写操作，另外，select … in share mode 这个读操作也显式要求当前事务的读操作使用当前读，我们接下来的讨论暂不考虑这种情况。当前读涉及到行锁、临键锁和 MVCC。
执行上述写事务操作时，读操作会读取相应数据记录的最新版本（即读到其它事务已提交的数据），并对读取的记录加锁（X 锁），阻塞其它事务同时修改相同的记录。
假设有如下三个事务。

-- T4
T4 start 
update student set age=12 where name='Tom';
T4 commit

-- T5
T5 start
insert into student(id, name, age) values(5, 'Mary', 13);
T5 commit

-- T6
T6 start
delete from student where id=3;
T6 commit

这三个事务的执行时序如下。

T4 start
-- T5 start
-- T6 start
-- delete from student where id=3; (a)
-- T6 commit
update student set age=12 where name='Tom';   -- (b)
-- insert into student(id, name, age) values(5, 'Mary', 13); -- blocked until T4 commit
T4 commit
-- insert into student(id, name, age) values(5, 'Mary', 13);
T5 commit;

1) (a) 处执行时(T6)，对 id=3 的记录加 X 锁，删除该记录，T6 结束时释放该锁；
2) (b) 处执行时，执行引擎会首先读到所有满足条件的记录的最新版本，此时只有 id=2 的记录满足条件，对该记录加 X 锁。另外，由于 name 是非唯一索引，还会对 (‘Lucy’, ‘Tom’], (‘Tom’, +∞) 这两个区间加间隙锁。这也是导致 T5 的插入事务必须等到 T4 完成后才能执行的原因。

注意：在高并发场景下，间隙锁有可能导致死锁现象。mysql 死锁问题

• Previous
  集群节点批量分区及 docker 镜像存储空间变更
• Next
  Redis 基础知识汇总