MySQL-事务

吹梦到西洲

发布于：Aug 1, 2020

MySQL-事务与锁

事务四大特性

原子性（atomicity): 一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。

一致性（consistency): 数据库总是从一个一致性的状态转换到另外一个一致性的状态。
隔离性（isolation): 一个事务所做的修改在最终提交以前，对其他事务是不可见的。
持久性（durability): 一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

就像锁粒度的升级会增加系统开销一样，这种事务处理过程中额外的安全性，也会需要数据库系统做更多的额外工作。一个实现了 $ACID$ 的数据库，相比没有实现 $ACID$ 的数据库，通常会需要更强的 $CPU$ 处理能力、更大的内存和更多的磁盘空间。

事务隔离级别

在 $SQL$ 标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

READ UNCOMMITTED（未提交读）

在 READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读（Dirty Read）。
READ COMMITTED（提交读）

大多数数据库系统的默认隔离级别都是 READ COMMITTED（但 $MySQL$ 是REPEATABLE READ ）。READ COMITTED 满足前面提到的隔离性的简单定义：一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读（nonrepeatable read），因为两次执行同样的查询，可能会得到不一样的结果。
REPEATABLE READ（可重复读）MySQL 的认事务隔离级别

REPEATABLE READ 解决了脏读的问题，保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读（Phantom Read）的问题。

所谓幻读，指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）。InnoDB 和 XtraDB 存储引擎通过多版本并发控制（MVCC, Multiversion Concurrency Control）解决了幻读的问题。
SERIALIZABLE（可串行化）

最高的隔离级别。通过强制事务串行执行，避免了幻读问题。简单来说，SERIALIZABLE 会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。

隔离级别	脏读可能性	不可重复读可能性	幻读可能性	加锁读
`READ UNCOMMITTED`	$YES$	$YES$	$YES$	$NO$
`READ COMMITTED`	$NO$	$YES$	$YES$	$NO$
`REPEATABLE READ`	$NO$	$NO$	$YES$	$NO$
`SERIALIZABLE`	$NO$	$NO$	$NO$	$YES$

修改事务隔离级别

# 查看当前事务隔离级别
select @@tx_isolation;

# 设置事务隔离级别
set session transaction isolation level repeatable read;

# 默认 ON 只在当前 session 有效 先关闭进行并发模拟
show variables like 'autocommit';

# 关闭默认提交
set autocommit = 0;

# 开启一个事务
start transaction;
# 需要在一个事务中进行的操作
select * from t_name;
# 手动提交事务
commit;

并发访问问题及解决

更新丢失(Lost update)：$MySQL$ 所有事务隔离级别在数据库层面上均可避免。
脏读(Dirty Read)：$READ-COMMITED$ 事务隔离级别以上可避免。

InnoDB可重复读隔离级别下如何避免幻读

多版本并发控制 MVCC

可以认为 MVCC 是行级锁的一个变种，但是在多数情况下避免了加锁，因此开销较低。虽然 MySQL、Oracle、PostgreSQL 中 MVCC 的实现方式不尽相同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。

MVCC 的实现，是通过保存数据在某个时间点的快照来实现的(Snapshot 快照图)。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

另外，不同存储引擎的 MVCC 实现是不同的，典型的有乐观并发控制与悲观并发控制两种。下面简单地说一下 InnoDB 中的 MVCC 是如何工作的。

InnoDB 的 MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。存储的并不是实际的时间值，而是系统版本号（system version number）。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和査询到的每行记录的版本号进行比较。

下面看一下在 REPEATABLE READ 隔离级别下，MVCC 具体是如何操作的。

SELECT： InnoDB 会根据以下两个条件检查每行记录：

InnoDB 只査找版本早于当前事务版本的数据行（行的系统版本号 $\le$ 事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

只有符合上述两个条件的记录，才能返回作为査询结果。

INSERT

InnoDB 为新插人的每一行保存当前系统版本号作为行版本。

DELETE

InnoDB 为删除的每一行保存当前系统版本号作为行删除标识。

UPDATE

InnoDB 为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

保存这两个额外系统版本号，使大多数读操作都可以不用加锁。但是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

MVCC 只在 REPEATABLE READ 和 READ COMMITTED 两个隔离级别下工作。其他两个隔离级别都和 MVCC 不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

快照读与当前读

新建一张表到demo数据库中，作测试用。

CREATE TABLE `dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

insert into dept(name) values('后勤部');

然后打开两个 session 对话(两个查询窗口)，手动关闭事务自动提交set autocommit = 0;，进行如下操作：

手动时间戳	Transaction-1	Transaction-2
1	`set autocommit = 0;`	`set autocommit = 0;`
2	`start transaction;`	`start transaction;`
3	`select * from dept;`
4		`insert into dept(name) values ('研发部');`
5		`commit;`
6	`select * from dept;`
7	`commit;`
8	`select * from dept;`

执行结果是在时间戳 $3$ 只查到一条数据，时间戳 $6$ 只查到一条数据，而时间戳 $8$ 则查到两条数据。

此时，InnoDB 的默认隔离级别是 R R，在我们没有加锁，没有加索引，没有其他影响因素，并且仅使用 MVCC 的情况下，似乎是没有发生幻读。

上面 Transaction-1 的操作是 读操作(select)，接下来是一个写操作(update)吧。

手动时间戳	Transaction-1	Transaction-2
1	`set autocommit = 0;`	`set autocommit = 0;`
2	`start transaction;`	`start transaction;`
3	`select * from dept;`
4		`insert into dept(name) values ('研发部');`
5		`commit;`
6	`update dept set name = '行政部'(没写where)`
7	`select * from dept;`
8	`commit;`

最后的结果如下图所示，可以看到的是在执行到时间戳 $3$ 时，查询结果为 $3$ 条，但是到时间戳 $7$ 时，事务尚未提交，但是查询到的结果已经有 $4$ 条了，也就是在 Transaction-1 尚未结束前，Transaction-2 已经插入了，并不是 REPEATABLE READ，出现了幻读。

👨‍🏫：MVCC 解决幻读问题了吗？

👨‍🎓：在一定程度上解决了幻读问题(select)，但是没有彻底解决幻读问题(update)。在 InnoDB 的 RR 级别下，通过 MVCC 虽然让数据变得可重复读，但是读到的数据是历史数据(事务开始前的快照，同一个事务内可重复读。)，而不是数据库最新的数据。读取历史数据的方式叫做快照读，而读取最新版本数据的方式，叫做当前读。

SELECT 快照读

当执行 SELECT 操作时 InnoDB 默认会执行快照读，记录下此次 SELECT 的结果，之后再 SELECT 的则返回此次快照结果，即使其他事务提交了不会影响当前 SELECT 的数据，也就是可重复读。快照的生成时间为当前事务中第一次执行 SELECT 时刻，也就是说假设 A 开启了事务，然后在执行 SELECT 操作前，事务 B 插入了一条数据然后 COMMIT，此时 A 执行 SELECT，结果中将会出现 B 新添加的那条数据。之后即使仍有事务 C、D、E 继续开启事务、提交数据、提交事务，事务 A 的 SELECT 结果不变，因为快照已经生成。

当前读

对于写操作(update、insert、delete)均采用当前读。执行这三个操作时会读取最新的记录，即使是别的事务新提交的数据也可以查询到。比如事务 A 准备要 UPDATE 一条数据，但是事务 B 已经 DELETE 这条数据并且 COMMIT ，此时事务 A 进行 UPDATE 将会失败，在 UPDATE 的时候需要知道最新的数据。正因如此，才导致上面 UPDATE 测试的那种情况。

SELECT 的当前读需要手动加锁：

1
2
3

select * from table where ? lock in share mode;

select * from table where ? for update;

所以 $BB$ 了这么久怎么解决幻读？🙄

最简单的方式，将上面的例子时间戳 $3$ 手动加锁，改为select * from dept for update;，此时 Transaction-2 将会阻塞。

InnoDB RR级别下如何避免幻读

刚刚看到 GitHub 上关于InnoDB 中 RR 隔离级别能否防止幻读？的讨论，很值得一读。

从上面一节可以看到在 RR 级别下没有彻底解决幻读，如果想要彻底解决的话有两种方式：

实现最高事务隔离级别 SERIALIZABLE；
MVCC $+$ Next-Key Locks: next-key locks由 record locks(索引加锁) 和 gap locks(间隙锁，每次锁住的不光是需要使用的数据，还会锁住这些数据附近的数据)。

MVCC $+$ Next-Key Locks

MySQL 官方 InnoDB Locking

先从上文中翻译几个锁的意思。

Record Locks

「Record Locks」: 即行锁，比如select c1 from t where c1 = 10 for update;这条查询将会阻止其他事务对 t.c1 = 10 这条记录的 insert、update、delete。

「Record Locks」总是会锁住带索引的记录，即使一张表上并没有定义任何索引，InnoDB 也会隐式地创建一个聚簇索引(主键)，并且将这个索引用于 record locking.

Gap Locks

「Gap Locks」用于锁定索引记录之间的间隙，或者锁定(before the first index record)第一条索引记录之前或(after the last index record)最后一条索引记录之后。比如select c1 from t where c1 between 10 and 20 for update;，由于10-20之间的数据被锁定，可以防止其他事务将t.c1 = 15 的记录插入表中。

「Gap Locks」可以包含单个索引值，多个索引值，或者为空。

「Gap Locks」是性能与并发之间的一种权衡与妥协，只能应用在某些事务隔离级别之下，并不是所有事务隔离级别。

对于使用「唯一索引」来锁定唯一行的语句不需要使用 Gap Locks 而只会使用 Record Locks (这并不包括查询条件包含多列索引其中部分列的情况，在这种情况下 Gap Locks 仍会出现)。比如，如果id这列包含唯一索引(unique index)，下面的这条语句只会使用index-record来锁定id = 100的行，即使其他事务可以在id = 100之前的间隙插入数据。

1	select * from child where id = 100;

如果 id 没有索引或者是非唯一索引，上面这条语句仍然会锁住id = 100的前置区间。

总结一下：

用于锁住Index Record之间的间隙
如果是通过唯一索引来搜索一行记录的时候，不需要使用Gap Locks，此时Gap Locks降级为Record Locks
Gap S-Lock与Gap X-Lock是兼容的
Gap Locks只能阻止其他事务在该Gap中插入记录，但无法阻止其他事务获取同一个Gap上的Gap Lock
可以通过将事务隔离级别设置为 READ COMMITTED 禁用Gap Locks。

Next-Key Locks

Next-Key Locks = Record Locks + Gap Locks
若在id列上没有索引或者是非唯一索引，并且有id = 10、11、13、20这几条数据，那么可以锁定的区间为 $(-\infty, 10],(10,11],(11,13],(13,20],(20,+\infty)$，左开右闭。若执行select * from t where id = 13 for update;
- 将在id = 13上有一个 X Lock，在$(11,13)$有个Gap Lock
- id = 13的Next-Key 为$20$，将在id = 20上有一个 X Lock，在$(13,20)$有一个Gap Lock
- 因此，在id = 13上有一个X Lock，在$(11,20)$上有一个Gap Lock
- 也可以理解为在id = 13和id = 20上各有一个X Lock，在 $(11,13)$ 和 $(13, 20)$ 上各有一个Gap Lock
在 InnoDB 的默认隔离级别 RR 下，支持 Next-Key Locks。

各种锁测试

[InnoDB – Next-Key Lock](InnoDB – Next-Key Lock)

RC、RR级别下的InnoDB的非阻塞读如何实现

其实主要就是上面提到的 MVCC.
MySQL 在 RC 隔离级别下是如何实现读不阻塞的？
MySQL数据库事务各隔离级别加锁情况–read committed && MVCC

MySQL 锁

锁的分类

按粒度划分：表级锁，行级锁，页级锁。
按读写划分：共享锁，排它锁。
按加锁方式划分：自动锁，显式锁。
按操作划分：$DML$锁，$DDL$锁。
按使用方式划分：乐观锁(通过版本号)，悲观锁。

锁的使用

# MyISAM 的表级锁
# read 读锁、共享锁 write 写锁、排它锁
lock tables t_name read | write;
unlock tables;

# InnoDB 的行级锁
# 加共享锁
select * from t_name where id = ? lock in share mode;
# 加排它锁
select * from t_name where id = ? for update;

共享锁与排它锁的兼容性

	X(排它锁)	S(共享锁)
X(排它锁)	冲突	冲突
S(共享锁)	冲突	兼容

隐式和显式锁定

InnoDB 采用的是两阶段锁定协议（two- phase locking protocol）。在事务执行过程中，随时都可以执行锁定，锁只有在执行 COMMIT 或者 ROLLBACK 的时候才会释放，并且所有的锁是在同一时刻被释放。InnoDB 会根据隔离级别在需要的时候自动加锁。

参考

更新于：Jun 20, 2021

MySQL 事务

MySQL-Explain中的列

EXPLAIN 中的列123456789101112131415MySQL> explain select 1\G*************************** 1. row **...

MySQL-索引与慢查询优化

闲聊几句数据库范式目前关系数据库有六种范式：第一范式$(1NF)$、第二范式$(2NF)$、第三范式$(3NF)$、巴斯-科德范式$(BCNF)$、第四范式$(4NF)$和第五范式$(5NF$，...