Mysql 数据库事物及隔离级别

我就是我 2022-02-04 12:23 234阅读 0赞

### Mysql 数据库事物及隔离级别 ###

*  事务的基本要素（ACID）
 *  事务的隔离级别问题
 *  事务的隔离级别
 *   *  READ UNCOMMITTED 读未提交
     *  READ COMMITED 读已提交
     *  REPEATABLE READ 可重复读
     *  SERIALIZABLE 可串行化
 *  数据准备
 *  READ UNCOMMITTED
 *  READ COMMITED
 *  REPEATABLE READ
 *  SERIALIZABLE

# 事务的基本要素（ACID） #

*  **原子性（Atomicity）**：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
 *  **一致性（Consistency）**：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。
 *  **隔离性（Isolation）**：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
 *  **持久性（Durability）**：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

# 事务的隔离级别问题 #

*  **脏读**：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
 *  **不可重复读**：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
 *  **幻读**：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

# 事务的隔离级别 #

## READ UNCOMMITTED 读未提交 ##

在READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称作脏读。这个级别会导致很多问题，从性能上来说，READ UNCOMMITTED不会比其他的级别好太多，但确缺乏其他级别的很多好处，除非真的有必要的理由，在实际应用中一般很少使用。

## READ COMMITED 读已提交 ##

大多数数据库系统的默认隔离级别是 READ COMMITED（但mysql不是）。READ COMMITED满足前面提到的隔离性简单定义：一个事务开始时，只能看见已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读，因为两次执行同样的查询，可能会得到不一样的结果。

## REPEATABLE READ 可重复读 ##

MySQL的默认事务隔离级别。  
REPEATABLE READ解决了脏读的问题。该级别保证了在同一个事务中多次读取同样的记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另一个幻读的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。InnoDB存储引擎通过多版本并发控制（MVCC）解决了幻读的问题。

## SERIALIZABLE 可串行化 ##

SERIALIZABLE 是最高的隔离级别。它通过强事务串行执行，避免了前面说的幻读的问题。简单来说，SERIALIZABLE会在曲度的每一行数据都加上锁，所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据一致性而且可以接受没有并发的情况下，才考虑使用该级别。

# 数据准备 #

CREATE TABLE `s_test` (
      `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
      `name` varchar(64) DEFAULT NULL,
      `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态。0-删除，1-可用',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    
    insert into s_test(`name`) values ('a'),('b'),('c'),('d');
    
    mysql> select * from s_test;
    +----+------+---------------------+---------------------+--------+
    | id | name | create_time         | update_time         | status |
    +----+------+---------------------+---------------------+--------+
    |  1 | a    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    +----+------+---------------------+---------------------+--------+
    4 rows in set (0.00 sec)

# READ UNCOMMITTED #

分别开启2个数据库连接终端，模拟2个事物操作。  
终端A 设置事物隔离级别Read UnCommited 然后读取测试表中的数据

mysql> set session transaction isolation level read uncommitted;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from s_test;
    +----+------+---------------------+---------------------+--------+
    | id | name | create_time         | update_time         | status |
    +----+------+---------------------+---------------------+--------+
    |  1 | a    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    +----+------+---------------------+---------------------+--------+
    4 rows in set (0.02 sec)

终端B设置事物隔离级别Read UnCommited 然后修改测试表中的数据，并不提交事物

mysql> set session transaction isolation level read uncommitted;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    mysql> update s_test set name = 'x' where id = 1;
    Query OK, 1 row affected (0.02 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

此时再去终端A中读取数据库中的数据

mysql> select * from s_test;
    +----+------+---------------------+---------------------+--------+
    | id | name | create_time         | update_time         | status |
    +----+------+---------------------+---------------------+--------+
    |  1 | x    | 2019-05-07 18:42:37 | 2019-05-08 10:46:50 |      1 |
    |  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    +----+------+---------------------+---------------------+--------+
    4 rows in set (0.00 sec)

可以发现事物A已经可以读到事物B未提交的修改，在READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。

此时，我们将终端B的事物回滚

mysql> rollback;
    Query OK, 0 rows affected (0.00 sec)

再到终端A查询数据库

mysql> select * from s_test;
    +----+------+---------------------+---------------------+--------+
    | id | name | create_time         | update_time         | status |
    +----+------+---------------------+---------------------+--------+
    |  1 | a    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    +----+------+---------------------+---------------------+--------+
    4 rows in set (0.00 sec)

发现数据已经回滚，则事物A中间那次读到的数据就是脏读。

# READ COMMITED #

同样，我们终端A 设置事物隔离级别Read Commited 然后读取测试表中的数据

mysql> set session transaction isolation level read committed;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from s_test;
    +----+------+---------------------+---------------------+--------+
    | id | name | create_time         | update_time         | status |
    +----+------+---------------------+---------------------+--------+
    |  1 | a    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    +----+------+---------------------+---------------------+--------+
    4 rows in set (0.00 sec)

然后在终端B设置事物隔离级别Read Commited 然后修改测试表中的数据，并不提交事物

mysql> set session transaction isolation level read committed;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> update s_test set name = 'x' where id = 1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

此时再次到终端A查询数据库记录

mysql> select * from s_test;
    +----+------+---------------------+---------------------+--------+
    | id | name | create_time         | update_time         | status |
    +----+------+---------------------+---------------------+--------+
    |  1 | a    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    +----+------+---------------------+---------------------+--------+
    4 rows in set (0.00 sec)

此时我们会发现，我们终端B未提交的修改，我们并没有读到，即不会产生脏读。

此时，我们到终端B将刚做的修改提交

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

再次回到终端A查询数据库数据

mysql> select * from s_test;
    +----+------+---------------------+---------------------+--------+
    | id | name | create_time         | update_time         | status |
    +----+------+---------------------+---------------------+--------+
    |  1 | x    | 2019-05-07 18:42:37 | 2019-05-08 10:59:54 |      1 |
    |  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    +----+------+---------------------+---------------------+--------+
    4 rows in set (0.00 sec)

此时我们发现，我们已经读到了终端B事物提交的事物，但是这也产生了一个新的问题，即事物A的2次提交读到的不同的数据。即产生了重复读的问题。

# REPEATABLE READ #

同样，我们终端A 设置事物隔离级别Repeatable Read然后读取测试表中的数据

mysql> set session transaction isolation level repeatable read;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from s_test;
    +----+------+---------------------+---------------------+--------+
    | id | name | create_time         | update_time         | status |
    +----+------+---------------------+---------------------+--------+
    |  1 | x    | 2019-05-07 18:42:37 | 2019-05-08 10:59:54 |      1 |
    |  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    +----+------+---------------------+---------------------+--------+
    4 rows in set (0.00 sec)

然后 在终端B 设置事物隔离级别Repeatable Read然后然后修改测试表中的数据，并提交事物

mysql> set session transaction isolation level repeatable read;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> update s_test set name = 'y' 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)
    
    mysql> select * from s_test;
    +----+------+---------------------+---------------------+--------+
    | id | name | create_time         | update_time         | status |
    +----+------+---------------------+---------------------+--------+
    |  1 | y    | 2019-05-07 18:42:37 | 2019-05-08 11:08:51 |      1 |
    |  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    +----+------+---------------------+---------------------+--------+
    4 rows in set (0.00 sec)

此时，再到终端A查看数据

mysql> select * from s_test;
    +----+------+---------------------+---------------------+--------+
    | id | name | create_time         | update_time         | status |
    +----+------+---------------------+---------------------+--------+
    |  1 | x    | 2019-05-07 18:42:37 | 2019-05-08 10:59:54 |      1 |
    |  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    +----+------+---------------------+---------------------+--------+
    4 rows in set (0.00 sec)

发现事物B一提交的修改不回影响事物A的重复读取，即不回产生重复读的问题。

此时，再到终端B中插入一条记录

mysql> insert into s_test(`name`) values ('e');
    Query OK, 1 row affected (0.02 sec)
    
    mysql> select * from s_test;
    +----+------+---------------------+---------------------+--------+
    | id | name | create_time         | update_time         | status |
    +----+------+---------------------+---------------------+--------+
    |  1 | y    | 2019-05-07 18:42:37 | 2019-05-08 11:08:51 |      1 |
    |  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  5 | e    | 2019-05-08 11:13:54 | 2019-05-08 11:13:54 |      1 |
    +----+------+---------------------+---------------------+--------+
    5 rows in set (0.00 sec)

然后再回到终端A，插入一条看起来不存在的记录

mysql> select * from s_test;
    +----+------+---------------------+---------------------+--------+
    | id | name | create_time         | update_time         | status |
    +----+------+---------------------+---------------------+--------+
    |  1 | x    | 2019-05-07 18:42:37 | 2019-05-08 10:59:54 |      1 |
    |  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    +----+------+---------------------+---------------------+--------+
    4 rows in set (0.00 sec)
    
    mysql> insert into s_test(id,name) values(5,'e');
    ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

即产生了幻读，读不出来已经存在的数据。

# SERIALIZABLE #

同样，我们在终端A中 设置事物隔离级别Serializable然后读取测试表中的数据

mysql> set session transaction isolation level serializable;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from s_test;
    +----+------+---------------------+---------------------+--------+
    | id | name | create_time         | update_time         | status |
    +----+------+---------------------+---------------------+--------+
    |  1 | y    | 2019-05-07 18:42:37 | 2019-05-08 11:08:51 |      1 |
    |  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
    |  5 | e    | 2019-05-08 11:13:54 | 2019-05-08 11:13:54 |      1 |
    +----+------+---------------------+---------------------+--------+
    5 rows in set (0.00 sec)

然后在终端B中设置事物隔离级别Serializable然后尝试修改测试表中的数据

mysql> set session transaction isolation level serializable;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> insert into s_test(`name`) values ('f');
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

然后会发现，终端B事务被挂住了，然后过了一段时间，提示了错误 (1205, u’Lock wait timeout exceeded; try restarting transaction’) ，说等待锁超时。

在串行化隔离级别中，事物会在读取的每一行数据都加上锁，也就是说，上面A事务在读取时，已经加了锁，此时B事务在插入操作时，得等待锁的放开，时间一长，A锁未放开，B就报错了。