devbox
小惠珠哦
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

mysql的事务隔离级别_mysql事务隔离级别

作者：小惠珠哦 | 2024-07-06 23:12:06

赞

踩

mysql事务隔离级别

目录

一致性读

事务的隔离级别

一致性读和隔离级别矩阵

innodb_locks_unsafe_for_binlog 

MYSQL隔离级别控制

transaction_isolation

SET TRANSACTION

问题深入

为什么mysql选择rr作为默认隔离级别？

为什么推荐使用rc隔离级别？

rr模式是否会造成幻读？

 

一致性读

脏读

一个事务读取到了其他事务中还没有提交的数据

不可重复读

不可重复读发生在更新的情景下。事务a读取数据，事务b更新数据并提交，事务a再次读取时读取到了b更新后的数据

幻读

幻读发生在插入的情景下。事务a读取数据，事务b插入数据并提交，事务a再次读取时读取到了b插入的数据

事务的隔离级别

在SQL:1992 standard事务隔离级别标准中包含4种隔离级别：

 Serializable(序列化)

  系统中所有的事务以串行地方式逐个执行，所以能避免所有数据不一致情况。 

  但是这种以排他方式来控制并发事务，串行化执行方式会导致事务排队，系统的并发量大幅下降，使用的时候要绝对慎重。 

Repeatable read(可重复读)

  一个事务一旦开始，事务过程中所读取的所有数据不允许被其他事务修改。 可重复读是mysql的默认隔离级别

Read Committed(已提交读)

一个事务能读取到其他事务提交过的数据。

 一个事务在处理过程中如果重复读取某一个数据，而且这个数据恰好被其他事务修改并提交了， 那么当前重复读取数据的事务就会出现同一个数据前后不同的情况。 

 在这个隔离级别会发生“不可重复读”和”幻读“的场景。

 Read Uncommitted(未提交读)

一个事务能读取到其他事务修改过，但是还没有提交的(Uncommitted)的数据。

数据被其他事务修改过，但还没有提交，就存在着回滚的可能性，这时候读取这些“未提交” 数据的情况就是“脏读”。

       在这个隔离级别会发生“脏读”场景。 

 

一致性读和隔离级别矩阵

事务隔离级别

脏读

不可重复读

幻影读

未提交读

可能

可能

可能

已提交读

不可能

可能

可能

可重复读

不可能

不可能

不可能

序列化

不可能

不可能

不可能

 

注意：可重复读是不会发生幻读的，百度的绝大部分文章都写的不对。可以看下面的”rr模式是否会造成幻读？“那段

 

 

innodb_locks_unsafe_for_binlog 

该参数已过期（Deprecated）。可能在未来的版本被删除，可以不用管这个参数。

innodb_locks_unsafe_for_binlog默认为OFF，表示gap lock打开。next-key是innodb用来控制gap之前的记录的锁，next-key是gap-key和record key的结合。

如果将innodb_locks_unsafe_for_binlog设置为ON，也就是关闭了gap lock，也就没有了next-key，这是相当于rc隔离级别。因为gap-lock被禁止，所以这个时候就会发生insert幻读的情况

 

 

MYSQL隔离级别控制

transaction_isolation

mysql通过参数控制事务隔离级别

mysql5.7官方文档：

"transaction_isolation was added in MySQL 5.7.20 as a synonym for tx_isolation, which is now deprecated and is removed in MySQL 8.0. Applications should be adjusted to use transaction_isolation in preference to tx_isolation"

<5.7.19用tx_isolation，>=5.7.20用transaction_isolation，8.0及以后就不会有tx_isolation

 

transaction_isolation是动态参数，可以直接修改

set global transaction_isolation='READ COMMITTED'  --更改全局事务隔离级别，对新会话有效，对已有回合无效

set transaction_isolation='READ COMMITTED' 和 set session transaction_isolation='READ COMMITTED'都是更改当前会话的隔离级别

SET TRANSACTION

SET  [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL { REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE };

例如：set session TRANSACTION ISOLATION LEVEL read committed;

 

set transaction xxx：设置下一个事务的隔离级别，不能在事务中使用

set session transaction xxx：设置当前会话下所有事务的隔离级别。跟set transaction_isolation和set session transaction_isolation类似

set global transaction xxx：设置新会话的隔离级别，对已连接的会话没有影响。跟set global transaction_isolation类似

  1. --set transaction不能在事务中执行，set session transaction可以，因为set session transaction才是设置当前会话下所有事务的语句，set transaction是下一个事务
  2. mysql> start transaction;
  3. Query OK, 0 rows affected (0.00 sec)
  4.  
  5. mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  6. ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress
  7. mysql>  set session  TRANSACTION ISOLATION LEVEL READ COMMITTED;
  8. Query OK, 0 rows affected (0.03 sec)
  1. mysql> set global TRANSACTION ISOLATION LEVEL READ COMMITTED;
  2. Query OK, 0 rows affected (0.00 sec)
  3. mysql> show variables like '%iso%';
  4. +-----------------------+-----------------+
  5. | Variable_name         | Value           |
  6. +-----------------------+-----------------+
  7. | transaction_isolation | REPEATABLE-READ |
  8. | tx_isolation          | REPEATABLE-READ |
  9. +-----------------------+-----------------+
  10. --set global不会更改当前会话的事物隔离级别
  11. --开启新会话
  12. mysql> show variables like '%iso%';
  13. +-----------------------+----------------+
  14. | Variable_name         | Value          |
  15. +-----------------------+----------------+
  16. | transaction_isolation | READ-COMMITTED |
  17. | tx_isolation          | READ-COMMITTED |
  18. +-----------------------+----------------+
  19.

 

  1. --set session TRANSACTION只会更改当前会话
  2. mysql>  set session  TRANSACTION ISOLATION LEVEL repeatable read;
  3. Query OK, 0 rows affected (0.00 sec)
  4.  
  5. mysql> show global variables like '%iso%';
  6. +-----------------------+----------------+
  7. | Variable_name         | Value          |
  8. +-----------------------+----------------+
  9. | transaction_isolation | READ-COMMITTED |
  10. | tx_isolation          | READ-COMMITTED |
  11. +-----------------------+----------------+
  12. 2 rows in set (0.01 sec)
  13.  
  14. mysql> show  session variables like '%iso%';
  15. +-----------------------+-----------------+
  16. | Variable_name         | Value           |
  17. +-----------------------+-----------------+
  18. | transaction_isolation | REPEATABLE-READ |
  19. | tx_isolation          | REPEATABLE-READ |
  20. +-----------------------+-----------------+
  21. 2 rows in set (0.02 sec)

问题深入

为什么mysql选择rr作为默认隔离级别？

在解答上面的问题之前需要了解mysql的binlog模式。binlog有三种模式：statement、row、mixed。

  • statement：基于SQL 语句的模式，binlog 数据量小
  • mixed：混合模式，根据语句来选用是statement 还是row 模式
  • row：基于行的模式，记录的是行的完整变化。安全，但binlog 会比其他两种模式大很多

mysql5.0之前只有statement一种模式，这种模式在rc隔离级别下可能会导致主备不一致（sql执行顺序在主备库上不一样）。所以mysql采用了rr级别为默认隔离级别。rr级别下会有间隙锁保证事务的先后顺序，既可以保证主备一致。

binlog为row模式现已经是mysql的默认模式，它是基于行改变的binlog。在row模式下，无论是rr还是rc都不会导致主备不一致。

所以rc隔离级别在row模式下是可以用的，而且也建议使用rc。

在rc隔离级别下，只支持binlog日志模式为row，就算是mixed，mysqlserver还是使用row来记录binlog。

回到问题，为什么mysql选择可重复读rr作为默认隔离级别？因为mysql.50之前binlog只有statement模式，statement模式下只有使用rr隔离级别才不会产生binlog记录事务顺序错乱。mysql后续的版本也没有改变默认隔离级别为rc

为什么推荐使用rc隔离级别？

在rr隔离级别下，需要保证事务的快照读。快照读可以防止可重复读和幻读的发生。实现快照读就需要比rc更多的锁（gap lock间隙锁仅存在于rr模式下），死锁的概率要高很多。

比如显示事务a：select * from tab1 where col1>100，事务a在没有提交和回滚的情况下，其他事务不可以插入和更新tab1上的至少col1>100的数据。col1<100也是有可能被锁住的（可以看下下面的测试）。

在真实场景中mysql在rr模式下可能会造成2个条件不交叉的语句造成死锁的情况。

而rc级别不会有这样的情况，因为允许幻读，select肯定不会阻塞插入。

rr模式是否会造成幻读？

答案是不会。

“Repeatable read(可重复读) ：一个事务一旦开始，事务过程中所读取的所有数据不允许被其他事务修改。 

  这个隔离级别没有办法解决“幻影读”的问题。

  因为它只“保护”了它读取的数据不被修改，但是其他数据会被修改。如果其他数据被修改后恰好满足了 当前事务的过滤条件(where语句)，那么就会发生“幻影读”的情况。”

上面这段话是摘自网络，百度随便一搜都是可重复读会发生幻读，或者这样：

但是无论是mysql官方文档还是我自己的测试，可重复读级别都是不会发生幻读的

mysql官网对可重复读级别的解释：

Consistent reads within the same transaction read the snapshot established by the first read

可重复读是在一个事务中第一次select时建立的快照读。也就是说在同一个事务中同样的select不会有其他不一样的结果，也就不会有幻读和不可重复读

mysql官网对幻读的解释：

https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html

mysql为了解决幻读使用了next-key lock，next-key lock是row-index lock(记录锁）和gap lock（间隙锁）的结合。

gap lock是mysql在rr级别特有的，rc级别不存在。也就是说mysql是通过lock机制解决rr上的幻读。既然rr级别mysql通过gap锁和next-key锁解决了幻读的发生，那么rr就不会有幻读

 

（测试均关闭自动提交）

幻读测试：

rr级别下幻读测试
session1session2
mysql>  select * from testa;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | 1    |    1 |
|    1 | 3    |    3 |
|    1 | 3    |    3 |
+------+------+------+		mysql>  select * from testa;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | 1    |    1 |
|    1 | 3    |    3 |
|    1 | 3    |    3 |
+------+------+------+
 

mysql> insert into testa values(1,1,1);
Query OK, 1 row affected (0.03 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

 mysql>  select * from testa;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | 1    |    1 |
|    1 | 3    |    3 |
|    1 | 3    |    3 |
|    1 | 1    |    1 |
+------+------+------+
4 rows in set (0.00 sec)
mysql>  select * from testa;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | 1    |    1 |
|    1 | 3    |    3 |
|    1 | 3    |    3 |
+------+------+------+
3 rows in set (0.00 sec)		 

mysql> commit;--事务提交
Query OK, 0 rows affected (0.00 sec)

mysql> select * from testa;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | 1    |    1 |
|    1 | 3    |    3 |
|    1 | 3    |    3 |
|    1 | 1    |    1 |   --新的事务查到新插入的数据

 
结论：rr级别下没有幻读

 

不可重复读测试：

rr级别下不可重复读
session1session2
mysql> select * from testa where c=3; 
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | 3    |    3 |
|    1 | 3    |    3 |
+------+------+------+		mysql> select * from testa where c=3; 
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | 3    |    3 |
|    1 | 3    |    3 |
+------+------+------+
 mysql> update testa set a=2 where c=3;
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from testa where c=3; 
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    2 | 3    |    3 |
|    2 | 3    |    3 |
+------+------+------+

mysql> select * from testa where c=3;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | 3    |    3 |
|    1 | 3    |    3 |  --查看不到update后的数据
+------+------+------+		 

mysql> commit;--事务提交
Query OK, 0 rows affected (0.00 sec)

mysql> select * from testa where c=3;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    2 | 3    |    3 |
|    2 | 3    |    3 |
+------+------+------+
2 rows in set (0.00 sec)

 
rr级别没有不可重复读

 

 

 

 

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小惠珠哦/article/detail/794255
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号