PostgreSQL 索引与分布式锁的正确姿势

事情是这样的

最近一个订单同步脚本，三台机器跑同一个 cron。我一开始的想法很朴素：数据库里加张 lock 表，lock_key 建唯一索引，谁 insert 成功谁执行。

然后线上翻车了。

任务确实只 insert 成功一次，但后面的 HTTP 拉数据是在事务提交以后跑的，另一台机器进来一看 key 已经存在，也觉得自己拿到了锁。我当时就有点绷不住了，这锁到底是在锁啥？

后来翻文档、本地 psql 反复试，才发现几个坑：锁的生命周期、事务边界、索引有没有用上、hashtext 碰撞。下面按我踩完以后认为比较稳的方式记一下。

先说结论：advisory lock 比“自己建锁表”省心

如果你的互斥单位是“这个任务名 / 这个订单 / 这个文件”，PostgreSQL 有现成的 advisory lock。它不用表，不用你维护唯一索引，生命周期跟事务绑定，非常符合“我就是在这一小段代码里不想让同一把 key 同时跑”。

BEGIN;

SELECT pg_try_advisory_xact_lock(hashtext('orders_sync')::bigint);
-- 返回 true：拿到锁；返回 false：别人正在跑

-- 在这里查状态、更新任务、调用下游
-- COMMIT 时自动释放

COMMIT;

这段的重点不是那条 SELECT，是 BEGIN / COMMIT。

如果你用 ORM 默认 autocommit，每执行一条 SQL 就一个事务，那 pg_try_advisory_xact_lock 基本等于白写：锁刚拿到，第一个事务就提交了，锁也释放了。然后后面真正的业务又变成裸奔。

Python 里我会这么写，psycopg 3 版本：

import psycopg

def run_job(job_id):
    with psycopg.connect("dbname=mydb user=postgres password=postgres") as conn:
        with conn.transaction():
            with conn.cursor() as cur:
                cur.execute(
                    "SELECT pg_try_advisory_xact_lock(hashtext(%s)::bigint)",
                    ("orders_sync",),
                )
                got = cur.fetchone()[0]
                if not got:
                    return

                cur.execute(
                    """
                    UPDATE jobs
                    SET status = 'running', started_at = now()
                    WHERE id = %s
                    """,
                    (job_id,),
                )

如果你用 psycopg2，逻辑一样，关键是别让连接池在一个 query 后自动 commit，或者你至少要显式 BEGIN / COMMIT 包住“取锁 + 干活”。

但是 hashtext 别太信任

hashtext('orders_sync') 是 32 位 int，转 bigint 只是符号位的事。它快，但它会撞。

如果你的任务名特别多、又是金融/订单这种不能重复执行的场景，我会更建议用应用层生成 64 位稳定 key，或者至少把业务 ID 放进去：

SELECT pg_try_advisory_xact_lock(
  (('x' || substr(md5('orders_sync'), 1, 8))::bit(32)::int),
  (('x' || substr(md5('10086'), 1, 8))::bit(32)::int)
);

这写法有点丑，但能用两个 32 位参数降低撞 key 的概率。别问我为什么不是直接 bigint hash，PostgreSQL advisory lock 的 bigint 版本内部也是组合 classid/objid 那套。

唯一索引还是有用，但别把它当“锁住了整个事务”

唯一索引真正的强项是“插入/记录层面只成功一次”，也就是幂等。比如你不在乎谁执行，只要保证同一个 job_id 只被成功记录一次，这就够了：

BEGIN;

INSERT INTO job_history(job_id, worker_id, started_at)
VALUES ('10086', 'worker-a', now())
ON CONFLICT (job_id) DO NOTHING
RETURNING job_id;

-- 返回 1 行：我是第一个记录的人，继续执行
-- 返回 0 行：已经有别人记录了，放弃或走补偿

COMMIT;

但如果你把这段当成互斥锁，后面长时间调用外部 API，然后等 API 回来再 commit，那锁确实会等到 commit 才释放。

问题是你这个“锁”只锁住了 job_history 这一行，如果别的进程走另一套逻辑，比如它先 update 状态再执行，它根本不认你这张历史表，还是会并发。

所以我的姿势是：

需要“同一时刻只允许一个动作”，用 advisory transaction lock。
需要“同一件事最多被成功记一次”，用唯一索引 + ON CONFLICT。
两个一起用也可以：先 advisory 锁住，再 insert 幂等记录，这样即使代码漏了某条路径，唯一索引还能兜底。

如果你做的是任务队列，其实不该自己搞锁表

多台机器抢任务这个场景，PostgreSQL 原生就支持得挺优雅：

BEGIN;

SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;

UPDATE jobs
SET status = 'running', worker = 'worker-a'
WHERE id = 1;

COMMIT;

这里真正关键的是索引，否则你会一边抢任务一边全表扫，顺便把很多行锁来锁去。建议：

CREATE INDEX idx_jobs_pending_id ON jobs (id)
WHERE status = 'pending';

或者如果你查询带 created_at 排序：

CREATE INDEX idx_jobs_pending_created
ON jobs (created_at, id)
WHERE status = 'pending';

表达式/部分索引必须跟你的查询条件完全一致。别表里写 status = 'pending'，代码里查 lower(status) = 'pending'，然后又问为什么不走索引。PostgreSQL 不傻，但查询计划也不会给你免费翻译。

一个本地快速验证的例子

如果你想自己看 advisory lock 是不是跟事务绑在一起，可以开两个 psql。我这里用 Docker 快速起一个：

docker run -it --rm -e POSTGRES_PASSWORD=postgres -p 5432:5432 postgres:16

如果 Docker Hub 慢，可以把镜像名前面换成你自己的加速域名；我这里没换，因为不想把临时笔记搞得太依赖某个加速站。

进去以后：

-- 终端 A
BEGIN;
SELECT pg_try_advisory_xact_lock(hashtext('test_lock')::bigint);
-- true

-- 终端 B
BEGIN;
SELECT pg_try_advisory_xact_lock(hashtext('test_lock')::bigint);
-- false

-- 终端 A
COMMIT;

-- 终端 B 再执行
SELECT pg_try_advisory_xact_lock(hashtext('test_lock')::bigint);
-- true

终端 B 后面能拿到，是因为 A 的事务已经提交了，transaction lock 随着 A 的事务结束释放了。

如果你用的是 pg_try_advisory_lock，那就是 session 级，事务提交后还会继续存在，直到当前连接释放，或者你显式 pg_advisory_unlock。这就是另一个坑：连接池里 session lock 不释放，下一次借连接的人莫名其妙的“被锁住了”。

索引姿势错误时，锁等待会变成玄学

我见过一种写法，把 lock name 存成 text，然后查询：

SELECT * FROM distributed_locks
WHERE lock_name = 'orders'
FOR UPDATE;

这没问题。

然后有人改字段成 lock_key text primary key，但查询变成：

SELECT * FROM distributed_locks
WHERE md5(lock_key) = md5('orders')
FOR UPDATE;

然后索引失效，全表扫。更坑的是它还会锁扫描到的行，取决于事务隔离级别和计划，有时表现得像“怎么这个无关 key 也被 block 了”。

所以如果你的锁表真要用唯一索引做 key，就老老实实让 lock_key 走 B-tree primary key。需要短一点的索引键，可以在插入前应用层算一个 hash/UUID 存进去，别在 WHERE 里现场算函数表达式，除非你建了表达式索引。

最后说人话版本

临时互斥：用 pg_try_advisory_xact_lock，必须包在事务里。
幂等记录：用唯一索引 + ON CONFLICT DO NOTHING，也别在事务外执行。
任务队列：用 FOR UPDATE SKIP LOCKED + 部分索引。
锁表 key：别在查询里随手 md5() / lower() / substring()，要么存归一化值，要么建表达式索引。
连接池：session lock 慎用，transaction lock 更适合大部分业务。
hashtext：小工具能用，核心业务自己算一个稳定 64-bit key。

不出问题的话就没有问题了。如果还出问题，大概率是事务边界、连接复用、或者你以为走了索引其实查询计划没走。把 EXPLAIN 和 BEGIN/COMMIT 一起看一眼，通常会安静很多。

事情是这样的

先说结论：advisory lock 比“自己建锁表”省心

但是 hashtext 别太信任

唯一索引还是有用，但别把它当“锁住了整个事务”

如果你做的是任务队列，其实不该自己搞锁表

一个本地快速验证的例子

索引姿势错误时，锁等待会变成玄学

最后说人话版本

评论

发表评论

PostgreSQL 索引与 分布式锁 的正确姿势

事情是这样的

先说结论：advisory lock 比“自己建锁表”省心

但是 hashtext 别太信任

唯一索引还是有用，但别把它当“锁住了整个事务”

如果你做的是任务队列，其实不该自己搞锁表

一个本地快速验证的例子

索引姿势错误时，锁等待会变成玄学

最后说人话版本

评论

发表评论

PostgreSQL 索引与分布式锁的正确姿势