曾多次听到“mysql为什么选择rr为默认隔离级别”的问题,其实这是个历史遗留问题,当前以及解决,但是mysql的各个版本沿用了原有习惯。历史版本中的问题是什么,本次就通过简单的测试来说明一下。
部署一套主从架构的集群,创建过程较简单,可以参考历史文章部署 mysql主从复制搭建 部署一主一从即可。
在主库中创建表及测试数据
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: 0 mysql> 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
mysql默认的隔离级别为 rr(repeatable read),在此隔离级别下,对比binlog格式为row、statement是否会造成主从数据不一致
其实不用测试大家也应该对rr级别下row格式的binlog有信心,但是,万事皆需实践检验。
步骤说明如下:
具体步骤如下:
步骤 | 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 |
在从库查看数据 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)
|
为了和之前的步骤一致,先初始化数据
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: 0 root@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格式(全局及会话级都改一下,或者修改全局变量后重新登录也行,当然 只改会话级别的也可以测试),然后 再次进行测试。
步骤说明如下:
步 骤 | 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.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 |
查看从库数据 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里的顺序 | 语句内容 |
1 |
update users set c_note='t1' where c_id in (select c_id from class);
|
2 | delete from class where c_id=2; |
3 | update users set c_note='t2' where c_id in (select c_id from class); |
与主库执行的顺序是一致的,因此,主从的结果是一致的。
为了和之前的步骤一致,先初始化数据
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: 0 root@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格式(全局及会话级都改一下,或者修改全局变量后重新登录也行,当然 只改会话级别的也可以测试),然后 再次进行测试。
步骤说明如下:
步 骤 | session a | session 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 |
查看从库数据 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) |
也就是此时主从结果也是一致的。
因为当前版本已经不支持rc+statement组合下数据的操作,否则将报如下错误:
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.
因此单纯根据步骤讲解
步骤 | session a | session 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; |
|
3 | mysql>update users set c_note='t2' where c_id in (select c_id from class); | |
4 | commit; |
因为binlog是按照commit时间的顺序保存,因此上述步骤在binlog里会以如下顺序存储:
binlog里的顺序 | 语句内容 |
1 |
delete from class where c_id=2;
|
2 | update users set c_note='t1' where c_id in (select c_id from class); |
3 | update users set c_note='t2' where c_id in (select c_id from class); |
从库通过binlog应用后,最终的结果将导致主库的数据不一样(具体案例后续安装低版本后演示)。
因而,此种场景下很容易导致数据不一样。
通过上述的实践,可以发现在rr级别下,binlog为任何格式均不会造成主从数据不一致的情况出现,但是当低版本mysql使用rc+statement组合时(mysql5.1.5前只有statement格式)将会导致主从数据不一致。当前这个历史遗漏问题以及解决,大家可以将其设置为rc+row组合的方式(例如oracle等数据库隔离级别就是rc),而不是必须使用rr(会带来更多的锁等待),具体可以视情况选择。
如对本文有疑问, 点击进行留言回复!!
一步步教你用Prometheus搭建实时监控系统系列(一)——上帝之火,普罗米修斯的崛起
网友评论