Help with inventory control

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/

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

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