mysql-选择使用Repeatable read的原因
mysql-选择使用Repeatable read的原因
问题背景
在mysql调优的过程中发现，mysql的默认隔离级别是可重复读（repeatable read），其他几类关系型数据库pg，以及sybase，oracle，sqlserver的默认的隔离级别都是读已提交(read committed)。
我们都知道隔离级别一共有四种，读未提交，读已提交，可重复读，序列化。隔离级别越高，并发性能也就越低。
疑问
1、那么mysql为什么要选择使用可重复读来作为默认的隔离级别呢？
2、可重复读，会带来哪些问题？
3、我们在开发过程中是否要修改默认值，将其改为我们常见的读已提交呢？
- 可重复读(Repeatable Read)，简称为RR；
- 读已提交(Read Commited)，简称为RC；
四种隔离级别
首先我们了解下四种隔离级别的区别。
-
READ UNCOMMITTED
：未提交读
- 读取未提交内容，所有事务可看到其他未提交事务的结果，很少实际使用
- 读取未提交的数据称为脏读（Dirty Read）
-
READ COMMITTED
：提交读
- 多数数据库的默认隔离级别（MySQL默认不是，默认为REPEATABLE-READ）
- 满足隔离的简单定义：一个事务只能看到已提交事务所做的改变
- 这种隔离级别，支持所谓的不可重读（Non-repeatable Read），同一事务的其他实例在该实例过程中可能有新commit，所以同一个select可能返回不同结果（同一个事务如何做到其他实例？）
-
REPEATABLE READ
：重复读
- 可重复读(MySQL默认事务隔离)，但可能出现幻读(Phantom Read)
幻读(Phantom Read)：当用户读取某范围数据行时，另一事务在此范围内插入新行，当用户再次读取此范围数据行时，读取到新的幻影行- InnoDB通过多版本并发控制MVCC机制解决该问题
- PS：新版MySQL采用
Next-Key锁来解决幻读问题
-
SERIALIZABLE
：串行化
- 最高隔离级别，强制事务排序（串行化），不会互相冲突
- 每个读数据航增加共享锁
- 此级别，可能导致大量超时现象和锁竞争
按事务隔离级别来说，级别越低数据一致性保障效果越差，而并发能力则越强。
为什么选择REPEATABLE READ？
mysql为什么选择使用可重复读来作为默认的隔离级别呢？
查了下文档，发现是有历史原因的，这和mysql的复制有关系，mysql的复制基于binlog，在配置文件中我们可以发现有一个参数binlog_format，binlog有三种格式
# binary logging format - mixed recommended
binlog_format=row
statement:记录的是修改SQL语句
row：记录的是每行实际数据的变更
mixed：statement和row模式的混合
在mysql5.0以前binlog只支持statement这种格式，这种格式在读已提交（read commited）这个隔离级别下主从复制是有bug的。
产生bug的原因如下图：在主库上面执行先删除后插入，但是在从库如果binlog为statement格式，记录的顺序就是先插入后删除，从库执行的顺序和主库不一致，最后主库有数据，从库的数据被删掉了。
因此mysql将可重复读（repeatable read）作为默认的隔离级别！
当然在可重复读上面也有解决方案
一是使用间隙锁，当session 1执行delete语句的时候，锁住间隙，session 2就会被阻塞
二是将binlog_format设置为row格式，基于行的复制，就不会出现sql执行顺序不一样的问题。但是这个格式是mysql5.1以后才有的。由于历史的原因，mysql将默认的隔离级别设置为可重复读，并一直延续了下来，保证主从复制不出问题。
可重复读，会带来哪些问题？
1、隔离级别越高，并发能力越低。
2、在可重复读级别下，如果使用间隙锁的方式，那么导致死锁的几率比读已提交大的多。
select *from test where n_id < 5 for update
在可重复读级别下，可以锁住间隙，防止其他事务插入数据。
在读已提交级别下，不会影响插入，其他事务任然可以插入数据。
3、在可重复读级别下，条件列未命中索引会锁表！而在读已提交隔离级别下只锁行
INSERT INTO order_record SELECT
*
FROM
order_today
WHERE
pay_success_time < '2020-03-08 00:00:00';
在可重复读的隔离级别下，执行上面的sql，会对order_record加表锁，order_today逐步锁（扫描一个锁一个）
当pay_success_time没有索引，或者因为其它原因导致没有走锁定的时候order_today就会被锁住。
测试：
mysql> show profiles;
Empty set (0.00 sec)
--默认隔离级别 REPEATABLE-READ
mysql> select @@global.tx_isolation,@@session.tx_isolation;
+-----------------------+------------------------+
| @@global.tx_isolation | @@session.tx_isolation |
+-----------------------+------------------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+------------------------+
1 row in set (0.00 sec)
--测试1 使用默认的binlog_format
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT |
+-----------------+
1 row in set (0.00 sec)
--session 1
mysql> insert into test2 select * from test limit 500000;
Query OK, 500000 rows affected, 1 warning (31.10 sec)
Records: 500000 Duplicates: 0 Warnings: 0
--session 2 可以看到插入test的数据会一直等待session 1执行完成才能插入
mysql> insert into test values('4028e481513e66bc015156c3e359001a','1','1');
Query OK, 1 row affected (29.48 sec)
--测试2 将binlog_format设置为row
mysql> set global binlog_format='row';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)
-- session 1
mysql> insert into test2 select * from test limit 500000;
Query OK, 500000 rows affected (32.09 sec)
Records: 500000 Duplicates: 0 Warnings: 0
--session 2 可以看到session 1不会阻塞session 2 的插入
mysql> insert into test values('4028e481513e66bc015156c3e359001a','1','1');
Query OK, 1 row affected (0.00 sec)
--测试3 将binlog_format设置为mixed
mysql> set global binlog_format='mixed';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| MIXED |
+-----------------+
1 row in set (0.00 sec)
--session 1
mysql> insert into test2 select * from test limit 500000;
Query OK, 500000 rows affected (32.16 sec)
Records: 500000 Duplicates: 0 Warnings: 0
--session 2 ，session 1会阻塞session2的插入
mysql> insert into test values('4028e481513e66bc015156c3e359001a','1','1');
Query OK, 1 row affected (30.71 sec)
mysql在默认隔离级别可重复读（REPEATABLE-READ）时，binlog_format设置为statement和mixed都会阻塞，设置为row模式时不会阻塞。
insert into select 有两种阻塞方式，一种是逐行锁住，一种是全表锁住。详情
解决这个阻塞的方式有两种：一是可以先把查询出来的数据落地，然后在还原到另外一张表。或者将binlog_format改为row
最好的方式是使用读已提交的模式，并且将binlog_format设置为row
另外。在mysql中设置隔离级别为读已提交时，binlog_format如果设置为statement插入数据的时候会报错：
--默认binlog_format=statement会报错
mysql> create table test3(id int)engine=innodb;
Query OK, 0 rows affected (0.16 sec)
mysql> insert into test3 values(1);
ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
--设置binlog_format=row无问题
mysql> set session binlog_format=row;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test3 values(1);
Query OK, 1 row affected (0.06 sec)
--设置binlog_format=mixed无问题
mysql> set session binlog_format=mixed;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test3 values(1);
Query OK, 1 row affected (0.07 sec)
报错的原因是因为read committed可能会导致不可重复读，也就是说可以读取到后面进入并提交的数据，如果基于STATEMENT格式的话，会导致主从数据不一样，因为STATEMENT是基于SQL语句的复制模式。
使用读已提交的时候，binlog_format只能设置为row或者mixed。建议使用row
总结
1、mysql为什么选择使用可重复读来作为默认的隔离级别？
原因是在mysql5.0以前binlog只支持statement这种格式，这种格式在读已提交（read commited）这个隔离级别下主从复制是有bug的，因此mysql将可重复读（repeatable read）作为默认的隔离级别！
2、可重复读会带来那些问题？
1）、隔离级别越高，并发能力越低。
2）、在可重复读级别下，如果使用间隙锁的方式，那么导致死锁的几率比读已提交大的多。
3）、在可重复读级别下，条件列未命中索引会锁表！而在读已提交隔离级别下只锁行
3、是否可以将mysql的默认隔离级别改为读已提交（read commited）
这个是可以的，在修改隔离级别为读已提交的同时，将binlog_format修改为row，可以提高并发能力。
