MySQL默认事务隔离级别的一些问题

1. MySQL事务隔离级别

2. MySQL默认事务隔离级别

root@localhost[(none)]> select version();
+-----------+
| version() |
+-----------+
| 5.6.36    |
+-----------+
1 row in set (0.00 sec)

root@localhost[(none)]> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

root@localhost[(none)]> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
  • REPEATABLE-READ(可重复读)
  • 这是MySQL的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。不过理论上,这会导致另一个棘手的问题:幻读 (Phantom Read)。简单的说,幻读指当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)机制解决了该问题。

    3. 幻读验证

  • Session 1
  • root@localhost[test]> select * from book;
    +----+------+
    | id | name |
    +----+------+
    |  1 | a    |
    |  2 | b    |
    |  3 | c    |
    |  4 | dd   |
    +----+------+
    4 rows in set (0.00 sec)
    
    root@localhost[test]> begin
        -> ;
    Query OK, 0 rows affected (0.00 sec)
    
  • Session 2
  • root@localhost[test]> insert into book values(5,'e');
    Query OK, 1 row affected (0.03 sec)
    
  • Session 1
  • root@localhost[test]> select * from book;
    +----+------+
    | id | name |
    +----+------+
    |  1 | a    |
    |  2 | b    |
    |  3 | c    |
    |  4 | dd   |
    |  5 | e    |
    +----+------+
    5 rows in set (0.00 sec)
    

    这里Session 1事务没提交,就直接看到了Session 2插入的行,这就是幻读

  • Session 2
  • root@localhost[test]> update book set name = 'cc' where id = 3;
    Query OK, 1 row affected (0.02 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
  • Session 1
  • root@localhost[test]> select * from book;
    +----+------+
    | id | name |
    +----+------+
    |  1 | a    |
    |  2 | b    |
    |  3 | c    |
    |  4 | dd   |
    |  5 | e    |
    +----+------+
    5 rows in set (0.00 sec)
    

    从这里可以看到,Session 2的update,Session 1没有读到。

    4. InnoDB如何解决幻读

    4.1 创建快照

    通过SELECT开始前创建事务快照,可以避免幻读

  • Session 1
  • root@localhost[test]> select * from book;
    +----+------+
    | id | name |
    +----+------+
    |  1 | a    |
    |  2 | b    |
    |  3 | cc   |
    |  4 | dd   |
    |  5 | e    |
    +----+------+
    5 rows in set (0.01 sec)
    
    root@localhost[test]> START TRANSACTION WITH CONSISTENT SNAPSHOT;
    Query OK, 0 rows affected (0.00 sec)
    
  • Session 2
  • root@localhost[test]> insert into book values(6,'f');
    Query OK, 1 row affected (0.00 sec)
    
  • Session 1
  • root@localhost[test]> select * from book;
    +----+------+
    | id | name |
    +----+------+
    |  1 | a    |
    |  2 | b    |
    |  3 | cc   |
    |  4 | dd   |
    |  5 | e    |
    +----+------+
    5 rows in set (0.00 sec)
    

    5. 快照造成的未知状态问题

    初始状态

    root@localhost[test]> select * from book;
    +----+------+
    | id | name |
    +----+------+
    |  1 | a    |
    |  2 | b    |
    |  3 | cc   |
    |  4 | dd   |
    |  5 | e    |
    |  6 | ff   |
    +----+------+
    6 rows in set (0.00 sec)
    
  • Session 1
  • root@localhost[test]> START TRANSACTION WITH CONSISTENT SNAPSHOT;
    Query OK, 0 rows affected (0.00 sec)
    
  • Session 2
  • root@localhost[test]> begin;
    Query OK, 0 rows affected (0.01 sec)
    

    插入并修改数据

    root@localhost[test]> insert into book values(7,'g');
    Query OK, 1 row affected (0.00 sec)
    
    root@localhost[test]> update book set name = 'fff' where id = 6;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    root@localhost[test]> commit;
    Query OK, 0 rows affected (0.02 sec)
    
  • Session 1
  • root@localhost[test]> select * from book;
    +----+------+
    | id | name |
    +----+------+
    |  1 | a    |
    |  2 | b    |
    |  3 | cc   |
    |  4 | dd   |
    |  5 | e    |
    |  6 | ff   |
    +----+------+
    6 rows in set (0.00 sec)
    

    在Session 1里,没commit前,看不到Session 2做出的修改。
    如果这时侯Session 1也执行了update id = 7,是可以执行成功的,并且可以查看到id=7这行记录
    但是Session 2执行的update id=6,却没有看到

    root@localhost[test]> update book set name = 'gg' where id = 7;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    root@localhost[test]> select * from book;
    +----+------+
    | id | name |
    +----+------+
    |  1 | a    |
    |  2 | b    |
    |  3 | cc   |
    |  4 | dd   |
    |  5 | e    |
    |  6 | ff   |
    |  7 | gg   |
    +----+------+
    7 rows in set (0.00 sec)
    

    commit后,再看,Session 2的update id=6这行记录就看到了.

    root@localhost[test]> commit;
    Query OK, 0 rows affected (0.02 sec)
    
    root@localhost[test]> select * from book;
    +----+------+
    | id | name |
    +----+------+
    |  1 | a    |
    |  2 | b    |
    |  3 | cc   |
    |  4 | dd   |
    |  5 | e    |
    |  6 | fff  |
    |  7 | gg   |
    +----+------+
    7 rows in set (0.00 sec)
    

    关于紫砂壶

    感悟技术人生
    此条目发表在MySQL分类目录,贴了标签。将固定链接加入收藏夹。