数据库事务

Devops海洋的渔夫

发布于 2021-03-12 13:25:35

7100

发布于 2021-03-12 13:25:35

文章被收录于专栏：Devops专栏

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';