1. 数据库事务
1.1 事务的概述
业务场景
1566694672043
什么是事务
如果在执行一个业务操作的时候，需要执行多条SQL语句，必须保证所有的SQL语句都执行成功。只要其中有一条执行失败，则所有的SQL语句都要进行回滚
事务必须是所有的SQL语句都执行成功，或者都执行失败
事务的四大特性ACID
事务特性 | 含义 |
原子性（Atomicity） | 所有的SQL操作是一个整体，不可再拆分。事务是应该是最小的执行单元 |
一致性（Consistency） | 事务执行前，执行后。数据库的状态应该是一致的。 如：转账前2个人总额与转换后2个人总额应该是一样的 |
隔离性（Isolation） | 事务是可以并发执行的，理想的情况应该是所有的事务之间不能相互影响 |
持久性（Durability） | 如果事务对数据库进行了操作，对数据库中数据影响是持久的 |
需求
不使用事务，用SQL模拟Jack给Rose转账500？
应用
-- 1. 下面我们进行转账操作，首先要准备好数据。
-- 创建数据表account（id 整型 自增主键，name 变长字符串30，balance 整型）
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
balance double
);
-- 添加数据（Jack，1000）、（Rose，1000）
INSERT INTO account (name, balance) VALUES ('Jack', 1000), ('Rose', 1000);
-- 查询准备好的account表
mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 1 | Jack | 1000 |
| 2 | Rose | 1000 |
+----+------+---------+
2 rows in set (0.00 sec)
mysql>
-- 2.下面来进行转账的SQL执行操作
-- 2.1 如果要完成Jack转账给Rose : 500
-- 至少2条，Jack扣钱，Rose加钱，要有2条update语句
update account set balance = balance - 500 where name='Jack';
update account set balance = balance + 500 where name='Rose';
-- 执行如下：
mysql> update account set balance = balance - 500 where name='Jack'; -- Jack的账号扣500元
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update account set balance = balance + 500 where name='Rose'; -- Rose的账号增加500元
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from account; -- 查询account表，确认两个账号的转账情况：可以看到Jack和Rose账号成功转账。
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 1 | Jack | 500 |
| 2 | Rose | 1500 |
+----+------+---------+
2 rows in set (0.00 sec)
mysql>
-- 账户全部还原成1000
update account set balance = 1000;
在上面的执行情况中，我们可以看到Jack和Rose账号成功转账。但是凡事总会存在一些意外，例如网络异常、程序异常等情况。
假设当Jack账号上进行 -500元 后 服务器崩溃了。Rose的账号并没有 +500元，数据就出现问题了：Jack的账号丢失了 500，但是 Rose的账号并没有增加500。
对于这种异常的情况，我们该怎么去保障呢？
我们需要保证其中一条SQL语句出现问题，整个转账就算失败。如果失败，那么将数据直接回滚到操作之前，这样才可以保障数据的安全。
也就是只有两条SQL都成功了转账才算成功。这个时候就需要用到事务。
1.2 事务的操作
1. 手动开启，需手动提交
2. 自动开启，提交由两种模式
a. 手动提交
b. 自动提交
1.2.1 手动开启
SQL语句
功能 | SQL语句 |
开启事务 | start transaction |
提交事务 | commit |
回滚事务 | rollback |
提交事务的过程
- 开启事务
- 执行SQL语句
- 提交或回滚事务
案例：事务提交
模拟Jack给Rose转500元钱成功的情况
- 进入MySQL
- 执行以下SQL语句：
- 开启事务
- Jack账号-500
- Rose账号+500
- 查看数据库：发现数据并没有改变
- 在控制台执行commit提交任务：
- 查看数据库：发现数据改变
执行如下：
-- 1. 开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
-- 2.Jack的账号扣500元
mysql> update account set balance = balance - 500 where name='Jack';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 3.Rose的账号增500元
mysql> update account set balance = balance + 500 where name='Rose';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 4. 提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
-- 5. 查看转账后的数据
mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 1 | Jack | 500 |
| 2 | Rose | 1500 |
+----+------+---------+
2 rows in set (0.00 sec)
mysql>
案例：事务回滚
模拟Jack给Rose转500元钱失败的情况，在控制台执行以下SQL语句：
- 开启事务
- Jack账号-500，Rose账户+500
- 查看数据库：发现数据并没有改变
- 在控制台执行rollback回滚事务
- 查看数据库：发现数据没有改变
-- 1. 开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
-- 2. 查看当前的account账号
mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 1 | Jack | 1000 |
| 2 | Rose | 1000 |
+----+------+---------+
2 rows in set (0.00 sec)
-- 3.Jack的账号扣500元
mysql> update account set balance = balance - 500 where name='Jack';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 4.Rose的账号增500元
mysql> update account set balance = balance + 500 where name='Rose';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 5.查看当前的account账号数据
mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 1 | Jack | 500 |
| 2 | Rose | 1500 |
+----+------+---------+
2 rows in set (0.00 sec)
-- 6.执行rollback回滚事务
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
-- 7.再次查看回滚后的数据，发现回滚到执行之前的数据情况
mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 1 | Jack | 1000 |
| 2 | Rose | 1000 |
+----+------+---------+
2 rows in set (0.00 sec)
1.2.2 自动开启
事务自动开启时，提交有两种模式，利用autocommit关键字来实现
-- 1.关键字autocommit：1表示自动提交，0表示手动提交
-- 1.1 查询事务提交模式
select @@autocommit;
-- 执行如下：
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.01 sec)
mysql>
-- 1.2 修改事务提交模式
set @@autocommit=0;
-- 执行如下：
mysql> set @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)
在默认的情况下，MySQL中每条SQL语句执行前都会创建一个事务，执行结束后就关闭事务
1566695270031
1566522035599
案例：自动提交事务
- 将账户金额重置为1000
- 更新Jack的账户
- 使用Sqlyog查看数据库：发现数据已经改变
1566282049536
案例：取消自动提交事务
- 查看MySQL是否开启自动提交事务
- 如果是自动提交，就取消自动提交事务
- Jack账户扣款500
- 使用Sqlyog查看数据库，发现数据并没有改变
- 执行commit提交任务
- 使用Sqlyog查看数据库，发现数据已经改变
1566282918868
但是Sqlyog里面看到，Jack账户金额依然是1000
1566282966442
继续，在命令行里提交事务
1566282581122
最后，在Sqlyog里可以看到，Jack的账户金额被修改了。说明，事务被修改成了手动模式
1566283059762
1.3 事务的回滚点和执行原理
什么是回滚点
上面的操作，如果回滚，直接回滚到事务开始前。
有时我们并不需要回滚到最开始的状态，可能只需要回滚到中间的某个位置，就可以设置回滚点
语法
回滚点的操作语句 | 语句 |
设置回滚点 | savepoint 名字 |
回到回滚点 | rollback to 名字 |
应用
- 将账户数据还原到1000
- 开启事务
- 让Jack账号减2次钱，每次10块
- 设置回滚点：savepoint two_times;
- 让Jack账号减2次钱，每次10块
- 回到回滚点：rollback to two_times;
- 最后commit提交事务
-- 6.执行rollback回滚事务
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
-- 7.再次查看回滚后的数据，发现回滚到执行之前的数据情况
mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 1 | Jack | 1000 |
| 2 | Rose | 1000 |
+----+------+---------+
2 rows in set (0.00 sec)
-- 1. 开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
-- 2. 查看当前的account账号
mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 1 | Jack | 1000 |
| 2 | Rose | 1000 |
+----+------+---------+
2 rows in set (0.00 sec)
-- 3.Jack的账号扣500元
mysql> update account set balance = balance - 500 where name='Jack';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 4.Rose的账号增500元
mysql> update account set balance = balance + 500 where name='Rose';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 5.查看当前的account账号数据
mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 1 | Jack | 500 |
| 2 | Rose | 1500 |
+----+------+---------+
2 rows in set (0.00 sec)
-- 6.保存当前的数据为 回滚点 point_one
mysql> savepoint point_one;
Query OK, 0 rows affected (0.00 sec)
-- 7. 再次变更账号数据
mysql> update account set balance = balance - 500 where name='Jack';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update account set balance = balance + 500 where name='Rose';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 1 | Jack | 0 |
| 2 | Rose | 2000 |
+----+------+---------+
2 rows in set (0.00 sec)
-- 8. 将数据回滚到 回滚点 point_one 处
mysql> rollback to point_one;
Query OK, 0 rows affected (0.00 sec)
-- 9. 确认回滚后的数据
mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 1 | Jack | 500 |
| 2 | Rose | 1500 |
+----+------+---------+
2 rows in set (0.01 sec)
-- 10. 提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql>
可以看到，最终Jack账户金额是500，表示虽然转账2次，但是又回滚了1次，因此一共减去了500
执行原理
1566286392652
- 一个用户登录成功以后，服务器会创建一个临时日志文件。日志文件用来保存用户事务状态
- 如果没有使用事务，则所有的操作直接写到数据库中，不会使用日志文件
- 如果开启事务，将所有的写操作写到日志文件中
- 如果这时用户提交了事务，则将日志文件中所有的操作写到数据库中
- 如果用户回滚事务，则日志文件会被清空，不会影响到数据库的操作
1.4 并发访问数据库导致的数据问题
并发访问的问题
因为一张表可以同时有多个用户在访问，相互之间会有影响，会引发以下三种并发访问的问题
并发访问的问题 | 含义 |
脏读 | 一个事务读取到了另一个事务没有提交的数据 |
不可重复读 | 在数据库中一个事务无论查询多少次数据，每次查询的结果应该是一样的， 这叫可以重复读取。如果两次读取数据不同，则称为不可重复读。 通常是一个事务在查询的时候，另一个事务更新了表中数据 |
幻读 | 一个事务在查询表中记录数量的时候， 如果有另一个事务插入或删除了表中的记录。 就会导致这个事务多次查询记录数不同 |
并发访问的问题可以通过数据库隔离级别来解决
1.4.1 事务的隔离级别
1566287181083
隔离级别的特点：
- 级别1最低，级别4最高
- 隔离级别越高，性能越低，安全性越高
MySQL与隔离级别相关的命令
-- 1. 查询事务隔离级别
select @@tx_isolation;
-- 执行如下
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ | -- Mysql默认隔离级别：可重复读
+-----------------+
1 row in set (0.00 sec)
mysql>
-- 2.设置全局事务隔离级别
set global transaction isolation level 四种级别名字;
-- 执行：
mysql> set global transaction isolation level REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)
四种隔离级别起的作用
- Read uncommitted (读未提交)：级别最低，会同时出现三种问题
- Read committed (读已提交)：可以解决脏读的问题
- Repeatable read (可重复读)：可以解决脏读和不可重复读的问题
- Serializable (串行化)：所有的事务都是以串行的方式执行，没有并发执行的事务
1.4.2 脏读
准备数据
-- 创建数据表，账户表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE -- 金额
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('Jack', 1000), ('Rose', 1000);
select * from account;
-- 还原
update account set balance = 1000;
脏读演示
- 首先打开一个命令行窗口Jack：
-- 1. 打开一个命令行Jack，设置全局的隔离级别为最低：读未提交 read uncommitted
set global transaction isolation level read uncommitted;
-- 2. 选择数据库，开启事务
use db1;
start transaction;
-- 3. 更新2个人的账户，未提交
update account set balance = balance - 500 where name = 'Jack';
update account set balance = balance + 500 where name = 'Rose';
image-20210212212442153
- 打开另一个窗口Rose执行：
-- 4. 打开另一个命令行Rose，选择数据库，开启事务
use db1;
start transaction;
-- 5. 查询账户，发现钱已经到账，发货
select * from account;
image-20210212212722268
3.在第一个命令行窗口Jack执行数据回滚，撤销转账的数据；在第二个窗口Rose查看数据，发现钱没了，但是货已经发出去了。
-- 6. 命令行Jack，回滚
rollback;
-- 7. 命令行Rose，查询账户，钱没了
select * from account;
image-20210212212906875
解决办法
-- 1. 打开命令行Jack，设置全局的隔离级别为 read committed
set global transaction isolation level read committed;
-- 2. 重复上面的操作：需要重新登录。命令行Jack在没有提交和回滚之前，Rose账户看到的金额不变
会发现命令行Jack在没有提交和回滚之前，命令行Rose看不到账户发生任何变化
-- 3. 命令行Jack。使用commit提交以后，Rose账户看到的金额变化
命令行Rose，可以看到账户发生了变化
image-20210212213329171
1.4.3 不可重复读
不可重复读演示
1.首先打开一个命令行窗口Jack：
-- 1.将数据进行恢复，并关闭窗口重新登录。
update account set balance=1000;
-- 2.开启一个命令行Jack，确保当前的事务隔离是read committed
select @@tx_isolation;
-- 3.选择数据库，开启一个事务
use db1;
start transaction;
-- 4.查询用户Jack的账户，查到是1000块
select * from account where name='Jack';
image-20210212215958435
2.打开另一个命令行窗口Rose
-- 5.开启另一个命令行Rose，选择数据库，开启一个事务
use db1;
start transaction;
-- 6.更新Jack账户，减500元
update account set balance=balance-500 where name='Jack';
-- 7.提交事务
commit;
image-20210212220213489
3.在命令行Jack再次查询Jack的账户，查到是500块
-- 8.命令行Jack再次查询Jack的账户，查到是500块
select * from account where name='Jack';
-- 9.提交事务
commit;
image-20210212220407232
两次查询输出的结果不同，到底哪次是对的？
解决办法
-- 1. 打开命令行Jack，设置全局的隔离级别为 可以重复读 REPEATABLE READ
set global transaction isolation level REPEATABLE READ;
-- 2. 重复上面的操作：需要重新登录
会发现命令行Rose事务提交后，命令行Jack依然读到1000，表示可以重复读。
重要：也就是保障在一个事务中，只有一个唯一的查询结果。
image-20210212221154062
1.4.4 幻读
幻读演示
- 开启一个命令行Jack窗口，执行：
-- 1.将数据恢复成1000，并关闭窗口重新登录。
update account set balance=1000;
-- 2.开启一个命令行Jack，确保当前的事务隔离是repeatable read
select @@tx_isolation;
-- 3.选择数据库，开启一个事务
use db1;
start transaction;
-- 4.查询id大于1的账户信息，查到1条记录
select * from account where id > 1;
image-20210212221505358
- 开启另一个命令行Rose窗口，执行：
-- 5.开启另一个命令行Rose，选择数据库，开启事务
use db1;
start transaction;
-- 6.新增一条记录
insert into account values (null, 'Tom', 300);
-- 7.提交事务
commit;
image-20210212221615645
3.回到命令行Jack窗口查询数据
-- 8.命令行Jack再次查询id大于1的账户信息，查到1条记录
select * from account where id > 1;
-- 9.修改id大于1的账户金额为200，发现修改了2条记录
update account set balance = 200 where id > 1;
-- 10.再次查询id大于1的账户信息，查到2条记录，出现幻觉
select * from account where id > 1;
image-20210212221843890
查到2条记录，出现幻觉？
解决办法
-- 1. 打开命令行Jack，设置全局的隔离级别为 serializable
set global transaction isolation level serializable;
-- 2. 重复上面的操作：需要重新登录
会发现命令行Rose新增SQL提交后，没有立即执行，而是在等待Jack的事务结束
我们提交了Jack事务后，Rose新增SQL才执行完成，表示目前事务是串行化的
-- 3. 注意：删除刚刚插入的Tom数据
delete from account where name = 'Tom';
image-20210212222400165
