TCL

1. TCL 概述

事务控制语言（Transaction Control Language，TCL）用于管理 SQL 事务的生命周期，确保数据操作的原子性和一致性。

1.1 事务的生命周期

BEGIN → SQL操作 → COMMIT/ROLLBACK
              ↘ SAVEPOINT → 部分回滚 → COMMIT/ROLLBACK

1.2 核心语句

2. BEGIN 开始事务

2.1 语法

-- SQL 标准
BEGIN;
BEGIN TRANSACTION;
BEGIN WORK;

-- PostgreSQL
BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN READ ONLY;
BEGIN READ WRITE;

2.2 自动提交模式

-- 大多数数据库默认自动提交（autocommit）
-- 每条 SQL 语句自动成为一个事务

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

-- 显式事务（推荐）
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

3. COMMIT 提交事务

3.1 基本用法

BEGIN;
INSERT INTO orders (user_id, amount) VALUES (42, 99.99);
UPDATE inventory SET stock = stock - 1 WHERE product_id = 100;
COMMIT;  -- 两条语句的变更同时持久化

3.2 提交的保证

COMMIT 成功后，数据库保证：

• 变更已写入重做日志（redo log / WAL）
• 即使系统崩溃，变更也不会丢失
• 其他事务可以看到这些变更

3.3 链式提交

-- PostgreSQL：COMMIT AND CHAIN 自动开始新事务
COMMIT AND CHAIN;
-- 等价于 COMMIT; BEGIN;

4. ROLLBACK 回滚事务

4.1 完全回滚

BEGIN;
UPDATE accounts SET balance = balance - 1000000 WHERE id = 1;
-- 发现操作错误
ROLLBACK;  -- 撤销所有变更，恢复到事务开始前的状态

4.2 隐式回滚

-- 以下情况事务自动回滚：
-- 1. 连接断开
-- 2. 语句执行错误（部分数据库）
-- 3. 死锁被选中牺牲

-- PostgreSQL：错误后事务进入 aborted 状态
BEGIN;
INSERT INTO orders VALUES (1, 99.99);
INSERT INTO orders VALUES ('invalid', 99.99);  -- 类型错误
-- 事务进入 aborted 状态，后续语句都被忽略
INSERT INTO orders VALUES (2, 49.99);  -- 被忽略
ROLLBACK;  -- 必须显式回滚

4.3 链式回滚

-- PostgreSQL：ROLLBACK AND CHAIN
ROLLBACK AND CHAIN;
-- 等价于 ROLLBACK; BEGIN;

5. SAVEPOINT 保存点

5.1 基本用法

BEGIN;
INSERT INTO orders (id, amount) VALUES (1, 100);

SAVEPOINT sp1;

UPDATE inventory SET stock = stock - 1 WHERE product_id = 100;
-- 发现库存不足，回滚到保存点
ROLLBACK TO SAVEPOINT sp1;

-- 尝试其他操作
UPDATE inventory SET stock = stock - 1 WHERE product_id = 200;

COMMIT;
-- 最终：订单1已插入，库存200已减少，库存100未变更

5.2 多级保存点

BEGIN;
SAVEPOINT level1;

INSERT INTO table_a VALUES (1);

SAVEPOINT level2;

INSERT INTO table_b VALUES (2);

SAVEPOINT level3;

INSERT INTO table_c VALUES (3);

-- 回滚到 level2，level3 的变更被撤销
ROLLBACK TO SAVEPOINT level2;

-- level1 和 level2 的变更仍然保留
COMMIT;

5.3 释放保存点

-- RELEASE SAVEPOINT：释放保存点，不能再回滚到该点
BEGIN;
SAVEPOINT sp1;
INSERT INTO table_a VALUES (1);
RELEASE SAVEPOINT sp1;
-- ROLLBACK TO SAVEPOINT sp1;  -- 错误！保存点已释放
ROLLBACK;  -- 回滚整个事务

6. 嵌套事务

6.1 SQL 标准不支持真正的嵌套事务

-- 大多数数据库不支持嵌套 BEGIN
BEGIN;
INSERT INTO table_a VALUES (1);
BEGIN;  -- 错误或被忽略
INSERT INTO table_b VALUES (2);
COMMIT;
COMMIT;

6.2 使用保存点模拟嵌套事务

-- 外层事务
BEGIN;
SAVEPOINT outer;

INSERT INTO table_a VALUES (1);

-- 模拟内层事务
SAVEPOINT inner;

INSERT INTO table_b VALUES (2);

-- 内层回滚
ROLLBACK TO SAVEPOINT inner;

-- 外层继续
INSERT INTO table_c VALUES (3);

COMMIT;

6.3 SQL Server 嵌套事务

-- SQL Server 支持嵌套 BEGIN TRANSACTION
BEGIN TRANSACTION outer;
INSERT INTO table_a VALUES (1);

BEGIN TRANSACTION inner;
INSERT INTO table_b VALUES (2);
COMMIT TRANSACTION inner;  -- 减少嵌套计数

COMMIT TRANSACTION outer;  -- 真正提交

7. 事务与并发

7.1 事务隔离级别

-- 设置事务隔离级别
BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- MySQL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;

7.2 只读事务

-- 只读事务：优化器可以做更多优化
BEGIN READ ONLY;
SELECT * FROM employees WHERE dept_id = 5;
COMMIT;

7.3 事务超时

-- PostgreSQL：设置事务超时
SET idle_in_transaction_session_timeout = '5min';
-- 事务空闲超过5分钟自动回滚

-- MySQL：锁等待超时
SET innodb_lock_wait_timeout = 50;  -- 50秒

8. 事务最佳实践

8.1 事务应尽可能短

-- 不推荐：事务中包含耗时操作
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 调用外部 API（耗时数秒）
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 推荐：先完成耗时操作，再开启事务
-- 调用外部 API
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

8.2 避免大事务

-- 不推荐：一次性更新百万行
BEGIN;
UPDATE large_table SET status = 'archived' WHERE date < '2020-01-01';
COMMIT;

-- 推荐：分批更新
BEGIN;
UPDATE large_table SET status = 'archived'
WHERE date < '2020-01-01' LIMIT 10000;
COMMIT;
-- 重复执行直到影响行数为0