PostgreSQL 11办公工位预订系统避免重复插入方案咨询

核心思路：数据库约束 + 原子化插入逻辑 #

先查后插的逻辑在高并发场景下存在竞态漏洞（两个请求同时通过查询校验，随后同时插入导致重复数据）。最可靠的方案是用数据库唯一约束做底层兜底，再通过PL/pgSQL函数封装原子化插入逻辑，处理冲突并返回明确结果。

1. 完善表结构与约束 #

首先给预订表添加唯一约束，从数据库层面强制遵守预订规则：

-- 创建预订表（若未创建）
CREATE TABLE workstation_booking (
  id SERIAL PRIMARY KEY,
  booked_date DATE NOT NULL,
  seat_id INT NOT NULL,
  employee_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 添加两个唯一约束，分别实现两条规则
ALTER TABLE workstation_booking
ADD CONSTRAINT uq_employee_day UNIQUE (booked_date, employee_id), -- 每人每日仅能预订一个工位
ADD CONSTRAINT uq_seat_day UNIQUE (booked_date, seat_id); -- 工位每日不可重复预订

这两个约束是数据一致性的最后防线，任何违反规则的插入都会被数据库直接拦截。

2. 用PL/pgSQL函数封装插入逻辑 #

编写PL/pgSQL函数，将插入操作原子化，并捕获约束冲突，返回明确的操作结果：

CREATE OR REPLACE FUNCTION book_workstation(p_booked_date DATE, p_seat_id INT, p_employee_id INT)
RETURNS TEXT AS $$
BEGIN
  -- 执行原子插入
  INSERT INTO workstation_booking (booked_date, seat_id, employee_id)
  VALUES (p_booked_date, p_seat_id, p_employee_id);
  
  RETURN '预订成功';
EXCEPTION
  WHEN unique_violation THEN
    -- 根据触发的约束返回具体错误信息
    IF SQLERRM LIKE '%uq_employee_day%' THEN
      RETURN '错误：您今日已预订过工位';
    ELSIF SQLERRM LIKE '%uq_seat_day%' THEN
      RETURN '错误：该工位今日已被其他员工预订';
    ELSE
      RETURN '错误：预订失败，存在重复记录';
    END IF;
END;
$$ LANGUAGE plpgsql;

3. 调用函数完成预订 #

应用层直接调用该函数即可完成预订，无需自行处理查询与插入的并发问题：

-- 示例：预订2024-05-20日的101号工位给员工5001
SELECT book_workstation('2024-05-20', 101, 5001);

方案优势 #

• 原子性：插入操作单事务原子执行，彻底规避先查后插的竞态漏洞。
• 约束兜底：即使函数逻辑出现异常，数据库约束仍能阻止违规数据写入。
• 清晰反馈：函数返回明确的结果或错误信息，方便应用层直接对接用户展示。

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