跳到主要内容

PostgreSQL 事务与并发控制

事务是数据库管理的核心概念，确保数据的一致性和完整性。本章将详细介绍 PostgreSQL 中的事务处理和并发控制机制。

事务基础​

什么是事务？​

事务是一组原子性的 SQL 操作，要么全部成功，要么全部失败。这确保了数据库始终处于一致状态。

ACID 特性​

特性说明
原子性（Atomicity）事务是最小执行单位，不可分割
一致性（Consistency）事务执行前后，数据库状态保持一致
隔离性（Isolation）并发事务互不干扰
持久性（Durability）事务提交后，结果永久保存

事务控制​

基本事务操作​

-- 方法1：使用 BEGIN 和 COMMIT
BEGIN;
    INSERT INTO accounts (name, balance) VALUES ('张三', 1000);
    INSERT INTO accounts (name, balance) VALUES ('李四', 500);
COMMIT;

-- 方法2：使用 START TRANSACTION
START TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE name = '张三';
    UPDATE accounts SET balance = balance + 100 WHERE name = '李四';
COMMIT;

-- 方法3：单行事务（PostgreSQL 特性）
INSERT INTO accounts (name, balance) VALUES ('王五', 2000) RETURNING id;

回滚操作​

-- 回滚整个事务
BEGIN;
    INSERT INTO accounts (name, balance) VALUES ('测试', 100);
ROLLBACK;

-- 使用保存点（部分回滚）
BEGIN;
    INSERT INTO accounts (name, balance) VALUES ('张三', 1000);
    SAVEPOINT sp1;
    
    INSERT INTO accounts (name, balance) VALUES ('李四', 500);
    
    -- 回滚到保存点
    ROLLBACK TO SAVEPOINT sp1;
    
    -- 继续执行
    INSERT INTO accounts (name, balance) VALUES ('王五', 800);
COMMIT;

保存点操作​

-- 创建保存点
SAVEPOINT savepoint_name;

-- 回滚到保存点
ROLLBACK TO SAVEPOINT savepoint_name;

-- 释放保存点（释放后不能回滚）
RELEASE SAVEPOINT savepoint_name;

-- 实际应用：批量插入并跳过错误
BEGIN;
    INSERT INTO products (name, price) VALUES ('A', 10);
    SAVEPOINT sp1;
    
    INSERT INTO products (name, price) VALUES ('B', 20);
    SAVEPOINT sp2;
    
    -- 模拟错误
    -- PostgreSQL 不支持自动跳过错误，需要捕获
    
COMMIT;

事务隔离级别​

PostgreSQL 隔离级别​

PostgreSQL 支持四种隔离级别：

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

设置隔离级别​

-- 设置隔离级别（会话级）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 设置隔离级别（事务级）
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 查询当前隔离级别
SHOW transaction_isolation;
SELECT current_setting('transaction_isolation');

隔离级别示例​

-- READ COMMITTED（默认）
-- 事务只能看到其他事务已提交的修改
BEGIN;
SELECT * FROM accounts WHERE name = '张三';  -- balance: 1000
-- 另一个事务：UPDATE accounts SET balance = 2000 WHERE name = '张三'; COMMIT;
SELECT * FROM accounts WHERE name = '张三';  -- balance: 2000（看到新值）
COMMIT;

-- REPEATABLE READ
-- 事务期间看到的数据一致
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE name = '张三';  -- balance: 1000
-- 另一个事务：UPDATE accounts SET balance = 2000 WHERE name = '张三'; COMMIT;
SELECT * FROM accounts WHERE name = '张三';  -- balance: 1000（仍看到旧值）
-- 更新时会失败：ERROR: could not serialize access
COMMIT;

-- SERIALIZABLE
-- 事务完全串行执行
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 所有操作都像是完全串行执行一样
COMMIT;

并发控制 - MVCC​

MVCC 原理​

PostgreSQL 使用多版本并发控制（MVCC）实现事务隔离：

事务ID​

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

-- 查看当前活跃事务
SELECT * FROM pg_stat_activity WHERE state = 'active';

-- 查看事务状态
SELECT 
    xid,
    status,
    age(xid)
FROM pg_prepared_xacts;

可见性判断​

-- PostgreSQL 使用以下规则判断可见性：
-- 1. 如果事务已提交且在当前事务开始前提交，可见
-- 2. 如果事务未提交，不可见
-- 3. 如果事务在当前事务开始后提交，不可见（REPEATABLE READ+）

-- 事务快照
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM pg_snapshot;

锁机制​

表级锁​

-- 锁类型
LOCK TABLE table_name IN [lock_mode] MODE;

-- 锁模式（从轻到重）
-- ACCESS SHARE：SELECT
-- ROW SHARE：SELECT FOR UPDATE
-- ROW EXCLUSIVE：UPDATE, DELETE, INSERT
-- SHARE UPDATE EXCLUSIVE：VACUUM, ANALYZE
-- SHARE：CREATE INDEX
-- SHARE ROW EXCLUSIVE：ALTER TABLE
-- EXCLUSIVE：DROP TABLE, 大量 UPDATE
-- ACCESS EXCLUSIVE：ALTER TABLE DROP COLUMN

-- 示例
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;

-- 查看当前锁
SELECT 
    locktype,
    database,
    relation,
    mode,
    granted,
    pid
FROM pg_locks;

-- 查看锁等待
SELECT 
    a.pid,
    a.usename,
    a.query,
    l.mode,
    l.granted
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;

行级锁​

-- 行级锁：SELECT FOR UPDATE
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 其他人无法修改这行，直到事务结束
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- SELECT FOR UPDATE NOWAIT：立即失败
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

-- SELECT FOR UPDATE SKIP LOCKED：跳过已锁定的行
SELECT * FROM accounts WHERE id = 1 FOR UPDATE SKIP LOCKED;

-- SELECT FOR NO KEY UPDATE：轻量级锁（不允许 FOR KEY SHARE）
SELECT * FROM accounts WHERE id = 1 FOR NO KEY UPDATE;

死锁处理​

-- 死锁超时设置
SET deadlock_timeout = '1s';

-- 检测死锁
-- PostgreSQL 自动检测死锁并回滚一个事务

-- 查看最近死锁
SELECT * FROM pg_stat_database WHERE datname = current_database();
-- 检查 deadlocks 计数器

-- 避免死锁的最佳实践
-- 1. 始终以相同顺序访问资源
-- 2. 尽量使用较低级别的锁
-- 3. 缩短事务时间
-- 4. 使用 NOWAIT 或 SKIP LOCKED

乐观锁与悲观锁​

悲观锁​

-- 悲观锁：假设会发生冲突，先锁定再操作
BEGIN;
SELECT * FROM products WHERE id = 1 FOR UPDATE;
-- 处理业务逻辑
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

乐观锁​

-- 乐观锁：通过版本号控制冲突

-- 添加版本号列
ALTER TABLE products ADD COLUMN version INTEGER DEFAULT 1;

-- 乐观锁更新
UPDATE products 
SET stock = stock - 1, 
    version = version + 1 
WHERE id = 1 AND version = 1;

-- 检查是否更新成功（affected_rows = 0 表示冲突）
-- 如果冲突，应用层可以选择重试

两阶段提交​

-- 两阶段提交用于分布式事务

-- 准备阶段
BEGIN;
PREPARE TRANSACTION 'transaction_id';

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

-- 回滚阶段
ROLLBACK PREPARED 'transaction_id';

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

实践示例：转账业务​

-- 创建账户表
CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE,
    balance NUMERIC(10, 2) CHECK (balance >= 0)
);

INSERT INTO accounts (name, balance) VALUES 
('张三', 1000), ('李四', 500);

-- 转账操作（正确的实现）
DO $$
DECLARE
    v_from_account CONSTANT VARCHAR(50) := '张三';
    v_to_account CONSTANT VARCHAR(50) := '李四';
    v_amount NUMERIC(10, 2) := 100;
BEGIN
    -- 开始事务
    BEGIN
        -- 检查余额
        IF (SELECT balance FROM accounts WHERE name = v_from_account) < v_amount THEN
            RAISE EXCEPTION '余额不足';
        END IF;
        
        -- 扣款
        UPDATE accounts 
        SET balance = balance - v_amount 
        WHERE name = v_from_account;
        
        -- 存款
        UPDATE accounts 
        SET balance = balance + v_amount 
        WHERE name = v_to_account;
        
        -- 提交
        RAISE NOTICE '转账成功：% 转账 % 元到 %', v_from_account, v_amount, v_to_account;
    EXCEPTION
        WHEN OTHERS THEN
            -- 回滚
            RAISE NOTICE '转账失败：%', SQLERRM;
            RAISE;
    END;
END $$;

小结​

本章我们学习了 PostgreSQL 事务和并发控制的各个方面：

  1. 事务基础：ACID 特性、事务控制（COMMIT、ROLLBACK、SAVEPOINT）
  2. 隔离级别：READ COMMITTED、REPEATABLE READ、SERIALIZABLE
  3. MVCC：多版本并发控制原理
  4. 锁机制：表级锁、行级锁、死锁处理
  5. 乐观锁与悲观锁：两种并发控制策略
  6. 两阶段提交：分布式事务支持

练习​

  1. 实现一个转账功能，确保原子性
  2. 设置不同的隔离级别，测试可见性
  3. 模拟死锁情况并分析
  4. 实现乐观锁版本的库存扣减
  5. 使用保存点实现部分回滚