MySQL之事务

事务的概念
事务的ACID特性
事务的隔离级别
- - - 1.事务并发存在的问题
    - 2.隔离级别
MySQL中事务的操作
案例
事务日志
事务隔离级别的实现原理

环境MySQL8.0.34，存储引擎InnoDB。

事务的概念

事务：一组原子性的SQL查询。事务内的语句要么全部执行成功，要么全部执行失败。

事务的ACID特性

原子性（atomic）：事务的原子性是指一个事务中的全部操作要么全部提交成功，要么全部失败回滚。
一致性（consistency）：一个事务在执行之前和执行之后，表中的数据必须保持一致性状态。例如小明向小王转账500元，如果事务成功提交则账户表中小明的余额减少500元，小王的账户余额增加500元，事务执行失败则小明和小王的余额应当和事务执行之前一样。
隔离性（isolation）：通常来说，一个事务所作的修改在最终提交之前，对其他事务是不可见的。
持久性（duration）：事务一旦提交，则其所作的修改就会永久保存到数据库中。即使服务器宕机，还会有redo log和undo log来保证持久性。

事务的ACD特性由undo log以及redo log保证，日志在MySQL之日志这一篇中讲解；而隔离性由锁机制和MVCC来保证，锁机制和MVCC在MySQL之锁机制中讲解。

事务的隔离级别

1.事务并发存在的问题

脏读：一个事务A读取了另一个事务B未提交的数据，如果另一个事务B执行过程中失败回滚，则A读取到的数据就是脏数据。
不可重复读：一个事务的操作导致另一个事务前后两次读取到不同的数据，即事务A读取了事务B已经提交的记录。
幻读：一个事务的操作导致另一个事务前后两次查询的结果不同，即事务B读取了事务A新增加的记录或者读取不到事务A中满足事务B第一次查询，却被删除的记录。

2.隔离级别

在SQL中定义了四种隔离级别，如下所示。隔离级别越高，并发性越低，安全性越高，系统的开销越高。

未提交读（read uncommitted）：这个隔离级别，事务中的修改，即使没有提交，对其他事务也是可见的。允许脏读产生，这个隔离界别在实际应用中一般很少使用，因为他没有做任何并发控制。
已提交读（read committed）：大部分数据库系统的默认隔离级别是已提交读，比如说Oracle。一个事务从开始到提交之前，所做的任何修改对其他事务都是不可见的，这就避免了脏读，但是还是会有不可重复读的情况，因为两次执行同样的查询，可能得到不一样的结果。
可重复读（repeatable read）：这个级别保证了在同一个事务中多次读取同样记录的结果是一样的，解决了不可重复读以及部分幻读(例如执行insert和delete时)，还是会有幻读的情况发生。这是MySQL默认的事务隔离级别。
可串行化（serializable）：强制事务串行执行，可以完全避免幻读问题。这个隔离界别在应用中很少用到，因为没有了并发。

MySQL中事务的操作

开启一个事务：start transaction
事务的提交：commit，提交事务会将修改的数据持久保留
事务的回滚：rollback，回滚事务将会撤销所有的更改
设置一个保存点：savepoint 保存点名称
事务回滚到指定的保存点：rollback to 保存点名称
设置事务的提交方式：在MySQL中默认采用自动提交事务：select @@autocommit;，其值为1。

# 设置事务的提交方式为手动提交，在当前客户端会话中生效
set autocommit = 0;
# 设置事务的提交方式为手动提交，在所有会话中生效
set global autocommit = 0;

查看当前会话事务的隔离级别：结果就是MySQL默认的隔离级别

# MySQL8.0以上
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

设置事务的隔离级别

# MySQL8.0以上
# 设置当前会话的隔离级别为未提交读
set transaction_isolation = 'read-uncommitted';
# 设置所有会话中生效
set global transaction_isolation = 'read-uncommitted';

案例

1，为了更深理解MySQL的隔离级别，将事务的提交方式更改为手动。使用一个表，选择不同隔离级别进行测试，表如下：

mysql> select * from student;
+-----+-----------+------+--------+
| uid | name      | age  | sex    |
+-----+-----------+------+--------+
|   1 | 张三      |   23 | male   |
|   2 | 李四      |   22 | male   |
|   3 | 刘娟      |   20 | female |
|   4 | 王丽丽    |   26 | female |
+-----+-----------+------+--------+

将事务的隔离级别更改为未提交读

# 会话1
mysql> begin;       # 1
Query OK, 0 rows affected (0.00 sec)

mysql> update student set name='NrvCer' where uid = 1;                  # 4
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback;    # 6
Query OK, 0 rows affected (0.04 sec)

# 会话2
mysql> begin;       # 2
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where uid = 1; # 3
+-----+--------+------+------+
| uid | name   | age  | sex  |
+-----+--------+------+------+
|   1 | 张三   |   23 | male |
+-----+--------+------+------+
1 row in set (0.00 sec)

mysql> select * from student where uid = 1; # 5
+-----+--------+------+------+
| uid | name   | age  | sex  |
+-----+--------+------+------+
|   1 | NrvCer |   23 | male |
+-----+--------+------+------+
1 row in set (0.00 sec)

mysql> select * from student where uid = 1; # 7
+-----+--------+------+------+
| uid | name   | age  | sex  |
+-----+--------+------+------+
|   1 | 张三   |   23 | male |
+-----+--------+------+------+
1 row in set (0.00 sec)


# 如上所示：在第五步中，会话2可以读取会话1未提交的数据，因此在未提交读这种
# 隔离级别中，会有脏读发生。

将事务的隔离级别更改为已提交读

# 会话1
mysql> begin;           # 1
Query OK, 0 rows affected (0.00 sec)

mysql> update student set name='NrvCer' where uid = 1;                      # 4
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

# 会话2
mysql> begin;           # 2
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where uid = 1; # 3
+-----+--------+------+------+
| uid | name   | age  | sex  |
+-----+--------+------+------+
|   1 | 张三   |   23 | male |
+-----+--------+------+------+
1 row in set (0.00 sec)

mysql> select * from student where uid = 1; # 5
+-----+--------+------+------+
| uid | name   | age  | sex  |
+-----+--------+------+------+
|   1 | 张三   |   23 | male |
+-----+--------+------+------+
1 row in set (0.00 sec)

mysql> select * from student where uid = 1; # 7
+-----+--------+------+------+
| uid | name   | age  | sex  |
+-----+--------+------+------+
|   1 | NrvCer |   23 | male |
+-----+--------+------+------+
1 row in set (0.00 sec)

# 如上所示：在第五步中，会话2不会读取会话1未提交的记录，所以说已提交读可以避免脏读
# 但是不可避免不可重复读的问题发生，因为第五步和第七步结果不一致。

将隔离级别设置为可重复读

# 示例1：

# 会话1
mysql> begin;               # 1
Query OK, 0 rows affected (0.00 sec)

mysql> update student set name = 'NrvCer' where uid = 2;                        # 4
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

# 会话2
mysql> begin;               # 2
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where uid = 2; # 3
+-----+--------+------+------+
| uid | name   | age  | sex  |
+-----+--------+------+------+
|   2 | 李四   |   22 | male |
+-----+--------+------+------+
1 row in set (0.00 sec)

mysql> select * from student where uid = 2; # 5
+-----+--------+------+------+
| uid | name   | age  | sex  |
+-----+--------+------+------+
|   2 | 李四   |   22 | male |
+-----+--------+------+------+
1 row in set (0.00 sec)

mysql> select * from student where uid = 2; # 7
+-----+--------+------+------+
| uid | name   | age  | sex  |
+-----+--------+------+------+
|   2 | 李四   |   22 | male |
+-----+--------+------+------+
1 row in set (0.00 sec)


# 如上所示：在第五步和第七步中，会话2在会话1提交前后，读取到的记录都是一样的。这就解决了不可重复读的问题
# 至于如何保证的，这个和MVCC有关，在MySQL之锁机制中讲解

# 示例2：

# 会话1
mysql> begin;       # 1
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student(name,age,sex) values('小孙',21, 'male'); # 4
Query OK, 1 row affected (0.00 sec)

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

# 会话2
mysql> begin;       # 2 
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where age = 22; # 3
+-----+--------+------+------+
| uid | name   | age  | sex  |
+-----+--------+------+------+
|   2 | NrvCer |   22 | male |
+-----+--------+------+------+
1 row in set (0.00 sec)

mysql> update student set age = 22 where name = '小孙';     # 6 当前读，读取的是记录的最新版本
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student where age = 22; # 7
+-----+--------+------+------+
| uid | name   | age  | sex  |
+-----+--------+------+------+
|   2 | NrvCer |   22 | male |
|  10 | 小孙   |   22 | male |
+-----+--------+------+------+
2 rows in set (0.00 sec)

# 如上所示：在第3步和第七步中，两次读取的结果不一致，第七步读取了会话1添加的记录，产生幻读。

将隔离级别设置为串行化

set global transaction_isolation = 'serializable';

# 会话1
mysql> begin; # 1
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;  # 3
+-----+-----------+------+--------+
| uid | name      | age  | sex    |
+-----+-----------+------+--------+
|   1 | 关羽      |   23 | male   |
|   2 | NrvCer    |   22 | male   |
|   3 | NrvCer    |   20 | female |
|   4 | 王丽丽    |   26 | female |
|   6 | 小王      |   20 | male   |
|   7 | 刘小红    |   26 | female |
|   8 | 关羽      |   23 | male   |
|   9 | 小明      |   22 | male   |
|  10 | 小孙      |   21 | male   |
+-----+-----------+------+--------+
9 rows in set (0.00 sec)



# 会话2
mysql> begin;      # 2
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student(name,age,sex) values('老六',26,'female'); # 4
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> select * from student; # 5
+-----+-----------+------+--------+
| uid | name      | age  | sex    |
+-----+-----------+------+--------+
|   1 | 关羽      |   23 | male   |
|   2 | NrvCer    |   22 | male   |
|   3 | NrvCer    |   20 | female |
|   4 | 王丽丽    |   26 | female |
|   6 | 小王      |   20 | male   |
|   7 | 刘小红    |   26 | female |
|   8 | 关羽      |   23 | male   |
|   9 | 小明      |   22 | male   |
|  10 | 小孙      |   21 | male   |
+-----+-----------+------+--------+
9 rows in set (0.00 sec)

# 如上所示，在第四步中，直接阻塞，得不到想要的结果.
# 而在第五步中，可以得到想要的结果
# 读读操作获取的是共享锁，共享锁与共享锁可以兼容

事务日志

事务日志主要包括redo log（未做日志）和undo log（回滚日志），这一部分在MySQL之日志这一篇中介绍。

事务隔离级别的实现原理

1.MVCC

隔离级别已提交读和可重复读和MVCC有很大的关系，因此先介绍MVCC。

MVCC：全称Multi-Version Concurrency Control，多版本并发控制
1. MVCC是通过保存数据在某个时间点的快照实现的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据是不一样的。
2. 在MVCC中，读操作可以分为两类：
  1. 快照读：读的是记录的可见版本，不需要加锁。比如说不显式加锁的select查询都是从快照读取数据
  2. 当前读：读取的是记录的最新版本，并且返回当前读的记录。比如所insert、delete、update、select ... lock in share mode/for update。
3. 在MVCC中每一行记录实际上有多个版本，每个版本的记录处理数据本身之外，还增加了其他字段。
  1. DB_TRX_ID：记录当前事务的id，每次使用begin开启一个事务，都会有一个事务id
  2. DB_ROLL_PTR：指向undo log日志上数据的指针。
4. 快照内容的读取原则：
  1. 版本未提交，则无法读取生成快照
  2. 版本已经提交，但是在快照创建后提交的，无法读取
  3. 版本已经提交，但是在快照创建前提交的，可以读取
  4. 当前事务内自己的更新，可以读取到

2.未提交读

未提交读没有做任何的并发控制，所有会有脏读、不可重复读、幻读的问题

3.已提交读

已提交读为什么可以解决脏读问题？因为每一次select，都会重新产生一次新的数据快照。另一个事务提交了，则当前事务执行select的时候，会产生一次新的数据快照。另一个事务未提交，则当前事务执行select的时候，产生的数据快照和之前的一样。
已提交读为什么不可以解决不可重复读问题？因为每一次select都会重新产生一次数据快照，其他事务对数据已经更改并且成功提交，是可以实时反馈到当前事务的select查询结果当中的。
已提交读为什么不可以解决幻读问题？因为每一次select都会重新产生一次数据快照，在其他事务中增加了符合当前事务查询条件的记录并且成功提交，导致当前事务再次以相同的条件select查询时，符合的记录数变多了。

关键词：每一次select都会重新产生一次新的数据快照

4.可重复读

可重复读为什么解决了脏读问题？因为可重复读隔离级别下，只会在第一次select时产生数据快照，而且只产生一次。
可重复读为什么解决了不可重复读问题？因为当前事务第一次select产生数据快照，其他事务虽然对数据已经更改并且成功提交，但是当前事务再次select时不会产生新的数据快照，依然查看的是最初的快照数据。
可重复读为什么解决了部分幻读问题？和第二点一样，此外事务可以看见当前事务对数据的更新和修改操作。

关键词：第一次select产生数据快照，而且只产生一次。

5.串行化

串行化为什么可以解决幻读的问题？这个和间隙锁有关，参见MySQL之锁机制这一篇的介绍。

posted on 2023-10-02 12:48 scrutiny-span 阅读(21) 评论(0) 编辑收藏举报

刷新页面返回顶部

导航