用 PostgreSQL 排他约束防止同一资源预约重叠

问题场景

会议室、测试设备、车辆等资源预约系统中，一个常见需求是：同一资源在同一时间段内只能被一个用户占用。很多实现会先在应用层查询是否存在重叠预约，再执行插入。这种“先查后插”在并发下并不可靠：两个请求可能同时查到“无冲突”，然后都插入成功，最终产生重叠预约。

更稳妥的做法是把“不重叠”规则交给数据库约束来保证。PostgreSQL 提供排他约束 EXCLUDE USING gist，配合范围类型 tstzrange，可以在数据库层直接阻止同一资源的时间段重叠。冲突发生时，插入语句会抛出 exclusion_violation（SQLSTATE 23P01），应用层捕获后返回友好提示即可。

数据模型与排他约束

核心思路是：用一列表示资源标识，用 tstzrange 表示预约时间段，然后建立如下约束：

• resource_id WITH =：同一个资源
• during WITH &&：时间段有重叠

两者同时满足时，约束拒绝插入。因为 gist 索引原生支持范围类型的 && 操作符，但等值操作符需要 btree_gist 扩展提供支持，所以建表前必须先启用该扩展。

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE reservation (
    id bigserial PRIMARY KEY,
    resource_id text NOT NULL,
    user_id text NOT NULL,
    during tstzrange NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    EXCLUDE USING gist (resource_id WITH =, during WITH &&)
);

这里 during 使用 tstzrange，即带时区的时间戳范围。默认边界是 [)：包含开始时间，不包含结束时间。这样 [09:00, 10:00) 与 [10:00, 11:00) 不会被认为重叠，符合大多数预约场景。

最小可运行示例

1. 初始化数据库

将上面的建表语句保存为 schema.sql，然后执行：

psql "host=localhost port=5432 dbname=postgres user=postgres password=<your-password>" -f schema.sql

2. 插入预约并捕获冲突

下面是一个 Python 示例，使用 psycopg 3 连接 PostgreSQL。插入成功则提交，遇到排他约束冲突则回滚并返回友好提示。

import psycopg
from psycopg.errors import ExclusionViolation


def add_reservation(conn, resource_id, user_id, start_iso, end_iso):
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                INSERT INTO reservation (resource_id, user_id, during)
                VALUES (%s, %s, tstzrange(%s::timestamptz, %s::timestamptz, '[)'))
                RETURNING id
                """,
                (resource_id, user_id, start_iso, end_iso),
            )
            new_id = cur.fetchone()[0]
        conn.commit()
        return {"ok": True, "id": new_id}
    except ExclusionViolation:
        conn.rollback()
        return {
            "ok": False,
            "message": "该资源在所选时间段内已被预约，请更换时间或资源",
        }


if __name__ == "__main__":
    conn = psycopg.connect(
        "host=localhost port=5432 dbname=postgres "
        "user=postgres password=<your-password>"
    )

    # 第一次插入：成功
    print(add_reservation(
        conn, "room-001", "user-a",
        "2025-01-01 09:00+08", "2025-01-01 10:00+08"
    ))

    # 第二次插入：与第一次重叠，返回友好提示
    print(add_reservation(
        conn, "room-001", "user-b",
        "2025-01-01 09:30+08", "2025-01-01 10:30+08"
    ))

    # 相邻但不重叠：成功
    print(add_reservation(
        conn, "room-001", "user-c",
        "2025-01-01 10:00+08", "2025-01-01 11:00+08"
    ))

    conn.close()

如果使用 psycopg2，异常类是 psycopg2.errors.ExclusionViolation；如果使用 Java/JDBC，可以捕获 SQLState 23P01。核心逻辑相同：事务失败后必须回滚，否则同一连接上的后续语句会处于失败事务状态。

运行方法

如果没有本地 PostgreSQL，可以用容器快速启动一个测试实例：

docker run --name pg-reservation \
  -e POSTGRES_PASSWORD=<your-password> \
  -p 5432:5432 \
  -d postgres:16

安装 Python 依赖：

pip install "psycopg[binary]"

先执行 schema.sql 建表和扩展，再运行上面的 Python 脚本。预期输出中，第一次和第三次插入成功，第二次插入返回“该资源在所选时间段内已被预约，请更换时间或资源”。

边界与限制

1. 扩展依赖：btree_gist 必须安装在目标数据库中。如果使用云数据库，通常需要具备相应权限，或在控制台允许的扩展列表中启用。

2. 约束范围：排他约束只作用于当前表。如果预约记录会被软删除，可以结合部分排他约束，例如只约束状态为有效的记录：

   EXCLUDE USING gist (resource_id WITH =, during WITH &&)
   WHERE (status = 'active')
   

   该语法在 PostgreSQL 中受支持，但具体行为仍建议在目标版本上验证。

3. 时间边界：tstzrange 默认 [)，开始时间包含、结束时间不包含。需要根据业务确认是否接受“结束时间等于下一次开始时间”的相邻预约。如果业务要求闭区间，可以显式使用 []，但要注意边界相等时的冲突判定。

4. 空范围与 NULL：during 设为 NOT NULL，避免 NULL 带来的不确定行为。同时应用层应校验开始时间小于结束时间，因为空范围（起止相等）通常不与其他范围重叠。

5. 并发行为：两个事务同时插入重叠区间时，一个会成功，另一个会收到 exclusion_violation。失败方需要回滚整个事务，再向用户返回提示。不要只依赖应用层查询来防止重叠。

6. 修改预约：UPDATE 预约时间也可能触发排他约束冲突，需要像插入一样捕获 ExclusionViolation 并回滚。

7. 索引与性能：排他约束底层依赖 GiST 索引，范围比较和冲突检测效率较好，但写入时索引维护有成本。资源 ID 基数很高、预约表很大时，索引体积和写入开销需要关注，可结合归档、分区等策略。

8. 类型支持：btree_gist 为常见等值类型提供操作符类，但并非所有自定义类型都直接支持。如果 resource_id 使用特殊类型，需要确认是否存在对应操作符类，或改用 text、uuid 等常见类型。

小结

用 PostgreSQL 排他约束防止预约时间重叠，关键点只有三个：启用 btree_gist，用 EXCLUDE USING gist 组合“资源相等”和“范围重叠”，捕获 exclusion_violation 后回滚并返回友好提示。这样可以把并发下的重叠风险交给数据库约束兜底，应用层只需处理成功或冲突两种结果。