多表一致性保障方案的并发正确性审核及测试场景咨询

• R1：组织单元层级结构为 super -> main -> child，层级关联
• R2：员工汇报对象仅允许以下三种组合：(super, main, child)、(super, main) 或 (super)
• 一致性要求：
  • R3：若某单元或其父单元被员工引用，则不允许移动该单元
  • R4：员工必须始终引用一致的层级树——汇报对象需存在自底向上的合法路径，禁止引用外部单元

• 针对R3无需锁定资源：最坏情况下有人在COMMIT前修改员工数据，仍能保证一致性
• R4与R3需协同工作，另一种可选方案是在组织单元变更后检查所有受影响的员工

请审核以下PostgreSQL实现方案的并发正确性？下方UPDATE测试案例已按预期运行（第二个事务被阻塞），但还有其他需要测试的场景吗？

CREATE TABLE public.super
(
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE public.main
(
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  super_id_fkey BIGINT NOT NULL REFERENCES public.super(id),
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE public.child
(
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  main_id_fkey BIGINT NOT NULL REFERENCES public.main(id),
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE public.employee
(
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,    
  report_to_super_id_fkey BIGINT REFERENCES public.super(id),
  report_to_main_id_fkey BIGINT REFERENCES public.main(id),
  report_to_child_id_fkey BIGINT REFERENCES public.child(id),
  CHECK
  (
    (report_to_super_id_fkey IS NOT NULL) OR
    (report_to_super_id_fkey IS NOT NULL AND report_to_main_id_fkey IS NOT NULL) OR
    (report_to_super_id_fkey IS NOT NULL AND report_to_main_id_fkey IS NOT NULL AND report_to_child_id_fkey IS NOT NULL)
  )
);

CREATE OR REPLACE FUNCTION public.check_main_reference()
  RETURNS TRIGGER
  AS $$
BEGIN  
  IF 
    (
      WITH data AS
      (
        SELECT public.super.id AS super_id, public.main.id AS main_id FROM public.super
        JOIN public.main ON public.main.super_id_fkey = public.super.id
        WHERE public.main.super_id_fkey = OLD.id
      )
      SELECT EXISTS
      (
        SELECT FROM public.employee WHERE
        EXISTS (SELECT FROM data WHERE employee.report_to_super_id_fkey = data.super_id) OR
        EXISTS (SELECT FROM data WHERE employee.report_to_main_id_fkey = data.main_id)
      )
    )
  THEN
    RAISE EXCEPTION 'Organizations units or child units having referenced employees can''t be moved';
  END IF;
   
  RETURN NEW;
END;
$$
LANGUAGE plpgsql
VOLATILE;

CREATE OR REPLACE FUNCTION public.check_child_reference()
  RETURNS TRIGGER
  AS $$
BEGIN  
  IF 
    (
      WITH data AS
      (
        SELECT public.super.id AS super_id, public.main.id AS main_id, public.child.id AS child_id FROM public.super
        JOIN public.main ON public.main.super_id_fkey = public.super.id
        JOIN public.child ON public.child.main_id_fkey = public.main.id
        WHERE public.child.main_id_fkey = OLD.id
      )
      SELECT EXISTS
      (
        SELECT FROM public.employee WHERE
        EXISTS (SELECT FROM data WHERE employee.report_to_super_id_fkey = data.super_id) OR
        EXISTS (SELECT FROM data WHERE employee.report_to_main_id_fkey = data.main_id) OR
        EXISTS (SELECT FROM data WHERE employee.report_to_child_id_fkey = data.child_id)
      )
    )
  THEN
    RAISE EXCEPTION 'Organizations units or child units having referenced employees can''t be moved';
  END IF;
   
  RETURN NEW;
END;
$$
LANGUAGE plpgsql
VOLATILE;

CREATE TRIGGER trigger_insert_update_super_id_fkey 
BEFORE UPDATE OF super_id_fkey ON public.main
FOR EACH ROW 
WHEN (OLD.super_id_fkey IS DISTINCT FROM NEW.super_id_fkey)
EXECUTE FUNCTION public.check_main_reference();

CREATE TRIGGER trigger_insert_update_child_main_id_fkey 
BEFORE UPDATE OF main_id_fkey ON public.child
FOR EACH ROW 
WHEN (OLD.main_id_fkey IS DISTINCT FROM NEW.main_id_fkey)
EXECUTE FUNCTION public.check_child_reference();

CREATE OR REPLACE FUNCTION public.check_hierarchy_consistency()
  RETURNS TRIGGER
  AS $$
BEGIN
  -- 锁定引用的层级路径——从顶层到底层
  PERFORM FROM public.super WHERE id = NEW.report_to_super_id_fkey FOR NO KEY UPDATE;
  PERFORM FROM public.main WHERE id = NEW.report_to_main_id_fkey FOR NO KEY UPDATE;
  PERFORM FROM public.child WHERE id = NEW.report_to_child_id_fkey FOR NO KEY UPDATE;
 
  IF 
    (
      SELECT NOT EXISTS
      (
        SELECT FROM public.super
        LEFT JOIN public.main ON public.main.super_id_fkey = public.super.id
        LEFT JOIN public.child ON public.child.main_id_fkey = public.main.id
        WHERE
          (NEW.report_to_super_id_fkey IS NULL OR public.super.id = NEW.report_to_super_id_fkey) AND
          (NEW.report_to_main_id_fkey IS NULL OR public.main.id = NEW.report_to_main_id_fkey) AND
          (NEW.report_to_child_id_fkey IS NULL OR public.child.id = NEW.report_to_child_id_fkey)
      )
    )
  THEN
    RAISE EXCEPTION 'Reporting only to consistent hierarchy allowed';
  END IF;

 RETURN NEW;
END;
$$
LANGUAGE plpgsql
VOLATILE;

CREATE TRIGGER trigger_insert_update_employee_fkeys 
BEFORE INSERT OR UPDATE OF report_to_super_id_fkey, report_to_main_id_fkey, report_to_child_id_fkey ON public.employee
FOR EACH ROW 
EXECUTE FUNCTION public.check_hierarchy_consistency();

INSERT INTO public.super (name) VALUES ('1'), ('2'), ('3');

INSERT INTO public.main (super_id_fkey, name) VALUES
  (1, '1.1'), (1, '1.2'),
  (2, '2.1'), (2, '2.2'), (2, '2.3'),
  (3, '3.1'), (3, '3.2');

INSERT INTO public.child (main_id_fkey, name) VALUES
  ((SELECT id FROM public.main WHERE name = '1.1'), ('1.1.1')),
  ((SELECT id FROM public.main WHERE name = '1.1'), ('1.1.2')),
  ((SELECT id FROM public.main WHERE name = '1.2'), ('1.2.1')),
  ((SELECT id FROM public.main WHERE name = '2.1'), ('2.1.1'));
 
 
INSERT INTO public.employee (report_to_super_id_fkey, report_to_main_id_fkey, report_to_child_id_fkey) VALUES
(
    (SELECT id FROM public.super WHERE name = '1'),
    (SELECT id FROM public.main WHERE name = '1.1'),
    (SELECT id FROM public.child WHERE name = '1.1.2')
);
BEGIN;
  UPDATE public.employee SET report_to_child_id_fkey = (SELECT id FROM public.child WHERE name = '1.1.1') WHERE id = 1;
  -- 在其他会话执行：UPDATE public.main SET super_id_fkey = (SELECT id FROM public.super WHERE name = '3') WHERE name = '1.1';
COMMIT;

并发正确性分析 #

1. R4的锁定机制：员工变更时通过FOR NO KEY UPDATE锁定层级路径上的所有节点，能有效防止组织单元在员工事务提交前被移动，避免出现层级不一致的情况，当前测试案例中第二个事务被阻塞符合预期。
2. R3的并发风险：当前R3的实现仅在移动单元前检查员工引用，未锁定相关员工数据。若在检查后、事务提交前，有其他事务新增/修改员工引用该单元或其父单元，会导致R3规则被破坏。例如：
   • 事务A：开始移动某main单元，检查时无员工引用
   • 事务B：新增员工引用该main单元，提交成功
   • 事务A：提交移动操作，此时该单元已被引用，但R3未拦截

补充测试场景 #

1. R3的并发冲突场景：
   • 会话1：启动事务，执行移动某未被引用的main单元的操作（不提交）
   • 会话2：新增员工引用该main单元，提交事务
   • 会话1：提交移动事务，验证是否被拦截
2. 多员工同时修改同一层级路径：
   • 会话1：修改员工A的汇报对象到层级X，启动事务不提交
   • 会话2：修改员工B的汇报对象到层级X，验证是否能正常执行（或按顺序执行，无死锁）
3. 组织单元移动与员工跨层级修改：
   • 会话1：尝试移动某单元，启动事务不提交
   • 会话2：修改员工汇报对象到该单元的新层级（若允许），验证是否被拦截
4. 空值场景测试：
   • 新增仅引用super的员工，尝试移动该super单元，验证是否被拦截
   • 修改员工从(super, main)变为仅(super)，同时移动原main单元，验证并发行为
5. 死锁场景验证：
   • 会话1：修改员工A引用层级super1 -> main1，锁定super1和main1
   • 会话2：修改员工B引用层级super2 -> main2，锁定super2和main2
   • 会话1：尝试修改员工A引用层级super2 -> main2
   • 会话2：尝试修改员工B引用层级super1 -> main1
   • 验证是否出现死锁，或锁定顺序是否合理避免死锁

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