UPDATE查询中乐观锁实现：如何区分成功、版本冲突与实体未找到？

完全可以实现，通过CTE（公共表表达式）结合条件判断，就能在单条查询里区分更新成功、版本冲突、实体未找到三种状态。以下是针对你需求的具体实现（以PostgreSQL为例）：

WITH target_record AS (
    -- 先查询目标ID对应的记录，确认实体是否存在
    SELECT id, last_updated_at 
    FROM vessel_clearance 
    WHERE id = {vesselclearanceid}
),
update_operation AS (
    -- 执行更新操作，仅当版本匹配时生效
    UPDATE vessel_clearance vc
    SET status = 'blah',
        last_updated_at = {new_ts},
        expiry = {some_time}
    FROM target_record
    WHERE vc.id = target_record.id
        AND vc.last_updated_at = {current_version_ts}
    RETURNING 'UPDATED' AS operation_status
)
-- 最终判断并返回状态
SELECT 
    CASE
        WHEN EXISTS (SELECT 1 FROM target_record) THEN
            CASE WHEN EXISTS (SELECT 1 FROM update_operation) THEN 'UPDATED' ELSE 'VERSION_CONFLICT' END
        ELSE 'NOT_FOUND'
    END AS final_status;

逻辑说明 #

• target_record CTE：先定位指定ID的记录，后续用来判断实体是否存在
• update_operation CTE：执行实际的更新，只有当last_updated_at匹配当前版本时，才会修改记录并返回'UPDATED'
• 外层SELECT通过两层CASE判断：
  1. 若target_record无数据，直接返回NOT_FOUND（实体未找到）
  2. 若target_record存在，但update_operation无数据，说明版本不匹配，返回VERSION_CONFLICT（版本冲突）
  3. 若update_operation有数据，说明更新成功，返回UPDATED

执行这条查询后，只需读取final_status字段的值，就能直接区分三种状态。

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