postgresql - INSERT 的并发场景

Question

我正在用 PHP + PostgreSQL 设计一个预订系统。我无法为基于 INSERT 操作的并发问题找到一个干净的解决方案。

数据库系统主要由这些表组成:

CREATE TABLE booking (
booking_id INT,
user_id INT,
state SMALLINT,
nb_coupons INT
);

CREATE booking_state_history (
booking_state_history_id INT,
timestamp TIMESTAMP,
booking_id INT,
state SMALLINT);

CREATE TABLE coupon_purchase(
coupon_purchase_id,
user_id INT,
nb INT,
value MONEY)

CREATE TABLE coupon_refund(
coupon_refund_id INT,
user_id,
nb INT,
value MONEY)

CREATE TABLE booking_payment(
booking_payment_id INT,
user_id,
booking_id,
nb INT,
value MONEY)

预订必须使用用户之前购买的优惠券支付。部分优惠券可能已退款。所有这些操作都存储在两个相应的表中以保留历史记录并能够计算优惠券余额。约束:优惠券余额在任何时候都不能为负。

当使用优惠券支付时，预订完成。

然后会发生以下操作:

BEGIN;    
(1) Check there are enough coupons remaining to pay the booking. (SELECT)    
(2) Decide which coupons (number and value) will be used to pay the booking 
(mainly, higher cost coupon used first. But that is not the issue here.)    
(3) Add records to booking_payment (INSERTs)    
(4) Move the booking to state="PAID" (integer value representing "PAID") (UPDATE)
(5) Add a record to booking_state_history (INSERT)    
COMMIT;

这些操作需要是原子的以保持数据库信息的一致性。

因此，使用允许COMMIT 或ROLLBACK 的事务以防失败、DB 异常、PHP 异常或操作过程中的任何其他问题。

场景一

由于我处于并发访问环境(网站)中，所以没有什么能阻止用户(例如)在进行预订付款的同时要求优惠券退款。

场景二

他还可以在两个不同的交易中同时触发两个并发预订支付。

所以会发生以下情况:

场景 1(1)完成后，用户触发退还优惠券，后续优惠券余额已不足以支付预订费用。当它COMMITs时，余额变为负数。注意:即使我在新的 (6) 步骤中重新检查优惠券余额，也有可能在 (6) 和 COMMIT 之间同时发生优惠券退款。

场景二

两个并发的预订支付交易，支付的优惠券总数太多，导致全局余额无法保持正值。只有其中之一会发生。交易 1 和交易 2 正在检查余额并查看足够的优惠券以用于步骤 (1) 中各自的付款。他们继续他们的行动并COMMIT。新余额为负且与约束冲突。注意:即使我在新的 (6) 步骤中重新检查优惠券余额，交易也看不到另一个交易尚未提交的操作。所以他们盲目地继续COMMIT。

我想这是一个常见的并发情况，但我在互联网上找不到解决这个问题的模式。

我想在 COMMIT 之后重新检查余额，这样我就可以手动撤消所有操作。但这并不完全安全，因为如果在提交后发生异常，UNDO 将不会完成。

有解决这个并发问题的想法吗？

谢谢。

Answer 1

您的问题归结为“什么应该是同步锁”。从您的问题来看，预订似乎不是特定项目的预订。但假设用户正在预订特定的酒店房间，因此您需要解决两个问题:

• 防止超额预订(例如，为两个人预订同一件事)
• 防止平行账户状态计算错误

因此，当用户到达他/她即将点击确认 按钮的位置时，这是您可以实现的可能场景:

1. 开始交易

2. 锁定用户入口以阻止并行进程

   SELECT * FROM user FOR UPDATE WHERE id = :id

3. 重新检查账户余额，如果资金不足则抛出异常/回滚

4. 锁定要预订的项目以防止超额预订

   SELECT * FROM room FOR UPDATE WHERE id = :id

5. 重新检查预订可用性并在项目已预订时抛出异常/回滚

6. 创建预订条目并从用户帐户中扣除资金

7. 提交事务(所有锁都会被释放)

如果在您的情况下，您不需要检查超额预订，只需跳过/忽略第 4 步和第 5 步。