事务基础：BEGIN、COMMIT、ROLLBACK

什么是事务

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

问题：转账操作

-- 没有事务保护
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- A 扣款
-- 如果这里系统崩溃...
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- B 收款

如果第一条执行成功后系统崩溃，钱从 A 扣除但 B 没有收到，数据不一致！

解决：使用事务

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

现在两条 UPDATE 被包装成一个事务，要么都成功，要么都失败。

事务的三个命令

BEGIN：开始事务

BEGIN;  -- PostgreSQL
-- 或
START TRANSACTION;  -- MySQL 和 PostgreSQL 都支持

开始一个新事务，之后的操作不会立即生效。

COMMIT：提交事务

COMMIT;

将事务中的所有更改永久保存到数据库。

ROLLBACK：回滚事务

ROLLBACK;

撤销事务中的所有更改，恢复到事务开始前的状态。

完整示例

成功的转账

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 检查余额
SELECT balance FROM accounts WHERE id = 1;  -- 应该减少 100

COMMIT;  -- 提交

失败的转账

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- 检查余额是否足够（假设不够）
SELECT balance FROM accounts WHERE id = 1;

-- 发现余额不足，回滚
ROLLBACK;

所有更改被撤销，账户余额恢复原状。

SAVEPOINT：部分回滚

SAVEPOINT 允许在事务中设置保存点，回滚到某个保存点而不是整个事务。

BEGIN;

INSERT INTO orders (user_id, product, amount) VALUES (1, 'Laptop', 1200);
SAVEPOINT order_created;

UPDATE inventory SET stock = stock - 1 WHERE product = 'Laptop';
-- 发现库存不足

ROLLBACK TO SAVEPOINT order_created;  -- 只回滚库存更新
-- 订单插入保留

COMMIT;  -- 提交订单

语法：

• SAVEPOINT name：创建保存点
• ROLLBACK TO SAVEPOINT name：回滚到保存点
• RELEASE SAVEPOINT name：删除保存点

自动提交模式

PostgreSQL

默认情况下，每条 SQL 语句自动包装在一个事务中：

-- 这条语句自动提交
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

等价于：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

MySQL

MySQL 的 autocommit 可以配置：

-- 查看当前状态
SELECT @@autocommit;  -- 1 表示开启

-- 关闭自动提交
SET autocommit = 0;

-- 现在需要手动 COMMIT
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- 恢复自动提交
SET autocommit = 1;

事务中的 SELECT

在事务中，SELECT 看到的数据取决于隔离级别（下一节详细讲解）。

BEGIN;

SELECT balance FROM accounts WHERE id = 1;  -- 100

-- 其他事务修改了这行
-- 再次查询
SELECT balance FROM accounts WHERE id = 1;  -- 可能仍然是 100（取决于隔离级别）

COMMIT;

事务的常见错误

错误 1：忘记 COMMIT

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 忘记 COMMIT，断开连接

结果：更改丢失（自动 ROLLBACK）。

错误 2：长时间持有事务

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 等待用户输入（几分钟）...
COMMIT;

问题：锁定资源太久，阻塞其他事务。

错误 3：在事务中 DDL

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
ALTER TABLE accounts ADD COLUMN status VARCHAR(20);  -- DDL
COMMIT;

PostgreSQL：支持事务中的 DDL
MySQL：DDL 会隐式提交事务（更改立即生效）

实际应用场景

场景 1：批量插入

BEGIN;

INSERT INTO orders (user_id, product, amount) VALUES
    (1, 'Laptop', 1200),
    (1, 'Mouse', 25),
    (2, 'Keyboard', 80);

-- 如果任何一行插入失败，全部回滚
COMMIT;

场景 2：复杂更新

BEGIN;

-- 创建临时订单
INSERT INTO orders (user_id, product, amount, status) 
VALUES (1, 'Laptop', 1200, 'pending');

-- 扣减库存
UPDATE inventory SET stock = stock - 1 WHERE product = 'Laptop';

-- 扣款
UPDATE accounts SET balance = balance - 1200 WHERE id = 1;

-- 更新订单状态
UPDATE orders SET status = 'completed' WHERE id = LAST_INSERT_ID();

COMMIT;

场景 3：数据迁移

BEGIN;

-- 从旧表复制数据
INSERT INTO new_users SELECT * FROM old_users;

-- 验证数据
SELECT COUNT(*) FROM new_users;
SELECT COUNT(*) FROM old_users;

-- 确认无误后提交
COMMIT;

事务的最佳实践

1. 保持事务短小：长事务会锁定资源
2. 尽早提交或回滚：不要在事务中等待用户输入
3. 在应用层控制事务：不要在数据库中长时间持有事务
4. 使用 SAVEPOINT：部分回滚而非全部回滚
5. 处理异常：在应用代码中捕获异常并 ROLLBACK

示例：Python 中的事务

import psycopg2

conn = psycopg2.connect("dbname=mydb user=postgres")
cur = conn.cursor()

try:
    cur.execute("BEGIN")
    cur.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    cur.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
    conn.commit()
    print("转账成功")
except Exception as e:
    conn.rollback()
    print(f"转账失败：{e}")
finally:
    cur.close()
    conn.close()

练习

1. 基本事务：创建一个转账事务，从账户 A 转 50 到账户 B。

2. 回滚测试：开始事务，插入一行数据，然后回滚，验证数据未插入。

3. SAVEPOINT：在一个事务中创建两个保存点，插入数据后回滚到第一个保存点。

4. 批量操作：用事务包装批量插入 100 行数据。

5. 错误处理：模拟转账失败（余额不足），使用 ROLLBACK。

6. 自动提交：测试 MySQL 的 autocommit 开关，观察行为差异。

7. 长事务问题：开始事务但不提交，从另一个会话尝试修改同一行，观察阻塞。

8. 实际场景：设计一个订单创建流程，包含订单插入、库存扣减、积分增加，用事务保护。

完成练习后，打开本章答案页核对。

下一步

学会了事务基础操作。下一节学习 ACID 原则：理解事务的四大特性如何保证数据一致性。

回到第五章概览 · 返回SQL 学习路线