《以下内容来源于一篇网络文章,暂时找不到出处了。我只是加了一些注解》
一般的DBMS系统,默认都会使用读已提交(Read-Comitted,RC)作为默认隔离级别,如Oracle、SQLServer等,而MySQL却使用可重复读(Read-Repeatable,RR)。要知道,越高的隔离级别,能解决的数据一致性问题越多,理论上性能损耗更大,可并发性越低。隔离级别依次为 "串行化 > RR > RC >读未提交"。
主从复制是基于binlog的,而binlog有三种格式:
statement:记录的是修改SQL语句
row:记录的是每行实际数据的变更
mixed:statement和row模式的混合
Mysql在5.0这个版本以前,binlog只支持STATEMENT这种格式!而这种格式在读已提交(Read Commited)这个隔离级别下主从复制是有bug的,因此Mysql将可重复读(Repeatable Read)作为默认的隔离级别!
接下来,就要说说当binlog为STATEMENT格式,且隔离级别为读已提交(Read Commited)时,有什么bug呢?如下图所示,在主(master)上执行如下事务:
session1 | session2 |
use test; #初始化数据 create table t(c1 int primary key, c2 int) engine = innodb; insert into t values(1,1); | |
#设置隔离级别 set tx_isolation = 'read-commited'; query ok, 0 rows affected(0.00 sec) | #设置隔离级别 set tx_isolation = 'read-commited'; query ok, 0 rows affected(0.00 sec) |
mysql>Begin; query ok, 0 rows affected(0.03 sec) | mysql>Begin; query ok, 0 rows affected(0.03 sec) |
mysql>delete from t where c1 query ok, 0 rows affected(0.03 sec) | |
mysql>insert into t values(2, 2); query ok, 0 rows affected(0.03 sec) | |
#比session1提交的要更早 mysql> commit ; | |
#比session2提交的要更晚 mysql> commit ; |
此时在主库中查询:
select * from t;
输出结果:
+---+---+
| c1 |c2
+---+---+
| 2 | 2
+---+---+
1 row in set
由于在主库上是先删除再插入,所以这个结果没有问题。
在从库中查询:
select * from t;
输出结果:
Empty set
这里出现了主从不一致性的问题!原因其实很简单,就是在master上执行的顺序为先删后插!而此时如果binlog为STATEMENT格式,并且由于session2比session1先提交事务,导致binlog记录的顺序为先插后删!另外,从库(slave)同步的是binglog,因此从库执行的顺序和主机不一致,就会出现主从不一致!
如何解决上述问题?
解决方案有两种:
(1)隔离级别设为可重复读(Repeatable Read),在该隔离级别下引入间隙锁。当Session 1执行delete语句时,会锁住间隙。那么,Session 2执行插入语句就会阻塞住,等待session1释放间隙锁之后才能继续执行,从而使得session2事务的提交必定在session1事务之后,这样就保证了不管是master上的实际执行顺序,还是从库同步binlog的顺序都是“先删除再插入”。
(2)将binglog的格式修改为row格式,此时是基于行数据变更的复制,自然就不会出现sql执行顺序不一样的问题!奈何这个格式在mysql5.1版本开始才引入。因此由于历史原因,mysql将默认的隔离级别设为可重复读(Repeatable Read),保证主从复制不出问题!