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使用该格式!