MySQL 事务

事务

事务：事务是一组操作的集合，该集合中的所有操作被视为一个整体，要么全部执行成功，要么全部执行失败。若任意一个操作执行失败，则事务回滚，所有操作全部撤销。

MySQL 中默认开启事务，但是该事务只是针对一条 SQL 语句，而不是一组。如果想要给一组 SQL 语句开启事务，则需要手动编写 SQL 语句，使用 BEGIN 开启事务，使用 COMMIT 提交事务，使用 ROLLBACK 回滚事务。

事务的操作

操作	描述
SELECT @@AUTOCOMMIT;	查看当前数据库是否自动提交，1 表示自动提交，0 表示手动提交
SET AUTOCOMMIT = 0 / 1;	关闭 / 开启自动提交
BEGIN; / START TRANSACTION;	开启事务
COMMIT;	提交事务
ROLLBACK;	回滚事务

▼
sql
复制代码
CREATE TABLE account
(
    id    INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    name  VARCHAR(10) NOT NULL COMMENT '姓名',
    money INT         NOT NULL COMMENT '余额'
) COMMENT '账户表';

INSERT INTO account(name, money)
VALUES ('张三', 2000),
       ('李四', 2000);

-- 没有事务
-- 转账操作，一共需要三步
-- 1. 查询张三的余额，判断是否大于 1000
SELECT account.money
FROM account
WHERE name = '张三';

-- 2. 将张三的余额减少 1000
UPDATE account
SET money = money - 1000
WHERE name = '张三';

-- 假设程序出现异常，步骤 3 没有执行，那么张三的余额减少 1000，而李四的余额并没有增加 1000

-- 3. 将李四的余额增加 1000
UPDATE account
SET money = money + 1000
WHERE name = '李四';

▼
sql
复制代码
-- MySQL 默认自动提交事务
SELECT @@AUTOCOMMIT;

-- 改为手动提交
-- 此时执行完 SQL 语句后，需要手动执行 COMMIT 命令提交事务
-- 若不执行 COMMIT 命令，即使 SQL 执行了，数据也不会有改动
SET AUTOCOMMIT = 0;

-- 使用事务
-- 转账操作，一共需要三步
-- 1. 查询张三的余额，判断是否大于 1000
SELECT account.money
FROM account
WHERE name = '张三';

-- 2. 将张三的余额减少 1000
UPDATE account
SET money = money - 1000
WHERE name = '张三';

-- 假设程序出现异常，步骤 3 没有执行，那么张三的余额减少 1000，而李四的余额并没有增加 1000

-- 3. 将李四的余额增加 1000
UPDATE account
SET money = money + 1000
WHERE name = '李四';

-- 当全部 SQL 执行成功后，提交事务
COMMIT;

-- 当有任意 SQL 执行失败，回滚事务
ROLLBACK;

▼
sql
复制代码
-- MySQL 默认自动提交事务
SELECT @@AUTOCOMMIT;

-- 改为自动提交
SET AUTOCOMMIT = 1;

-- 使用事务
-- 开启事务，虽然是自动提交，但是开启事务后，MySQL 就不会提交了
START TRANSACTION;

-- 转账操作，一共需要三步
-- 1. 查询张三的余额，判断是否大于 1000
SELECT account.money
FROM account
WHERE name = '张三';

-- 2. 将张三的余额减少 1000
UPDATE account
SET money = money - 1000
WHERE name = '张三';

-- 假设程序出现异常，步骤 3 没有执行，那么张三的余额减少 1000，而李四的余额并没有增加 1000

-- 3. 将李四的余额增加 1000
UPDATE account
SET money = money + 1000
WHERE name = '李四';

-- 当全部 SQL 执行成功后，提交事务
COMMIT;

-- 当有任意 SQL 执行失败，回滚事务
ROLLBACK;

事务四大特性 ACID

1. 原子性（Atomicity）：事务是一个不可分割的工作单位，事务中的操作要么全部完成，要么全部不完成，不会只完成一部分。
2. 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态。一致性状态：指事务执行前后数据库中数据的完整性和正确性。
3. 隔离性（Isolation）：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据，对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
4. 持久性（Durability）：事务执行完成之后，对数据库中数据的改变就是永久的，即数据改变之后，其他事务可以访问这些数据。

并发事务

当多个事务并发执行时，会出现并发问题，比如多个事务同时对同一个数据进行修改，那么就会产生数据不一致的情况。

问题	描述
脏读	B 事务修改了一条数据，但没有提交，此时 A 事务查询该值，读到了 B 事务未提交的数据，然后 B 事务遇到异常回滚了，那么 A 事务查询到的数据就是脏数据
不可重复读	A 事务两次读取到同一个数据，两次结果不一致
幻读	B 事务插入一条数据但没提交，A 事务查询时没有这条数据，但是 A 在插入数据时，B 提交了事务，导致 A 插入数据失败

隔离级别

隔离级别越高越安全，但是并发性能越差。

隔离级别	描述	可能出现问题
READ UNCOMMITTED	读未提交	脏读、不可重复读、 幻读
READ COMMITTED	读已提交	不可重复读、幻读
REPEATABLE READ（默认）	可重复读	幻读
SERIALIZABLE	串行化

1. 查询当前数据库的隔离级别

   SELECT @@TRANSACTION_ISOLATION;

2. 修改数据库的隔离级别

   SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL 隔离级别;

脏读

▼
sql
复制代码
-- 查询当前的隔离级别
SELECT @@TRANSACTION_ISOLATION;
-- REPEATABLE-READ

-- 脏读
-- 1. 使用命令提示符打开两个窗口并登录
-- A: 设置隔离界别为 读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- A: 开启事务
START TRANSACTION;

-- A: 查询账户表数据
SELECT *
FROM account;

-- B: 开启事务
START TRANSACTION;

-- B: 修改数据
UPDATE account
SET money = money - 1000
WHERE name = '张三';

-- A: 再次查询账户表数据
-- 虽然 B 未提交事务，但是已经能查询出 B 事务更新后的数据
SELECT *
FROM account;

-- 设置隔离级别为 读已提交，重复上述操作，可以解决脏读问题
-- 只有当 B 提交事务后，A 才能查询到 B 修改后的数据，但是这会引发不可重复读问题
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

不可重复读

▼
sql
复制代码
-- 不可重复读取
-- 1. 使用命令提示符打开两个窗口并登录
-- A: 设置隔离界别为 读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- A: 开启事务
START TRANSACTION;

-- A: 查询账户表数据
SELECT *
FROM account;

-- B: 开启事务
START TRANSACTION;

-- B: 修改数据
UPDATE account
SET money = money - 1000
WHERE name = '张三';

-- B: 提交事务
COMMIT;

-- A: 再次查询账户表数据
-- 因为 B 的修改已经提交，所以 A 可以查询到 B 的修改
SELECT *
FROM account;

-- 设置隔离级别为 可重复读，重复上述操作，可以解决不可重复读问题
-- 在事务 A 中，永远读取的是事务 A 中查询的数据，不会被事务 B 改变，但是这会引发幻读问题
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

幻读

▼
sql
复制代码
-- 幻读
-- 1. 使用命令提示符打开两个窗口并登录
-- A: 设置隔离界别为 可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- A: 开启事务
START TRANSACTION;

-- A: 查询账户表数据
SELECT *
FROM account;

-- B: 开启事务
START TRANSACTION;

-- B: 修改数据
INSERT INTO account
VALUES (3, '王五', 2000);

-- B: 提交事务
COMMIT;

-- A: 再次查询账户表数据，但是因为不可重复读的原因，A 无法查询到 id 为 3 的数据
SELECT *
FROM account;

-- A: 也插入 id 为 3 的数据
-- B 已经插入了数据，但是因为不可重复读的原因导致 A 并不知道 B 插入了数据
-- 所以此时 A 会插入失败
INSERT INTO account
VALUES (3, '王五', 2000);

-- 设置隔离级别为 串行化，重复上述操作，可以解决幻读问题
-- 如果事务 A 一直未结束，则其他事务无法操作表数据，必须等待事务 A 结束后才能继续执行，一次会导致并发效率低问题
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;