MySQL事务并发问题和事务隔离级别

事务的并发问题

• 脏读（Dirty read）：当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
• 丢失修改（Lost to modify）：指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
• 不可重复读（Unrepeatable read）：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两 次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不 可重复读。例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果 只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。
• 幻读（Phantom read）：幻读名如其文，它就像发生了某种幻觉一样，在一个事务中明明没有查到主键为 X 的数据，但主键为 X 的数据就是插入不进去，就像某种幻觉一样。

不可重复读和幻读的区别：

• 不可重复读是读异常，但幻读则是写异常。
• 不可重复读是读异常的意思是，如果你不多select几次，你是发现不了你曾经select过的数据行已经被其他人update过了。避免不可重复读主要靠一致性快照。
• 幻读是写异常的意思是，如果不自己insert一下，你是发现不了其他人已经偷偷insert过相同的数据了。解决幻读主要靠间隙锁。

事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

MySQL默认的事务隔离级别是可重复读(Repeatable Read)。

Oracle，SqlServer中都是选择读已提交(Read Commited)作为默认的隔离级别。

我们在项目中一般用读已提交(Read Commited)这个隔离级别。

为什么MySQL默认是可重复读（RR）级别

这个是有历史原因的，当然要从我们的主从复制开始讲起了！

主从复制，是基于什么复制的？是基于binlog复制的，简单理解为binlog是一个记录数据库更改的文件。

binlog有几种格式？

• statement模式 
  • 基于SQL语句的复制(statement-based replication, SBR)
  • 每条修改操作SQL语句都会记录到binlog
  • 优点是日志量比较少，缺点是会导致主从不一致（如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题）
• row模式
  • 基于行的复制(row-based replication, RBR)
  • 逐行记录每条被实际修改的数据
  • 优点：STATEMENT模式数据不一致的问题得到解决。缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。
• mixed模式
  • 混合模式复制(mixed-based replication, MBR)
  • 优先使用STATEMENT模式，某些特殊情况，使用ROW模式
  • 集合了两种模式的优点，规避了缺点

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

首先我们创建一个表table并插入一些数据：

CREATE TABLE tem_table(
	b1 int,
	b2 int
)

把自动提交关闭执行两个会话：

此时查看会话1的提交结果：

dba> select * from t1;
+------+------+
| b1   | b2   |
+------+------+
|    1 |    4 |
|    2 |    8 |
|    3 |    4 |
|    4 |    8 |
|    5 |    4 |
+------+------+
5 rows in set (0.00 sec)

这个结果不会有任何问题。

假设在RC隔离级别下支持STATEMENT格式的binlog，并且binlog是打开的。binlog的记录顺序是按照事务commit顺序为序的。那么显而易见，binlog中的顺序为： 

会话2：

dba> set tx_isolation='read-committed';
dba> BEGIN;
dba> update t1 set b2=4 where b2=2;
dba> commit;

会话1：

dba> set tx_isolation='read-committed';  
dba> BEGIN;（开启事务）
dba> update t1 set b2=8  where b2=4;
#会话1进行提交
dba> commit;

那么此时在主从复制的从库上看到的结果应为：

dba> select * from t1;
+------+------+
| b1   | b2   |
+------+------+
|    1 |    8 |
|    2 |    8 |
|    3 |    8 |
|    4 |    8 |
|    5 |    8 |
+------+------+
5 rows in set (0.00 sec)

可见，在RC隔离级别下，如果支持STATEMENT格式的binlog，是有可能导致主从数据不一致的！

那么你可能会问，在RC隔离级别下，如果binlog格式为ROW或者MIXED，难道就不会有主从数据不一致的风险吗？答案是肯定的，如果binlog的格式是ROW或者MIXED，在RC隔离级别下，不会导致主从数据不一致。为什么呢？

因为ROW或者MIXED格式的binlog，是基于数据的变动。在进行update或者delete操作，记录到binlog，同时会把数据的原始记录写入到binlog。所以日志文件会比Statement大些，上述演示过程，binlog的记录顺序仍然是按照事务的commit顺序为序的，binlog的顺序仍然为：

会话2：

dba> set tx_isolation='read-committed';
dba> BEGIN;
dba> update t1 set b2=4 where b2=2;
dba> commit;

会话1：

dba> set tx_isolation='read-committed';  
dba> BEGIN;（开启事务）
dba> update t1 set b2=8  where b2=4;
#会话1进行提交
dba> commit;

在从库仍然是按照这个binlog的执行时序，进行更新操作。但不同之处在于。

会话2的update操作：

dba> update t1 set b2=4 where b2=2;

写入到binlog时，会把原始的记录也记录下来。它是这样记录的：

update dba.t1
where 
 b1=1
 b2=2
set
 b1=1
 b2=4

update dba.t1
where 
 b1=3
 b2=2
set
 b1=3
 b2=4

update dba.t1
where
 b1=5
 b2=2
set
 b1=5
 b2=4

从库上会话2的更新操作完成之后，接着执行会话1的更新操作：

dba> update t1 set b2=8  where b2=4;

binlog中的记录为：

update dba.t1
where
 b1=2
 b2=4
set
 b1=2
 b2=8

update dba.t1
where
 b1=4
 b2=4
set
 b1=4
 b2=8

这样从库看到的结果就是：

dba> select * from t1;
+------+------+
| b1   | b2   |
+------+------+
|    1 |    4 |
|    2 |    8 |
|    3 |    4 |
|    4 |    8 |
|    5 |    4 |
+------+------+
5 rows in set (0.00 sec)

这样，主从数据就是一致的。

为什么项目中选读已提交(Read Commited)作为事务隔离级别

我们首先要先明白一点！项目中是不用读未提交(Read UnCommitted)和串行化(Serializable)两个隔离级别，原因有二：

• 采用读未提交(Read UnCommitted)，一个事务读到另一个事务未提交读数据，这个从逻辑上都说不过去！
• 采用串行化(Serializable)，每次读操作都会加锁，快照读失效，一般是使用mysql自带分布式事务功能时才使用该隔离级别！(因为这是XA事务，是强一致性事务，性能不佳！互联网的分布式方案，多采用最终一致性的事务解决方案！)

也就是说，我们该纠结都只有一个问题，究竟隔离级别是用读已提交(RC)还是可重复读(RR)？

假设表结构：

 CREATE TABLE `test` (
`id` int(11) NOT NULL,
`color` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB

数据如下：

+----+-------+
| id | color |
+----+-------+
|  1 |  red  |
|  2 | white |
|  5 |  red  |
|  7 | white |
+----+-------+

在RR隔离级别下，存在间隙锁，导致出现死锁的几率比RC大的多

此时执行语句：

select * from test where id <3 for update;

在RR隔离级别下，存在间隙锁，可以锁住(2,5)这个间隙，防止其他事务插入数据！

而在RC隔离级别下，不存在间隙锁，其他事务是可以插入数据！

注：在RC隔离级别下并不是不会出现死锁，只是出现几率比RR低而已！

在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行

此时执行语句：

update test set color = 'blue' where color = 'red'; 

在RC隔离级别下，其是先走聚簇索引，进行全部扫描。加锁如下：

但在实际中，MySQL做了优化，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录放锁。

实际加锁如下：

然而，在RR隔离级别下，走聚簇索引，进行全部扫描，最后会将整个表锁上，如下所示：

在RC隔离级别下，半一致性读(semi-consistent)特性增加了update操作的并发性

在5.1.15的时候，innodb引入了一个概念叫做“semi-consistent”，减少了更新同一行记录时的冲突，减少锁等待。

所谓半一致性读就是，一个update语句，如果读到一行已经加锁的记录，此时InnoDB返回记录最近提交的版本，由MySQL上层判断此版本是否满足update的where条件。若满足(需要更新)，则MySQL会重新发起一次读操作，此时会读取行的最新版本(并加锁)！

具体表现如下：

此时有两个Session，Session1和Session2！

Session1执行：

update test set color = 'blue' where color = 'red'; 

先不Commit事务！与此同时Ssession2执行

update test set color = 'blue' where color = 'white'; 

Session 2尝试加锁的时候，发现行上已经存在锁，InnoDB会开启semi-consistent read，返回最新的committed版本(1,red),(2，white),(5,red),(7,white)。MySQL会重新发起一次读操作，此时会读取行的最新版本(并加锁)!

而在RR隔离级别下，Session2只能等待！

在RC级别下，不可重复读问题需要解决么？
不用解决，这个问题是可以接受的！毕竟你数据都已经提交了，读出来本身就没有太大问题！Oracle的默认隔离级别就是RC。

在RC级别下，主从复制用什么binlog格式？
在该隔离级别下，用的binlog为row格式，是基于行的复制！Innodb的创始人也是建议binlog使用该格式！