在mysql调优的过程中发现,mysql的默认隔离级别是可重复读(repeatable read),其他几类关系型数据库pg,以及sybase,oracle,sqlserver的默认的隔离级别都是读已提交(read committed)。
我们都知道隔离级别一共有四种,读未提交,读已提交,可重复读,序列化。隔离级别越高,并发性能也就越低。
1、那么mysql为什么要选择使用可重复读来作为默认的隔离级别呢?
2、可重复读,会带来哪些问题?
3、我们在开发过程中是否要修改默认值,将其改为我们常见的读已提交呢?
首先我们了解下四种隔离级别的区别。
幻读(Phantom Read)
:当用户读取某范围数据行时,另一事务在此范围内插入新行,当用户再次读取此范围数据行时,读取到新的幻影行Next-Key锁
来解决幻读问题按事务隔离级别来说,级别越低数据一致性保障效果越差,而并发能力则越强。
mysql为什么选择使用可重复读来作为默认的隔离级别呢?
查了下文档,发现是有历史原因的,这和mysql的复制有关系,mysql的复制基于binlog,在配置文件中我们可以发现有一个参数binlog_format,binlog有三种格式
# binary logging format - mixed recommended
binlog_format=rowstatement:记录的是修改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 < 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 |+-----------------------+------------------------+ row in set (0.00 sec)--测试1 使用默认的binlog_formatmysql> select @@binlog_format;+-----------------+| @@binlog_format |+-----------------+| STATEMENT |+-----------------+ row in set (0.00 sec)--session 1 mysql> insert into test2 select * from test limit ;
Query OK, rows affected, warning (31.10 sec)
Records: Duplicates: Warnings: --session 2 可以看到插入test的数据会一直等待session 1执行完成才能插入mysql> insert into test values('4028e481513e66bc015156c3e359001a','1','1');
Query OK, row affected (29.48 sec)--测试2 将binlog_format设置为rowmysql> set global binlog_format='row';
Query OK, rows affected (0.00 sec)
mysql> select @@binlog_format;+-----------------+| @@binlog_format |+-----------------+| ROW |+-----------------+ row in set (0.00 sec)-- session 1mysql> insert into test2 select * from test limit ;
Query OK, rows affected (32.09 sec)
Records: Duplicates: Warnings: --session 2 可以看到session 1不会阻塞session 2 的插入mysql> insert into test values('4028e481513e66bc015156c3e359001a','1','1');
Query OK, row affected (0.00 sec)--测试3 将binlog_format设置为mixedmysql> set global binlog_format='mixed';
Query OK, rows affected (0.00 sec)
mysql> select @@binlog_format;+-----------------+| @@binlog_format |+-----------------+| MIXED |+-----------------+ row in set (0.00 sec)--session 1mysql> insert into test2 select * from test limit ;
Query OK, rows affected (32.16 sec)
Records: Duplicates: Warnings: --session 2 ,session 1会阻塞session2的插入mysql> insert into test values('4028e481513e66bc015156c3e359001a','1','1');
Query OK, 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, rows affected (0.16 sec)
mysql> insert into test3 values();
ERROR (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, rows affected (0.00 sec)
mysql> insert into test3 values();
Query OK, row affected (0.06 sec)--设置binlog_format=mixed无问题mysql> set session binlog_format=mixed;
Query OK, rows affected (0.00 sec)
mysql> insert into test3 values();
Query OK, 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,可以提高并发能力。