（九）MySQL学习笔记——事务

一、事务的概念

• 事务：多条SQL语句组成一个执行单元，这些语句要么都执行成功，要么都执行失败。
• 如果都执行成功则此事务执行成功；如果有一条执行失败则导致整个事务所有语句执行失败，整个事务回滚到执行前的状态。

二、如何使用事务

1.数据准备

首先设定场景——银行转账。简历一张account表存放账户信息。

-- 数据准备:创建银行账户信息表
CREATE DATABASE db10;
USE db10;
CREATE TABLE account(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    money DOUBLE
);
-- 插入数据
INSERT INTO account VALUES (NULL,'小李',2000),
                           (NULL,'小王',1000),
                           (NULL,'小赵',5000);

在不使用事务的情况下，现在进行转账操作：

--  现有需求：小王给小李转账500元。
-- 如果不使用事务：
UPDATE account SET money = money - 500 WHERE name='小王';
UPDATE account SET money = money + 500 WHERE name='小李';
-- 如果第二句语句因为某些原因执行失败，那将导致小王的账户减少500，而小李的账户没有增加。

2.使用事务

1. 先开启事务，记录回滚点，此时即将执行一组操作，要么同时成功，要么同时失败。
2. 开始执行多条SQL语句。
3. 结束事务：要么都成功，则提交事务；要么都失败，则回滚事务。

-- 使用事务:
START TRANSACTION; -- 先开启事务
-- 执行语句
UPDATE account SET money = money - 500 WHERE name='小王';
UPDATE account SET money = money + 500 WHERE name='小李';
SELECT * FROM account;
ROLLBACK; -- 有失败语句则 回滚事务
COMMIT; -- 都执行成功则 提交事务

三、事务的提交方式

事务的提交方式有两种：

1. 自动提交（默认方式）
2. 手动提交：执行完SQL语句后需要手动进行事务提交，不提交则不存储更改。

-- 事务的提交方式
-- 查询当前事务的提交方式:0为手动提交；1为自动提交
SELECT @@AUTOCOMMIT;
-- 修改事务的提交方式：
SET @@AUTOCOMMIT=1;

四、事务的四大特征（ACID）

1. 原子性（Atomicity）：事务包含的操作要么全部成功，成功后完全应用到数据库；要么全部失败，失败后不能对执行前的数据库造成任何影响。
2. 一致性（Consistency）：事务必须使数据库从一个一致性状态变到另一个一致性状态。即事务执行前和执行后都必须处于一致性状态。例如：银行转账要保证转账前后总金额不能变。
3. 隔离性（Isolcation）：当多个用户并发访问数据库时，例如操作同一张表时，数据库为每一个用户单独开启事务，事务之间没有干扰，并发事务之间相互隔离。分为不同的隔离级别。
4. 持久性（Durability）：事务一旦提交，对数据库中的数据改变是永久性的。

五、事务的隔离级别

1.隔离级别概念

当事务并行时，为了保证各个事务之间是隔离的、独立的，不能相互影响，所以需要设置不同的隔离级别，保证事务之间的独立性。

2.四种隔离级别

隔离级别名称	级别代码	可能引发问题
读未提交	read uncommitted	脏读、不可重复读、幻读
读已提交	read committed	不可重复读、幻读
可重复读（默认级别）	repeatable read	幻读
串行化	serializable	无

自上而下隔离级别越来越高，数据越来越安全，但效率越来越低。

3.不同隔离级别可能引发的问题

问题	现象
脏读	在一个事务处理过程中读取了另一个未提交的事务中的数据 , 导致两次查询结果不一致
不可重复读	是指在一个事务处理过程中读取了另一个事务中修改并已提交的数据, 导致两次查询结果不一致
幻读	①查询某记录是否存在，不存在时准备插入此记录，但执行 insert 插入时发现此记录已存在，无法插入。②查询某数据发现不存在，此时执行删除操作，却发现删除成功

4.查询及修改事务隔离级别

-- 查询事务的隔离级别
SELECT @@TX_ISOLATION; -- MySQL8.0之前可用
SELECT @@TRANSACTION_ISOLATION; -- MqSQL8.0之后用这个

-- 修改数据库隔离级别，重新连接数据库刷新
SET GLOBAL TRANSACTION ISOLATION LEVEL 级别名;

5.脏读、不可重复读、幻读问题测试

5.1 脏读问题测试

• 隔离级别为读未提交，会产生脏读问题。

-- 1.读未提交级别 产生 脏读 问题
-- 修改数据库隔离级别为read uncommitted（读未提交）
SET GLOBAL TRANSACTION ISOLATION LEVEL read uncommitted;
-- 当前级别是read uncommitted

SELECT * FROM account;
START TRANSACTION; -- 开启事务，开始转账

UPDATE account SET money = money - 500 WHERE name='小王';
UPDATE account SET money = money + 500 WHERE name='小李';

-- 此时事务1转账已完成，但还未提交。此时事务2查询转账结果，查到转账后的结果,此时出现脏读。

ROLLBACK; -- 事务1回滚事务，事务2之前查到的数据是不正确的。

5.2 不可重复读问题测试

• 隔离级别为读已提交，将产生不可重复读问题。

-- 2.读已提交级别 产生 不可重复读 问题
-- 修改数据库隔离级别为read committed
SET GLOBAL TRANSACTION ISOLATION LEVEL read committed;
-- 当前级别是read committed

SELECT * FROM account;
START TRANSACTION; -- 开启事务，开始转账

UPDATE account SET money = money - 500 WHERE name='小王';
UPDATE account SET money = money + 500 WHERE name='小李';

-- 此时事务1转账已完成，但还未提交。此时事务2查询转账结果，查到转账前的结果。

COMMIT; -- 事务1提交事务后，事务2再次查询转账结果，查到转账后的结果，两次结果不一致，出现不可重复读。

• 隔离级别设为可重复读，解决不可重复读问题。

-- 3.可重复读级别 解决了 不可重复读 问题
-- 修改数据库隔离级别为repeatable read（可重复读、默认级别）
SET GLOBAL TRANSACTION ISOLATION LEVEL repeatable read;
-- 当前级别是repeatable read（默认级别），解决 不可重复读 问题

SELECT * FROM account;
START TRANSACTION; -- 事务1和2都开启事务。
-- 事务1开始转账
UPDATE account SET money = money - 500 WHERE name='小王';
UPDATE account SET money = money + 500 WHERE name='小李';

-- 此时事务1转账已完成，但还未提交。此时事务2查询转账结果，查到 转账前 的结果。

COMMIT; -- 事务1提交事务后，事务2再次查询转账结果，查到的还是 转账前 的结果。

-- 当事务2提交后再次查询，查到的是 转账后 的结果，避免了 不可重复读 问题。

5.3 幻读问题测试

• 隔离级别为可重复读，产生幻读问题。

-- 4.可重复读级别 产生 幻读 问题
-- 当前级别是repeatable read（可重复读、默认级别）

SELECT * FROM account;
START TRANSACTION; -- 首先，事务1和2都开启事务。
-- 然后，事务1插入一条新数据——编号为4的小孙。
INSERT INTO account VALUES (4,'小孙',6000);

-- 此时事务1插入数据已完成，但还未提交，此时能查到新插入的数据。此时事务2查询表，查到 插入新数据前 的结果，于是事务2想插入这条新数据
-- 但是事务2此时会卡死，无法插入数据。
COMMIT; -- 事务1提交事务后，事务2直接报错：[23000][1062] Duplicate entry '4' for key 'account.PRIMARY'
-- 事务2很疑惑：明明没有这条数据，为何我添加不进去呢？

-- 当事务2提交后再次查询，查到的是 插入新数据后 的结果...出现幻觉了？

• 隔离级别设为串行化，解决幻读问题。

-- 5.串行化解决了 幻读 问题
-- 修改数据库隔离级别为 serializable（串行化）
SET GLOBAL TRANSACTION ISOLATION LEVEL serializable;
-- 当前级别是：serializable（串行化）

SELECT * FROM account;
START TRANSACTION; -- 首先，事务1和2都开启事务。
-- 然后，事务1插入一条新数据——编号为4的小孙。
INSERT INTO account VALUES (4,'小孙',6000);

-- 此时事务1插入数据已完成，能查到新插入的数据，但还未提交。此时事务2查询表，会卡死，无法查询数据。
COMMIT; -- 事务1提交事务后，事务2立刻就查到了新插入的数据。