Postgres 14条件阻止并发插入的控制方案咨询

针对你需要的仅阻塞特定厂商（Volkswagen）的插入操作、允许其他厂商正常插入的需求，结合PostgreSQL 14的advisory lock（ advisory锁）和前置触发器可以完美实现。以下是具体方案：

核心思路 #

利用PostgreSQL的事务级advisory锁，为特定厂商生成唯一锁键：

1. 事务A在处理Volkswagen相关业务时，先获取该厂商对应的排他锁；
2. 其他事务插入数据时，通过前置触发器检查：若插入的是Volkswagen，则尝试获取相同锁键的排他锁（会被事务A持有的锁阻塞）；插入其他厂商则直接放行。

具体实现步骤 #

1. 创建触发器函数 #

编写PL/pgSQL函数，作为插入前的检查逻辑：

CREATE OR REPLACE FUNCTION block_target_manufacturer_insert()
RETURNS TRIGGER AS $$
BEGIN
    -- 仅对Volkswagen执行锁检查
    IF NEW.manufacturer = 'Volkswagen' THEN
        -- 将厂商名称转换为唯一bigint锁键（通过MD5哈希截断转换）
        PERFORM pg_advisory_xact_lock(
            ('x' || substr(md5('Volkswagen'), 1, 16))::bit(64)::bigint
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

• pg_advisory_xact_lock是事务级锁，事务提交/回滚时会自动释放，避免会话级锁残留问题；
• 用MD5哈希+类型转换生成唯一锁键，确保不同厂商对应不同锁，互不干扰。

2. 绑定触发器到目标表 #

假设你的表名为car_info，创建前置触发器：

CREATE TRIGGER trigger_block_volkswagen_insert
BEFORE INSERT ON car_info
FOR EACH ROW
EXECUTE FUNCTION block_target_manufacturer_insert();

3. 事务A的业务流程 #

事务A处理Volkswagen相关操作时，先获取对应锁：

BEGIN;
-- 获取Volkswagen的事务级排他锁
SELECT pg_advisory_xact_lock(
    ('x' || substr(md5('Volkswagen'), 1, 16))::bit(64)::bigint
);

-- 执行你的核心业务操作（如修改Volkswagen相关数据等）
-- UPDATE car_info SET ... WHERE manufacturer = 'Volkswagen';

-- 事务提交时自动释放锁
COMMIT;

测试验证 #

• 事务A启动并获取锁后，事务B插入manufacturer = 'Volkswagen'的记录会被阻塞，直到事务A提交/回滚；
• 事务B插入manufacturer = 'Ferrari'或其他值的记录，会立即执行，不受阻塞。

扩展优化（可选） #

如果需要动态配置需要阻塞的厂商，可创建一个配置表替代硬编码：

1. 创建配置表：

CREATE TABLE locked_manufacturers (
    manufacturer text PRIMARY KEY
);
-- 初始化需要锁定的厂商
INSERT INTO locked_manufacturers VALUES ('Volkswagen');

2. 修改触发器函数：

CREATE OR REPLACE FUNCTION block_configured_manufacturer_insert()
RETURNS TRIGGER AS $$
DECLARE
    lock_key bigint;
BEGIN
    -- 查询该厂商是否在锁定列表中
    SELECT ('x' || substr(md5(NEW.manufacturer), 1, 16))::bit(64)::bigint
    INTO lock_key
    FROM locked_manufacturers
    WHERE manufacturer = NEW.manufacturer;

    -- 若在锁定列表中，则获取锁
    IF FOUND THEN
        PERFORM pg_advisory_xact_lock(lock_key);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

后续只需修改locked_manufacturers表，即可动态添加/移除需要阻塞的厂商，无需修改触发器代码。

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