PostgreSQL – Validate Availability for New Order

join;postgresqlquerytrigger

I have a management application of sales, stock and payment on a warehouse whole saler from a web interface. In particular, when a order is effectuated it must create a line corresponding to each product ordered with the respective quantity. The validation of the stock availability is done at the moment of the order.

Considering the following two ways of validation on order:

  1. Use a trigger BEFORE INSERT on the table OrderLine, that does a SELECT on Product verifying there is enough stock.

  2. Do a SELECT ... FROM OrderLine JOIN Product WHERE quant < stock.

My question is: Which of these two alternatives is preferable, and why / for what scenario?

Best Answer

I only see you doing a SELECT in both variants. If you want to make sure you don't sell more than you have in store (stock), you must decrease your stock in the same transaction you place the order. Use a data-modifying CTE for the job (Postgres 9.1 or later):

WITH u AS (
   UPDATE product SET quant = quant - <put_order_quant_here>
   WHERE  product_id = <order_prod_id>
   AND    quant >= <put_order_quant_here>
   RETURNING product_id, <put_order_quant_here> AS quant
   )
INSERT INTO order_detail (order_id, product_id, quant)
SELECT <put_order_id_here>, product_id, quant
FROM   u;

The UPDATE in the CTE only returns values if the product has sufficient stock (and it takes a write lock on the row). In this case, the quantity is reduced in the same transaction, just before the order is placed.

Put all order-details into one transaction, if any of them fails to INSERT then ROLLBACK.

Possible deadlocks

This scenario can easily lead to deadlocks. Say, you have two orders coming in at the same time, both want product A and B. The first order starts with A, the second starts with B. Then the two transactions block each other out. Each waits for the other to complete. A deadlock ensues.

In PostgreSQL a transaction will wait for some time when it is stalled by locks. Depending on your setting of deadlock_timeout (default is 1 sesond), checks for possible deadlocks are performed.

Once detected, one transaction is aborted and a deadlock exception reported. The other one can finish. Which one is hard to predict.

There is a simple way to avoid this kind of deadlocks: Always place your order_details in a consistent order. Like products ordered by product_id. This way, the above scenario can never happen.

Related Question