mysql系列VI-事务

发表于 更新于 分类于 ， Changyan： 本文字数： 3.7k 阅读时长 ≈ 14 分钟

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

Mysql系列文章

  1. mysql系列I-概述

  2. mysql系列II-sql语法

    ①. mysql系列II-sql语法-(1)-概述

    ②. mysql系列II-sql语法-(2)-DDL

    ③. mysql系列II-sql语法-(3)-DML

    ④. mysql系列II-sql语法-(4)-DQL

    ⑤. mysql系列II-sql语法-(5)-DCL

  3. mysql系列III-函数

  4. mysql系列IV-约束

  5. mysql系列V-多表查询

  6. mysql系列VI-事务

  7. mysql系列VII-存储引擎

  8. mysql系列VIII-索引

    ①. mysql系列VIII-索引(1)-概述

    ②. mysql系列VIII-索引(2)-结构

    ③. mysql系列VIII-索引(3)-分类

    ④. mysql系列VIII-索引(4)-语法

    ⑤. mysql系列VIII-索引(5)-SQL性能分析

    ⑥. mysql系列VIII-索引(6)-索引使用

    ⑦. mysql系列VIII-索引(7)-设计原则

  9. mysql系列IX-SQL优化

  10. mysql系列X-视图

  11. mysql系列XI-存储过程

  12. mysql系列XII-存储函数

  13. mysql系列XIII-触发器

  14. mysql系列XIV-锁

就比如: 张三给李四转账1000块钱，张三银行账户的钱减少1000，而李四银行账户的钱要增加1000。 这一组操作就必须在一个事务的范围内，要么都成功，要么都失败。

正常情况: 转账这个操作, 需要分为以下这么三步来完成 , 三步完成之后, 张三减少1000, 而李四增加1000, 转账成功 :

异常情况: 转账这个操作, 也是分为以下这么三步来完成 , 在执行第三步是报错了, 这样就导致张三减少1000块钱, 而李四的金额没变, 这样就造成了数据的不一致, 就出现问题了。

为了解决上述的问题，就需要通过数据的事务来完成，我们只需要在业务逻辑执行之前开启事务，执行完毕后提交事务。如果执行过程中报错，则回滚事务，把数据恢复到事务开始之前的状态。

注意： 默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐式的提交事务。

事务操作

数据准备：

1
2
3
4
5
6
7
8
9
10
11
12
drop table if exists account;

create table account
(
    id    int primary key AUTO_INCREMENT comment 'ID',
    name  varchar(10) comment '姓名',
    money double(10, 2) comment '余额'
) comment '账户表';

insert into account(name, money)
VALUES ('张三', 2000),
       ('李四', 2000);

未控制事务

测试正常情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 1. 查询张三余额
select *
from account
where name = '张三';

-- 2. 张三的余额减少1000
update account
set money = money - 1000
where name = '张三';

-- 3. 李四的余额增加1000
update account
set money = money + 1000
where name = '李四';

测试完毕之后检查数据的状态, 可以看到数据操作前后是一致的。

2). 测试异常情况

1
2
3
4
5
6
7
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
出错了....
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

我们把数据都恢复到2000， 然后再次一次性执行上述的SQL语句(出错了…. 这句话不符合SQL语法,执行就会报错)，检查最终的数据情况, 发现数据在操作前后不一致了。

控制事务一

1). 查看/设置事务提交方式

1
2
SELECT @@autocommit ;
SET @@autocommit = 0 ;

2). 提交事务

1
COMMIT;

3). 回滚事务

1
ROLLBACK;

注意：上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。

控制事务二

1). 开启事务

1
START TRANSACTION 或 BEGIN ;

2). 提交事务

1
COMMIT;

3). 回滚事务

1
ROLLBACK;

转账案例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 开启事务
start transaction;

-- 1. 查询张三余额
select *
from account
where name = '张三';

-- 2. 张三的余额减少1000
update account
set money = money - 1000
where name = '张三';

-- 3. 李四的余额增加1000
update account
set money = money + 1000
where name = '李四';

-- 如果正常执行完毕, 则提交事务
commit;

-- 如果执行过程中报错, 则回滚事务
-- rollback;

事务四大特性

  • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

上述就是事务的四大特性，简称ACID。

并发事务问题

  1. 脏读：一个事务读到另外一个事务还没有提交的数据。

    比如B读取到了A未提交的数据。

  2. 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

    事务A两次读取同一条记录，但是读取到的数据却是不一样的。

  3. 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 “幻影”。

事务隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种

隔离级别脏读不可重复读幻读
Read uncommitted（读未提交：并发事务3个问题都不能解决）✔✔✔
Read committed（读已提交：oracle默认，解决了脏读的问题）✘✔✔
Repeatable Read(可重复读：mysql默认，解决了脏读和不可重复读的问题)✘✘✔
Serializable（串行化：并发事务的3个问题都得到了解决，但是性能相对较低）✘✘✘

1). 查看事务隔离级别

1
SELECT @@TRANSACTION_ISOLATION;

2). 设置事务隔离级别

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

注意：事务隔离级别越高，数据越安全，但是性能越低。

解决并发事务问题示例

读未提交(READ UNCOMMITTED)

READ-UNCOMMITTED未解决事务并发的任务问题，我们在这个隔离级别下测试下脏读的问题

查看当前事务隔离级别

1
2
3
4
5
6
7
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

我们先将事务隔离级别调整至Read uncommitted。

1
2
3
4
5
6
7
8
9
10
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)

我们用两个窗口模拟一下并发事务。

首先查询数据内容：

1
2
3
4
5
6
7
8
mysql> select * from account;
+----+------+---------+
| id | name | money   |
+----+------+---------+
|  1 | 张三 | 2000.00 |
|  2 | 李四 | 2000.00 |
+----+------+---------+
2 rows in set (0.00 sec)

两个窗口都开启了事务，左边为事务1；右边为事务2.

  1. 事务1先去查询了id为1的记录，发现张三余额为2000
  2. 此时事务2更新了id为1的记录，将张三余额加了100，也就是变为2100，此时事务2还未提交
  3. 当事务2还未commit的时候事务1又去查询了id为1的记录，发现张三余额变为了2100

这种场景事务1读到了事务2未提交的数据这就叫脏读

读已提交(READ-COMMITTED)

Read committed解决了脏读的问题，但是没有解决不可重复读和幻读的问题，我们将隔离级别调整至Read committed，复现下脏读问题是否还存在以及不可重复读问题是否解决。

首先调整两个事务隔离级别为READ-COMMITTED
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)
恢复数据
1
2
3
4
5
6
7
8
9
mysql> select * from account;
+----+------+---------+
| id | name | money   |
+----+------+---------+
|  1 | 张三 | 2000.00 |
|  2 | 李四 | 2000.00 |
+----+------+---------+
2 rows in set (0.00 sec)

模拟并发执行事务

同样有两个并发事务：

  1. 事务1查询id为1的用户余额为2000；
  2. 此时事务2将id为1的用于余额加了100；
  3. 然后事务1又查询了下id为1的用户余额还是2000；
  4. 这时候事务2提交了事务，等于确实将id为1的用户余额加了100；
  5. 最后事务1又查询了一次id为1的用户余额，发现余额变为2100；

从1-3可以看出，READ-COMMITTED确实解决了脏读的问题，事务1不会读到事务2未提交的数据了；总体来看1-5，事务1前两次查询的结果与第三次查询结果不一致，第三次查询到了事务二提交后的数据，这种现象就是不可重复读。

之前一直混淆脏读和不可重复读这两个问题，表面上来看不都是并发事务过程中因为其他事务对数据的修改导致了事务1两次查询数据的不一致问题，但是经过实践可以明确感知到脏读和不可重复读的区别。

  • 脏读指的是事务1中读取到了其他事务更新但未提交的数据；
  • 不可重复读指的是事务1中读到了其他事务更新且提交后的数据导致的事务1多次查询结果不一致的问题；
示例总结

由此可以知道，READ-COMMITTED确实解决了脏读的问题，但是却没有解决不可重复读的问题

可重复读(REPEATABLE-READ)

REPEATABLE-READ解决了脏读和不可重复读的问题，但是没有解决幻读的问题，我们将隔离级别调整至``REPEATABLE-READ`，复现下脏读和不可重复读问题是否还存在以及幻读问题是否解决。

调整事务隔离级别

将事务隔离级别调整为REPEATABLE-READ

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)
恢复数据
1
2
3
4
5
6
7
8
9
10
11
mysql> update account set money = 2000;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

mysql> select * from account;
+----+------+---------+
| id | name | money   |
+----+------+---------+
|  1 | 张三 | 2000.00 |
|  2 | 李四 | 2000.00 |
+----+------+---------+
模拟并发执行事务
验证脏读、不可重复读是否解决

两个同时在执行的事务：

  1. 事务1第1次查询用户1余额为2000
  2. 事务2将用户1余额增加100；
  3. 事务1第2次查询用户1余额为2000；
  4. 事务2提交了事务；
  5. 事务1第3次查询用户1余额为2000；

由此可见，不管其他事务对数据的更新有无提交事务，事务1的查询结果都不变，这就验证了，REPEATABLE-READ解决了脏读和不可重复读的问题，接下来我们验证，它能否解决幻读问题呢？

验证能否解决幻读问题

两个同时在执行的事务：

  1. 事务1第1次查询用户3不存在；
  2. 事务2插入了用户3王五；
  3. 事务1第2次查询用户3依然不存在；
  4. 事务1决定添加用户3，执行光标停在此处等待；

实践来看事务1貌似在等待其他事务提交，我们接下来将事务2提交试试看。

  1. 事务2提交了事务
  2. 事务1瞬间有了执行结果，提示用户3已存在

我们继续往下执行，在事务1内，既然3不存在那我就插入，插入提示已存在，再查还是查不到用户3

  1. 事务1第3次查询用户3不存在
  2. 事务1插入用户3提示已存在
  3. 事务1第4次查询用户3不存在
  4. 事务1插入用户3提示已存在

明明我查询的是用户3不存在，怎么一添加用户3就提示重复已存在呢？这是幻觉吗？这种场景就叫做幻读。

串行化（SERIALIZABLE）

鉴于前面几种隔离级别已经将脏读、不可重复读问题解决了，接下来我们直接验证SERIALIZABLE能否解决幻读问题。

调整事务隔离级别

将事务隔离级别调整为SERIALIZABLE

1
2
3
4
5
6
7
8
9
10
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| SERIALIZABLE            |
+-------------------------+
1 row in set (0.00 sec)
恢复数据
1
2
3
4
5
6
7
8
mysql> select * from account;
+----+------+---------+
| id | name | money   |
+----+------+---------+
|  1 | 张三 | 2000.00 |
|  2 | 李四 | 2000.00 |
+----+------+---------+
2 rows in set (0.00 sec)
模拟并发执行事务

  1. 事务1第1次查询用户3是否存在，不存在

  2. 事务2添加用户3王五，结果阻塞等待状态

  1. 事务1一直未提交事务，导致事务2添加用户3超时失败

    1
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

  2. 事务2再次尝试添加用户3，依然阻塞等待

  3. 事务1添加了用户3王老五，添加成功

  4. 事务1提交了事务

  5. 事务1提交事务瞬间事务2提示添加用户3失败

以上流程可看出，并发事务变成了串行去执行，一个事务执行时，其他并发事务阻塞等待。

总概念来看其实不太容易理解三个事务并发问题，通过实践，清晰的能够看到这三种问题是在什么节点出现的，您有学到吗？

Mysql系列文章

  1. mysql系列I-概述

  2. mysql系列II-sql语法

    ①. mysql系列II-sql语法-(1)-概述

    ②. mysql系列II-sql语法-(2)-DDL

    ③. mysql系列II-sql语法-(3)-DML

    ④. mysql系列II-sql语法-(4)-DQL

    ⑤. mysql系列II-sql语法-(5)-DCL

  3. mysql系列III-函数

  4. mysql系列IV-约束

  5. mysql系列V-多表查询

  6. mysql系列VI-事务

  7. mysql系列VII-存储引擎

  8. mysql系列VIII-索引

    ①. mysql系列VIII-索引(1)-概述

    ②. mysql系列VIII-索引(2)-结构

    ③. mysql系列VIII-索引(3)-分类

    ④. mysql系列VIII-索引(4)-语法

    ⑤. mysql系列VIII-索引(5)-SQL性能分析

    ⑥. mysql系列VIII-索引(6)-索引使用

    ⑦. mysql系列VIII-索引(7)-设计原则

  9. mysql系列IX-SQL优化

  10. mysql系列X-视图

  11. mysql系列XI-存储过程

  12. mysql系列XII-存储函数

  13. mysql系列XIII-触发器

  14. mysql系列XIV-锁