Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Which DBMS do you use? –  Erwin Brandstetter Feb 14 '12 at 0:34
    
@ErwinBrandstetter It's PostgreSQL –  hashdava Feb 14 '12 at 1:56
add comment

2 Answers

up vote 2 down vote accepted

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. In PostgreSQL 9.1 you could use a data-modifying CTE for the job:

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. 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, ROLLBACK.


Possible deadlocks

One more piece of advice: this scenario could 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 by placing the order_detail on A, the second starts with B. Then the two transactions block each other out. Each of them would wait 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 1s, which I set to at least 5s on untroubled production servers), checks for a possible deadlock condition will be performed.

Once detected, one transaction will be aborted and report a deadlock exception. 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.

share|improve this answer
    
Nice, and certainly a PostgreSQL solution as Oracle (as of 11.2) can't do an UPDATE in a CTE. +1 –  Leigh Riffel Feb 14 '12 at 14:34
    
PostgreSQL 9.1's docs say that using transactions at the serializable isolation level can't cause a deadlock. (Search postgresql.org/docs/9.1/static/transaction-iso.html for "causing a deadlock".) At least I think that's what it says. –  Mike Sherrill 'Cat Recall' Feb 15 '12 at 1:08
    
@Catcall: but that isolation level can report serialization failures instead (and more often than actual deadlocks would occur). So the app would have to retry transaction on serialization failure. For the scenario at hand it should be sufficient, simpler and faster to just place order_details in a consistent order. –  Erwin Brandstetter Feb 15 '12 at 1:30
add comment

Update:

I made this post before the postgresql tag was added, but the concepts should be similar. Basically you need to be able to lock the record in some way to ensure that the stock value you select remains the stock value you decrease. The same transaction that locks the record should update the stock and insert into the OrderLine table. As soon as you determine that stock isn't available roll the whole transaction back.

If this processing is done in a batch operation by one process then you don't need to be concerned with locking individual records, but you should ensure that only one batch process can be running at the same time.


Look into...

  1. The WAIT clause of the SELECT statement.
  2. The BULK COLLECT clause of the SELECT statement.
  3. The FORALL statement.

You probably also would want to ensure that all entries had enough stock, so you may want some to aggregate whether the stock is available or not.

share|improve this answer
    
I know fegol's not using SQL Server because of the natural join, but do we know it's Oracle? –  Aaron Bertrand Feb 14 '12 at 1:48
    
@AaronBertrand Sorry, It's PostgreSQL –  hashdava Feb 14 '12 at 1:56
    
@Aaron, fegol - Sorry about that, made an assumption. –  Leigh Riffel Feb 14 '12 at 14:16
    
@LeighRiffel: I would expect that multiple processes can run at a time. PostgreSQL transactions acquire locks along the way and release them on commit / rollback. As long as order details are placed in a consistent order, concurrency should be no problem, at least with my query. Later concurrent transactions competing for the same product wait for the earlier to finish and then proceed accordingly. The MVCC model with its default read committed isolation level should handle that gracefully. –  Erwin Brandstetter Feb 15 '12 at 1:19
    
@Erwin My "lock the record in some way" does not exclude implicit locks that solutions such as yours employ. Consistent order only prevents deadlocks in a properly locking application. If multiple processes are selecting stock and then updating it without having the record locked then there will be problems regardless of the consistency of the order. –  Leigh Riffel Feb 15 '12 at 14:22
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.