PostgreSQL LOCK（锁）

下面是一份 PostgreSQL 锁（LOCK） 的 完整实战手册，涵盖 锁类型、锁粒度、加锁语法、死锁检测、锁监控、性能优化、并发控制、常见陷阱与最佳实践，适合开发、DBA、架构师使用。

一、PostgreSQL 锁概览

PostgreSQL 使用 多版本并发控制（MVCC），读不阻塞写，写不阻塞读，但 写写之间仍需锁。

锁粒度	说明
行级锁（Row-level）	最常用，粒度最小
页级锁（Page-level）	内部使用，少见
表级锁（Table-level）	影响大，慎用
事务级锁	控制事务行为

二、行级锁（Row-level Locks）—— 核心

锁模式	说明	冲突情况
`FOR UPDATE`	排他锁，禁止他人修改/删除	其他 `FOR UPDATE`、`UPDATE`、`DELETE`
`FOR NO KEY UPDATE`	较弱排他锁，允许 `FOR SHARE`	其他 `FOR NO KEY UPDATE`、`FOR UPDATE`
`FOR SHARE`	共享锁，允许多人读，禁止修改	其他 `FOR UPDATE`、`UPDATE`、`DELETE`
`FOR KEY SHARE`	最弱共享锁，允许 `FOR NO KEY UPDATE`	仅 `FOR UPDATE`

语法示例

BEGIN;

-- 1. 排他锁（悲观锁）
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- 2. 允许读，但禁止修改
SELECT * FROM products WHERE id = 10 FOR SHARE;

-- 3. 允许弱更新
SELECT * FROM orders WHERE id = 100 FOR NO KEY UPDATE;

-- 4. 最弱锁
SELECT * FROM logs WHERE id = 1000 FOR KEY SHARE;

COMMIT;

三、表级锁（Table-level Locks）

锁模式	说明	语法
`ACCESS SHARE`	`SELECT` 自动获取	最弱
`ROW SHARE`	`SELECT FOR SHARE` 获取
`ROW EXCLUSIVE`	`UPDATE/DELETE/INSERT` 获取
`SHARE`	允许读，禁止结构变更	`LOCK TABLE t IN SHARE MODE;`
`SHARE ROW EXCLUSIVE`	更强共享
`EXCLUSIVE`	禁止读写	`LOCK TABLE t IN EXCLUSIVE MODE;`
`ACCESS EXCLUSIVE`	最强，禁止一切	`ALTER TABLE`, `DROP TABLE` 自动获取

显式加表锁

-- 允许读，禁止写
LOCK TABLE products IN SHARE MODE;

-- 禁止所有访问（用于维护）
LOCK TABLE big_table IN ACCESS EXCLUSIVE MODE;

注意：表锁影响大，生产慎用

四、加锁最佳实践：悲观锁 vs 乐观锁

1. 悲观锁（Pessimistic Locking）—— `FOR UPDATE`

BEGIN;

SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

-- 检查余额
IF balance < 100 THEN
    RAISE EXCEPTION 'Insufficient funds';
END IF;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

COMMIT;

2. 乐观锁（Optimistic Locking）—— 版本号

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    stock INT,
    version INT DEFAULT 1
);

-- 更新时检查版本
UPDATE products 
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;

-- 影响行数 = 0 → 冲突，重试

五、死锁（Deadlock）检测与处理

PostgreSQL 自动检测死锁，每隔 deadlock_timeout（默认 1s）检查。

ERROR:  deadlock detected
DETAIL:  Process 123 waits for ShareLock on transaction 456...
HINT:  See server log for query details.

死锁示例

-- 会话1
BEGIN;
UPDATE accounts SET balance = 100 WHERE id = 1;
UPDATE accounts SET balance = 200 WHERE id = 2;  -- 等待

-- 会话2
BEGIN;
UPDATE accounts SET balance = 300 WHERE id = 2;
UPDATE accounts SET balance = 400 WHERE id = 1;  -- 死锁！

解决策略

统一加锁顺序
缩短事务时间
使用 FOR UPDATE SKIP LOCKED（跳过被锁行）
重试机制

-- 跳过被锁行（适合队列）
SELECT * FROM tasks 
WHERE status = 'pending' 
FOR UPDATE SKIP LOCKED
LIMIT 10;

六、锁监控与诊断

1. 查看当前锁

SELECT 
    pid,
    datname,
    usename,
    relation::regclass AS table,
    mode,
    granted,
    query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE relation IS NOT NULL;

2. 查看阻塞关系

SELECT 
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.database = blocking_locks.database
    AND blocked_locks.relation = blocking_locks.relation
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;

3. 终止锁

-- 终止阻塞进程
SELECT pg_terminate_backend(123);

-- 取消查询
SELECT pg_cancel_backend(123);

七、特殊锁场景

1. `INSERT` 锁

INSERT 获取 行级排他锁
冲突：主键/唯一键重复

INSERT INTO users(email) VALUES ('a@example.com');
-- 若 email 重复 → 等待或错误

2. `VACUUM` 锁

VACUUM 获取 SHARE UPDATE EXCLUSIVE 锁
允许读写，但禁止 ALTER TABLE

3. `CREATE INDEX CONCURRENTLY`

获取 SHARE UPDATE EXCLUSIVE 锁
不阻塞读写

CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

八、性能优化技巧

技巧	说明
避免 `SELECT * FOR UPDATE`	只锁必要列
使用 `NOWAIT`	立即失败，避免等待
使用 `SKIP LOCKED`	高并发队列
缩短事务	减少锁持有时间
分区表	减小锁冲突

-- 立即失败
SELECT * FROM queue FOR UPDATE NOWAIT;

-- 跳过被锁行
SELECT * FROM queue FOR UPDATE SKIP LOCKED LIMIT 1;

九、常见陷阱与避坑

问题	原因	解决
死锁频繁	加锁顺序不一致	统一顺序
查询慢	锁等待	用 `NOWAIT` 或 `SKIP LOCKED`
表被锁住	显式 `LOCK TABLE`	避免表锁
`FOR UPDATE` 没生效	在子查询中	放在最外层
分布式死锁	多节点	用 `SERIALIZABLE` 或应用锁

十、最佳实践脚本

1. 高并发转账（悲观锁 + 重试）

CREATE OR REPLACE FUNCTION transfer_money(
    from_id INT, to_id INT, amount NUMERIC, max_retries INT = 3
) RETURNS BOOLEAN AS $$
DECLARE
    i INT := 0;
    locked_from BOOLEAN;
    locked_to BOOLEAN;
BEGIN
    LOOP
        i := i + 1;
        BEGIN
            -- 固定顺序：小 ID 先锁
            IF from_id < to_id THEN
                SELECT TRUE FROM accounts WHERE id = from_id FOR UPDATE NOWAIT INTO locked_from;
                SELECT TRUE FROM accounts WHERE id = to_id FOR UPDATE NOWAIT INTO locked_to;
            ELSE
                SELECT TRUE FROM accounts WHERE id = to_id FOR UPDATE NOWAIT INTO locked_to;
                SELECT TRUE FROM accounts WHERE id = from_id FOR UPDATE NOWAIT INTO locked_from;
            END IF;

            -- 执行转账
            UPDATE accounts SET balance = balance - amount WHERE id = from_id;
            UPDATE accounts SET balance = balance + amount WHERE id = to_id;

            RETURN TRUE;

        EXCEPTION
            WHEN lock_not_available THEN
                IF i >= max_retries THEN RAISE; END IF;
                PERFORM pg_sleep(0.05 * i);
            WHEN deadlock_detected THEN
                IF i >= max_retries THEN RAISE; END IF;
                PERFORM pg_sleep(0.1 * i);
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

2. 队列消费（SKIP LOCKED）

BEGIN;

SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY priority DESC, created_at
FOR UPDATE SKIP LOCKED
LIMIT 10;

-- 处理后更新状态
UPDATE job_queue SET status = 'processing', worker = current_user
WHERE id IN (...);

COMMIT;

十一、锁模式兼容性表

当前锁 \ 请求锁	ACCESS SHARE	ROW SHARE	ROW EXCLUSIVE	SHARE	SHARE ROW EXCLUSIVE	EXCLUSIVE	ACCESS EXCLUSIVE
ACCESS SHARE	Yes	Yes	Yes	Yes	Yes	Yes	No
ROW SHARE	Yes	Yes	Yes	Yes	Yes	No	No
ROW EXCLUSIVE	Yes	Yes	Yes	No	No	No	No
SHARE	Yes	Yes	No	Yes	No	No	No
EXCLUSIVE	Yes	No	No	No	No	No	No
ACCESS EXCLUSIVE	No	No	No	No	No	No	No

十二、速查表

命令	用途
`FOR UPDATE`	排他锁
`FOR SHARE`	共享锁
`FOR UPDATE SKIP LOCKED`	跳过被锁行
`FOR UPDATE NOWAIT`	立即失败
`LOCK TABLE t IN EXCLUSIVE MODE;`	表级排他锁
`SELECT pg_terminate_backend(pid);`	杀进程
`deadlock_timeout = 1s`	死锁检测间隔

十三、锁决策树

graph TD
    A[需要防止并发修改?] -->|是| B{是否高并发?}
    B -->|是| C{是否可接受冲突?}
    C -->|是| D[用乐观锁 version]
    C -->|否| E[用 FOR UPDATE SKIP LOCKED]
    B -->|否| F[用 FOR UPDATE]
    A -->|否| G[无需锁]

十四、常见面试题

问题	答案
`SELECT` 会加锁吗？	不会，除非 `FOR UPDATE`
死锁如何产生？	循环等待 + 不可抢占
`FOR UPDATE` 锁的是什么？	行级排他锁
如何避免死锁？	固定加锁顺序 + 缩短事务
`SKIP LOCKED` 适用场景？	任务队列、秒杀

需要我提供一个完整的 “高并发秒杀系统 + 锁监控 + 自动重试” 的生产级模板吗？

一、PostgreSQL 锁概览

二、行级锁（Row-level Locks）—— 核心

语法示例

三、表级锁（Table-level Locks）

显式加表锁

四、加锁最佳实践：悲观锁 vs 乐观锁

1. 悲观锁（Pessimistic Locking）—— `FOR UPDATE`

2. 乐观锁（Optimistic Locking）—— 版本号

五、死锁（Deadlock）检测与处理

死锁示例

解决策略

六、锁监控与诊断

1. 查看当前锁

2. 查看阻塞关系

3. 终止锁

七、特殊锁场景

1. `INSERT` 锁

2. `VACUUM` 锁

3. `CREATE INDEX CONCURRENTLY`

八、性能优化技巧

九、常见陷阱与避坑

十、最佳实践脚本

1. 高并发转账（悲观锁 + 重试）

2. 队列消费（SKIP LOCKED）

十一、锁模式兼容性表

十二、速查表

十三、锁决策树

十四、常见面试题

PostgreSQL WHERE 子句

PostgreSQL INSERT INTO 语句

PostgreSQL 创建数据库

PostgreSQL 常用函数

Windows 上安装 PostgreSQL

PostgreSQL ALTER TABLE 命令

发表回复取消回复

一、PostgreSQL 锁概览

二、行级锁（Row-level Locks）—— 核心

语法示例

三、表级锁（Table-level Locks）

显式加表锁

四、加锁最佳实践：悲观锁 vs 乐观锁

1. 悲观锁（Pessimistic Locking）—— FOR UPDATE

2. 乐观锁（Optimistic Locking）—— 版本号

五、死锁（Deadlock）检测与处理

死锁示例

解决策略

六、锁监控与诊断

1. 查看当前锁

2. 查看阻塞关系

3. 终止锁

七、特殊锁场景

1. INSERT 锁

2. VACUUM 锁

3. CREATE INDEX CONCURRENTLY

八、性能优化技巧

九、常见陷阱与避坑

十、最佳实践脚本

1. 高并发转账（悲观锁 + 重试）

2. 队列消费（SKIP LOCKED）

十一、锁模式兼容性表

十二、速查表

十三、锁决策树

十四、常见面试题

类似文章

发表回复 取消回复

1. 悲观锁（Pessimistic Locking）—— `FOR UPDATE`

1. `INSERT` 锁

2. `VACUUM` 锁

3. `CREATE INDEX CONCURRENTLY`

发表回复取消回复