4、数据库事务
4.1 什么是事务
事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有一条SQL出现异常,整个操作就会回滚,整个业务执行失败
比如: 银行的转账业务,张三给李四转账500元 , 至少要操作两次数据库, 张三 -500, 李四 + 500,这中间任何一步出现问题,整个操作就必须全部回滚, 这样才能保证用户和银行都没有损失。
- 回滚
即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，滚回到事务开始时的状态。（在提交之前执行）
4.2 模拟转账操作
- 创建 账户表
-- 创建账户表
CREATE TABLE account(
-- 主键
id INT PRIMARY KEY AUTO_INCREMENT,
-- 姓名
NAME VARCHAR(10),
-- 余额
money DOUBLE
);
-- 添加两个用户
INSERT INTO account (NAME, money) VALUES ('tom', 1000), ('jack', 1000);
- 模拟tom 给 jack 转 500 元钱，一个转账的业务操作最少要执行下面的 2 条语句：
-- tom账户 -500元
UPDATE account SET money = money - 500 WHERE NAME = 'tom';
-- jack账户 + 500元
UPDATE account SET money = money + 500 WHERE NAME = 'jack';
注:
假设当tom 账号上 -500 元,服务器崩溃了。jack 的账号并没有+500 元，数据就出现问题了。
我们要保证整个事务执行的完整性,要么都成功, 要么都失败. 这个时候我们就要学习如何操作事务。
4.3 MySQL事务操作
- MYSQL 中可以有两种方式进行事务的操作：
手动提交事务
自动提交事务
4.3.1 手动提交事务
| 功能 | 语句 |
|---|---|
| 开启事务 | start transaction; 或者 BEGIN; |
| 提交事务 | commit; |
| 回滚事务 | rollback; |
START TRANSACTION
这个语句显式地标记一个事务的起始点。
COMMIT
表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写 到磁盘上的物理数据库中，事务正常结束。
ROLLBACK
表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中 对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态
- 执行成功的情况： 开启事务 -> 执行多条 SQL 语句 -> 成功提交事务
- 执行失败的情况： 开启事务 -> 执行多条 SQL 语句 -> 事务的回滚
成功案例 演示
USE db2;
1 开启事务
start transaction;
2 tom账户 -500
update account set money = money - 500 where name = 'tom';
3 jack账户 +500
update account set money = money + 500 where name = 'jack';
4 此时我们使用 sqlYog查看表,发现数据并没有改变
5 在控制台执行 commit 提交事务
commit;
6 再次使用sqlYog查看, 发现数据在事务提交之后,发生改变
事务回滚演示
如果事务中，有某条sql语句执行时报错了，我们没有手动的commit，那整个事务会自动回滚
- 命令行 开启事务
start transaction;
- 插入两条数据
INSERT INTO account VALUES(NULL,'张百万',3000);
INSERT INTO account VALUES(NULL,'有财',3500);
不去提交事务 直接关闭窗口,发生回滚操作,数据没有改变
如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。 如果事务中 SQL 语句有问题，rollback 回滚事务，会回退到开启事务时的状态。
4.3.2 自动提交事务
- MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕 自动提交事务，MySQL 默认开始自动提交事务
- MySQL默认是自动提交事务
自动提交事务演示
- 将tom账户金额 +500元
取消自动提交
1 登录mysql，查看autocommit状态。
SHOW VARIABLES LIKE 'autocommit';
2 把 autocommit 改成 off;
SET @@autocommit=off;
3 再次修改,需要提交之后才生效
-- 选择数据库
use db2;
-- 修改数据
update account set money = money - 500 where name = 'jack';
-- 手动提交
commit;
4.4 事务的四大特性
| 特性 | 含义 |
|---|---|
| 原子性 | 每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功， 要么都失败。 |
| 一致性 | 事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2个人的 总金额是 2000，转账后 2 个人总金额也是 2000. |
| 隔离性 | 事务与事务之间不应该相互影响，执行时保持隔离的状态. |
| 持久性 | 一旦事务执行成功，对数据库的修改是持久的。就算关机，数据也是要保存下来的. |
4.5 MySQL 事务隔离级别(了解)
4.5.1 数据并发访问
一个数据库可能拥有多个访问客户端,这些客户端都可以并发方式访问数据库. 数据库的相同数据可能被多个事务同时访问,如果不采取隔离措施,就会导致各种问题, 破坏数据的完整性
4.5.2 并发访问会产生的问题
事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个 数据。可能引发并发访问的问题
并发访问的问题
- 脏读： 一个事务读取到了另一个事务中尚未提交的数据。
- 不可重复读：一个事务中两次读取的数据内容不一致，要求的是在一个事务中多次读取时数据是一致的。 这是进行 update 操作时引发的问题
- 幻读 ：一个事务中,某一次的 select 操作得到的结果所表征的数据状态， 无法支撑后续的业务操作. 查询得到的数据状态不准确，导致幻读。
4.5.3 四种隔离级别
通过设置隔离级别,可以防止上面的三种并发问题.
MySQL数据库有四种隔离级别 上面的级别最低，下面的级别最高。
✔ 会出现问题
✘ 不会出现问题
4.5.4 隔离级别相关命令
- 查看隔离级别
select @@tx_isolation;
- 设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化
set global transaction isolation level 级别名称;
read uncommitted 读未提交
read committed 读已提交
repeatable read 可重复读
serializable 串行化
例如: 修改隔离级别为 读未提交
set global transaction isolation level read uncommitted;
4.6 隔离性问题演示
4.6.1 脏读演示
脏读: 一个事务读取到了另一个事务中尚未提交的数据
use db2;
设置隔离级别为最低 读未提交
set global transaction isolation level read uncommitted;
关闭窗口,开一个新的窗口A ,再次查询隔离级别
再开启一个新的窗口 B
开启事务
start transaction;
查询
select * from account;
A窗口执行
开启事务
start transaction;
执行操作
-- tom账户 -500元
UPDATE account SET money = money - 500 WHERE NAME = 'tom';
-- jack账户 + 500元
UPDATE account SET money = money + 500 WHERE NAME = 'jack';
B 窗口查询数据
select * from account;
A窗口转账异常,进行回滚
rollback;
B 窗口再次查询 账户
select * from account;
4.6.2 解决脏读问题
- 脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入 500 块，然后打电话给李四说钱 已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。
解决方案
将全局的隔离级别进行提升为: read committed
1 在 A 窗口设置全局的隔离级别为 read committed
set global transaction isolation level read committed;
2. 重新开启A窗口, 查看设置是否成功
select @@tx_isolation;
3 开启B 窗口, A 和 B 窗口选择数据库后, 都开启事务
4 A 窗口 只是更新两个人的账户, 不提交事务
-- tom账户 -500元
UPDATE account SET money = money - 500 WHERE NAME = 'tom';
-- jack账户 + 500元
UPDATE account SET money = money + 500 WHERE NAME = 'jack';
5 B 窗口进行查询,没有查询到未提交的数据
mysqlselect * from account;
6 A窗口commit提交数据
commit;
7 B 窗口查看数据
select * from account;
4.6.3 不可重复读演示
- 不可重复读: 同一个事务中,进行查询操作,但是每次读取的数据内容是不一样的
1 打开两个 窗口A 和 窗口B,选择数据库后 开启事务
2 B 窗口开启事务后, 先进行一次数据查询
3 在 A 窗口开启事务后，将用户tom的账户 + 500 ,然后提交事务
-- 修改数据
update account set money = money + 500 where name = 'tom';
-- 提交事务
commit;
- 两次查询输出的结果不同，到底哪次是对的？
不知道以哪次为准。 很多人认为这种情况就对了，无须困惑， 当然是后面的为准。
我们可以考虑这样一种情况:
比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客 户，结果在一个事中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作 人员就不知道以哪个为准了
4.6.4 解决不可重复读问题
将全局的隔离级别进行提升为： repeatable read
1 打开A 窗口, 设置隔离级别为：repeatable read
-- 查看事务隔离级别
select @@tx_isolation;
-- 设置事务隔离级别为 repeatable read
set global transaction isolation level repeatable read;
2 重新开启 A,B 窗口 选择数据库 ,同时开启事务
3 B 窗口事务 先进行第一次
4 A 窗口更新数据, 然后提交事务
-- 修改数据
update account set money = money + 500 where name = 'tom';
-- 提交事务
commit;
5 B 窗口 再次查询
- 同一个事务中为了保证多次查询数据一致，必须使用 repeatable read 隔离级别
4.6.5 幻读演示
幻读: select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，
无法插入，此时就发生了幻读
1 打开 A B 窗口, 选择数据库 开启事务
2 A 窗口 先执行一次查询操作
-- 假设要再添加一条id为3的 数据,在添加之前先判断是否存在
select * from account where id = 3;
3 B 窗口 插入一条数据 提交事务
INSERT INTO account VALUES(3,'lucy',1000);
commit;
4 A 窗口执行 插入操作, 发现报错. 出现幻读
我刚才读到的结果应该可以支持我这样操作才对啊，为什么现在不可以
4.6.6 解决幻读问题
- 将事务隔离级别设置到最高 SERIALIZABLE ，以挡住幻读的发生
如果一个事务，使用了SERIALIZABLE——可串行化隔离级别时，在这个事务没有被提交之前 , 其他的线程，只能等到当前操作完成之后，才能进行操作，这样会非常耗时，而且，影响数据库的性能，数据库不会使用这种隔离级别
1 打开A 窗口 将数据隔离级别提升到最高
set global transaction isolation level SERIALIZABLE;
2 打开 A B 窗口, 选择数据库 开启事务
3 A 窗口 先执行一次查询操作
SELECT * FROM account WHERE id = 3;
4 B 窗口插入一条数据
INSERT INTO account VALUES(3,'lucy',1000);
5 A 窗口执行 插入操作, 提交事务 数据插入成功。
INSERT INTO account VALUES(3,'lucy',1000);
commit;
6 B 窗口在 A窗口提交事务之后, 再执行,但是主键冲突出现错误
总结:
serializable 串行化可以彻底解决幻读,但是 事务只能排队执行,严重影响效率,数据库不会使用这种隔离级别
扫一扫
420
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
