事务与并发控制

数据库的并发控制决定了多个用户同时读写时，数据一致性和性能的平衡。PostgreSQL 使用 MVCC（多版本并发控制）来优雅地解决这个问题。

什么是事务？

事务是一组 SQL 操作的逻辑单位，满足 ACID 特性：

原子性（A）：要么全部成功，要么全部回滚
一致性（C）：事务前后数据状态一致
隔离性（I）：并发事务互不干扰（程度可调）
持久性（D）：提交后数据不会丢失

基本事务控制

-- 准备测试数据
CREATE TABLE bank_accounts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    balance NUMERIC(10,2) DEFAULT 0 CHECK (balance >= 0)
);

INSERT INTO bank_accounts (name, balance) VALUES
    ('张三', 10000),
    ('李四', 5000),
    ('王五', 20000);

BEGIN / COMMIT / ROLLBACK

-- 转账操作：张三转 1000 给李四
BEGIN;

UPDATE bank_accounts SET balance = balance - 1000 WHERE name = '张三';
UPDATE bank_accounts SET balance = balance + 1000 WHERE name = '李四';

-- 检查余额是否正确（此时其他会话还看不到变化）
SELECT * FROM bank_accounts ORDER BY id;

-- 确认无误后提交
COMMIT;

回滚事务

BEGIN;

UPDATE bank_accounts SET balance = balance - 50000 WHERE name = '张三';  -- ❌ 余额不足

-- 发现错误，回滚全部操作
ROLLBACK;

-- 查看数据，发现数据变回事务开始前的状态
SELECT * FROM bank_accounts;

SAVEPOINT 保存点

BEGIN;

INSERT INTO bank_accounts (name, balance) VALUES ('赵六', 3000);
SAVEPOINT after_insert;

UPDATE bank_accounts SET balance = balance + 5000 WHERE name = '张三';
-- 决定撤销这笔更新
ROLLBACK TO after_insert;

-- 张三的余额不受影响，但赵六已插入
COMMIT;

隔离级别

SQL 标准定义了四个隔离级别，从低到高依次限制不同的并发问题：

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

PostgreSQL 的默认隔离级别是 READ COMMITTED，这也是大多数应用的最佳选择。

查看和设置隔离级别

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

-- 设置当前事务的隔离级别
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- do something
COMMIT;

-- 设置会话级别的默认隔离级别
SET default_transaction_isolation = 'repeatable read';

演示：不可重复读

会话 A：

-- 隔离级别设为 READ COMMITTED
BEGIN;
SELECT balance FROM bank_accounts WHERE name = '张三';  -- 读出 10000
-- 此时不提交，等待会话 B 操作

会话 B：

UPDATE bank_accounts SET balance = 15000 WHERE name = '张三';
COMMIT;

会话 A：

-- 再次查询，发现值变了（不可重复读）
SELECT balance FROM bank_accounts WHERE name = '张三';  -- 现在读出 15000
COMMIT;

用 REPEATABLE READ 避免

会话 A：

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM bank_accounts WHERE name = '张三';  -- 读出 10000
-- 等待会话 B
SELECT balance FROM bank_accounts WHERE name = '张三';  -- 仍然读出 10000
COMMIT;
SELECT balance FROM bank_accounts WHERE name = '张三';  -- 提交后看到 15000

锁机制

行级锁

PostgreSQL 的行级锁通过 SELECT ... FOR UPDATE 实现，在读取时就锁定行以防止其他事务修改：

-- 乐观锁：先查后改（可能出现竞态条件）
SELECT balance FROM bank_accounts WHERE name = '张三';
-- 其他事务可以在这期间修改！
UPDATE bank_accounts SET balance = balance - 1000 WHERE name = '张三';

-- 悲观锁：锁定行
BEGIN;
SELECT balance FROM bank_accounts WHERE name = '张三' FOR UPDATE;
-- 其他事务此时无法修改或 FOR UPDATE 这行（会等待）
UPDATE bank_accounts SET balance = balance - 1000 WHERE name = '张三';
COMMIT;  -- 锁释放

表级锁

-- 阻止写操作
LOCK TABLE bank_accounts IN ACCESS EXCLUSIVE MODE;

-- 阻止所有操作（含读）
LOCK TABLE bank_accounts IN ACCESS EXCLUSIVE MODE;

死锁

死锁是两个事务互相等待对方释放资源：

-- 事务 1
BEGIN;
UPDATE bank_accounts SET balance = balance - 100 WHERE id = 1;  -- 锁住 id=1
UPDATE bank_accounts SET balance = balance + 100 WHERE id = 2;  -- 等待事务2释放 id=2
COMMIT;

-- 事务 2
BEGIN;
UPDATE bank_accounts SET balance = balance - 100 WHERE id = 2;  -- 锁住 id=2
UPDATE bank_accounts SET balance = balance + 100 WHERE id = 1;  -- 等待事务1释放 id=1（死锁！）
COMMIT;

PostgreSQL 会自动检测死锁，杀掉其中一个事务并报错：

ERROR: deadlock detected
DETAIL: Process 123 waits for ShareLock on transaction 456; blocked by process 789.

避免死锁：所有事务按相同顺序更新资源。

MVCC 原理（了解）

PostgreSQL 的 MVCC 核心思想是读不阻塞写，写不阻塞读：

每个事务看到的是数据在某个时间点的快照
UPDATE 操作产生一个新的元组版本，旧版本保留
当事务不再需要旧版本时，VACUUM 清理死元组

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

-- 查看表的死元组数量
SELECT n_dead_tup FROM pg_stat_user_tables 
WHERE relname = 'bank_accounts';

实战：秒杀场景的并发控制

-- 商品表
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    stock INTEGER NOT NULL CHECK (stock >= 0),
    version INTEGER DEFAULT 1  -- 乐观锁版本号
);

INSERT INTO products (name, stock) VALUES ('限量版手办', 10);

方案一：悲观锁

BEGIN;

SELECT stock FROM products WHERE product_id = 1 FOR UPDATE;
-- 检查库存
-- 库存 > 0 则执行后续操作
UPDATE products SET stock = stock - 1 WHERE product_id = 1;
INSERT INTO orders (customer_id, product_id, quantity) VALUES (?, 1, 1);

COMMIT;

优点：简单可靠，防止超卖缺点：并发较高时排队，吞吐量受限

方案二：乐观锁（版本号）

BEGIN;

SELECT stock, version FROM products WHERE product_id = 1;
-- 应用层检查库存，记下 version = 当前版本号

-- 更新时检查版本号不变（原子操作）
UPDATE products 
SET stock = stock - 1, version = version + 1
WHERE product_id = 1 AND version = :读取时的version号;

-- 如果 UPDATE 影响的行数为 0，说明被别人改过
-- 需要重试整个流程
GET DIAGNOSTICS updated_rows = ROW_COUNT;

COMMIT;

方案三：原子条件更新（最推荐）

-- 一行 SQL 解决，不需要事务
UPDATE products 
SET stock = stock - 1
WHERE product_id = 1 AND stock > 0;

-- 检查影响行数，0 表示库存不足

常见问题

长事务

-- 查看当前运行中的事务
SELECT 
    pid,
    state,
    now() - pg_stat_activity.query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY duration DESC;

长事务的坏处：

阻止 VACUUM 清理死元组
导致表膨胀
增加死锁概率

序列化失败

-- REPEATABLE READ 下的序列化冲突
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

UPDATE bank_accounts SET balance = balance - 100 WHERE name = '张三';

-- 另一个事务已更新同一行并提交，这里会报错：
-- ERROR: could not serialize access due to concurrent update
COMMIT;

小结

事务和并发控制是关系型数据库最核心的难点。PostgreSQL 的 MVCC 机制提供了读不阻塞写的优秀隔离能力，但开发者需要理解不同隔离级别的行为和锁的代价。核心建议：默认用 READ COMMITTED，需要一致读时用 REPEATABLE READ，只在确认需要时才用 FOR UPDATE。

下一篇文章将学习 CTE 和递归查询——处理复杂层级数据的利器。

Summary: ACID、隔离级别、锁机制（FOR UPDATE）、MVCC 与并发实战。