Postgres视图是否有等效FOR UPDATE的锁？解决更新竞态问题

我在视图usr_view上创建了INSTEAD OF UPDATE触发器，视图定义如下：

CREATE VIEW usr_view AS
  SELECT
    us.id,
    uv.col1,
    uv.col2,
    uv.col3
  FROM usr_static AS us
  LEFT JOIN usr_version AS uv ON us.id = us.id AND uv.create_time = (
    SELECT MAX(uv2.create_time)
    FROM usr_version AS uv2
    WHERE uv2.id = us.id
  )

触发器函数负责向底层版本表插入新行：

CREATE FUNCTION shared.on_update_view() RETURNS TRIGGER
    LANGUAGE plpgsql
AS $$
DECLARE
    view_name text := TG_TABLE_NAME;
    schema_name text := TG_TABLE_SCHEMA;
    version_table_name text := schema_name || '.' || view_name || '_version';
BEGIN
    -- Insert into the version table
    EXECUTE format('INSERT INTO %s (col1, col2, col3) SELECT col1, col2, col3 FROM (SELECT $1.*) AS new_table', version_table_name)
    USING NEW;

    RETURN NEW;
END;
$$;

当两个UPDATE查询同时操作该视图时，出现了竞态问题：

初始usr_version表数据：

(id, col1, col2, col3, ...)  VALUES (1, 'A', 'B', 'C', ...)

同时执行两个更新：

-- 查询1
UPDATE usr_view SET col1 = 'foo' WHERE id = 1;

-- 查询2
UPDATE usr_view SET col2 = 'bar' WHERE id = 1;

查询1插入的行：

(id, col1, col2, col3, ...)  VALUES (1, 'foo', 'B', 'C', ...)

查询2插入的行：

(id, col1, col2, col3, ...)  VALUES (1, 'A', 'Bar', 'C', ...)

视图只会展示usr_version中最新的行，最终结果要么是查询1的内容，要么是查询2的内容，但预期应该是合并两个更新的结果：

(id, col1, col2, col3, ...)  VALUES (1, 'Foo', 'Bar', 'C', ...)

PostgreSQL更新普通表时会通过FOR UPDATE锁避免这类问题，但视图的INSTEAD OF UPDATE触发器似乎没有这个机制，请问如何确保两个查询串行执行以得到预期结果？

1. 锁定底层静态表行（推荐，保证强一致性） #

修改触发器函数，先对usr_static表中对应id的行加FOR UPDATE锁，强制并发更新串行执行，同时合并最新版本数据：

CREATE FUNCTION shared.on_update_view() RETURNS TRIGGER
    LANGUAGE plpgsql
AS $$
DECLARE
    view_name text := TG_TABLE_NAME;
    schema_name text := TG_TABLE_SCHEMA;
    version_table_name text := schema_name || '.' || view_name || '_version';
    latest_row record;
BEGIN
    -- 锁定usr_static中目标行，阻塞并发更新，保证串行处理
    PERFORM 1 FROM usr_static WHERE id = NEW.id FOR UPDATE;

    -- 获取当前最新的版本行数据
    SELECT uv.col1, uv.col2, uv.col3 INTO latest_row
    FROM usr_version AS uv
    WHERE uv.id = NEW.id
    ORDER BY uv.create_time DESC
    LIMIT 1;

    -- 合并用户更新的字段与最新行的其他字段
    IF latest_row IS NOT NULL THEN
        NEW.col1 := COALESCE(NEW.col1, latest_row.col1);
        NEW.col2 := COALESCE(NEW.col2, latest_row.col2);
        NEW.col3 := COALESCE(NEW.col3, latest_row.col3);
    END IF;

    -- 插入合并后的新行到版本表
    EXECUTE format('INSERT INTO %s (id, col1, col2, col3) VALUES ($1.id, $1.col1, $1.col2, $1.col3)', version_table_name)
    USING NEW;

    RETURN NEW;
END;
$$;

核心逻辑： #

• FOR UPDATE锁会锁定usr_static中对应行，其他并发UPDATE必须等待锁释放后执行，确保更新按顺序处理。
• 每次更新前拉取最新版本数据，将用户修改的字段与最新行的未修改字段合并，保证插入的新行包含所有累积更新。

2. 乐观锁方案（适合高并发低冲突场景） #

如果不想用行锁牺牲并发性能，可以给usr_version添加版本号字段，通过版本校验处理冲突：

1. 给版本表添加版本号字段：

ALTER TABLE usr_version ADD COLUMN version_num INT DEFAULT 1;

2. 修改视图，包含版本号字段：

CREATE VIEW usr_view AS
  SELECT
    us.id,
    uv.col1,
    uv.col2,
    uv.col3,
    uv.version_num
  FROM usr_static AS us
  LEFT JOIN usr_version AS uv ON us.id = us.id AND uv.create_time = (
    SELECT MAX(uv2.create_time)
    FROM usr_version AS uv2
    WHERE uv2.id = us.id
  )

3. 修改触发器函数，加入版本校验和更新：

CREATE FUNCTION shared.on_update_view() RETURNS TRIGGER
    LANGUAGE plpgsql
AS $$
DECLARE
    view_name text := TG_TABLE_NAME;
    schema_name text := TG_TABLE_SCHEMA;
    version_table_name text := schema_name || '.' || view_name || '_version';
    latest_row record;
BEGIN
    -- 获取当前最新版本行及版本号
    SELECT uv.col1, uv.col2, uv.col3, uv.version_num INTO latest_row
    FROM usr_version AS uv
    WHERE uv.id = NEW.id
    ORDER BY uv.create_time DESC
    LIMIT 1;

    -- 校验版本号，若不匹配则抛出异常（需应用层重试）
    IF latest_row IS NOT NULL AND NEW.version_num != latest_row.version_num THEN
        RAISE EXCEPTION '数据已被其他更新修改，请重试';
    END IF;

    -- 合并更新字段并递增版本号
    IF latest_row IS NOT NULL THEN
        NEW.col1 := COALESCE(NEW.col1, latest_row.col1);
        NEW.col2 := COALESCE(NEW.col2, latest_row.col2);
        NEW.col3 := COALESCE(NEW.col3, latest_row.col3);
        NEW.version_num := latest_row.version_num + 1;
    ELSE
        NEW.version_num := 1;
    END IF;

    -- 插入新的版本行
    EXECUTE format('INSERT INTO %s (id, col1, col2, col3, version_num) VALUES ($1.id, $1.col1, $1.col2, $1.col3, $1.version_num)', version_table_name)
    USING NEW;

    RETURN NEW;
END;
$$;

核心逻辑： #

• 每次更新需要携带当前视图返回的版本号，若版本号不匹配，说明数据已被修改，抛出异常让应用层重试。
• 这种方式不阻塞并发，但需要应用侧处理重试逻辑，适合冲突较少的高并发场景。

内容的提问来源于stack exchange，提问作者Marnix.hoh