Thread: Help with inventory control

I need the following to work with PostgreSQL.  I was thinking of reading uncommitted transactions, but just found out they do not work on PG.  I need to have user 1 take an item form inventory for an order and before user 1 finishes the transaction user 2 will query the inventory.  I need user 2 to see the quantity on-hand reduced by the amount user 1 took.

Because read uncommitted is not supported is there a way to do this?

Thank you for all your cooperation.

Fernando

On Fri, Sep 30, 2005 at 10:21:14AM -0400, Fernando Grijalba wrote:
> I need the following to work with PostgreSQL. I was thinking of reading
> uncommitted transactions, but just found out they do not work on PG. I need
> to have user 1 take an item form inventory for an order and before user 1
> finishes the transaction user 2 will query the inventory. I need user 2 to
> see the quantity on-hand reduced by the amount user 1 took.

Why not just commit user 1's transaction then user 2 can see it.
Transactions should only be open for fractions of seconds in the
situation you're describing. You're not rewriting the whole database
here.

> Because read uncommitted is not supported is there a way to do this?

Read Uncommitted goes against the very nature of a database...
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Thank you for your response.

I want to avoid the following situation.

User1 starts order and takes the last two units.  User2 starts order 1 minute 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?

Thank you,

Fernando

Fernando Grijalba <fgrijalba@gmail.com> writes:

> Thank you for your response.
>
> I want to avoid the following situation.
>
> User1 starts order and takes the last two units.  User2 starts order 1
> minute 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?

I think just using SELECT FOR UPDATE should solve this problem--it
will lock the row and SELECT FOR UPDATE in the second transaction
won't return until the first transaction commits.

-Doug

Thank you for your input Doug, but that did not work. User2 was still getting the qty unchanged.

Any more ideas?

I was thinking of creating a table that will hold the product code and the qty taken by an order.  check this table evey minute and if a product has been there for more that 10 minutes and return it to inventory if that is the case.  After I save the order I would remove the prodcut for this order from this table.  This way I can just update the qty in one transaction when the user gets the product for the order and then save the order in a different transaction.  This will allow me to reduce the quantity at the time the order obtained the product and increase it when the order is cancelled or if the user forgets or his/her computer crashes.

Is this a good idea?  Your input is appreciated.

Fernando

> 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