MySQL事务隔离级别小记

待我称王封你为后i 2023-07-17 05:59 3阅读 0赞

## 概要：在数据库操作中，为了保证并发读取数据的正确性，一致性，提出的事务隔离级别，隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。 ##

**一、脏读、不可重复读、幻读**

*  脏读  
    事务A读取事务B尚未提交的数据，如果事务B执行回滚操作，那么A事务读取到的数据就是脏数据。
 *  不可重复读  
    在一个事务中，对同一行数据重复读取两次，但是却得到了不同的结果。
 *  幻读  
    事 务在操作过程中进行两次查询，两次查询的数据记录数不匹配。

**二、事务的隔离级别**  
MySQL事务有四个级别：未提交读(read uncommitted)、已提交读(read committed)、可重复读(repeatable read)、串行化(serializable)

*  默认事务隔离级别
    
        mysql>  SELECT @@tx_isolation;
        ERROR 2006 (HY000): MySQL server has gone away
        No connection. Trying to reconnect...
        Connection id:    138591
        Current database: FA
        
        +-----------------+
        | @@tx_isolation  |
        +-----------------+
        | REPEATABLE-READ |
        +-----------------+
 *  **未提交读(read uncommitted)**  
    在事务A中可以读取到事务B未提交的数据，但是可能产生 ***脏读***， ***不可重复读*** 和 ***幻读*** 。
    
    测试脏读：
    
        mysql> set global transaction_isolation ='READ-UNCOMMITTED';
        Query OK, 0 rows affected (0.00 sec)
        
        +------------------+
        | @@tx_isolation   |
        +------------------+
        | READ-UNCOMMITTED |
        +------------------+
        1 row in set, 1 warning (0.00 sec)
    
    1、事务A：
    
        mysql> START TRANSACTION;
        Query OK, 0 rows affected (0.00 sec)
        
        mysql> SELECT * FROM INFO;
        +----+------+------+
        | ID | NAME | AGE  |
        +----+------+------+
        |  1 | lisi |   18 |
        +----+------+------+
        1 row in set (0.00 sec)
    
    2、事务B
    
        mysql> START TRANSACTION;
        Query OK, 0 rows affected (0.00 sec)
        
        mysql> INSERT INTO INFO(NAME, AGE) VALUES('wangwu', 12);
        Query OK, 1 row affected (0.00 sec)
    
    3、事务A
    
        mysql> SELECT * FROM INFO;
        +----+--------+------+
        | ID | NAME   | AGE  |
        +----+--------+------+
        |  1 | lisi   |   18 |
        |  2 | wangwu |   12 |
        +----+--------+------+
        2 rows in set (0.00 sec)
    
    4、事务B
    
        mysql> ROLLBACK;
        Query OK, 0 rows affected (0.01 sec)
    
    5、事务A
    
        mysql> SELECT * FROM INFO;
        +----+------+------+
        | ID | NAME | AGE  |
        +----+------+------+
        |  1 | lisi |   18 |
        +----+------+------+
        1 row in set (0.00 sec)
        
        mysql> COMMIT;
        Query OK, 0 rows affected (0.00 sec)
    
    > 由上可知：事务B在执行完步骤2后未提交，此时事务A执行查询，查询到B未提交的数据，然后B因某原因回滚了数据，此时事务A读取的两条数据中有一条是不存在的，是脏数据。
 *  **已提交读(read committed)**  
    读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该数据行。可以避免脏读，但可能产生不可重复读和幻读
    
    测试不可重复读：
    
        mysql> set global transaction_isolation ='READ-COMMITTED';
        Query OK, 0 rows affected (0.00 sec)
        
        mysql>  SELECT @@tx_isolation;
        +------------------+
        | @@tx_isolation   |
        +------------------+
        | READ-COMMITTED |
        +------------------+
        1 row in set, 1 warning (0.00 sec)
    
    1、事务A
    
        mysql> START TRANSACTION;
        Query OK, 0 rows affected (0.00 sec)
        
        mysql> SELECT * FROM INFO;
        +----+------+------+
        | ID | NAME | AGE  |
        +----+------+------+
        |  1 | lisi |   18 |
        +----+------+------+
        1 row in set (0.00 sec)
    
    2、事务B
    
        mysql> START TRANSACTION;
        Query OK, 0 rows affected (0.00 sec)
        
        mysql> UPDATE INFO SET NAME='maliu' WHERE ID='1';
        Query OK, 1 row affected (0.00 sec)
        Rows matched: 1  Changed: 1  Warnings: 0
    
    3、事务A
    
        mysql> SELECT * FROM INFO;
        +----+------+------+
        | ID | NAME | AGE  |
        +----+------+------+
        |  1 | lisi |   18 |
        +----+------+------+
        1 row in set (0.00 sec)
    
    4、事务B
    
        mysql> COMMIT;
        Query OK, 0 rows affected (0.01 sec)
    
    5、事务A
    
        mysql> SELECT * FROM INFO;
        +----+-------+------+
        | ID | NAME  | AGE  |
        +----+-------+------+
        |  1 | maliu |   18 |
        +----+-------+------+
        1 row in set (0.00 sec)
    
    > 由上可知：当事务B修改数据后，但未提交，此时事务A是读取不到的，所有该隔离级别可以防止脏读；当事务B提交事务后，此时事务A再次查询，发现数据已经被修改，两次读取结果不一致，产生不可重复读。
 *  **可重复读(repeatable read)**  
    事务A在开启事务期间，所访问的数据是不可修改的，可以避免不可重复读，但是可能产生幻读。
    
        mysql> set global transaction_isolation ='REPEATABLE-READ';
        Query OK, 0 rows affected (0.00 sec)
        
        mysql>  SELECT @@tx_isolation;
        +-----------------+
        | @@tx_isolation  |
        +-----------------+
        | REPEATABLE-READ |
        +-----------------+
        1 row in set, 1 warning (0.00 sec)
    
    1、事务A
    
        mysql> START TRANSACTION;
        Query OK, 0 rows affected (0.00 sec)
        
        mysql> SELECT * FROM INFO;
        +----+-------+------+
        | ID | NAME  | AGE  |
        +----+-------+------+
        |  1 | maliu |   18 |
        +----+-------+------+
        1 row in set (0.00 sec)
    
    2、事务B
    
        mysql> UPDATE INFO SET NAME='adam' WHERE ID='1';
        Query OK, 1 row affected (0.00 sec)
        Rows matched: 1  Changed: 1  Warnings: 0
        
        mysql> COMMIT;
        Query OK, 0 rows affected (0.01 sec)
    
    3、事务A
    
        mysql> SELECT * FROM INFO;
        +----+-------+------+
        | ID | NAME  | AGE  |
        +----+-------+------+
        |  1 | maliu |   18 |
        +----+-------+------+
        1 row in set (0.00 sec)
    
    4、事务A
    
        mysql> COMMIT;
        Query OK, 0 rows affected (0.00 sec)
        
        mysql> SELECT * FROM INFO;
        +----+------+------+
        | ID | NAME | AGE  |
        +----+------+------+
        |  1 | adam |   18 |
        +----+------+------+
        1 row in set (0.00 sec)
    
    > 由上可知：当事务隔离级别为可重复读时，在事务A未提交前，是看不到其他事务提交的数据，可以避免不可重复读，但是可能产生幻读。
    
    当SQL增加FOR UPDATE时，是禁止其他事务对NAME=‘adam’的数据进行操作（新增删除）
    
        SELECT * FROM INFO WHERE NAME = 'adam' FOR UPDATE;
    
    FOR UPDATE是一种行级锁，又叫排它锁。可以解决可重复读级别下的幻读现象。
 *  **串行化(serializable)**  
    提供严格的事务隔离。它要求事务串行化执行，事务只能一个接着一个地执行，不能并发执行。可以解决脏读，不可重复读，幻读现象，但大大降低了并发性。