LEFT JOIN外连接下FOR UPDATE多表锁触发0A000报错咨询

执行带FOR UPDATE的左连接查询时触发报错：

SQL Error [0A000]: ERROR: FOR UPDATE cannot be applied on the nullable side of the outer join

两段复现SQL如下：

可正常执行的SQL（仅锁定左表TB1） #

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 FOR UPDATE OF TB1 NOWAIT ;

触发报错的SQL（同时锁定四张关联表） #

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 FOR UPDATE OF TB1,TB2,TB3,TB4 NOWAIT; 

这个报错是数据库层面的机制限制，核心逻辑有两点：

• 左外连接（LEFT JOIN）的返回规则是保留左表全部匹配行，右表未匹配上的位置全部填充NULL值。这些填充出来的NULL行只是结果集里的临时展示值，并不是右表中真实存在的物理数据行。
• FOR UPDATE 行锁的本质是给数据库表中真实存在的物理行加互斥锁，不存在的虚拟行、临时填充的NULL值没有对应的物理存储位置，无法完成加锁操作。

对应两段SQL的执行差异：

• 仅锁定TB1时，TB1是所有左连接的左表（非可空侧），结果集中所有TB1的行都是表中真实存在的物理行，没有空值填充的场景，因此可以正常加锁执行。
• 同时锁定TB2、TB3、TB4时，这三张表都是左连接的右表（可空侧），只要存在TB1的记录在这三张表中没有匹配项的情况，结果集里对应右表的位置就是临时生成的NULL值，数据库无法定位到要加锁的真实物理行，就会抛出该错误。

根据业务场景可以选两种处理方式：

• 如果业务允许过滤掉右表无匹配的记录，把对应右表和TB1的关联从LEFT JOIN改成INNER JOIN即可。内连接返回的所有右表记录都是真实存在的匹配行，满足加锁条件。
• 如果业务必须保留左连接逻辑（即不能丢失右表无匹配的TB1记录），就拆分加锁逻辑：先在查询中仅对TB1加锁，拿到关联ID后，单独查询右表中ID匹配的真实记录并加锁，不要在同一个左连接查询中直接对右表加锁。

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