MySQL——事务篇 学习笔记

事务是一组操作的集合，它具有以下关键特性，通常被称为ACID特性：

• 原子性（Atomicity）：事务是一个不可分割的工作单位，要么全部执行成功，要么全部失败回滚，不会处于中间状态。
• 一致性（Consistency）：事务必须使数据库从一个一致性状态转移到另一个一致性状态，事务执行的结果必须是合法的数据库状态。
• 隔离性（Isolation）：并发执行的事务彼此隔离，不会互相干扰。
• 持久性（Durability）：一旦事务提交，其结果就是永久性的，即使发生系统故障也不会丢失。

例子

正常情况： 

假设有一个在线预订机票的系统，用户赵六想要预订一张从北京到上海的机票。这个预订过程可以分为以下三个步骤：

1. 系统检查赵六选择的航班是否有足够的座位。
2. 系统从赵六的银行账户中扣除机票费用。
3. 系统为赵六生成一张机票预订记录。

如果这三个步骤都成功完成，那么赵六的预订过程就是一个成功的事务。赵六的账户被扣款，航班的座位数相应减少，并且赵六收到了机票预订确认。

异常情况： 

在执行上述三个步骤时，如果在执行第二步之后，第三步在生成预订记录时系统出现故障或错误，那么就会发生以下情况：

1. 航班的座位已经被预留。
2. 赵六的银行账户已经被扣款。
3. 机票预订记录未能生成。

在这种情况下，如果没有事务管理，就会导致数据不一致：航班座位减少，但赵六没有得到机票，同时钱也被错误地扣除了。

通过事务管理，如果第三步失败，整个事务会回滚，即：

• 航班的座位数会被重新释放。
• 赵六的银行账户会退还扣款。

这样，要么整个预订过程全部成功，要么全部失败，确保了系统数据的一致性和完整性。

注意：默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐 式的提交事务。 

---

 数据准备

-- 创建航班表
CREATE TABLE flights (
    flight_id INT PRIMARY KEY,
    seat_count INT
);

-- 创建银行账户表
CREATE TABLE bank_accounts (
    account_id INT PRIMARY KEY,
    balance DECIMAL(10, 2)
);

-- 创建预订记录表
CREATE TABLE bookings (
    booking_id INT PRIMARY KEY AUTO_INCREMENT,
    flight_id INT,
    account_id INT,
    amount DECIMAL(10, 2),
    FOREIGN KEY (flight_id) REFERENCES flights(flight_id),
    FOREIGN KEY (account_id) REFERENCES bank_accounts(account_id)
);

-- 插入航班数据
INSERT INTO flights (flight_id, seat_count) VALUES (1234, 150);

-- 插入银行账户数据
INSERT INTO bank_accounts (account_id, balance) VALUES (6789, 5000);

测试正常情况

-- 开始事务
START TRANSACTION;

-- 执行步骤1: 检查航班座位并预留
UPDATE flights SET seat_count = seat_count - 1 WHERE flight_id = 1234 AND seat_count > 0;

-- 执行步骤2: 扣除费用
UPDATE bank_accounts SET balance = balance - 1000 WHERE account_id = 6789 AND balance >= 1000;

-- 执行步骤3: 生成预订记录
INSERT INTO bookings (flight_id, account_id, amount) VALUES (1234, 6789, 1000);

-- 提交事务
COMMIT;

测试异常情况

-- 开始事务
START TRANSACTION;

-- 执行步骤1: 检查航班座位并预留
UPDATE flights SET seat_count = seat_count - 1 WHERE flight_id = 1234 AND seat_count > 0;

-- 执行步骤2: 扣除费用
UPDATE bank_accounts SET balance = balance - 1000 WHERE account_id = 6789 AND balance >= 1000;

-- 假设执行步骤3时发生错误
-- 执行步骤3: 生成预订记录
-- 由于某些原因，这个步骤失败了（比如，外键约束失败，或者系统错误）

-- 回滚事务
ROLLBACK;

 出错后回滚事务，运行后不要提交事务

控制事务一

-- 查看当前自动提交设置
SHOW VARIABLES LIKE 'autocommit';

-- 设置自动提交为 OFF
SET autocommit = OFF;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

控制事务二

-- 开启事务
START TRANSACTION;

-- 执行一系列操作...
-- 例如，更新数据库记录

-- 提交事务
COMMIT;

-- 或者，如果在操作过程中发生错误
-- 回滚事务
ROLLBACK;

并发事务问题

并发事务问题是指在多个事务同时执行时可能出现的各种问题，这些问题可能会导致数据不一致或者违反数据库的一致性约束。以下是三种常见的并发事务问题：脏读、不可重复读和幻读。

脏读（Dirty Read）

脏读发生在当一个事务读取了另一个尚未提交的事务修改过的数据时。

例子：

1. 事务A开始，并读取某行数据。
2. 事务B开始，并修改了事务A刚刚读取的那行数据。
3. 事务A再次读取同一行数据，这次它读取到的是事务B尚未提交的修改后的数据。
4. 事务B回滚，那么事务A之前读取的数据就是无效的（脏数据）。

脏读会导致事务A基于错误的数据做出决策。

不可重复读（Non-Repeatable Read）

不可重复读发生在当事务多次读取同一数据时，由于其他事务的修改操作，导致每次读取的结果不一致。

例子：

1. 事务A开始，并读取某行数据。
2. 事务B开始，并修改了事务A刚刚读取的那行数据，然后提交。
3. 事务A再次读取同一行数据，发现数据已经被修改，与第一次读取的结果不同。

不可重复读会导致事务A在执行过程中数据不一致。

幻读（Phantom Read）

幻读发生在当事务在读取某个范围的数据时，由于其他事务插入新行，导致事务在后续的读取操作中看到了之前未见的行。

例子：

1. 事务A开始，并执行一个查询，返回符合某些条件的行集合。
2. 事务B开始，并插入一条符合事务A查询条件的新行，然后提交。
3. 事务A再次执行相同的查询，发现返回的行集合中包含了事务B插入的新行。

幻读会导致事务A在执行过程中看到不一致的数据范围。

具体场景我观看了教学视频中的模拟操作过程

---

解决并发事务问题的方法

为了解决这些并发事务问题，数据库提供了不同的事务隔离级别：

• READ UNCOMMITTED：允许脏读，最低的隔离级别。
• READ COMMITTED：防止脏读，但不可重复读和幻读仍然可能发生。
• REPEATABLE READ：防止脏读和不可重复读，但幻读仍然可能发生。
• SERIALIZABLE：最高的隔离级别，防止脏读、不可重复读和幻读。

1) 查看事务隔离级别

要查看当前会话的事务隔离级别，可以使用以下 SQL 语句：

SELECT @@TRANSACTION_ISOLATION;

2) 设置事务隔离级别

要设置事务隔离级别，可以使用以下 SQL 语句：

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL
    (READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE);

这里的 SESSION 关键字用于设置当前会话的隔离级别，而 GLOBAL 关键字用于设置全局的隔离级别，影响之后所有新创建的会话。

事务隔离级别越高，数据越安全，但是性能越低。