PostgreSQL TRANSACTION（事务）

下面是一份 PostgreSQL 事务（TRANSACTION） 的 完整实战手册，涵盖 语法、隔离级别、事务控制、错误处理、嵌套事务、性能优化、并发控制、常见陷阱与最佳实践，适合开发、DBA、架构师使用。

---

一、什么是事务？

事务是数据库操作的原子单位，满足 ACID 特性：

属性	说明
Atomicity（原子性）	全成功或全失败
Consistency（一致性）	从一个一致状态到另一个一致状态
Isolation（隔离性）	并发事务互不干扰
Durability（持久性）	提交后永久保存

---

二、基本语法

BEGIN;
    -- SQL 语句
COMMIT;   -- 或 ROLLBACK;

隐式事务（PostgreSQL 自动开启）

-- 每条语句自动是一个事务
INSERT INTO users(name) VALUES ('Alice');

---

三、显式事务控制

BEGIN;

INSERT INTO accounts(id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

若出错 → ROLLBACK 回滚所有

---

四、事务隔离级别（Isolation Levels）

级别	是否读脏	是否不可重复读	是否幻读	性能
Read Uncommitted	是	是	是	最快
Read Committed（默认）	否	是	是	快
Repeatable Read	否	否	是	中
Serializable	否	否	否	最慢

设置隔离级别

-- 会话级
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 事务级
BEGIN ISOLATION LEVEL SERIALIZABLE;
    -- ...
COMMIT;

---

五、SAVEPOINT（保存点）—— 部分回滚

BEGIN;

INSERT INTO users(name) VALUES ('Alice');
SAVEPOINT sp1;

INSERT INTO users(email) VALUES (NULL);  -- 违反 NOT NULL
-- ERROR!

ROLLBACK TO SAVEPOINT sp1;  -- 只回滚到 sp1 之后

INSERT INTO users(name, email) VALUES ('Alice', 'a@example.com');

COMMIT;  -- 成功提交 Alice

RELEASE SAVEPOINT sp1; 释放保存点

---

六、错误处理：EXCEPTION

BEGIN;

INSERT INTO users(name) VALUES ('Bob');

BEGIN
    INSERT INTO users(email) VALUES (NULL);
EXCEPTION
    WHEN not_null_violation THEN
        RAISE NOTICE 'Email cannot be NULL, skipping...';
        -- 继续执行
END;

INSERT INTO logs(action) VALUES ('User Bob created');

COMMIT;

---

七、嵌套事务？→ 实际是 SAVEPOINT

PostgreSQL 不支持真正嵌套事务，但可用 SAVEPOINT 模拟。

CREATE OR REPLACE FUNCTION transfer_money(
    from_id INT, to_id INT, amount NUMERIC
) RETURNS VOID AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    IF NOT FOUND THEN RAISE EXCEPTION 'Source not found'; END IF;

    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
    IF NOT FOUND THEN RAISE EXCEPTION 'Target not found'; END IF;
EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END;
$$ LANGUAGE plpgsql;

-- 主事务
BEGIN;

SAVEPOINT sp_transfer;
PERFORM transfer_money(1, 2, 100);

-- 出错了？回滚转账
ROLLBACK TO sp_transfer;

-- 继续其他操作
INSERT INTO audit_log(action) VALUES ('Transfer failed');

COMMIT;

---

八、事务与锁

操作	锁类型
SELECT	无锁（除 FOR UPDATE）
SELECT ... FOR UPDATE	行级排他锁
SELECT ... FOR SHARE	行级共享锁
UPDATE / DELETE	行级排他锁
INSERT	行级排他锁（冲突时）

示例：悲观锁

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

-- 其他事务无法修改 id=1
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

COMMIT;

---

九、死锁（Deadlock）检测与处理

PostgreSQL 自动检测死锁，抛出错误：

ERROR:  deadlock detected
DETAIL:  Process 123 waits for ShareLock on transaction 456...

解决策略

1. 固定加锁顺序
2. 缩短事务时间
3. 重试机制

DO $$
DECLARE
    retries INT := 3;
BEGIN
    FOR i IN 1..retries LOOP
        BEGIN
            PERFORM transfer_with_lock(1, 2, 100);
            EXIT;  -- 成功
        EXCEPTION WHEN deadlock_detected THEN
            IF i = retries THEN RAISE; END IF;
            PERFORM pg_sleep(0.1 * i);
        END;
    END LOOP;
END $$;

---

十、事务性能优化

技巧	说明
缩短事务时间	减少锁持有时间
批量操作	INSERT ... VALUES (),(),()
避免热点行	分区、哈希
使用 ON COMMIT	临时表自动清理
显式 COMMIT	避免长事务

临时表 + 事务结束自动删除

BEGIN;

CREATE TEMP TABLE temp_data (...) ON COMMIT DROP;

INSERT INTO temp_data VALUES (...);

-- COMMIT 后自动删除
COMMIT;

---

十一、事务日志（WAL）与持久性

• 所有变更写入 WAL（Write-Ahead Log）
• COMMIT 后 → 持久化（即使宕机也能恢复）
• synchronous_commit = off 可提升性能（风险：宕机丢最近事务）

-- 配置文件
synchronous_commit = on    -- 默认，安全
synchronous_commit = off   -- 更快，风险

---

十二、查看当前事务

-- 查看所有事务
SELECT pid, datname, usename, state, query, backend_start
FROM pg_stat_activity
WHERE state != 'idle';

-- 查看当前事务 ID
SELECT txid_current();

-- 查看锁
SELECT * FROM pg_locks WHERE NOT granted;

---

十三、常见陷阱与避坑

问题	原因	解决
长时间锁	长事务	拆分事务
死锁频繁	加锁顺序乱	统一顺序
幻读	隔离级别低	用 SERIALIZABLE
事务未提交	忘记 COMMIT	自动提交或提醒
临时表泄露	未 ON COMMIT DROP	加 ON COMMIT

---

十四、最佳实践脚本

1. 安全转账函数（带重试）

CREATE OR REPLACE FUNCTION safe_transfer(
    from_id INT, to_id INT, amount NUMERIC, max_retries INT DEFAULT 3
) RETURNS BOOLEAN AS $$
DECLARE
    i INT := 0;
BEGIN
    LOOP
        i := i + 1;
        BEGIN
            BEGIN
                UPDATE accounts SET balance = balance - amount WHERE id = from_id;
                IF NOT FOUND THEN RAISE EXCEPTION 'Source account not found'; END IF;

                UPDATE accounts SET balance = balance + amount WHERE id = to_id;
                IF NOT FOUND THEN RAISE EXCEPTION 'Target account not found'; END IF;

                RETURN TRUE;
            EXCEPTION
                WHEN deadlock_detected THEN
                    IF i >= max_retries THEN RAISE; END IF;
                    PERFORM pg_sleep(0.1 * i);
            END;
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

2. 批量导入事务模板

BEGIN;

COPY temp_import FROM '/data/import.csv' CSV HEADER;

INSERT INTO users(name, email)
SELECT name, email FROM temp_import
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

DROP TABLE temp_import;

COMMIT;

---

十五、速查表

命令	用途
BEGIN;	开始事务
COMMIT;	提交
ROLLBACK;	回滚
SAVEPOINT sp;	保存点
ROLLBACK TO sp;	部分回滚
SET TRANSACTION ISOLATION LEVEL ...	设置隔离级别
SELECT ... FOR UPDATE;	行锁
ON COMMIT DROP	临时表自动删

---

十六、事务决策树

graph TD
    A[需要多个语句原子执行?] -->|是| B{是否涉及并发修改?}
    B -->|是| C{是否需要防幻读?}
    C -->|是| D[用 SERIALIZABLE]
    C -->|否| E[用 REPEATABLE READ]
    B -->|否| F[用 READ COMMITTED 或默认]
    A -->|否| G[单语句即可]

---

十七、常见面试题

问题	答案
PostgreSQL 默认隔离级别？	Read Committed
SELECT 会加锁吗？	不会，除非 FOR UPDATE
如何实现“先查后改”不被干扰？	SELECT ... FOR UPDATE
死锁如何处理？	自动回滚一个，重试
事务中可以嵌套事务吗？	不支持，用 SAVEPOINT

---

需要我提供一个完整的 “高并发转账系统 + 死锁重试 + 监控” 的生产级模板吗？