SQL 事务

事务是数据库管理系统的基本概念，用于保证数据的一致性和完整性。本章将详细介绍事务的原理和使用方法。

什么是事务？

事务（Transaction）是一组数据库操作的逻辑单元，这些操作要么全部成功，要么全部失败。

为什么需要事务？

考虑银行转账场景：

-- 账户 A 向账户 B 转账 100 元
-- 这涉及两个操作：
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';  -- 1. A 账户扣款
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';  -- 2. B 账户入账

-- 如果第一条成功，第二条失败会怎样？
-- A 账户少了 100 元，B 账户却没有收到
-- 数据不一致！

-- 使用事务可以保证两个操作要么都成功，要么都失败

事务的 ACID 特性

事务具有四个关键特性，简称 ACID：

1. 原子性（Atomicity）

事务中的所有操作要么全部完成，要么全部回滚：

START TRANSACTION;

-- 执行多个操作
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';

-- 如果任何操作失败，所有操作都会回滚
COMMIT;  -- 提交事务，所有操作生效
-- 或
ROLLBACK;  -- 回滚事务，所有操作撤销

2. 一致性（Consistency）

事务必须使数据库从一个一致状态转换到另一个一致状态：

-- 转账前后，两个账户的总金额应该不变
-- 转账前：A 有 500，B 有 300，总计 800
-- 转账后：A 有 400，B 有 400，总计 800

-- 约束检查也会保证一致性
CREATE TABLE accounts (
    id VARCHAR(10) PRIMARY KEY,
    balance DECIMAL(10, 2) CHECK (balance >= 0)  -- 余额不能为负
);

-- 如果转账导致余额为负，事务会因为约束违反而回滚

3. 隔离性（Isolation）

多个并发事务之间互不干扰：

-- 事务 A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 'A';  -- 读取余额为 500
-- 此时事务 B 修改了余额
-- 事务 A 再次读取
SELECT balance FROM accounts WHERE id = 'A';  -- 读取结果取决于隔离级别
COMMIT;

4. 持久性（Durability）

事务一旦提交，对数据的修改就是永久的：

-- 事务提交后，即使数据库崩溃，数据也不会丢失
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
COMMIT;  -- 提交后，修改被持久化到磁盘

-- 数据库通过日志（redo log）保证持久性

事务基本操作

开始事务

-- 方式 1：使用 START TRANSACTION
START TRANSACTION;

-- 方式 2：使用 BEGIN
BEGIN;

-- 方式 3：禁用自动提交
SET autocommit = 0;
-- 执行 SQL 语句...
COMMIT;
SET autocommit = 1;  -- 恢复自动提交

提交事务

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';

COMMIT;  -- 提交事务，永久保存更改

回滚事务

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 'A';

-- 发现问题，需要撤销
ROLLBACK;  -- 回滚事务，撤销所有更改

保存点（Savepoint）

在事务中设置保存点，可以部分回滚：

START TRANSACTION;

INSERT INTO orders (id, customer_id) VALUES (1, 100);
SAVEPOINT order_created;

INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 'P1', 2);
SAVEPOINT items_added;

INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 'P2', 1);
-- 发现商品 P2 缺货，需要撤销这条插入

ROLLBACK TO items_added;  -- 回滚到保存点

COMMIT;  -- 提交事务，只有第一个商品被添加

事务隔离级别

隔离级别概述

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	✓	✓	✓
READ COMMITTED	✗	✓	✓
REPEATABLE READ	✗	✗	✓
SERIALIZABLE	✗	✗	✗

问题解释：

脏读（Dirty Read）：读取到其他事务未提交的数据
不可重复读（Non-repeatable Read）：同一事务中两次读取结果不同（其他事务修改了数据）
幻读（Phantom Read）：同一事务中两次读取结果行数不同（其他事务插入或删除了数据）

READ UNCOMMITTED（读未提交）

最低隔离级别，可能读取到其他事务未提交的数据：

-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 事务 A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
-- 未提交...

-- 事务 B（可以读取到未提交的数据）
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 'A';  -- 可能读取到修改后的值
COMMIT;

-- 如果事务 A 回滚，事务 B 读取的就是脏数据

READ COMMITTED（读已提交）

只能读取其他事务已提交的数据：

-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 事务 A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 'A';  -- 读取余额：500

-- 事务 B（修改并提交）
START TRANSACTION;
UPDATE accounts SET balance = 400 WHERE id = 'A';
COMMIT;

-- 事务 A 再次读取
SELECT balance FROM accounts WHERE id = 'A';  -- 读取余额：400（已改变）
COMMIT;

-- 这就是"不可重复读"问题

REPEATABLE READ（可重复读）

保证同一事务中多次读取结果相同：

-- 设置隔离级别（MySQL 默认）
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 事务 A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 'A';  -- 读取余额：500

-- 事务 B（修改并提交）
START TRANSACTION;
UPDATE accounts SET balance = 400 WHERE id = 'A';
COMMIT;

-- 事务 A 再次读取
SELECT balance FROM accounts WHERE id = 'A';  -- 读取余额：500（不变）
COMMIT;

-- MySQL 的 InnoDB 通过 MVCC 和 Next-Key Lock 解决了幻读问题

SERIALIZABLE（串行化）

最高隔离级别，完全串行执行事务：

-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 事务 A
START TRANSACTION;
SELECT * FROM accounts WHERE id = 'A';  -- 获取共享锁

-- 事务 B（会被阻塞）
START TRANSACTION;
UPDATE accounts SET balance = 400 WHERE id = 'A';  -- 等待事务 A 释放锁
COMMIT;

-- 事务 A
COMMIT;  -- 释放锁，事务 B 继续执行

查看和设置隔离级别

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 查看全局隔离级别
SELECT @@global.transaction_isolation;

-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

并发问题和锁机制

脏读示例

-- 隔离级别：READ UNCOMMITTED

-- 事务 A
START TRANSACTION;
UPDATE accounts SET balance = 0 WHERE id = 'A';  -- 未提交

-- 事务 B
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 'A';  -- 读到 balance = 0（脏数据）
COMMIT;

-- 事务 A
ROLLBACK;  -- 回滚，balance 恢复为原值
-- 事务 B 读到的 0 是无效数据

不可重复读示例

-- 隔离级别：READ COMMITTED

-- 事务 A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 'A';  -- 第一次读取：500

-- 事务 B
START TRANSACTION;
UPDATE accounts SET balance = 400 WHERE id = 'A';
COMMIT;

-- 事务 A
SELECT balance FROM accounts WHERE id = 'A';  -- 第二次读取：400（不同！）
COMMIT;

幻读示例

-- 隔离级别：REPEATABLE READ（某些数据库会有幻读）

-- 事务 A
START TRANSACTION;
SELECT * FROM accounts WHERE balance > 400;  -- 返回 2 行

-- 事务 B
START TRANSACTION;
INSERT INTO accounts VALUES ('C', 500);
COMMIT;

-- 事务 A
SELECT * FROM accounts WHERE balance > 400;  -- 返回 3 行（多了一行！）
COMMIT;

-- MySQL InnoDB 通过 Next-Key Lock 解决了这个问题

InnoDB 锁类型

1. 共享锁（S Lock）和排他锁（X Lock）

-- 共享锁（读锁）
SELECT * FROM accounts WHERE id = 'A' LOCK IN SHARE MODE;
-- 或 MySQL 8.0+
SELECT * FROM accounts WHERE id = 'A' FOR SHARE;

-- 排他锁（写锁）
SELECT * FROM accounts WHERE id = 'A' FOR UPDATE;

锁兼容性：

	共享锁（S）	排他锁（X）
共享锁（S）	兼容	冲突
排他锁（X）	冲突	冲突

2. 意向锁

意向锁是表级锁，表示事务打算在表中的行上加锁：

IS（意向共享锁）：事务打算在某些行上加共享锁
IX（意向排他锁）：事务打算在某些行上加排他锁

-- 当执行以下语句时，会自动获取意向锁
SELECT * FROM accounts WHERE id = 'A' FOR UPDATE;
-- 获取：表上的 IX 锁 + 行上的 X 锁

3. 记录锁（Record Lock）

锁定索引记录：

-- 记录锁只锁住索引记录
SELECT * FROM accounts WHERE id = 'A' FOR UPDATE;
-- 锁住 id = 'A' 这一行

4. 间隙锁（Gap Lock）

锁定索引记录之间的间隙：

-- 假设 accounts 表有 id: A, B, D, E
SELECT * FROM accounts WHERE id = 'C' FOR UPDATE;
-- 会锁定 B 和 D 之间的间隙，防止插入 id = 'C' 的记录

5. 临键锁（Next-Key Lock）

记录锁 + 间隙锁的组合：

-- 假设 accounts 表有 id: A, B, D, E
SELECT * FROM accounts WHERE id >= 'B' AND id < 'E' FOR UPDATE;
-- 锁定：
-- - 记录 B（记录锁）
-- - 间隙 B-D（间隙锁）
-- - 记录 D（记录锁）
-- - 间隙 D-E（间隙锁）

死锁

死锁产生

当两个事务互相等待对方释放锁时，就会产生死锁：

-- 事务 A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
-- 等待...

-- 事务 B
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 'B';
UPDATE accounts SET balance = balance + 100 WHERE id = 'A';  -- 等待事务 A 释放 A 的锁

-- 事务 A
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';  -- 等待事务 B 释放 B 的锁
-- 死锁！

死锁检测和处理

MySQL 会自动检测死锁，并回滚其中一个事务：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

避免死锁

按相同顺序访问表和行

-- 好的做法：总是按 id 顺序更新
-- 事务 A 和事务 B 都先更新 A，再更新 B
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;

减少事务持有锁的时间

-- 把不需要在事务中的操作移到外面
-- 好的做法
SELECT balance FROM accounts WHERE id = 'A';  -- 查询放外面
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
COMMIT;

-- 不好的做法
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 'A';  -- 不必要的锁持有
-- 执行其他耗时操作...
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
COMMIT;

使用较低的隔离级别

-- 如果业务允许，使用 READ COMMITTED 可以减少锁的范围
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

事务最佳实践

1. 保持事务简短

-- 不好的做法：事务中有大量业务逻辑
START TRANSACTION;
-- 查询数据
-- 处理业务逻辑（耗时）
-- 调用外部 API（不可控）
-- 更新数据
COMMIT;

-- 好的做法：事务只包含必要的数据库操作
-- 先处理业务逻辑
-- 再开启事务
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;

2. 避免大事务

-- 不好的做法：一条事务更新大量数据
START TRANSACTION;
UPDATE orders SET status = 'completed' WHERE create_time < '2024-01-01';  -- 可能影响百万行
COMMIT;

-- 好的做法：分批处理
-- 在应用层分批提交

3. 正确处理异常

-- 在存储过程中使用异常处理
DELIMITER //
CREATE PROCEDURE transfer(
    IN from_id VARCHAR(10),
    IN to_id VARCHAR(10),
    IN amount DECIMAL(10, 2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT '转账失败' AS message;
    END;
    
    START TRANSACTION;
    
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
    
    COMMIT;
    SELECT '转账成功' AS message;
END //
DELIMITER ;

4. 使用合适的隔离级别

-- 大多数场景使用默认的 REPEATABLE READ
-- 如果业务对一致性要求不高，可以使用 READ COMMITTED 提高并发

-- 报表查询可以使用 READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 执行报表查询...

分布式事务

XA 事务

MySQL 支持 XA 事务，用于跨数据库的分布式事务：

-- 开始 XA 事务
XA START 'xa1';
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
XA END 'xa1';

-- 准备阶段
XA PREPARE 'xa1';

-- 提交或回滚
XA COMMIT 'xa1';  -- 提交
-- 或
XA ROLLBACK 'xa1';  -- 回滚

-- 查看挂起的 XA 事务
XA RECOVER;

小结

本章我们学习了：

事务概念：ACID 特性
事务操作：START TRANSACTION、COMMIT、ROLLBACK、SAVEPOINT
隔离级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE
并发问题：脏读、不可重复读、幻读
锁机制：共享锁、排他锁、意向锁、记录锁、间隙锁、临键锁
死锁：产生原因和避免方法
最佳实践：保持事务简短、避免大事务

练习

实现一个银行转账的事务，确保原子性
测试不同隔离级别下的并发问题
使用保存点实现部分回滚
分析一个死锁场景并解决

什么是事务？​

为什么需要事务？​

事务的 ACID 特性​

1. 原子性（Atomicity）​

2. 一致性（Consistency）​

3. 隔离性（Isolation）​

4. 持久性（Durability）​

事务基本操作​

开始事务​

提交事务​

回滚事务​

保存点（Savepoint）​

事务隔离级别​

隔离级别概述​

READ UNCOMMITTED（读未提交）​

READ COMMITTED（读已提交）​

REPEATABLE READ（可重复读）​

SERIALIZABLE（串行化）​

查看和设置隔离级别​

并发问题和锁机制​

脏读示例​

不可重复读示例​

幻读示例​

InnoDB 锁类型​

1. 共享锁（S Lock）和排他锁（X Lock）​

2. 意向锁​

3. 记录锁（Record Lock）​

4. 间隙锁（Gap Lock）​

5. 临键锁（Next-Key Lock）​

死锁​

死锁产生​

死锁检测和处理​

避免死锁​

事务最佳实践​

1. 保持事务简短​

2. 避免大事务​

3. 正确处理异常​

4. 使用合适的隔离级别​

分布式事务​

XA 事务​

小结​

练习​

参考资源​