Posted by Rik on 05/29/06 13:53

Jerry Stuckle wrote:
>>> Hi again,
>>> I am constructing a site on a small scale where people can buy nik
>>> naks and pay for them via paypal. Everything works fine unless there
>>> is only one of an item left for sale. What if 2 people try to buy
>>> this item at the same time?
>>> I have partially overcome this problem by adding an extra column
>>> called status to my catalogue table. When somebody tries to buy the
>>> item I have created an interim page where the user is asked to
>>> confirm his purchase before sending the info to paypal and I have
>>> inserted an update query that changes the status from "available" to
>>> "pending sale" on this page.
>> I expect you keep the data of (to be) purchased items in some kind of
>> session.
>> If you choose to store this session in the database instead of the
>> default handler:
>> - You could check wether a product is available by looking at stock
>> minus
>> the amount in the (several) session.
>> - Other people will immediately see that an item is out of stock, or
>> perhaps "reserved" if you choose to.
>> - As soon as their purchase is aborted or time out after a arbitrary
>> amount of time, items are available again.
> This is a typical problem, especially when working on the web.
> However, it has existed ever since databases started tracking data
> :-).
>
> One of the worst things you can do is keep this in a session.
> Session data may not be written to the database immediately, causing
> you to sell the last item twice. Additionally, session data is not
> necessarily cleared from the database when the session expires. So
> you may not be able to sell an item which is available.
>
> A database is a good place to keep session data for session data's
> sake. But don't try to use it to access data from another session.
>
> Rather, post the potential sale to a pending transaction table, along
> with the current timestamp. Then have a proc run which goes through
> the transaction table and looks for timestamps older than X minutes
> (i.e 15 min.- whatever your session timeout is). If any are found,
> add the item back into the inventory and remove the entry from the
> list.

Essentially, this is what I said: write the pending orders to a table, as
soon as the purchase is aborted or "timed out", remove it.

Maybe my wording was a bit of: in stead of saving the orders in a session,
save them to a table directly. No ambiguity there.

Grtz,
--
Rik Wasmus

[Back to original message]