1
-- 场景：大量读操作阻塞写操作
2
-- 事务 A：长时间读取
3
BEGIN;
4
SELECT * FROM orders WHERE status = 'pending';  -- 获取 S 锁
5
-- 此时事务 B 想要更新数据
6
-- 事务 B 被阻塞，直到事务 A 提交
7

8
-- 事务 B：等待更新
9
UPDATE orders SET status = 'processing' WHERE id = 1;  -- 等待 X 锁

1
-- 查看隐藏列
2
SELECT xmin, xmax, ctid, cmin, cmask, *
3
FROM users
4
LIMIT 5;

1
// 简化的可见性判断逻辑
2
bool IsVisible(Row row, Snapshot snap) {
3
    // 1. 插入事务必须在快照之前提交
4
    if (!TransactionIdDidCommit(row.xmin) ||
5
        row.xmin > snap.xmax) {
6
        return false;
7
    }
8

9
    // 2. 如果 xmax = 0 或未提交，则可见
10
    if (row.xmax == 0 ||
11
        !TransactionIdDidCommit(row.xmax)) {
12
        return true;
13
    }
14

15
    // 3. 如果删除事务在快照之后，则可见
16
    if (row.xmax > snap.xmax) {
17
        return true;
18
    }
19

20
    return false;
21
}

1
-- 事务 100: 插入数据
2
BEGIN;
3
INSERT INTO users (id, name) VALUES (1, 'Alice');
4
-- xmin=100, xmax=0
5
COMMIT;
6

7
-- 事务 101: 查询数据（看到 Alice）
8
BEGIN;
9
SELECT * FROM users WHERE id = 1;
10
-- 结果: Alice（xmin=100, xmax=0 可见）
11
COMMIT;
12

13
-- 事务 102: 更新数据
14
BEGIN;
15
UPDATE users SET name = 'Bob' WHERE id = 1;
16
-- 旧行: xmin=100, xmax=102（被标记删除）
17
-- 新行: xmin=102, xmax=0
18
COMMIT;
19

20
-- 事务 103: 查询数据（看到 Bob）
21
BEGIN;
22
SELECT * FROM users WHERE id = 1;
23
-- 结果: Bob（新行可见，旧行已被 xmax=102 标记）
24
COMMIT;

1
// PostgreSQL 快照结构（简化）
2
typedef struct SnapshotData {
3
    TransactionId xmin;  // 最小活跃事务 ID
4
    TransactionId xmax;  // 下一个要分配的事务 ID
5

6
    // 活跃事务 ID 数组
7
    TransactionId *xip;
8
    uint32 xcnt;
9

10
    // 时间戳
11
    TimestampTz snapshottime;
12
} SnapshotData;

1
-- 会话 A：使用可重复读
2
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3
-- 快照创建，假设此时 xmax = 100
4

5
SELECT balance FROM accounts WHERE id = 1;
6
-- 结果: 1000
7

8
-- 会话 B：更新数据并提交
9
BEGIN;
10
UPDATE accounts SET balance = 2000 WHERE id = 1;
11
COMMIT;
12

13
-- 会话 A：再次查询
14
SELECT balance FROM accounts WHERE id = 1;
15
-- 结果: 1000（仍然是快照中的值，不是 2000）
16

17
COMMIT;

1
-- 手动执行 Vacuum
2
VACUUM users;           -- 清理死元组，标记空间可重用
3
VACUUM FULL users;      -- 重建表，回收空间（会锁表）
4
VACUUM ANALYZE users;   -- 清理 + 更新统计信息
5

6
-- 查看表的死元组数量
7
SELECT relname, n_live_tup, n_dead_tup
8
FROM pg_stat_user_tables
9
WHERE relname = 'users';

1
# postgresql.conf 配置
2
autovacuum = on                    # 启用自动清理
3
autovacuum_vacuum_threshold = 50   # 基础阈值
4
autovacuum_vacuum_scale_factor = 0.2  # 死元组比例阈值
5

6
# 触发条件计算公式：
7
# 触发阈值 = autovacuum_vacuum_threshold +
8
#            autovacuum_vacuum_scale_factor * 表行数
9

10
# 例如：100 万行的表
11
# 触发阈值 = 50 + 0.2 * 1000000 = 200050 个死元组

1
-- 查看事务 ID 状态
2
SELECT relname, age(relfrozenxid) as xid_age,
3
       pg_size_pretty(pg_total_relation_size(oid)) as size
4
FROM pg_class
5
WHERE relkind = 'r'
6
ORDER BY age(relfrozenxid) DESC
7
LIMIT 10;
8

9
-- 年龄接近 20 亿时，需要手动冻结
10
VACUUM FREEZE tablename;

1
-- PostgreSQL: 直接扫描堆表，找到可见版本
2
SELECT * FROM users WHERE id = 1;
3

4
-- 扫描过程：
5
-- 1. 找到 id=1 的所有版本（可能有多个）
6
-- 2. 根据快照判断哪个版本可见
7
-- 3. 返回可见版本

1
-- MySQL: 通过 Undo Log 回滚到可见版本
2
SELECT * FROM users WHERE id = 1;
3

4
-- 读取过程：
5
-- 1. 读取最新版本
6
-- 2. 检查 TRX_ID 是否在 ReadView 中可见
7
-- 3. 若不可见，通过回滚指针找 Undo Log
8
-- 4. 回滚到可见版本

1
-- 1. 配置合理的 Autovacuum 参数
2
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;
3
ALTER SYSTEM SET autovacuum_vacuum_threshold = 100;
4

5
-- 2. 对高更新表单独配置
6
ALTER TABLE hot_table SET (
7
    autovacuum_vacuum_scale_factor = 0.05,
8
    autovacuum_vacuum_cost_delay = 2
9
);
10

11
-- 3. 监控表膨胀
12
SELECT schemaname, relname,
13
       n_live_tup, n_dead_tup,
14
       last_vacuum, last_autovacuum
15
FROM pg_stat_user_tables
16
WHERE n_dead_tup > 10000
17
ORDER BY n_dead_tup DESC;
18

19
-- 4. 避免长事务
20
SELECT pid, now() - pg_stat_activity.query_start AS duration,
21
       query, state
22
FROM pg_stat_activity
23
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';