一、事务的应用场景说明
什么是事务:
在实际的开发过程中,一个业务操作如:转账,往往是要多次访问数据库才能完成的。转账是一个用户扣钱,另一个用户加钱。如果其中有一条 SQL 语句出现异常,这条 SQL 就可能执行失败。
事务执行是一个整体,所有的 SQL 语句都必须执行成功。如果其中有 1 条SQL 语句出现异常,则所有的SQL 语句都要回滚,整个业务执行失败。
- 转账的操作
-- 创建数据表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10),
balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000);
模拟张三给李四转 500 元钱,一个转账的业务操作最少要执行下面的 2 条语句:
张三账号-500
李四账号+500
-- 张三账号-500
update account set balance = balance - 500 where name='张三';
-- 李四账号+500
update account set balance = balance + 500 where name='李四';
假设当张三账号上-500 元,服务器崩溃了。李四的账号并没有+500 元,数据就出现问题了。我们需要保证其中一条 SQL 语句出现问题,整个转账就算失败。只有两条 SQL 都成功了转账才算成功。这个时候就需要用到事务。
二、手动提交事务
MYSQL 中可以有两种方式进行事务的操作:
(1) 手动提交事务
(2) 自动提交事务
①手动提交事务的 SQL 语句
功能 | SQL 语句 |
---|---|
开启事务 | start transaction; |
提交事务 | commit; |
回滚事务 | rollback; |
手动提交事务使用过程:
-
执行成功的情况: 开启事务 → 执行多条 SQL 语句 →成功提交事务
-
执行失败的情况: 开启事务 →执行多条 SQL 语句 → 事务的回滚
案例演示 1:事务提交
模拟张三给李四转 500 元钱(成功) 目前数据库数据如下:
- 使用 DOS 控制台进入 MySQL
- 执行以下 SQL 语句: 1.开启事务, 2.张三账号-500, 3.李四账号+500
- 使用 SQLYog 查看数据库:发现数据并没有改变
- 在控制台执行 commit 提交事务:
- 使用 SQLYog 查看数据库:发现数据改变
案例演示 2:事务回滚
模拟张三给李四转 500 元钱(失败) 目前数据库数据如下:
- 在控制台执行以下 SQL 语句:1.开启事务, 2.张三账号-500
- 使用 SQLYog 查看数据库:发现数据并没有改变
- 在控制台执行 rollback 回滚事务:
- 使用 SQLYog 查看数据库:发现数据没有改变
总结: 如果事务中 SQL 语句没有问题,commit 提交事务,会对数据库数据的数据进行改变。 如果事务中 SQL
语句有问题,rollback 回滚事务,会回退到开启事务时的状态。
②自动提交事务
MySQL 默认每一条 DML(增删改)语句都是一个单独的事务,每条语句都会自动开启一个事务,语句执行完毕自动提交事务,MySQL 默认开始自动提交事务
案例演示 3:自动提交事务
- 将金额重置为 1000
- 更新其中某一个账户
- 使用 SQLYog 查看数据库:发现数据已经改变
取消自动提交
- 查看 MySQL 是否开启自动提交事务
@@表示全局变量,1 表示开启,0 表示关闭
- 取消自动提交事务
- 执行更新语句,使用 SQLYog 查看数据库,发现数据并没有改变
- 在控制台执行 commit 提交任务
三、事务原理
事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表中,其他任何情况都会清空事务日志(rollback,断开连接)
5.4.1 原理图:
5.4.2 事务的步骤:
- 客户端连接数据库服务器,创建连接时创建此用户临时日志文件
- 开启事务以后,所有的操作都会先写入到临时日志文件中
- 所有的查询操作从表中查询,但会经过日志文件加工后才返回
- 如果事务提交则将日志文件中的数据写到表中,否则清空日志文件。
四、回滚点
(一)什么是回滚点
在某些成功的操作完成之后,后续的操作有可能成功有可能失败,但是不管成功还是失败,前面操作都已经成功,可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置,而不是返回所有操作,这个点称之为回滚点。
(二)回滚点的操作语句
回滚点的操作语句 | 语句 |
---|---|
设置回滚点 | savepoint 名字 |
回到回滚点 | rollback to 名字 |
(三)具体操作:
- 将数据还原到 1000
- 开启事务
- 让张三账号减 3 次钱,每次 10 块
- 设置回滚点:savepoint three_times;
- 让张三账号减 4 次钱,每次 10 块
- 回到回滚点:rollback to three_times;
- 分析执行过程
总结:设置回滚点可以让我们在失败的时候回到回滚点,而不是回到事务开启的时候。
六、事务的隔离级别
(一)事务的四大特性 ACID
事务特性 | 含义 |
---|---|
原子性(Atomicity) | 每个事务都是一个整体,不可再拆分,事务中所有的 SQL 语句要么都执行成功, 要么都失败。 |
一致性(Consistency) | 事务在执行前数据库的状态与执行后数据库的状态保持一致。如:转账前2 个人的 总金额是 2000,转账后2 个人总金额也是 2000 |
隔离性(Isolation) | 事务与事务之间不应该相互影响,执行时保持隔离的状态。 |
持久性(Durability) | 一旦事务执行成功,对数据库的修改是持久的。就算关机,也是保存下来的。 |
(二)事务的隔离级别
**并发访问的问题 ** | 含义 |
---|---|
脏读 | 一个事务读取到了另一个事务中尚未提交的数据 |
不可重复读 | 一个事务中两次读取的数据内容不一致,要求的是一个事务中多次读取时数据是一致的,这 是事务 update 时引发的问题 |
幻读 | 一个事务中两次读取的数据的数量不一致,要求在一个事务多次读取的数据的数量是一致 的,这是 insert 或 delete 时引发的问题 |
(三)MySQL 数据库有四种隔离级别
上面的级别最低,下面的级别最高。“是”表示会出现这种问题,“否”表示不会出现这种问题。
级别 | 名字 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | 数据库默认隔离级别 |
---|---|---|---|---|---|---|
1 | 读未提交 | read uncommitted | 是 | 是 | 是 | |
2 | 读已提交 | read committed | 否 | 是 | 是 | Oracle 和 SQL Server |
3 | 可重复读 | repeatable read | 否 | 否 | 是 | MySQL |
4 | 串行化 | serializable | 否 | 否 | 否 |
隔离级别越高,性能越差,安全性越高。
(四)MySQL 事务隔离级别相关的命令
查询隔离级别 select @@tx_isolation;
-
查询全局事务隔离级别
-
设置事务隔离级别,需要退出 MySQL 再重新登录才能看到隔离级别的变化
设置隔离级别 set global transaction isolation level 级别字符串;
(五)脏读的演示
将数据进行恢复:UPDATE account SET balance = 1000;
- 打开 A 窗口登录 MySQL,设置全局的隔离级别为最低
mysql -uroot -proot
set global transaction isolation level read uncommitted;
2. 打开 B 窗口,AB 窗口都开启事务
use day23;
start transaction;
3. A 窗口更新 2 个人的账户数据,未提交
update account set balance=balance-500 where id=1; update account set balance=balance+500
- B 窗口查询账户
select * from account;
5. A 窗口回滚
rollback;
6. B 窗口查询账户,钱没了
脏读非常危险的,比如张三向李四购买商品,张三开启事务,向李四账号转入 500 块,然后打电话给李四说钱已经转了。李四一查询钱到账了,发货给张三。张三收到货后回滚事务,李四的再查看钱没了。
解决脏读的问题:将全局的隔离级别进行提升将数据进行恢复:
UPDATE account SET balance = 1000;
- 在 A 窗口设置全局的隔离级别为 read committed
set global transaction isolation level read committed;
B 窗口退出 MySQL,B 窗口再进入 MySQL
AB 窗口同时开启事务
- A 更新 2 个人的账户,未提交
update account set balance=balance-500 where id=1;
update account set balance=balance+500
- B 窗口查询账户
A 窗口 commit 提交事务
- B 窗口查看账户
结论:read committed 的方式可以避免脏读的发生
(六)不可重复读的演示
UPDATE account SET balance = 1000;
将数据进行恢复:
- 开启 A 窗口
set global transaction isolation level read committed;
2. 开启 B 窗口,在 B 窗口开启事务
start transaction;
select * from account;
3. 在 A 窗口开启事务,并更新数据
start transaction;
update account set balance=balance+500 where id=1;
commit;
- B 窗口查询
select * from account;
两次查询输出的结果不同,到底哪次是对的?不知道以哪次为准。 很多人认为这种情况就对了,无须困惑, 当然是后面的为准。我们可以考虑这样一种情况,比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户,结果在一个事务中针对不同的输出目的地进行的两次查询不一致,导致文件和屏幕中的结果不一致,银行工作人员就不知道以哪个为准了。
解决不可重复读的问题:
将全局的隔离级别进行提升为:repeatable read
UPDATE account SET balance = 1000;
将数据进行恢复:
- A 窗口设置隔离级别为:repeatable read
set global transaction isolation level repeatable read;
2. B 窗口退出 MySQL,B 窗口再进入 MySQL
start transaction;
select * from account;
- A 窗口更新数据
start transaction;
update account set balance=balance+500 where id=1;
commit;
4. B 窗口查询
select * from account;
结论:同一个事务中为了保证多次查询数据一致,必须使用 repeatable read 隔离级别
(七)幻读的演示
在 MySQL 中无法看到幻读的效果。
UPDATE account SET balance = 1000;
但我们可以将事务隔离级别设置到最高,以挡住幻读的发生 将数据进行恢复:
- 开启 A 窗口
set global transaction isolation level serializable; -- 设置隔离级别为最高
- A 窗口退出 MySQL,A 窗口重新登录 MySQL
start transaction;
select count(*) from account;
3. 再开启 B 窗口,登录 MySQL
- 在 B 窗口中开启事务,添加一条记录
start transaction; -- 开启事务
insert into account (name,balance) values ('LaoWang', 500);
5. 在 A 窗口中 commit 提交事务,B 窗口中 insert 语句会在 A 窗口事务提交后立马运行
6. 在 A 窗口中接着查询,发现数据不变
select count(*) from account;
- B 窗口中 commit 提交当前事务
- A 窗口就能看到最新的数据
结论:使用 serializable 隔离级别,一个事务没有执行完,其他事务的 SQL 执行不了,可以挡住幻读