Posted by Jerry Stuckle on 05/29/06 05:38

Rik wrote:
> Dynamo 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.
>
> Grtz,

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.

OTOH, if the person completes the sale, remove the item from the pending
transaction list and place it on a completed order list.

The program which cleans up the pending transaction table can be kicked off via
a cron job (my favorite way). Another way would be to execute it when someone
tries to purchase an item. In the latter case I would recommend some type of
locking action (i.e.an "I am running" entry in a table) to keep more than one
process from running at the same time.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]