不同表的INSERT与UPDATE并发操作为何触发死锁？

问题场景 #

现有两张PostgreSQL表A、B，表结构如下：

CREATE TABLE A
(
    id                    bigserial   NOT NULL,

    accounts_id           int8        NOT NULL REFERENCES users.accounts (id) ON DELETE CASCADE,
    entities_id           int8        NOT NULL REFERENCES entities.entities (id) ON DELETE CASCADE,
    webhooks_id           int8        NOT NULL REFERENCES settings.webhooks (id) ON DELETE CASCADE,

    PRIMARY KEY (id),
    UNIQUE (accounts_id, entities_id, webhooks_id, entity_revision)
);

CREATE TABLE B
(
    id BIGSERIAL PRIMARY KEY,
    accounts_id BIGINT REFERENCES users.accounts(id) NOT NULL,
    clients_id BIGINT REFERENCES users.clients(id) NOT NULL,

    UNIQUE (accounts_id, clients_id, uuid)
);

业务代码在单个事务中循环执行以下操作，多次迭代后提交事务：

-- 每次迭代执行：
-- 1. 表A的UPSERT操作
INSERT INTO A (
        accounts_id,
        entities_id,
        webhooks_id,
        metadata,
        state,
        available_after_time,
        retry_count,
        max_retries
    )
VALUES ($1, $2, $3, $4, $5, $6, $7, $8) 
ON CONFLICT (accounts_id, entities_id, webhooks_id) DO UPDATE
SET
    metadata = $4,
    state = $5,
    available_after_time = $6,
    retry_count = $7,
    max_retries = $8,
    created_at = $9;

-- 2. 表B的UPDATE操作
UPDATE B
    SET 
        index_required = NULL,
        index_current = NULL
    WHERE
        id = $1 AND accounts_id = $2;

生产环境并发请求时，出现跨表死锁，死锁由上述UPSERT和UPDATE操作相互等待触发。

死锁原因分析 #

1. UPSERT的锁行为特性 #

PostgreSQL的INSERT...ON CONFLICT（UPSERT）操作分为两个阶段：

• 阶段1：尝试插入新行时，会对目标行对应的唯一索引项加意向排他锁（用于协调表级锁与行级锁）；
• 阶段2：如果检测到唯一键冲突，会等待持有该行排他锁的事务释放，然后获取行级排他锁（ROW EXCLUSIVE）执行UPDATE逻辑。
  如果并发事务处理的行顺序不同，锁等待的顺序就可能形成循环。

2. 跨表事务的锁顺序不一致 #

死锁的核心条件是循环等待锁资源，即使操作的是不同表，只要两个并发事务的锁获取顺序颠倒，就会触发死锁：

• 事务1：先获取表A行X的锁 → 等待表B行Y的锁
• 事务2：先获取表B行Y的锁 → 等待表A行X的锁
  这种情况下，两个事务会互相等待对方释放锁，最终触发死锁。

3. 循环迭代的随机处理顺序 #

如果循环中处理的行没有固定排序（比如从数据库查询的结果未指定ORDER BY，或处理的是用户请求的随机顺序），不同并发事务的操作顺序就可能随机颠倒，大大增加死锁概率。

4. 外键约束的隐式锁影响 #

表A和B都包含指向users.accounts的外键，当修改子表行时，PostgreSQL会自动检查父表行的存在性，并对父表对应行加SHARE ROW EXCLUSIVE锁（防止父表行被删除或修改）。如果并发事务同时操作不同子表但同一父表行，也可能因锁顺序问题加剧死锁风险。

解决建议 #

• 强制统一锁获取顺序：所有事务都按固定顺序处理表（比如先完成所有表A的UPSERT操作，再执行表B的UPDATE），或对要操作的行按主键/唯一键升序排序后处理，确保所有事务的锁获取顺序完全一致。
• 缩小事务范围：避免在单个大事务中循环多次操作，尽量将每次迭代的操作拆分为独立小事务，减少锁的持有时间，降低冲突概率。
• 优化UPSERT逻辑：如果场景允许，可先通过SELECT ... FOR UPDATE提前锁定目标行，再执行UPSERT，避免锁等待顺序混乱。
• 固定循环处理顺序：确保循环中处理的行始终按固定规则排序（比如按id升序），消除随机顺序带来的锁顺序颠倒风险。

内容的提问来源于stack exchange，提问作者Kaushal28