事务

事务（Transaction）是数据库操作的基本单位，它将一组操作作为一个不可分割的工作单元。事务具有ACID特性，确保数据的完整性和一致性。本章将详细介绍PostgreSQL中的事务处理。

ACID特性

事务具有以下四个重要特性：

特性	说明
原子性（Atomicity）	事务中的所有操作要么全部完成，要么全部不完成
一致性（Consistency）	事务执行前后，数据库从一个一致状态转换到另一个一致状态
隔离性（Isolation）	并发事务之间相互隔离，互不干扰
持久性（Durability）	事务完成后，对数据的修改是永久的

基本事务操作

开始事务

sql

-- PostgreSQL中每条SQL语句默认在一个事务中执行
-- 显式开始事务
BEGIN;
-- 或
START TRANSACTION;

-- 开始事务并指定隔离级别
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

提交事务

sql

-- 创建示例表
CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    account_name VARCHAR(50) NOT NULL,
    balance NUMERIC(12, 2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts (account_name, balance) VALUES
    ('张三', 10000.00),
    ('李四', 5000.00),
    ('王五', 8000.00);

-- 转账事务示例
BEGIN;  -- 开始事务

-- 从张三账户扣款
UPDATE accounts SET balance = balance - 1000 WHERE account_name = '张三';

-- 向李四账户存款
UPDATE accounts SET balance = balance + 1000 WHERE account_name = '李四';

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

-- 查看结果
SELECT * FROM accounts;

回滚事务

sql

-- 回滚事务：撤销所有未提交的更改
BEGIN;

UPDATE accounts SET balance = balance - 5000 WHERE account_name = '张三';

-- 发现操作有误，回滚事务
ROLLBACK;

-- 验证数据未改变
SELECT * FROM accounts WHERE account_name = '张三';

事务状态检查

sql

-- 检查是否在事务中
SELECT txid_current();  -- 返回当前事务ID

-- 查看当前事务状态
SELECT * FROM pg_stat_activity 
WHERE pid = pg_backend_pid();

保存点

保存点（SAVEPOINT）允许在事务中创建标记点，可以回滚到该点而不是回滚整个事务。

sql

-- 使用保存点
BEGIN;

-- 第一次操作
INSERT INTO accounts (account_name, balance) VALUES ('赵六', 3000.00);

-- 创建保存点
SAVEPOINT sp1;

-- 第二次操作
UPDATE accounts SET balance = balance + 1000 WHERE account_name = '张三';

-- 创建另一个保存点
SAVEPOINT sp2;

-- 第三次操作
UPDATE accounts SET balance = balance - 500 WHERE account_name = '李四';

-- 回滚到sp2（撤销第三次操作）
ROLLBACK TO sp2;

-- 回滚到sp1（撤销第二次操作）
ROLLBACK TO sp1;

-- 释放保存点（释放后不能再回滚到该点）
RELEASE SAVEPOINT sp1;

-- 提交事务（只有第一次操作生效）
COMMIT;

SELECT * FROM accounts;

保存点完整示例

sql

-- 批量处理中使用保存点
BEGIN;

SAVEPOINT before_insert;

-- 尝试插入多条数据
INSERT INTO accounts (account_name, balance) VALUES ('测试用户1', 1000.00);

-- 如果某条插入失败，回滚到保存点继续处理
SAVEPOINT after_first;

INSERT INTO accounts (account_name, balance) VALUES ('测试用户2', 2000.00);

-- 条件性回滚
DO $$
BEGIN
    -- 模拟某些业务检查
    IF (SELECT balance FROM accounts WHERE account_name = '测试用户1') < 500 THEN
        ROLLBACK TO after_first;
        RAISE NOTICE '余额不足，已回滚部分操作';
    END IF;
END $$;

COMMIT;

事务隔离级别

PostgreSQL支持四种事务隔离级别：

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

*PostgreSQL的REPEATABLE READ实际上也防止了幻读。

READ COMMITTED（默认级别）

sql

-- 会话1
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 第一次查询
SELECT * FROM accounts WHERE account_name = '张三';
-- 结果：balance = 9000

-- 会话2同时执行
-- UPDATE accounts SET balance = 8500 WHERE account_name = '张三';
-- COMMIT;

-- 会话1再次查询（可以看到会话2的提交）
SELECT * FROM accounts WHERE account_name = '张三';
-- 结果：balance = 8500（看到了其他事务的提交）

COMMIT;

REPEATABLE READ

sql

-- 会话1
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 第一次查询
SELECT * FROM accounts WHERE account_name = '张三';
-- 结果：balance = 8500

-- 会话2同时执行
-- UPDATE accounts SET balance = 8000 WHERE account_name = '张三';
-- COMMIT;

-- 会话1再次查询（看不到会话2的提交）
SELECT * FROM accounts WHERE account_name = '张三';
-- 结果：balance = 8500（仍然是第一次查询的值）

COMMIT;

-- 新事务中可以看到最新数据
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE account_name = '张三';
-- 结果：balance = 8000
COMMIT;

SERIALIZABLE

sql

-- SERIALIZABLE是最严格的隔离级别
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 所有操作都像串行执行一样
SELECT * FROM accounts WHERE balance > 5000;

-- 如果检测到并发冲突，会抛出序列化失败错误
-- 需要重试事务

COMMIT;

设置隔离级别

sql

-- 开始事务时设置
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 设置当前会话的默认隔离级别
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 查看当前隔离级别
SHOW default_transaction_isolation;

-- 设置全局默认隔离级别（需要超级用户）
ALTER DATABASE mydb SET default_transaction_isolation TO 'repeatable read';

并发问题演示

脏读

脏读是指读取到其他事务未提交的数据。PostgreSQL默认防止脏读。

sql

-- PostgreSQL不会发生脏读，因为READ COMMITTED级别就不会读取未提交的数据
-- 会话1
BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE account_name = '张三';
-- 未提交

-- 会话2（READ COMMITTED级别）
BEGIN;
SELECT balance FROM accounts WHERE account_name = '张三';
-- 不会读取到会话1未提交的更改
COMMIT;

不可重复读

不可重复读是指同一事务中两次读取同一数据得到不同结果。

sql

-- READ COMMITTED级别会发生不可重复读
-- 会话1
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE account_name = '张三';  -- 第一次读取

-- 会话2
-- UPDATE accounts SET balance = 7000 WHERE account_name = '张三';
-- COMMIT;

-- 会话1
SELECT balance FROM accounts WHERE account_name = '张三';  -- 第二次读取，值可能不同
COMMIT;

-- REPEATABLE READ级别防止不可重复读
-- 会话1
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE account_name = '张三';  -- 第一次读取

-- 会话2
-- UPDATE accounts SET balance = 6000 WHERE account_name = '张三';
-- COMMIT;

-- 会话1
SELECT balance FROM accounts WHERE account_name = '张三';  -- 第二次读取，值相同
COMMIT;

幻读

幻读是指同一事务中两次查询返回的行数不同。

sql

-- REPEATABLE READ级别防止幻读
-- 会话1
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE balance > 5000;  -- 第一次查询

-- 会话2
-- INSERT INTO accounts (account_name, balance) VALUES ('新用户', 6000);
-- COMMIT;

-- 会话1
SELECT * FROM accounts WHERE balance > 5000;  -- 第二次查询，行数相同
COMMIT;

锁机制

PostgreSQL使用锁来实现事务隔离。

表级锁

sql

-- ACCESS SHARE：SELECT语句获取
SELECT * FROM accounts;

-- ROW SHARE：SELECT FOR UPDATE/FOR SHARE获取
SELECT * FROM accounts FOR UPDATE;

-- ROW EXCLUSIVE：INSERT/UPDATE/DELETE获取
UPDATE accounts SET balance = 7000 WHERE account_name = '张三';

-- SHARE：阻止并发写入
LOCK TABLE accounts IN SHARE MODE;

-- EXCLUSIVE：阻止并发读写
LOCK TABLE accounts IN EXCLUSIVE MODE;

-- ACCESS EXCLUSIVE：最严格的锁，阻止所有并发访问
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE;

-- 显式获取表锁
BEGIN;
LOCK TABLE accounts IN EXCLUSIVE MODE;
-- 执行操作
COMMIT;

行级锁

sql

-- FOR UPDATE：排他行锁
BEGIN;
SELECT * FROM accounts WHERE account_name = '张三' FOR UPDATE;
-- 其他事务不能修改这行，直到当前事务提交
UPDATE accounts SET balance = balance - 1000 WHERE account_name = '张三';
COMMIT;

-- FOR NO KEY UPDATE：较弱的排他锁
SELECT * FROM accounts WHERE account_name = '张三' FOR NO KEY UPDATE;

-- FOR SHARE：共享行锁
SELECT * FROM accounts WHERE account_name = '张三' FOR SHARE;

-- FOR KEY SHARE：最弱的共享锁
SELECT * FROM accounts WHERE account_name = '张三' FOR KEY SHARE;

-- NOWAIT：如果无法获取锁则立即报错
SELECT * FROM accounts WHERE account_name = '张三' FOR UPDATE NOWAIT;

-- SKIP LOCKED：跳过已锁定的行
SELECT * FROM accounts WHERE balance > 5000 FOR UPDATE SKIP LOCKED;

查看锁信息

sql

-- 查看当前锁
SELECT 
    pid,
    locktype,
    relation::regclass AS table_name,
    mode,
    granted
FROM pg_locks
WHERE relation IS NOT NULL;

-- 查看阻塞的查询
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.relation = blocked_locks.relation
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- 终止阻塞的查询
-- SELECT pg_cancel_backend(blocking_pid);
-- SELECT pg_terminate_backend(blocking_pid);

死锁

死锁示例

sql

-- 会话1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_name = '张三';

-- 会话2
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_name = '李四';

-- 会话1继续
UPDATE accounts SET balance = balance + 100 WHERE account_name = '李四';
-- 等待会话2释放李四的锁

-- 会话2继续
UPDATE accounts SET balance = balance + 100 WHERE account_name = '张三';
-- 死锁！PostgreSQL会检测并回滚其中一个事务

-- 错误信息：ERROR: deadlock detected

避免死锁

sql

-- 1. 按固定顺序访问资源
-- 好的做法：按账户ID排序后操作
BEGIN;
-- 先操作ID较小的账户
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

-- 2. 使用显式锁
BEGIN;
LOCK TABLE accounts IN EXCLUSIVE MODE;
-- 执行所有操作
COMMIT;

-- 3. 减少事务持有锁的时间
-- 事务尽量简短，尽快提交

-- 4. 设置锁超时
SET lock_timeout = '5s';  -- 5秒后超时

事务最佳实践

事务应该简短

sql

-- 不好的做法：事务中包含耗时操作
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE account_name = '张三';
-- 发送邮件（耗时操作）
-- SELECT send_email(...);
UPDATE accounts SET balance = balance + 1000 WHERE account_name = '李四';
COMMIT;

-- 好的做法：事务只包含必要的数据库操作
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE account_name = '张三';
UPDATE accounts SET balance = balance + 1000 WHERE account_name = '李四';
COMMIT;
-- 事务外发送邮件
-- SELECT send_email(...);

使用适当的隔离级别

sql

-- 大多数应用使用READ COMMITTED就足够了
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 需要一致性快照时使用REPEATABLE READ
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 高并发场景需要严格一致性时使用SERIALIZABLE
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

处理序列化失败

sql

-- SERIALIZABLE级别可能需要重试
DO $$
DECLARE
    retry_count INTEGER := 0;
    max_retries INTEGER := 3;
BEGIN
    WHILE retry_count < max_retries LOOP
        BEGIN
            -- 开始可序列化事务
            BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
            
            -- 执行业务操作
            UPDATE accounts SET balance = balance - 100 WHERE account_name = '张三';
            UPDATE accounts SET balance = balance + 100 WHERE account_name = '李四';
            
            COMMIT;
            EXIT;  -- 成功，退出循环
            
        EXCEPTION
            WHEN serialization_failure THEN
                ROLLBACK;
                retry_count := retry_count + 1;
                RAISE NOTICE '序列化失败，重试 %/%', retry_count, max_retries;
                
                IF retry_count >= max_retries THEN
                    RAISE EXCEPTION '达到最大重试次数';
                END IF;
        END;
    END LOOP;
END $$;

使用SELECT FOR UPDATE防止丢失更新

sql

-- 丢失更新问题
-- 会话1和会话2同时读取并更新同一行

-- 解决方案：使用SELECT FOR UPDATE
BEGIN;

-- 获取行锁
SELECT balance INTO v_balance 
FROM accounts 
WHERE account_name = '张三' 
FOR UPDATE;

-- 计算新余额
v_new_balance := v_balance - 100;

-- 更新
UPDATE accounts SET balance = v_new_balance WHERE account_name = '张三';

COMMIT;

两阶段提交

PostgreSQL支持两阶段提交（2PC），用于分布式事务：

sql

-- 准备阶段
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE account_name = '张三';
PREPARE TRANSACTION 'transfer_001';

-- 此时事务已准备好，可以断开连接
-- 其他节点可以查询该事务状态

-- 提交阶段
COMMIT PREPARED 'transfer_001';

-- 或者回滚
-- ROLLBACK PREPARED 'transfer_001';

-- 查看准备好的事务
SELECT * FROM pg_prepared_xacts;

小结

本章介绍了PostgreSQL事务的主要内容：

内容	说明
ACID特性	原子性、一致性、隔离性、持久性
基本操作	BEGIN、COMMIT、ROLLBACK
保存点	SAVEPOINT、ROLLBACK TO
隔离级别	READ COMMITTED、REPEATABLE READ、SERIALIZABLE
锁机制	表锁、行锁
死锁	检测与避免

事务是保证数据一致性的重要机制，下一章我们将学习用户权限管理。

事务 ​

ACID特性 ​

基本事务操作 ​

开始事务 ​

提交事务 ​

回滚事务 ​

事务状态检查 ​

保存点 ​

保存点完整示例 ​

事务隔离级别 ​

READ COMMITTED（默认级别） ​

REPEATABLE READ ​

SERIALIZABLE ​

设置隔离级别 ​

并发问题演示 ​

脏读 ​

不可重复读 ​

幻读 ​

锁机制 ​

表级锁 ​

行级锁 ​

查看锁信息 ​

死锁 ​

死锁示例 ​

避免死锁 ​

事务最佳实践 ​

事务应该简短 ​

使用适当的隔离级别 ​

处理序列化失败 ​

使用SELECT FOR UPDATE防止丢失更新 ​

两阶段提交 ​

小结 ​

事务