如何将Oracle带外连接的FOR UPDATE子句转换为PostgreSQL

核心语法冲突原因 #

PostgreSQL 对FOR UPDATE行锁的外连接场景做了限制：不允许对外连接的可空侧（左连接的右表、右连接的左表、全连接的两侧）直接加锁，直接执行原SQL会抛出固定错误：

ERROR: SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join

另外原SQL本身存在通用语法问题：SELECT字段列表最后一个字段末尾多了冗余逗号，Oracle和PostgreSQL都不支持这种写法，适配时需要同步修正。

适配方案 #

根据业务对左连接语义的依赖程度，选择对应写法即可，两种写法都保留了原SQLNOWAIT（拿锁失败立即报错不等待）、仅锁TB2/TB3/TB4匹配行不锁TB1的语义。

方案1：业务上TB2/TB3/TB4一定存在和TB1的匹配记录 #

如果原SQL写LEFT JOIN属于冗余写法（实际三个关联表都一定有对应TB1.ID的记录，不会出现右表字段为NULL的情况），直接把左连接改为内连接即可正常加锁，写法最简单，性能最好：

SELECT
    TB1.ID AS USER_ID,
    TB1.USER_NAME AS USER_NAME,
    TB1.BIRTHDATE AS BIRTHDATE,
    TB2.AGE AS AGE,
    TB3.GENDER AS GENDER,
    TB4.SUBJECT AS SUBJECT
FROM
    TABLE1 AS TB1
INNER JOIN TABLE2 AS TB2 ON TB1.ID = TB2.ID
INNER JOIN TABLE3 AS TB3 ON TB1.ID = TB3.ID
INNER JOIN TABLE4 AS TB4 ON TB1.ID = TB4.ID
FOR UPDATE OF TB2, TB3, TB4 NOWAIT;

方案2：必须保留左连接语义（允许右表无匹配记录） #

如果业务上确实存在TB2/TB3/TB4没有对应TB1记录的场景，需要保留左连接返回NULL的逻辑，可以通过CTE提前对三个右表的匹配行加锁，再做关联查询，完全对齐原Oracle语义：

WITH
-- 提前锁定TABLE2中符合关联条件的行
LOCK_TB2 AS (
    SELECT ID, AGE FROM TABLE2
    WHERE ID IN (SELECT ID FROM TABLE1)
    FOR UPDATE OF TB2 NOWAIT
),
-- 提前锁定TABLE3中符合关联条件的行
LOCK_TB3 AS (
    SELECT ID, GENDER FROM TABLE3
    WHERE ID IN (SELECT ID FROM TABLE1)
    FOR UPDATE OF TB3 NOWAIT
),
-- 提前锁定TABLE4中符合关联条件的行
LOCK_TB4 AS (
    SELECT ID, SUBJECT FROM TABLE4
    WHERE ID IN (SELECT ID FROM TABLE1)
    FOR UPDATE OF TB4 NOWAIT
)
-- 执行原左连接查询，此时匹配到的右表行已成功加锁
SELECT
    TB1.ID AS USER_ID,
    TB1.USER_NAME AS USER_NAME,
    TB1.BIRTHDATE AS BIRTHDATE,
    TB2.AGE AS AGE,
    TB3.GENDER AS GENDER,
    TB4.SUBJECT AS SUBJECT
FROM
    TABLE1 AS TB1
LEFT JOIN TABLE2 AS TB2 ON TB1.ID = TB2.ID
LEFT JOIN TABLE3 AS TB3 ON TB1.ID = TB3.ID
LEFT JOIN TABLE4 AS TB4 ON TB1.ID = TB4.ID;

注意事项 #

• PostgreSQL中FOR UPDATE OF后支持直接写表别名，和Oracle用法一致，不需要额外写原表名。
• 方案2中CTE内的加锁逻辑会在主查询执行前完成，只要三个CTE执行成功，后续关联查询中匹配到的右表行都已经被当前事务加锁，不会出现锁遗漏的问题。
• 如果你的PostgreSQL版本低于12，CTE默认是物化执行的，加锁逻辑依然生效；版本大于等于12时，可以给CTE加MATERIALIZED关键字强制物化，避免优化器内联CTE导致锁逻辑失效。

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