如何在PostgreSQL触发器中添加行级锁避免并发更新异常

问题背景 #

现有两张PostgreSQL表objects和metadata，表结构如下：

CREATE TABLE IF NOT EXISTS objects (
  object_id UUID PRIMARY KEY,
  storage_id UUID NOT NULL,
  size BIGINT NOT NULL,
  FOREIGN KEY (storage_id) REFERENCES metadata(storage_id)
);

CREATE TABLE IF NOT EXISTS metadata (
  storage_id UUID PRIMARY KEY,
  total_size BIGINT DEFAULT 0
);

通过触发器维护metadata.total_size，在objects插入/删除时更新对应storage_id的总大小，但并发操作时会出现total_size被覆盖、数据不一致的问题。

原插入触发器代码：

CREATE OR REPLACE FUNCTION update_size_on_insert() RETURNS TRIGGER AS $$
BEGIN
  UPDATE metadata
  SET total_size = total_size + NEW.size
  WHERE storage_id = NEW.storage_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_update_size_on_insert
AFTER INSERT ON objects
FOR EACH ROW
EXECUTE FUNCTION update_size_on_insert();

解决方案 #

方法：用PERFORM ... FOR UPDATE显式锁定行 #

在执行UPDATE前，通过PERFORM语句配合FOR UPDATE锁定目标metadata行，确保并发事务必须等待当前事务释放锁后，才能基于最新数据修改total_size，避免覆盖问题。

修改后的插入触发器函数

CREATE OR REPLACE FUNCTION update_size_on_insert() RETURNS TRIGGER AS $$
BEGIN
  -- 显式锁定对应storage_id的metadata行，阻止并发修改
  PERFORM 1 FROM metadata
  WHERE storage_id = NEW.storage_id
  FOR UPDATE;

  UPDATE metadata
  SET total_size = total_size + NEW.size
  WHERE storage_id = NEW.storage_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

对应的删除触发器函数

如果需要处理删除场景，同样添加行级锁：

CREATE OR REPLACE FUNCTION update_size_on_delete() RETURNS TRIGGER AS $$
BEGIN
  PERFORM 1 FROM metadata
  WHERE storage_id = OLD.storage_id
  FOR UPDATE;

  UPDATE metadata
  SET total_size = total_size - OLD.size
  WHERE storage_id = OLD.storage_id;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_update_size_on_delete
AFTER DELETE ON objects
FOR EACH ROW
EXECUTE FUNCTION update_size_on_delete();

原理说明 #

• FOR UPDATE会对匹配的行施加行级排他锁，直到当前事务提交或回滚才释放，其他事务必须等待锁释放后才能修改该行。
• 由于objects表的外键约束，metadata中对应的storage_id行必然存在，无需额外判断行存在性。

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