PostgreSQL数据库技术第 6 篇：事务处理与并发控制

摘要

本文深入探讨PostgreSQL的事务机制和并发控制技术。事务是数据库管理系统的核心概念，确保数据操作的原子性、一致性、隔离性和持久性。PostgreSQL采用MVCC（多版本并发控制）机制实现高效的事务处理，在不牺牲性能的前提下提供强大的并发能力。文章将系统讲解事务的ACID特性、MVCC实现原理、事务隔离级别、锁机制、死锁处理、写前日志（WAL）机制以及行级安全策略等核心内容。通过本文学习，读者将深入理解数据库如何在多用户并发环境下保证数据一致性和系统稳定性。

学习目标

理解事务的ACID特性及其在PostgreSQL中的实现
掌握MVCC多版本并发控制的原理和机制
深入理解四种事务隔离级别及其应用场景
掌握PostgreSQL的锁机制和锁定策略
理解死锁产生的原因和预防处理方法
了解写前日志（WAL）机制的作用和实现
掌握行级安全策略（RLS）的配置和使用

一、事务基础概念

1.1 事务的定义

事务是数据库管理系统执行过程中的一个逻辑单位，由一系列操作序列组成。这些操作要么全部成功执行，要么全部不执行，不会出现只执行其中一部分的情况。事务是保证数据一致性和完整性的基本机制。

在PostgreSQL中，事务通过BEGIN、COMMIT和ROLLBACK语句来控制。BEGIN语句标记事务的开始，COMMIT语句提交事务使修改永久生效，ROLLBACK语句回滚事务撤销所有修改。

-- 事务的基本使用
BEGIN;
 
-- 执行一系列操作
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
 
-- 如果一切正常，提交事务
COMMIT;
 
-- 如果出现错误，回滚事务
-- ROLLBACK;

1.2 事务的ACID特性

ACID是事务必须具备的四个特性的缩写，是衡量事务处理系统质量的标准。

原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成。如果事务在执行过程中发生故障，系统会自动回滚到事务开始前的状态，就像事务从未执行过一样。PostgreSQL通过写前日志（WAL）机制保证原子性，所有修改先记录到日志中，确保在系统崩溃时能够恢复。

一致性（Consistency）：事务执行前后，数据库都必须处于一致的状态。这意味着数据库的所有约束（外键约束、唯一约束、检查约束等）都必须得到满足。一致性依赖于应用层面的正确事务设计和数据库层面的约束机制共同保证。

隔离性（Isolation）：多个事务并发执行时，每个事务都应该感觉不到其他事务的存在。事务的中间状态对其他事务不可见。PostgreSQL通过MVCC机制实现隔离性，为每个事务提供数据的快照视图。

持久性（Durability）：一旦事务提交，其结果就会永久保存，即使系统发生故障也不会丢失。PostgreSQL通过确保数据修改写入磁盘并通过WAL机制保证持久性。

1.3 事务边界管理

PostgreSQL提供了多种管理事务边界的方式，开发者可以根据应用需求选择合适的方式。

-- 显式事务边界
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 101;
INSERT INTO orders (product_id, quantity) VALUES (101, 1);
COMMIT;
 
-- 事务块（事务内的语句块）
DO $$
BEGIN
    -- 在事务块内执行多个操作
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Account not found';
    END IF;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
END $$;
 
-- 保存点（Savepoint）
BEGIN;
INSERT INTO orders (customer_id) VALUES (1001);
SAVEPOINT order_created;
INSERT INTO order_items (order_id, product_id) VALUES (currval('orders_id_seq'), 101);
-- 如果出现错误
-- ROLLBACK TO order_created;
COMMIT;
 
-- 自动提交关闭（psql中）
\set AUTOCOMMIT off
-- 现在每条SQL命令需要显式COMMIT才会生效

二、MVCC多版本并发控制

2.1 MVCC基本原理

MVCC（Multiversion Concurrency Control）是一种多版本并发控制机制，通过为每个数据行维护多个版本来实现高效的并发访问。与传统使用锁的并发控制相比，MVCC读操作不会阻塞写操作，写操作也不会阻塞读操作，大大提高了系统的并发性能。

PostgreSQL的MVCC实现基于两个核心概念：事务ID（XID）和命令ID（CID）。每个事务在开始时被分配一个唯一的事务ID，数据行通过存储创建它的事务ID和删除它的事务ID（如果被删除）来实现版本控制。

2.2 MVCC数据结构

在PostgreSQL内部，每个数据行（称为元组Tuple）都包含以下关键信息：

xmin：创建该行的事务ID
xmax：删除该行的事务ID（如果未删除则为0）
cid：命令ID，表示在创建事务中的第几条命令创建的

此外，每个事务还会维护一个快照，包含活动事务列表，用于判断数据行的可见性。

-- 查看行的MVCC隐藏信息
SELECT ctid, xmin, xmax, cmin, cmax, *
FROM products
WHERE id = 1;
 
-- ctid: 行的物理位置（页号和偏移量）
-- xmin: 创建该行的事务ID
-- xmax: 删除该行的事务ID（0表示未删除）
-- cmin: 创建命令ID
-- cmax: 删除命令ID

2.3 可见性判断规则

PostgreSQL使用以下规则来判断数据行对当前事务是否可见：

行是否已插入：如果行的xmin小于当前事务快照中的最小活跃事务ID，且该事务已提交，则行已插入。
行是否已删除：如果行的xmax不为0，且xmax小于快照中的最小活跃事务ID，且该事务已提交，则行已删除。
行是否由当前事务创建：如果行的xmin等于当前事务ID，则行对当前事务可见。
行是否由当前事务删除：如果行的xmax等于当前事务ID，则行对当前事务不可见。

2.4 MVCC读操作图解

sequenceDiagram
    autonumber
    participant T1 as 事务T1<br/>(XID=100)
    participant T2 as 事务T2<br/>(XID=101)
    participant Data as 数据行<br/>(id=1, value='A')
    participant MVCC as MVCC控制

    Note over T1,MVCC: 初始状态：value='A', xmin=50, xmax=0

    T1->>MVCC: 1. BEGIN
    T1->>MVCC: 2. 获取快照<br/>(活跃事务: {100,102,103})
    T1->>Data: 3. SELECT value<br/>FROM t WHERE id=1

    Data-->>T1: 4. 返回 'A'<br/>(xmin=50 < 100, 已提交)

    T2->>MVCC: 5. BEGIN
    T2->>MVCC: 6. 获取快照<br/>(活跃事务: {101,102,103})
    T2->>Data: 7. UPDATE t<br/>SET value='B'<br/>WHERE id=1

    Data->>MVCC: 8. 标记旧版本<br/>xmax=101
    Data->>Data: 9. 创建新版本<br/>value='B',<br/>xmin=101, xmax=0

    T1->>Data: 10. SELECT value<br/>FROM t WHERE id=1
    Data-->>T1: 11. 返回 'A'<br/>(xmax=101 ≥ 快照最小值,<br/>不可见)

    T2->>MVCC: 12. COMMIT
    T2->>MVCC: 13. 事务101标记为已提交

    T1->>Data: 14. SELECT value<br/>FROM t WHERE id=1
    Data-->>T1: 15. 仍返回 'A'<br/>(基于快照的可见性)

图表说明：此序列图详细展示了MVCC机制如何处理并发读写操作。事务T1首先开始，获取快照并读取数据值’A’。随后事务T2开始并修改数据值为’B’，创建新版本。由于T1的快照是在T2修改之前获取的，T1仍然看到的是旧值’A’，即使T2已经提交。这就是MVCC提供的快照隔离，保证了读写操作互不阻塞。每个事务都看到的是事务开始时刻的一致性数据视图。

2.5 MVCC的优势与代价

优势：

读不阻塞写，写不阻塞读
无读锁，大大提高并发性能
快照一致性，事务看到稳定的数据视图
避免读写饥饿问题

代价：

需要维护多个版本，增加存储开销
需要定期清理旧版本（VACUUM）
可见性判断增加CPU开销
长事务会阻碍旧版本清理

三、事务隔离级别

3.1 隔离级别概述

SQL标准定义了四种事务隔离级别，用于在并发事务之间平衡一致性和性能。PostgreSQL支持这三种隔离级别（Read Uncommitted在实际中被视为Read Committed）：

Read Uncommitted（读未提交）：允许读取未提交的数据。PostgreSQL不支持此级别，实际行为等同于Read Committed。
Read Committed（读已提交）：只能读取已提交的数据，这是PostgreSQL的默认级别。
Repeatable Read（可重复读）：在同一事务中多次读取同一数据的结果一致。
Serializable（可串行化）：最高隔离级别，完全隔离事务，确保并发执行的效果与某种串行顺序相同。

3.2 Read Committed隔离级别

Read Committed是PostgreSQL的默认隔离级别。在此级别下，每个SELECT语句都会获取一个新的快照，因此可以看到其他已提交事务所做的修改。

-- 设置隔离级别为Read Committed
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
 
-- 第一次查询
SELECT balance FROM accounts WHERE id = 1;
-- 假设结果为1000
 
-- 此时另一个事务提交了修改，将balance改为900
 
-- 第二次查询会看到新的已提交修改
SELECT balance FROM accounts WHERE id = 1;
-- 结果为900
 
COMMIT;

Read Committed级别适合大多数应用场景，提供了良好的平衡：既防止了脏读，又不会过度限制并发。但在需要严格一致性保证的场景下可能不够用。

3.3 Repeatable Read隔离级别

Repeatable Read隔离级别使用事务开始时获取的快照，在整个事务期间保持不变。这意味着事务不会看到其他事务提交的修改，确保了可重复读。

-- 设置隔离级别为Repeatable Read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
 
-- 第一次查询
SELECT balance FROM accounts WHERE id = 1;
-- 结果为1000
 
-- 此时另一个事务提交了修改，将balance改为900
 
-- 第二次查询仍然返回原始值
SELECT balance FROM accounts WHERE id = 1;
-- 结果仍然是1000
 
COMMIT;

Repeatable Read级别防止了脏读、不可重复读和幻读，适合需要事务期间数据视图稳定的应用。但如果检测到可串行化冲突，事务会失败并需要重试。

3.4 Serializable隔离级别

Serializable是最高隔离级别，通过真正的可串行化调度确保并发事务的正确执行。PostgreSQL使用Serializable Snapshot Isolation（SSI）技术实现此级别，通过检测并发事务间的冲突来防止异常。

-- 设置隔离级别为Serializable
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
 
-- 执行查询和修改
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
 
-- 如果系统检测到可串行化冲突
-- 事务会失败，需要重试
 
COMMIT;

Serializable级别适合需要严格一致性保证的关键应用，如金融交易、库存管理等。但由于冲突检测和重试机制，可能影响性能和吞吐量。

3.5 隔离级别对比图

flowchart TD
    A[事务隔离级别选择] --> B{一致性需求}
    B --> C[基本一致性]
    B --> D[快照一致性]
    B --> E[严格一致性]

    C --> F[Read Committed<br/>默认级别]

    F --> F1[特点<br/>• 防止脏读<br/>• 每条语句新快照<br/>• 看到已提交修改]

    F --> F2[适用场景<br/>• 一般业务查询<br/>• 报表生成<br/>• Web应用]

    D --> G[Repeatable Read<br/>快照隔离]

    G --> G1[特点<br/>• 防止脏读<br/>• 防止不可重复读<br/>• 防止幻读<br/>• 事务级快照]

    G --> G2[适用场景<br/>• 批量处理<br/>• 数据分析<br/>• 报表统计]

    E --> H[Serializable<br/>可串行化]

    H --> H1[特点<br/>• 最高隔离级别<br/>• 真正可串行化<br/>• 冲突检测<br/>• 可能需要重试]

    H --> H2[适用场景<br/>• 金融交易<br/>• 库存管理<br/>• 关键业务操作]

    F1 --> I[权衡考虑<br/>一致性 vs 并发性]
    G1 --> I
    H1 --> I

图表说明：此决策图展示了如何根据一致性需求选择合适的事务隔离级别。Read Committed提供基本的一致性保证，适合大多数常规应用。Repeatable Read提供快照一致性，适合需要在事务期间看到稳定数据视图的场景。Serializable提供最严格的一致性保证，适合关键业务操作。图中还列出了每个级别的特点和典型应用场景，帮助开发者根据实际需求做出选择。

四、锁机制

4.1 锁的类型

PostgreSQL使用多种锁类型来控制并发访问。虽然MVCC机制大大减少了锁的需求，但在某些情况下仍需要使用锁来保证数据一致性。

表级锁：

ACCESS SHARE锁：SELECT命令获取，允许其他事务也获取ACCESS SHARE锁。
ROW SHARE锁：SELECT FOR UPDATE和SELECT FOR SHARE获取。
ROW EXCLUSIVE锁：INSERT、UPDATE、DELETE获取，允许其他事务获取ROW SHARE锁。
SHARE UPDATE EXCLUSIVE锁：VACUUM（非FULL）、ANALYZE、CREATE INDEX CONCURRENTLY获取。
SHARE锁：CREATE INDEX（非CONCURRENTLY）获取。
SHARE ROW EXCLUSIVE锁：某些DDL命令获取。
EXCLUSIVE锁：允许ROW SHARE锁，阻止其他事务修改。
ACCESS EXCLUSIVE锁：最严格的锁，DROP TABLE、TRUNCATE、ALTER TABLE等获取。

行级锁：

FOR UPDATE锁：SELECT FOR UPDATE获取，锁定行防止修改或删除。
NO KEY UPDATE锁：类似FOR UPDATE但不阻塞外键检查。
FOR SHARE锁：允许其他事务获取SHARE锁，阻止修改。
FOR KEY SHARE锁：最弱的行锁，允许其他事务修改但不删除。

4.2 锁的使用示例

-- 表级锁示例
BEGIN;
LOCK TABLE products IN ACCESS EXCLUSIVE MODE;
-- 现在只有当前事务可以访问该表
-- 执行需要独占访问的操作
TRUNCATE products;
COMMIT;
 
-- 行级锁示例
BEGIN;
-- 锁定特定行
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
 
-- 现在其他事务无法修改或删除该行
-- 其他事务会等待直到当前事务提交或回滚
 
UPDATE orders SET status = 'processing' WHERE id = 1;
COMMIT;
 
-- 带NOWAIT的行锁（不等待立即返回错误）
SELECT * FROM orders WHERE id = 1 FOR UPDATE NOWAIT;
 
-- 带SKIP LOCKED的行锁（跳过已锁定的行）
SELECT * FROM orders FOR UPDATE SKIP LOCKED
LIMIT 10;

4.3 锁冲突矩阵

请求的锁模式	当前的锁模式
ACCESS SHARE	ROW SHARE
------------	----------
ACCESS SHARE	✓
ROW SHARE	✓
ROW EXCLUSIVE	✓
SHARE UPDATE EXCLUSIVE	✓
SHARE	✓
SHARE ROW EXCLUSIVE	✓
EXCLUSIVE	✓
ACCESS EXCLUSIVE	✗

五、死锁与解决方案

5.1 死锁产生的原因

死锁是指两个或多个事务相互等待对方持有的资源，导致所有事务都无法继续执行的情况。在PostgreSQL中，死锁通常发生在以下场景：

不同顺序获取锁：事务A获取表1的锁然后等待表2的锁，而事务B获取表2的锁然后等待表1的锁。
行锁顺序不一致：两个事务以不同的顺序锁定多行。
长时间持有锁：事务持有锁的时间过长，增加与其他事务冲突的概率。

5.2 死锁示例

-- 会话1：事务A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 此时id=1的行被事务A锁定
 
-- 会话2：事务B
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
-- 此时id=2的行被事务B锁定
 
-- 会话1：事务A尝试锁定id=2的行
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 等待事务B释放id=2的锁
 
-- 会话2：事务B尝试锁定id=1的行
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- 等待事务A释放id=1的锁
 
-- 此时形成死锁，PostgreSQL会检测到并中止其中一个事务

5.3 死锁检测与处理

PostgreSQL使用死锁检测器定期检查是否存在死锁。当检测到死锁时，系统会选择牺牲其中一个事务（通常是进行修改较少的事务），抛出错误并回滚该事务，让其他事务继续执行。

-- 死锁错误信息示例
ERROR: could not obtain lock on row in relation "accounts"
DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 12346.
Process 12346 waits for ShareLock on transaction 67891; blocked by process 12345.
HINT: See server log for query details.
CONTEXT: while updating tuple (1,6) in relation "accounts"

5.4 死锁预防策略

预防死锁的最佳实践：

统一锁顺序：所有事务以相同的顺序获取锁。
减少锁持有时间：尽快提交或回滚事务。
使用适当的隔离级别：避免过高的隔离级别。
添加重试逻辑：在应用层实现事务重试机制。

-- 应用层重试示例（伪代码）
function transfer_with_retry(from_id, to_id, amount, max_retries=3):
    for attempt in range(max_retries):
        try:
            BEGIN;
            UPDATE accounts SET balance = balance - amount WHERE id = from_id;
            UPDATE accounts SET balance = balance + amount WHERE id = to_id;
            COMMIT;
            return success;
        except deadlock_error:
            ROLLBACK;
            if attempt < max_retries - 1:
                sleep(random_backoff(attempt));
            else:
                return error;

六、Advisory Lock

6.1 Advisory Lock概述

Advisory Lock（建议锁）是PostgreSQL提供的一种应用级锁机制。与表锁和行锁不同，Advisory Lock不由数据库自动管理，而是完全由应用程序控制。应用程序可以使用任意64位整数作为锁标识符，实现自定义的并发控制逻辑。

Advisory Lock有两种模式：

Exclusive Advisory Lock：排他建议锁，同一时间只有一个事务可以持有。
Shared Advisory Lock：共享建议锁，多个事务可以同时持有。

6.2 Advisory Lock使用示例

-- 获取排他建议锁
SELECT pg_advisory_lock(12345);
-- 如果锁已被其他会话持有，当前会话会等待
 
-- 尝试获取排他建议锁（不等待）
SELECT pg_try_advisory_lock(12345);
-- 返回true表示获取成功，false表示失败
 
-- 获取会话级建议锁（事务结束时自动释放）
SELECT pg_advisory_xact_lock(12345);
 
-- 获取共享建议锁
SELECT pg_advisory_lock_shared(12345);
 
-- 释放建议锁
SELECT pg_advisory_unlock(12345);
 
-- 释放所有会话级建议锁
SELECT pg_advisory_unlock_all();

6.3 Advisory Lock应用场景

Advisory Lock在以下场景中特别有用：

跨多个表的原子操作：当需要锁定多个不相关的表时，可以使用Advisory Lock保证操作的原子性。
外部资源访问控制：控制对外部资源（如文件、API）的并发访问。
自定义业务逻辑：实现特定于业务需求的并发控制逻辑。
分布式任务调度：在分布式环境中协调任务的执行。

-- 示例：确保只有一个进程执行定时任务
BEGIN;
 
-- 使用任务ID作为锁标识符
PERFORM pg_try_advisory_xact_lock(1001);
 
-- 如果获取失败，说明其他进程正在执行
IF NOT FOUND THEN
    ROLLBACK;
    RAISE NOTICE 'Task is already running';
    RETURN;
END IF;
 
-- 执行定时任务
-- ...
 
COMMIT;
-- 锁自动释放

七、写前日志（WAL）

7.1 WAL机制概述

Write-Ahead Logging（WAL）是PostgreSQL确保数据持久性和崩溃恢复的核心机制。WAL的核心思想是在数据修改实际写入数据文件之前，先将修改记录到日志文件中。这种机制确保了在系统崩溃后，可以通过重放日志来恢复数据。

WAL提供了以下关键保证：

原子性：事务的原子性通过WAL实现，所有修改要么全部记录，要么全部不记录。
持久性：提交的事务不会因为系统崩溃而丢失。
恢复能力：系统可以从崩溃中恢复到一致状态。

7.2 WAL工作流程

flowchart TD
    A[事务开始] --> B[Shared Buffer<br/>内存缓冲区]
    B --> C[修改数据<br/>写入Shared Buffer]
    C --> D[WAL Buffer<br/>日志缓冲区]
    D --> E{事务COMMIT}
    E --> F[WAL写入磁盘<br/>fsync]
    F --> G[事务提交成功]
    G --> H[Checkpoint进程<br/>定期触发]
    H --> I[将Shared Buffer<br/>脏页写入数据文件]
    I --> J[WAL日志可归档<br/>或删除]

    K[系统崩溃] --> L[重启恢复]
    L --> M[读取WAL日志]
    M --> N[重放未刷盘的事务]
    N --> O[恢复到一致状态]

图表说明：此流程图展示了WAL机制的完整工作流程。正常操作时，数据修改首先写入内存中的Shared Buffer，同时WAL记录写入WAL Buffer。事务提交时，WAL必须先写入磁盘（fsync），然后事务才被视为提交成功。Checkpoint进程定期将Shared Buffer中的脏页写入数据文件。如果系统崩溃，重启时会读取WAL日志，重放所有未刷盘的事务，恢复到一致状态。这个流程确保了数据在任何情况下都不会丢失。

7.3 WAL配置参数

-- 查看WAL相关配置
SHOW wal_level;              -- WAL级别：minimal, replica, logical
SHOW fsync;                  -- 是否强制fsync
SHOW synchronous_commit;     -- 同步提交模式
SHOW wal_buffers;            -- WAL缓冲区大小
SHOW wal_writer_delay;       -- WAL写入进程延迟
SHOW commit_delay;           -- 事务提交延迟
 
-- 调整WAL级别（需要重启）
ALTER SYSTEM SET wal_level = 'replica';
 
-- 调整同步提交模式（即时生效）
ALTER SYSTEM SET synchronous_commit = 'off';  -- 异步提交，最快但可能丢失
ALTER SYSTEM SET synchronous_commit = 'local';  -- 本地同步
ALTER SYSTEM SET synchronous_commit = 'remote_write';  -- 远程写入
ALTER SYSTEM SET synchronous_commit = 'on';  -- 完全同步
 
-- 调整WAL缓冲区（需要重启）
ALTER SYSTEM SET wal_buffers = '64MB';

7.4 WAL级别的选择

WAL级别	描述	用途	日志量
minimal	最少日志，不记录足够的归档和复制信息	单机环境，不需要时间点恢复	最少
replica	支持归档和流复制	主从复制、时间点恢复	较多
logical	支持逻辑解码	逻辑复制、数据变更捕获	最多

八、行级安全策略（RLS）

8.1 RLS概述

Row-Level Security（RLS）是PostgreSQL提供的表级安全机制，允许基于行数据的属性控制用户访问权限。RLS通过定义安全策略，确保用户只能访问符合特定条件的数据行，即使他们对表有访问权限。

RLS的核心概念：

表启用RLS：使用ALTER TABLE … ENABLE ROW LEVEL SECURITY启用
策略定义：使用CREATE POLICY定义访问规则
策略类型：SELECT、INSERT、UPDATE、DELETE、ALL
策略表达式：基于用户、数据值、时间等条件

8.2 RLS配置示例

-- 创建多租户表
CREATE TABLE tenant_data (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    data TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- 启用行级安全
ALTER TABLE tenant_data ENABLE ROW LEVEL SECURITY;
 
-- 创建策略：用户只能看到自己租户的数据
CREATE POLICY tenant_isolation ON tenant_data
    USING (tenant_id = current_setting('app.current_tenant_id')::INTEGER);
 
-- 或者使用应用传递的用户信息
CREATE POLICY user_isolation ON tenant_data
    USING (user_id = current_setting('app.current_user_id')::INTEGER);
 
-- 创建策略：管理员可以看到所有数据
CREATE POLICY admin_all_access ON tenant_data
    TO admin_role
    USING (true);
 
-- 设置当前用户上下文
SET app.current_tenant_id = '1001';
 
-- 现在查询只会返回tenant_id=1001的数据
SELECT * FROM tenant_data;

8.3 RLS策略类型

-- SELECT策略（控制读取权限）
CREATE POLICY user_select_policy ON user_data
    FOR SELECT
    USING (user_id = current_user_id());
 
-- INSERT策略（控制插入权限）
CREATE POLICY user_insert_policy ON user_data
    FOR INSERT
    WITH CHECK (user_id = current_user_id());
 
-- UPDATE策略（控制更新权限）
CREATE POLICY user_update_policy ON user_data
    FOR UPDATE
    USING (user_id = current_user_id())
    WITH CHECK (user_id = current_user_id());
 
-- DELETE策略（控制删除权限）
CREATE POLICY user_delete_policy ON user_data
    FOR DELETE
    USING (user_id = current_user_id());
 
-- ALL策略（所有操作）
CREATE POLICY user_all_policy ON user_data
    FOR ALL
    USING (user_id = current_user_id());

8.4 RLS最佳实践

默认拒绝：启用RLS后，默认拒绝所有访问，显式定义允许的策略。
使用角色：结合数据库角色实现细粒度权限控制。
策略顺序：策略按名称字母顺序应用，确保策略顺序正确。
性能考虑：策略表达式可能影响查询性能，注意索引使用。

-- 查看表上的策略
SELECT * FROM pg_policies WHERE tablename = 'tenant_data';
 
-- 修改策略
ALTER POLICY user_select_policy ON user_data
    USING (tenant_id = current_setting('app.current_tenant_id')::INTEGER);
 
-- 删除策略
DROP POLICY user_select_policy ON user_data;
 
-- 禁用RLS（谨慎使用）
ALTER TABLE tenant_data DISABLE ROW LEVEL SECURITY;
 
-- 强制RLS（即使超级用户也受限制）
ALTER TABLE tenant_data FORCE ROW LEVEL SECURITY;

九、并发控制知识总结

事务隔离级别对照表

隔离级别	脏读	不可重复读	幻读	性能	适用场景
Read Uncommitted	可能	可能	可能	最高	几乎不用
Read Committed	不可能	可能	可能	高	默认级别，大多数应用
Repeatable Read	不可能	不可能	可能	中	报表、批量处理
Serializable	不可能	不可能	不可能	低	关键业务、金融交易

锁类型速查表

锁类型	命令	冲突	用途
ACCESS SHARE	SELECT	与ACCESS EXCLUSIVE冲突	读取表数据
ROW EXCLUSIVE	INSERT/UPDATE/DELETE	与SHARE等冲突	修改数据
SHARE	CREATE INDEX	与ROW EXCLUSIVE冲突	创建索引
EXCLUSIVE	LOCK TABLE	与大多数锁冲突	表维护
ACCESS EXCLUSIVE	DROP/TRUNCATE	与所有锁冲突	DDL操作

十、常见问题解答

Q1：MVCC如何解决读写冲突？

答：MVCC通过为每个数据行维护多个版本解决读写冲突。

当读取数据时，事务看到的是特定时间点的快照，读取操作不获取任何锁。当写入数据时，创建新版本而不是覆盖旧版本，旧版本对其他事务仍然可见。

这样读操作永远不会被写操作阻塞，写操作也不会被读操作阻塞，大大提高了并发性能。这是PostgreSQL高并发能力的基础。

Q2：何时使用Serializable隔离级别？

答：Serializable隔离级别适合需要严格一致性保证的关键应用，如金融交易、库存管理、订单处理等。

这些场景下，即使极小的数据不一致也可能导致严重问题。

但需要注意，Serializable级别会检测可串行化冲突并导致事务失败，应用层需要实现重试逻辑。对于大多数常规应用，Read Committed或Repeatable Read级别已经足够，Serializable级别的额外一致性保证可能不值得其性能代价和复杂性。

Q3：如何减少死锁发生？

答：减少死锁的主要方法包括：1）统一锁顺序，确保所有事务以相同的顺序获取资源；2）减少事务大小和持锁时间，尽快提交或回滚；3）使用适当的隔离级别，避免不必要的高隔离级别；4）在应用层实现重试逻辑，当死锁发生时自动重试；5）使用LOCK TABLE或SELECT FOR UPDATE NOWAIT提前检测冲突。

此外，监控数据库日志中的死锁信息，分析死锁模式并进行针对性优化也很重要。

Q4：Advisory Lock与普通锁有什么区别？

答：Advisory Lock与普通锁的主要区别在于：1）控制权不同，Advisory Lock完全由应用程序控制，数据库不管理其语义；2）范围不同，Advisory Lock可以锁定任意64位整数标识的资源，不限于表或行；3）持久性不同，Advisory Lock可以跨事务存在（会话级锁）；4）用途不同，Advisory Lock用于实现自定义的并发控制逻辑，如跨表操作、外部资源控制、分布式任务协调等。

普通锁由数据库自动管理，用于保证数据一致性。

Q5：WAL机制如何影响性能？

答：WAL机制主要通过以下方式影响性能：1）写入延迟，每次提交需要fsync将WAL写入磁盘，这是主要的性能开销；2）WAL写入争用，高并发时多个事务等待WAL写入；3）磁盘I/O，WAL文件需要额外的磁盘写入；4）Checkpoint开销，定期checkpoint需要大量磁盘I/O。

性能优化方法包括：调整synchronous_commit参数（如设为off或local）、增加wal_buffers、使用RAID或SSD、调整checkpoint参数等。需要权衡数据安全性和性能需求。

十一、总结与下一篇预告

本文深入讲解了PostgreSQL的事务处理和并发控制机制，包括事务的ACID特性、MVCC实现原理、四种隔离级别、锁机制、死锁处理、WAL机制以及行级安全策略。这些机制共同构成了PostgreSQL强大的并发控制能力，使其能够在高并发环境下保证数据一致性和系统稳定性。

MVCC是PostgreSQL并发控制的核心，通过多版本机制实现读写互不阻塞，大大提高了系统吞吐量。事务隔离级别提供了不同级别的一致性保证，开发者可以根据应用需求选择合适的级别。锁机制为需要显式控制的场景提供了精细的控制能力。WAL机制确保了数据的持久性和可恢复性。

下一篇预告：《PostgreSQL数据库技术第 7 篇：数据库管理与维护》将讲解数据库的日常运维工作，包括角色与权限管理、数据库连接配置、服务器参数优化、日常维护任务（VACUUM、ANALYZE、REINDEX）、备份恢复策略、监控与日志分析等内容，帮助读者掌握PostgreSQL数据库的管理和维护技能。

本文作为PostgreSQL数据库技术系列的第六篇，专注于事务处理和并发控制机制的深入讲解，为后续学习数据库管理打下坚实基础。

51学通信

探索

PostgreSQL数据库技术 第 6 篇：事务处理与并发控制

PostgreSQL数据库技术 第 6 篇：事务处理与并发控制

摘要

学习目标

一、事务基础概念

1.1 事务的定义

1.2 事务的ACID特性

1.3 事务边界管理

二、MVCC多版本并发控制

2.1 MVCC基本原理

2.2 MVCC数据结构

2.3 可见性判断规则

2.4 MVCC读操作图解

2.5 MVCC的优势与代价

三、事务隔离级别

3.1 隔离级别概述

3.2 Read Committed隔离级别

3.3 Repeatable Read隔离级别

3.4 Serializable隔离级别

3.5 隔离级别对比图

四、锁机制

4.1 锁的类型

4.2 锁的使用示例

4.3 锁冲突矩阵

五、死锁与解决方案

5.1 死锁产生的原因

5.2 死锁示例

5.3 死锁检测与处理

5.4 死锁预防策略

六、Advisory Lock

6.1 Advisory Lock概述

6.2 Advisory Lock使用示例

6.3 Advisory Lock应用场景

七、写前日志（WAL）

7.1 WAL机制概述

7.2 WAL工作流程

7.3 WAL配置参数

7.4 WAL级别的选择

八、行级安全策略（RLS）

8.1 RLS概述

8.2 RLS配置示例

8.3 RLS策略类型

8.4 RLS最佳实践

九、并发控制知识总结

事务隔离级别对照表

锁类型速查表

十、常见问题解答

Q1：MVCC如何解决读写冲突？

Q2：何时使用Serializable隔离级别？

Q3：如何减少死锁发生？

Q4：Advisory Lock与普通锁有什么区别？

Q5：WAL机制如何影响性能？

十一、总结与下一篇预告

关系图谱

目录

PostgreSQL数据库技术第 6 篇：事务处理与并发控制

PostgreSQL数据库技术第 6 篇：事务处理与并发控制