一般的DBMS系统,默认都会使用读提交(Read-Comitted,RC)作为默认隔离级别,如Oracle、SQL Server等,而MySQL却使用可重复读(Read-Repeatable,RR)。要知道,越高的隔离级别,能解决的数据一致性问题越多,理论上性能损耗更大,可并发性越低。隔离级别依次为
SERIALIZABLE > RR > RC > Read-Uncommited
在SQL标准中,前三种隔离级别分别解决了幻象读、不可重复读和脏读的问题。那么,为什么MySQL使用可重复读作为默认隔离级别呢?
1. 从Binlog说起Binlog是MySQL的逻辑操作日志,广泛应用于复制和恢复。MySQL 5.1以前,Statement是Binlog的默认格式,即依次记录系统接受的SQL请求;5.1及以后,MySQL提供了Row和Mixed两个Binlog格式。
从MySQL 5.1开始,如果打开语句级Binlog,就不支持RC和Read-Uncommited隔离级别。要想使用RC隔离级别,必须使用Mixed或Row格式。
mysql> set tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(1,1);
ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
那么,为什么RC隔离级别不支持语句级Binlog呢?我们关闭binlog,做以下测试。
会话1
会话2
use test;
#初始化数据
create table t1(c1 int, c2 int) engine=innodb;
create table t2(c1 int, c2 int) engine=innodb;
insert into t1 values(1,1), (2,2);
insert into t2 values(1,1), (2,2);
#设置隔离级别
set tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)
#连续更新两次
mysql> Begin;
Query OK, 0 rows affected (0.03 sec)
mysql> update t2 set c2 = 3 where c1 in (select c1 from t1);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> update t2 set c2 = 4 where c1 in (select c1 from t1);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t2;
+------+------+
| c1 | c2 |
+------+------+
| 1 | 4 |
| 2 | 3 |
+------+------+
2 rows in set (0.00 sec)
mysql> commit;
#设置隔离级别
set tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)
#两次更新之间执行删除
mysql> delete from t1 where c1 = 2;
Query OK, 1 row affected (0.03 sec)
由以上测试知,RC隔离级别下,会话2执行时序在会话1事务的语句之间,并且会话2的操作影响了会话1的结果,这会对Binlog结果造成影响。
由于Binlog中语句的顺序以commit为序,如果语句级Binlog允许,两会话的执行时序是
#会话2
set tx_isolation='read-committed';
delete from t1 where c1 = 2;
commit;
#会话1
set tx_isolation='read-committed';
Begin;
update t2 set c2 = 3 where c1 in (select c1 from t1);
update t2 set c2 = 4 where c1 in (select c1 from t1);
select * from t2;
+------+------+
| c1 | c2 |
+------+------+
| 1 | 4 |
| 2 | 2 |
+------+------+
2 rows in set (0.00 sec)
commit;