> User1 starts order and takes the last two units. User2 starts order 1 minut= > e > after and checks inventory. He sees 2 units left and adds them to the his > order. User1 commits his order. Now User2 cannot finish his order because > the products are not available anymore. > > This is the problem I want to avoid. Therefore if User1 takes the product > but does not finish the order I want the inventory to still show that the > product is sold out to other users. > > Any suggestions on how to implemnt that?
One common way to deal with it is to have a separate 'hold quantity' field (or table) for items in pending orders. You can commit to that field or table as each line item is entered, revised or deleted during order entry. When the order is finalized, you simultaneously release the hold and take the item out of inventory.
The primary problem with this method is abandoned orders, because you want to release that inventory so someone else can order it.
That's more of an issue if you are writing an application for your customers than if it's being used by a sales staff who will know to cancel an abandoned order. (However, you probably still need a 'cancel pending transaction' capability to deal with things like system crashes.)
I once designed a web-based transaction system which kept a timestamp on each 'on hold' line item. It assumed that if the order wasn't completed within an hour the order had been abandoned, and at that point it released the hold on any items. (Actually it just checked the timestamp when adding up the 'on hold' quantity during an inventory check and ignored any timestamp that was more than an hour old.)
There was also a one hour inactivity timeout on the web form, as I recall.
You should be able to do most of this with trigger functions. -- Mike Nolan