如何在PostgreSQL中无冗余锁实现预约数限制与条件插入？

核心需求回顾 #

需要保证指定活动的有效预约数（已支付+未过期保留）不超过总票数N，同时支持高并发请求，仅在争夺最后剩余票时出现冲突。

PostgreSQL 实现方案 #

方案1：库存表+原子更新（推荐，高效且无重试依赖） #

创建独立的活动库存表避免频繁全表COUNT的性能问题，利用数据库行级锁保证并发安全：

1. 先创建库存表：

CREATE TABLE EventInventory (
    event_id INT PRIMARY KEY,
    available_tickets INT NOT NULL CHECK (available_tickets >= 0)
);

-- 初始化活动库存（示例：1000张票）
INSERT INTO EventInventory (event_id, available_tickets) VALUES (1, 1000);

2. 预约逻辑的事务语句：

BEGIN;
-- 原子扣减库存，仅当还有剩余票时执行
UPDATE EventInventory
SET available_tickets = available_tickets - 1
WHERE event_id = $1 AND available_tickets > 0;

-- 如果库存扣减成功，插入预约记录
IF FOUND THEN
    INSERT INTO Reservations (event_id, user_id, paid, hold_expires_at)
    VALUES ($1, $2, false, NOW() + INTERVAL '15 minutes');
END IF;
COMMIT;

• 原理：UPDATE语句会对目标活动的库存行加排他锁，并发请求会排队等待锁释放，彻底避免超卖。FOUND是PostgreSQL内置变量，用于判断UPDATE是否影响了行。
• 优势：在READ_COMMITTED隔离级别下即可保证正确性，无需依赖序列化错误重试，性能远优于全表COUNT。

方案2：合并INSERT与统计（依赖SERIALIZABLE隔离级别） #

如果无法新增库存表，可将统计与插入合并为单条SQL，结合SERIALIZABLE隔离级别实现并发控制：

-- 需将事务隔离级别设为SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;

INSERT INTO Reservations (event_id, user_id, paid, hold_expires_at)
SELECT $1, $2, false, NOW() + INTERVAL '15 minutes'
WHERE (
    SELECT COUNT(*)
    FROM Reservations
    WHERE event_id = $1
      AND (paid = true OR hold_expires_at > NOW())
) < $3;

COMMIT;

• 原理：PostgreSQL的SERIALIZABLE隔离级别会检测到并发事务间的写入冲突（比如两个事务同时统计到有效预约数为999并尝试插入），触发序列化错误（错误码40001）。
• 注意：应用层需要捕获该错误并重试请求，直到成功或确定库存不足。这种方式在库存充足时可并发处理请求，仅在争夺最后几张票时出现重试。

其他数据库/技术实现方式 #

MySQL（InnoDB引擎） #

与PostgreSQL方案1逻辑一致，利用InnoDB的行级锁保证原子性：

START TRANSACTION;
UPDATE EventInventory
SET available_tickets = available_tickets - 1
WHERE event_id = ? AND available_tickets > 0;

IF ROW_COUNT() > 0 THEN
    INSERT INTO Reservations (event_id, user_id, paid, hold_expires_at)
    VALUES (?, ?, false, DATE_ADD(NOW(), INTERVAL 15 MINUTE));
END IF;
COMMIT;

• ROW_COUNT()用于判断UPDATE是否成功修改行，无需依赖高级隔离级别。

SQL Server #

同样优先推荐库存表方案，或使用SERIALIZABLE隔离级别下的合并INSERT：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

INSERT INTO Reservations (event_id, user_id, paid, hold_expires_at)
SELECT @event_id, @user_id, 0, DATEADD(minute, 15, GETDATE())
WHERE (
    SELECT COUNT(*)
    FROM Reservations
    WHERE event_id = @event_id
      AND (paid = 1 OR hold_expires_at > GETDATE())
) < @max_tickets;

COMMIT TRANSACTION;

• 应用层需捕获40001序列化错误并重试。

Redis 高并发场景方案 #

对于超大规模并发请求，用Redis的原子操作先处理库存扣减，再同步到数据库：

import redis

r = redis.Redis(host='localhost', port=6379, db=0)

def reserve_ticket(event_id, user_id, max_tickets):
    event_key = f"event:{event_id}:available"
    # 原子减1，返回扣减后的值
    remaining = r.decr(event_key)
    
    if remaining >= 0:
        # 同步写入数据库（可异步优化）
        insert_reservation(event_id, user_id)
        return "预约成功"
    else:
        # 库存不足，恢复计数
        r.incr(event_key)
        return "票已售罄"

# 初始化库存
r.set("event:1:available", 1000)

• 优势：Redis单线程原子操作可支撑十万级QPS，避免数据库成为瓶颈，适合秒杀类高并发场景。

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