使用INSERT的并发情形 [英] Concurrency scenarios with INSERTs

问题描述

我在PHP + PostgreSQL中设计一个预订系统。
我不能根据INSERT操作找到一个并发问题的干净解决方案。

DB系统主要由这些表组成： p>

  CREATE TABLE预订（
 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）检查是否有足够的优惠券支付预订。 （SELECT）
（2）决定哪些优惠券（数字和价值）将用于支付预订
（主要是首先使用较高的成本票据，但这不是这里的问题。）
（3）将记录添加到booking_payment（INSERTs）
（4）将预订移至state =PAID（表示PAID的整数值）（更新）
（5）将记录添加到booking_state_history （INSERT）
 COMMIT; 
  

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

因此，使用允许 COMMIT 或 ROLLBACK 的事务（如果发生故障），数据库异常，PHP异常或任何其他问题操作。

情况1

（网站）没有阻止用户在同时进行预订付款时要求退还优惠券。

情况2

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

可能会发生以下情况：

情况1
在完成（1）之后，优惠券退款由用户触发，后续的票息余额不足以支付预订。
当 COMMITs 时，余额变为负值。
注意：
即使我在新的（6）步骤中重新检查优惠券余额，也有可能在期间（6） ） COMMIT 。

情况2

两个并发的预订付款交易，其中用于付款的优惠券的总数太多以至于全局平衡保持积极。只有其中一个可能发生。
交易1和交易2在步骤（1）中检查余额并且看到他们各自的支付的足够的优惠券。
他们继续他们的操作和 COMMIT 。新的平衡是负面的，并且与约束冲突。
注意：
即使我在新的（6）步骤中重试了优惠券余额，交易也无法看到另一个交易尚未提交的操作。
所以他们盲目地进行 COMMIT 。

我想这是一个通常的并发情况，但我找不到一个模式来解决

我想在COMMIT之后重新检查余额，以便我可以手动撤消所有操作。但是它不是完全安全的，因为如果在提交之后发生异常， UNDO 将不会执行。

任何想法来解决这种并发问题？

感谢。

解决方案

应该是同步锁定的问题。从你的问题，似乎预订不是一个特定的项目预订。但是假设用户正在预订特定的酒店房间，因此您需要解决两个问题：

• 防止超额预订

因此，当用户访问当他/她即将点击确认按钮时，这是一个可能的情况下，你可以实现：

1. begin transaction




2. 锁定用户条目以阻止并行进程

   
   
   

   code> SELECT * FROM user FOR UPDATE WHERE id =：id




3. 重新检查帐户余额并抛出异常/




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

   
   
   

   SELECT * FROM room FOR UPDATE WHERE id =：id




5. 重新检查预订空房情况并抛出异常/




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

   >提交事务（所有锁都将被释放）

如果在你的情况下，对于超量预订，只需跳过/忽略步骤4和5.

I'm designing a booking system in PHP + PostgreSQL. I'm not able to find a clean solution to a concurrency problem based on INSERTs operations.

The DB system is mainly made of these tables:

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)

A booking must be paid with coupons that have been previously purchased by the user. Some coupons may have been refund. All these operations are stored in the two corresponding tables to keep an history and be able to compute the coupon balance. Constraint: the coupon balance cannot be negative at any time.

A booking is finalized when it is paid with coupons.

Then the following operations happen:

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;

These operations need to be atomic to preserve DB information coherency.

Hence the usage of transactions that allow to COMMIT or ROLLBACK in case of failure, DB exception, PHP exception or any other issue in the middle of the operations.

Scenario 1

Since I'm in a concurrent access environment (web site) nothing prevents the user from (for instance) asking for a coupon refund while doing a booking payment at the same time.

Scenario 2

He can also trigger two concurrent booking payments at the same time in two different transactions.

So the following can happen:

Scenario 1 After (1) is done, the coupon refund is triggered by the user and the subsequent coupon balance is not enough to pay the booking any more. When it COMMITs the balance becomes negative. Note: Even if I do a recheck of coupon balance in a new (6) step, there is a possibility for the coupon refund to happen in the meantime between (6) and COMMIT.

Scenario 2

Two concurrent booking payment transactions for which total number of coupons for payment is too much for the global balance to stay positive. Only one of them can happen. Transaction 1 and transaction 2 are checking for balance and seeing enough coupons for their respective payment in step (1). They go on with their operations and COMMIT. The new balance is negative and conflicting with the constraint. Note: Even if I do a coupon balance recheck in a new (6) step the transactions cannot see the operations not yet commited by the other one. So they blindly proceed to COMMIT.

I guess this is an usual concurrency case but I cannot find a pattern to solve this on the internet.

I thought of rechecking the balance after the COMMIT so I can manually UNDO all the operations. But it is not totally safe since if an exception happen after the commit the UNDO won't be done.

Any idea to solve this concurrency problem?

Thanks.

解决方案

Your problem boils down to the question of "what should be the synchronization lock". From your question it seems that the booking is not booking of a specific item. But lets assume, that a user is booking a specific hotel room so you need to solve two problems:

• prevent overbooking (e.g. booking the same thing for two people)
• prevent parallel account state miscalculation

So when a user gets to a point when he/she is about to hit confirm button, this is a possible scenario you can implement:

1. begin transaction

2. lock the user entry so that parallel processes are blocked

   SELECT * FROM user FOR UPDATE WHERE id = :id

3. re-check account balance and throw exception / rollback if there are insufficient funds

4. lock the item to be booked to prevent overbooking

   SELECT * FROM room FOR UPDATE WHERE id = :id

5. re-check booking availability and throw exception / rollback if the item is already booked

6. create booking entry and subtract funds from user's account

7. commit transaction (all locks will be released)

If, in your case, you don't need to check for overbooking, just skip / ignore steps 4 and 5.

这篇关于使用INSERT的并发情形的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！