曾多次聽到“MySQL為什么選擇RR為默認隔離級別”的問題，其實這是個歷史遺留問題，當前以及解決，但是MySQL的各個版本沿用了原有習慣。歷史版本中的問題是什么，本次就通過簡單的測試來說明一下。
1、 準備工作
1.1 部署主從
部署一套主從架構的集群，創(chuàng)建過程較簡單，可以參考歷史文章部署
MySQL主從復制搭建 部署一主一從即可。
1.2 創(chuàng)建測試表及數(shù)據(jù)
在主庫中創(chuàng)建表及測試數(shù)據(jù)
mysql> create table users(id int primary key auto_increment,user_name varchar(20),c_id tinyint(4),c_note varchar(50),key c_id(c_id)) engine=innodb;Query OK, 0 rows affected (0.01 sec)mysql> insert into users values(1,'劉備',2,null),(2,'曹操',1,null),(3,'孫權',3,null),(4,'關羽',2,null),(5,'司馬懿',1,null);Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> create table class(c_id int primary key ,c_name varchar(1),c_note varchar(50)) engine=innodb;Query OK, 0 rows affected (0.00 sec)mysql> insert into class values(1,'魏',null),(2,'蜀',null),(3,'吳',null),(4,'晉','');Query OK, 4 rows affected (0.00 sec)Records: 4 Duplicates: 0 Warnings: 0
2、 RR隔離級別
MySQL默認的隔離級別為 RR（Repeatable Read），在此隔離級別下，對比binlog格式為ROW、STATEMENT是否會造成主從數(shù)據(jù)不一致
2.1 ROW格式
其實不用測試大家也應該對RR級別下ROW格式的binlog有信心，但是，萬事皆需實踐檢驗。
步驟說明如下：
步驟1 - 分別查看兩個會話中的事務隔離級別及binlog格式（隔離級別均為RR，binlog為ROW格式）
步驟2 - SESSION A 開啟事務，更新users 表中c_id字段存在于class表中的記錄，結果為5條記錄均更新，并將c_note內(nèi)容更新為 t1
步驟3- SESSION B 開啟事務，準備刪除class表中 c_id等于2的記錄，此時無法更新，處于阻塞狀態(tài)，因為在RR級別下需要保證重復讀。達到所等待超時時間后將會報錯。
步驟4- SESSION A 提交事務（此步驟也可以在步驟3時操作，結果不一樣，后續(xù)步驟中將采用此方式）
步驟5- SESSION B 重啟事務，再次刪除class表中 c_id等于2的記錄，此時提交可以成功了，成功刪除了一條記錄
步驟6- SESSION A 開啟事務，更新users 表中c_id字段存在于class表中的記錄，結果為3條記錄更新成功，并將c_note內(nèi)容更新為 t2，有2條記錄因為c_id不存在與class表中，因此不會更新
步驟7- 分別在SESSON A和SESSION B查看users表中的內(nèi)容，結果一致
步驟8- 在從庫查看users表中的內(nèi)容，數(shù)據(jù)與主庫一致
具體步驟如下：
步驟SESSION A
SESSION B
1
mysql>show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0.00 sec)
mysql>show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
mysql>show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0.00 sec)
mysql>show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.01 sec)
2
mysql>set autocommit=0;
mysql>update users set c_note='t1' where c_id in (select c_id from class);
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0
3
mysql>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql>delete from class where c_id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
4
mysql>commit;
Query OK, 0 rows affected (0.00 sec)
5
mysql>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql>delete from class where c_id=2;
Query OK, 1 row affected (0.00 sec)
mysql>commit;
Query OK, 0 rows affected (0.00 sec)
6
mysql>update users set c_note='t2' where c_id in (select c_id from class);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql>commit;
Query OK, 0 rows affected (0.00 sec)
7
mysql>select * from users;
+----+-----------+------+--------+
| id | user_name | c_id | c_note |
+----+-----------+------+--------+
| 1 | 劉備 | 2 | t1 |
| 2 | 曹操 | 1 | t2 |
| 3 | 孫權 | 3 | t2 |
| 4 | 關羽 | 2 | t1 |
| 5 | 司馬懿 | 1 | t2 |
+----+-----------+------+--------+
5 rows in set (0.00 sec)
mysql>select * from users;
+----+-----------+------+--------+
| id | user_name | c_id | c_note |
+----+-----------+------+--------+
| 1 | 劉備 | 2 | t1 |
| 2 | 曹操 | 1 | t2 |
| 3 | 孫權 | 3 | t2 |
| 4 | 關羽 | 2 | t1 |
| 5 | 司馬懿 | 1 | t2 |
+----+-----------+------+--------+
5 rows in set (0.00 sec)
8
在從庫查看數(shù)據(jù)
root@testdb:3307 12:02:20>select * from users;
+----+-----------+------+--------+
| id | user_name | c_id | c_note |
+----+-----------+------+--------+
| 1 | 劉備 | 2 | t1 |
| 2 | 曹操 | 1 | t2 |
| 3 | 孫權 | 3 | t2 |
| 4 | 關羽 | 2 | t1 |
| 5 | 司馬懿 | 1 | t2 |
+----+-----------+------+--------+
5 rows in set (0.00 sec)
2.2 STATEMENT格式
為了和之前的步驟一致，先初始化數(shù)據(jù)
root@testdb:3306 12:14:27>truncate table users;Query OK, 0 rows affected (0.08 sec)root@testdb:3306 12:14:29>truncate table class;Query OK, 0 rows affected (0.04 sec)root@testdb:3306 12:14:50>insert into users values(1,'劉備',2,null),(2,'曹操',1,null),(3,'孫 權',3,null),(4,'關羽',2,null),(5,'司馬懿',1,null);Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0root@testdb:3306 12:15:10>insert into class values(1,'魏',null),(2,'蜀',null),(3,'吳',null),(4,'晉','');Query OK, 4 rows affected (0.00 sec)Records: 4 Duplicates: 0 Warnings: 0
再將binlog日志格式改為STATAMENT格式（全局及會話級都改一下，或者修改全局變量后重新登錄也行，當然 只改會話級別的也可以測試），然后 再次進行測試。
步驟說明如下：
步驟1 - 分別查看兩個會話中的事務隔離級別及binlog格式（隔離級別均為RR，binlog為STATENENT格式）
步驟2 - SESSION A 開啟事務，更新users 表中c_id字段存在于class表中的記錄，結果為5條記錄均更新，并將c_note內(nèi)容更新為 t1
步驟3- SESSION B 開啟事務，準備刪除class表中 c_id等于2的記錄，此時無法更新，處于阻塞狀態(tài)，立即進行步驟4
步驟4- SESSION A 在SESSION B執(zhí)行commit的動作，則SESSION B的刪除操作可以執(zhí)行通過，但注意class表的數(shù)據(jù)兩個SESSION中查看到的是不一樣的
步驟5- 此時SESSION B執(zhí)行commit,否則后面session A 更新數(shù)據(jù)時也會阻塞。此時如果SESSION A不執(zhí)行commit，查看class表的結果也是不一樣的，如步驟中的情況
步驟6- SESSION A 開啟事務，更新users 表中c_id字段存在于class表中的記錄，結果為3條記錄更新成功，并將c_note內(nèi)容更新為 t2，另外2條記錄雖然本此時查看class表中存在對應的c_id，但是不會更新，此時提交事務，然后再次查看class的內(nèi)容，結果和SESSION B 查看的結果一致了（幻讀）
步驟7- 在從庫查看users、class表中的內(nèi)容，數(shù)據(jù)與主庫一致
步 驟SESSION ASESSION B
1
mysql>show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0.01 sec)
mysql>show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.01 sec)
mysql>show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0.01 sec)
mysql>show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.01 sec)
2
root@testdb:3306 12:37:04>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
root@testdb:3306 12:37:17>update users set c_note='t1' where c_id in (select c_id from class);
Query OK, 5 rows affected, 1 warning (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 1
3
root@testdb:3306 12:28:25>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
root@testdb:3306 12:38:06>delete from class where c_id=2;
Query OK, 1 row affected (4.74 sec)
4
root@testdb:3306 12:38:09>commit;
Query OK, 0 rows affected (0.00 sec)
root@testdb:3306 12:38:13>select * from users;
+----+-----------+------+--------+
| id | user_name | c_id | c_note |
+----+-----------+------+--------+
| 1 | 劉備 | 2 | t1 |
| 2 | 曹操 | 1 | t1 |
| 3 | 孫 權 | 3 | t1 |
| 4 | 關羽 | 2 | t1 |
| 5 | 司馬懿 | 1 | t1 |
+----+-----------+------+--------+
5 rows in set (0.00 sec)
root@testdb:3306 12:39:07>select * from class;
+------+--------+--------+
| c_id | c_name | c_note |
+------+--------+--------+
| 1 | 魏 | NULL |
| 2 | 蜀 | NULL |
| 3 | 吳 | NULL |
| 4 | 晉 | |
+------+--------+--------+
4 rows in set (0.00 sec)
5
root@testdb:3306 12:38:13>commit;
Query OK, 0 rows affected (0.00 sec)
root@testdb:3306 12:39:56>select * from class ;
+------+--------+--------+
| c_id | c_name | c_note |
+------+--------+--------+
| 1 | 魏 | NULL |
| 3 | 吳 | NULL |
| 4 | 晉 | |
+------+--------+--------+
3 rows in set (0.00 sec)
6
root@testdb:3306 12:52:23>update users set c_note='t2' where c_id in (select c_id from class);
Query OK, 3 rows affected, 1 warning (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 1
root@testdb:3306 12:52:45>select * from class;
+------+--------+--------+
| c_id | c_name | c_note |
+------+--------+--------+
| 1 | 魏 | NULL |
| 2 | 蜀 | NULL |
| 3 | 吳 | NULL |
| 4 | 晉 | |
+------+--------+--------+
4 rows in set (0.00 sec)
root@testdb:3306 12:52:49>select * from users;
+----+-----------+------+--------+
| id | user_name | c_id | c_note |
+----+-----------+------+--------+
| 1 | 劉備 | 2 | t1 |
| 2 | 曹操 | 1 | t2 |
| 3 | 孫 權 | 3 | t2 |
| 4 | 關羽 | 2 | t1 |
| 5 | 司馬懿 | 1 | t2 |
+----+-----------+------+--------+
5 rows in set (0.01 sec)
root@testdb:3306 12:53:03>commit;
Query OK, 0 rows affected (0.00 sec)
root@testdb:3306 12:53:06>select * from users;
+----+-----------+------+--------+
| id | user_name | c_id | c_note |
+----+-----------+------+--------+
| 1 | 劉備 | 2 | t1 |
| 2 | 曹操 | 1 | t2 |
| 3 | 孫 權 | 3 | t2 |
| 4 | 關羽 | 2 | t1 |
| 5 | 司馬懿 | 1 | t2 |
+----+-----------+------+--------+
5 rows in set (0.00 sec)
root@testdb:3306 12:53:11>select * from class;
+------+--------+--------+
| c_id | c_name | c_note |
+------+--------+--------+
| 1 | 魏 | NULL |
| 3 | 吳 | NULL |
| 4 | 晉 | |
+------+--------+--------+
3 rows in set (0.00 sec)
7
查看從庫數(shù)據(jù)
root@testdb:3307 12:44:22>select * from class;
+------+--------+--------+
| c_id | c_name | c_note |
+------+--------+--------+
| 1 | 魏 | NULL |
| 3 | 吳 | NULL |
| 4 | 晉 | |
+------+--------+--------+
3 rows in set (0.01 sec)
root@testdb:3307 12:57:07>select * from users;
+----+-----------+------+--------+
| id | user_name | c_id | c_note |
+----+-----------+------+--------+
| 1 | 劉備 | 2 | t1 |
| 2 | 曹操 | 1 | t2 |
| 3 | 孫 權 | 3 | t2 |
| 4 | 關羽 | 2 | t1 |
| 5 | 司馬懿 | 1 | t2 |
+----+-----------+------+--------+
5 rows in set (0.00 sec)
也就是此時主從結果也是一致的，原因在于，binlog里存儲的語句順序如下：
binlog里的順序語句內(nèi)容
1
update users set c_note='t1' where c_id in (select c_id from class);
2delete from class where c_id=2;
3update users set c_note='t2' where c_id in (select c_id from class);
與主庫執(zhí)行的順序是一致的，因此，主從的結果是一致的。
3、 RC隔離級別
3.1 ROW格式
為了和之前的步驟一致，先初始化數(shù)據(jù)
root@testdb:3306 12:14:27>truncate table users;Query OK, 0 rows affected (0.08 sec)root@testdb:3306 12:14:29>truncate table class;Query OK, 0 rows affected (0.04 sec)root@testdb:3306 12:14:50>insert into users values(1,'劉備',2,null),(2,'曹操',1,null),(3,'孫 權',3,null),(4,'關羽',2,null),(5,'司馬懿',1,null);Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0root@testdb:3306 12:15:10>insert into class values(1,'魏',null),(2,'蜀',null),(3,'吳',null),(4,'晉','');Query OK, 4 rows affected (0.00 sec)Records: 4 Duplicates: 0 Warnings: 0
再將binlog日志格式改為STATAMENT格式（全局及會話級都改一下，或者修改全局變量后重新登錄也行，當然 只改會話級別的也可以測試），然后 再次進行測試。
步驟說明如下：
步驟1 - 分別查看兩個會話中的事務隔離級別及binlog格式（隔離級別均為RC，binlog為ROW格式）
步驟2 - SESSION A 開啟事務，更新users 表中c_id字段存在于class表中的記錄，結果為5條記錄均更新，并將c_note內(nèi)容更新為 t1
步驟3- SESSION B 開啟事務，準備刪除class表中 c_id等于2的記錄，此時不會像RR事務隔離級別那樣處于阻塞狀態(tài)，而是可以直接執(zhí)行通過
步驟4- 此時SESSION A查看class數(shù)據(jù)還是刪除前的，因為session B 暫未提交
步驟5- SESSION B 提交事務，
步驟6- 更新users 表中c_id字段存在于class表中的記錄，結果為3條記錄更新成功，并將c_note內(nèi)容更新為 t2
步驟7- 在從庫查看users、class表中的內(nèi)容，數(shù)據(jù)與主庫一致
步 驟SESSION ASESSION B
1
root@testdb:3306 01:25:24>show variables like '%iso%';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
| tx_isolation | READ-COMMITTED |
+-----------------------+----------------+
2 rows in set (0.01 sec)
root@testdb:3306 01:25:36>show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.01 sec)
root@testdb:3306 01:24:57>show variables like '%iso%';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
| tx_isolation | READ-COMMITTED |
+-----------------------+----------------+
2 rows in set (0.01 sec)
root@testdb:3306 01:25:39>show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
2
root@testdb:3306 01:27:55>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
root@testdb:3306 01:28:27>update users set c_note='t1' where c_id in (select c_id from class);
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0
3
root@testdb:3306 01:26:07>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
root@testdb:3306 01:28:37>delete from class where c_id=2;
Query OK, 1 row affected (0.00 sec)
4
root@testdb:3306 01:28:27>select * from class;
+------+--------+--------+
| c_id | c_name | c_note |
+------+--------+--------+
| 1 | 魏 | NULL |
| 2 | 蜀 | NULL |
| 3 | 吳 | NULL |
| 4 | 晉 | |
+------+--------+--------+
4 rows in set (0.00 sec)
5
root@testdb:3306 01:28:41>commit;
Query OK, 0 rows affected (0.00 sec)
6
root@testdb:3306 01:28:59>select * from class;
+------+--------+--------+
| c_id | c_name | c_note |
+------+--------+--------+
| 1 | 魏 | NULL |
| 3 | 吳 | NULL |
| 4 | 晉 | |
+------+--------+--------+
3 rows in set (0.01 sec)
root@testdb:3306 01:29:13>update users set c_note='t2' where c_id in (select c_id from class);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
root@testdb:3306 01:29:26>select * from class;
+------+--------+--------+
| c_id | c_name | c_note |
+------+--------+--------+
| 1 | 魏 | NULL |
| 3 | 吳 | NULL |
| 4 | 晉 | |
+------+--------+--------+
3 rows in set (0.00 sec)
root@testdb:3306 01:29:31>select * from users;
+----+-----------+------+--------+
| id | user_name | c_id | c_note |
+----+-----------+------+--------+
| 1 | 劉備 | 2 | t1 |
| 2 | 曹操 | 1 | t2 |
| 3 | 孫 權 | 3 | t2 |
| 4 | 關羽 | 2 | t1 |
| 5 | 司馬懿 | 1 | t2 |
+----+-----------+------+--------+
5 rows in set (0.00 sec)
root@testdb:3306 01:29:38>commit;
7
查看從庫數(shù)據(jù)
root@testdb:3307 01:40:32>select * from users;
+----+-----------+------+--------+
| id | user_name | c_id | c_note |
+----+-----------+------+--------+
| 1 | 劉備 | 2 | t1 |
| 2 | 曹操 | 1 | t2 |
| 3 | 孫 權 | 3 | t2 |
| 4 | 關羽 | 2 | t1 |
| 5 | 司馬懿 | 1 | t2 |
+----+-----------+------+--------+
5 rows in set (0.00 sec)
root@testdb:3307 01:40:35>select * from class;
+------+--------+--------+
| c_id | c_name | c_note |
+------+--------+--------+
| 1 | 魏 | NULL |
| 3 | 吳 | NULL |
| 4 | 晉 | |
+------+--------+--------+
3 rows in set (0.00 sec)
也就是此時主從結果也是一致的。
3.2 STATEMENT格式
因為當前版本已經(jīng)不支持RC+STATEMENT組合下數(shù)據(jù)的操作，否則將報如下錯誤：
Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
因此單純根據(jù)步驟講解
步驟SESSION ASESSION B
1
mysql>set autocommit=0;
mysql>update users set c_note='t1' where c_id in (select c_id from class);
2
mysql>set autocommit=0;
mysql>delete from class where c_id=2;
mysql>commit;
3mysql>update users set c_note='t2' where c_id in (select c_id from class);
4commit；
因為binlog是按照commit時間的順序保存，因此上述步驟在binlog里會以如下順序存儲：
binlog里的順序語句內(nèi)容
1
delete from class where c_id=2;
2update users set c_note='t1' where c_id in (select c_id from class);
3update users set c_note='t2' where c_id in (select c_id from class);
從庫通過binlog應用后，最終的結果將導致主庫的數(shù)據(jù)不一樣（具體案例后續(xù)安裝低版本后演示）。
因而，此種場景下很容易導致數(shù)據(jù)不一樣。
4、總結
通過上述的實踐，可以發(fā)現(xiàn)在RR級別下，binlog為任何格式均不會造成主從數(shù)據(jù)不一致的情況出現(xiàn)，但是當?shù)桶姹綧ySQL使用RC+STATEMENT組合時（MySQL5.1.5前只有statement格式）將會導致主從數(shù)據(jù)不一致。當前這個歷史遺漏問題以及解決，大家可以將其設置為RC+ROW組合的方式（例如ORACLE等數(shù)據(jù)庫隔離級別就是RC），而不是必須使用RR（會帶來更多的鎖等待），具體可以視情況選擇。
reapeatable read
