Mysql事务隔离级别

最近看事务的时候发现了一些不了解的问题，比如：mysql为什么使用可重复读作为默认隔离级别、为什么一般工作中使用读已提交作为事务隔离级别。顺便整理了下，基础以及相对提高的内容。

1、事务的特性

事务有四个特性：ACID

• 原子性（Atomicity）：事务是一个原子操作，由一系列动作组成。事务的原子性确保动作要么全部完成，要么完全不起作用。mysql利用undo log（回滚日志）记录需要回滚的日志信息，当事务回滚时能够撤销所有已经成功执行的sql语句。
• 一致性（Consistency）：一旦事务完成（不管成功还是失败），系统必须确保它所建模的业务处于一致的状态，而不会是部分完成部分失败。在现实中的数据不应该被破坏。利用Innodb的redo log，不直接写磁盘是因为可能是随机读写，而redo log是末尾追加。
• 隔离性（Isolation）：可能有许多事务会同时处理相同的数据，因此每个事务都应该与其他事务隔离开来，防止数据损坏。mysql利用锁和MVCC机制来保证。MVCC(Multi Version Concurrency Control)即多版本并发控制，一个行记录数据有多个版本对快照数据，这些快照数据在undo log中。
• 持久性（Durability）：一旦事务完成，无论发生什么系统错误，它的结果都不应该受到影响，这样就能从任何系统崩溃中恢复过来。通常情况下，事务的结果被写到持久化存储器中。mysql利用binlog来做持久化操作。

脱离了事务，我们就无法保证上面的特效，就可能导致一些不期望发生的状况。如下面场景：A给B转账100，A账户扣款100，B账户增加100。如果没有事务，那么在A扣款失败后，B账户增加失败，那么就凭白少了100元。

2、事务隔离级别

了解事务隔离级别，先看看并发事务下会导致的一些问题：

• 脏读（Dirty reads）：A事务读取B事务改写但未提交的数据。如果改写在稍后被回滚了，那么A事务获取的数据就是无效的。
• 不可重复读（Nonrepeatable read）：一个事务执行相同的SQL查询一条记录多次，但是每次的某个字段值不同。这通常是另一个并发事务在两次查询期间进行了更新，和update操作相关。
• 幻读（Phantom read）：与不可重复读类似。一般的例子是：A读取了几行数据，B事务（T2）插入了一些数据。在随后的查询中，A事务就会发现突然多了一些原本不存在的数据，和insert/delete操作相关。

Tips：对于幻读，更好和不可重复读进行的例子可能如下。A对表的字段col全量更新成新值，B插入一条包含旧值的记录并提交事务，A在后面查询会发现一条含有旧值的记录，感觉就像没做更新一样。

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommited）	✔️	✔️	✔️
读已提交（read-committed）	❌	✔️	✔️
可重复读（repeatable-read）	❌	❌	✔️(?)
串行化（serializable）	❌	❌	❌

对于四种隔离级别，我们做如下测试：

CREATE TABLE `account` (
  `id` int(11) unsigned NOT NULL,
  `money` int(11) DEFAULT '0'
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO account(id, money) values(1, 100);

读未提交

可以读取到一个事务还没提交的数据

事务A	事务B
start transaction;	start transaction;
select * from account where id = 1; money = 100
	update account set money = money - 10;
select * from account where id = 1; money = 90（脏读了）

读已提交

可以读取到一个事务已经提交的数据

事务A	事务B
start transaction;	start transaction;
select * from account where id = 1; money = 100
	update account set money = money - 10;
select * from account where id = 1; money = 100（防止了脏读）
	commit;
select * from account where id = 1; money = 90（不可重复读）

可重复读与MVCC

事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一 致的

事务A	事务B
start transaction;	start transaction;
select * from account where id = 1; money = 100
	update account set money = money - 10; commit;
select * from account where id = 1; money = 100（防止了不可重复读）
update account set money = money - 10;
select * from account where id = 1; money = 80（注意这里是80而不是90，MVCC机制）
select count(*) from account; 结果是1	start transaction;
	INSERT INTO account(id, money) values(2, 100); commit;
select count(*) from account; 结果是1（没幻读幻读了，MVCC机制）
update account set money = money - 10; select count(*) from account; 结果是2（幻读了）

MVCC：多版本并发控制。简单来讲就是，InnoDB在每行记录添加版本号（查询无法看到），每当修改数据时，当前行的版本号加一，并且将当前事务版本置为此版本。读操作的时候，只会读取小于等于当前事务的版本号的数据。这样A事务版本为5，B事务插入数据版本为6，A也是读取不出来的，但是当A去更新了的时候，就可以获取到了。

Tips: 网上有很多说RR是否解决幻读的，如果使用Select for update当前读的话，由于next-key lock(RR或更高级别) 其实是可以解决。但是如果是select当前读的话，其实还是存在的。

串行化

MySQL高性能中提到：串行化会在读取的每一行数据上都加锁（这个意思是部分锁，但不会锁表）。如果是select * from account;这种情况，会锁表。而在另一个事务准备对加锁数据操作时，会提示Lock wait timeout exceeded; try restarting transaction。

3、mysql默认隔离级别由来

通过百度我们就能知道mysql默认隔离级别是repeatable，但是oracle、sql server的默认隔离级别是read commited。说这个问题之前先看看mysql的binlog。binlog是用来记录增删改等对数据更新的记录（查询不会记录），这样后期可以便于数据的恢复（比如误删数据）。
Binlog日志的三种模式：

• statement（SBR）：记录修改的sql都会记录到master的binlog中
• row（RBR）：记录成每一行数据具体被修改的内容，相比statement由于存了明细，日志会比较大
• mixed（MBR）：前两种模式的结合，MySQL根据执行的SQL来决定具体用哪个

Mysql在5.0这个版本以前，binlog只支持STATEMENT，而这种格式在读已提交(Read Commited)下主从复制是有bug的，因此Mysql将可重复读(Repeatable Read)一直作为默认的隔离级别。

STATEMENT测试

我们将事务隔离级别设置为读已提交(Read Commited)，并将binlog设置为STATEMENT模式，然后在master上面做如下操作：

INSERT INTO account(id, money) values(1, 100);
INSERT INTO account(id, money) values(2, 100);

事务A	事务B
start transaction;	start transaction;
delete from account where id <= 3;
	INSERT INTO account(id, money) values(3, 100);
	commit;
commit;

在主(master)上执行下列语句 select * from account；会有一条记录，但是在从(slave)上执行该语句，发现结果为空。
由于mysql的binlog记录是基于事务的，谁先提交了，就先记录提交事务的操作，那么STATEMENT模式下binlog可以简单理解为如下：

Begin
INSERT INTO account(id, money) values(3, 100);
commit
Begin
delete from account where id <= 3;
commit

由于master的事务隔离级别是RC，那么执行完之后还是能读取到的，但是由于主从复制，slave的数据就不正确了。
而如果是RR级别的话，由于该隔离级别下间隙锁，当事务A执行delete语句时，会锁住间隙。而这个时候事务B执行插入语句就会阻塞。那么也就不会出现insert先提交，delete后提交的情况了。

ROW测试

在my.cnf加入下面的配置，修改模式为ROW

log-bin=//var/lib/mysql/mysql-bin.log #开启binlog
expire-logs-days = 5 # binlog清理时间
binlog-format=ROW #模式
server-id = 1 # 指定为master否则启动失败

执行show variables like ‘log_bin’;查看binlog的开启状况。重新执行上面例子中的操作，然后通过如下命令来查看binlog日志

mysqlbinlog -d tz_test --base64-output=decode-rows -v mysql-bin.000002

-d 指定数据库、–base64-output=decode-rows对数据进行base64解码、-v换行查看
日志内容如下（删除了部分内容）

BEGIN
### INSERT INTO `tz_test`.`account`
### SET
###   @1=3
###   @2=100
COMMIT/*!*/;
BEGIN
### DELETE FROM `tz_test`.`account`
### WHERE
###   @1=1
###   @2=100
### DELETE FROM `tz_test`.`account`
### WHERE
###   @1=2
###   @2=100
COMMIT/*!*/;

可以看到ROW模式下记录了每次删除的行的ID，所以slave复制的时候也不会出现错误。mysql5.1版本开始才引入ROW模式。因此由于历史原因，将默认的隔离级别设为RR来保证主从复制的正确。

4、事务隔离级别的选择（RC）

可以看到oracle、sql server等都使用了RC级别，但是mysql由于历史原因使得默认隔离级别为RR。但是后面版本的更新，也表明其其实想换成RR。而且我们工作中也通常会将隔离级别设置为RR。再讲讲四个事务隔离级别：

• 读未提交(Read UnCommitted)：事务A读到事务B未提交读数据，这逻辑上就不通，不考虑
• 读已提交(Read Commited)：可以读到另一个事务已提交的数据，说的过去
• 可重复读(Repeatable Read)：相比于RC，RR的间隙锁，提高锁表，死锁等情况
• 串行化(Serializable)：读操作都会加锁，太影响性能，不考虑

首先，项目中不用和两个隔离级别。一个事务读到另一个事务未提交读数据，这个不用多说吧，从逻辑上都说不过去。，快照读失效，一般是使用mysql自带分布式事务功能时才使用该隔离级别。这是由于RR的间隙锁，会有锁表，死锁等情况的产生，出于性能等因素考虑。

间隙锁（Next-Key锁）

简单理解就是锁定了一个范围，具体范围取决于你的SQL。
MySQL的间隙锁是左闭右开的，比如SELECT * FROM table WHERE id > 100 AND id < 200会在id > 100的索引值上加锁，但不会在 id < 200 的索引值上加锁，如果存在100则会如下[100,200)
比如有如下数据：

INSERT INTO account(id, money) values(1, 100);
INSERT INTO account(id, money) values(3, 100);
INSERT INTO account(id, money) values(10, 100);
INSERT INTO account(id, money) values(20, 100);
INSERT INTO account(id, money) values(50, 100);
INSERT INTO account(id, money) values(100, 100);

案例一：死锁

select * from account where id < 3 or id > 100 for update;

在RR隔离级别下，存在间隙锁，锁住(1,10)（50, +），防止其他事务对其数据进行操作。
如果两个事务并发执行这个语句，事务A先锁住了（1，10）事务B锁住了（50，+），那么两个事务就相互等待，产生死锁。
而使用RC隔离级别下可以大大降低死锁的产生，因为行锁的粒度更小了。

案例二：锁表

update account set money = money + 10 where id = 40;

由于id的实际数据不存在，RR会进行锁表，而RC只是锁行数据，提升效率。

参考

MYSQL MVCC：https://www.cnblogs.com/chinesern/p/7592537.html
mysql的默认隔离级别：https://www.cnblogs.com/shoshana-kong/p/10516404.html
binlog：https://blog.csdn.net/selfsojourner/article/details/91379739
innodb下的记录锁，间隙锁，next-key锁：https://www.jianshu.com/p/bf862c37c4c9
MySQL 是如何解决幻读的：https://www.cnblogs.com/wdy1184/p/10655180.html