Posted by "Satyam" on 08/28/05 22:58

"Robert Cummings" <robert@interjinn.com> wrote in message
news:1125243084.29396.15.camel@blobule.suds...
> On Sun, 2005-08-28 at 06:10, Jasper Bryant-Greene wrote:
>> Dave Carrera wrote:
>> > If multiple users hit the php app at the same time how do i ensure that
>> > the correct amount of stock is taken from stock so that a users does
>> > not
>> > accidentally sell from stock which has already been sold.
>>
>> Even though multiple users may hit the PHP app "at the same time" (even
>> though single processor machines can only actually do one thing at a
>> time anyway), they can't all access the tables at the same time.
>>
>> MySQL does something called table locking, which means that if you're
>> updating a table then other clients SELECT statements for the same rows
>> will wait until the table has finished being updated (usually not many
>> milliseconds...)
>>
>> This means that if you have something like:
>>
>> UPDATE stock_table SET stock_count=stock_count-1 WHERE id=935882
>>
>> and someone else hits it at the same timeand asks:
>>
>> SELECT stock_count FROM stock_table WHERE id=935882
>>
>> MySQL won't answer until the UPDATE statement has finished. You likely
>> wouldn't even notice the delay though.
>>
>> In short, don't worry about it unless you're doing more complex things
>> where a bunch of statements need to be either all done at once, or not
>> done at all. In that case you might like to look in to making your
>> tables InnoDB and using the transaction features of MySQL.
>
> Yikes, the above is classic race condition scenario. You select the
> stock count, see you have the same amount, then write back an update
> statement. In between the select and update another user has just
> performed the same select, thinks there's sufficient stock, and then
> both users update the database table resulting in a stock of -1 if the
> original stock was 1. MySQL doesn't lock the table unless you explicitly
> lock it yourself. So the solution to the guys dilemma is to look into
> MySQL locking mechanisms. He will want to lock, select, update, unlock.
>

Locks are never a good idea.

An update query such as this:

update stock set qty = qty - $qty where qty > $qty

(assuming the ones with a $ are PHP variables expanded into the string) will
do the update if there is enough stock. You can immediatly check
mysql_num_rows() to see if the update was successfull. If it returns 0, it
means there wasn't enough stock. There is no locks involved, no previous
select.

Nevertheless, this is just one posibility, the other being first checking
the quantity available and then doing the sale. In this case, you would
have two separate transactions, one a select to see how many units are
available, a second to update the quantity. This two transactions require
user intervention in between, which might last an indefinite time, besides
the real possibility of the session being lost either due to communication
error or the user closing the browser. You cannot lock a database table in
between two transactions which are not assured to be completed in a single
operation. If you lock the table before doing the select and release it
after the update, you will be holding the system for all the other users.
This is not acceptable.

I am afraid that this second scenario is not feasible. You and your users
have to assume that all checks for availablility are contingent on final
confirmation. You may check for stock, but there is no way to ensure that
stock will hold.

Now, if a good management of stock is not good enough to ensure
availability, then you might have to do far more complex things. For
example, a purchase order (PO) might depend on a series of interdependent
materials and if one of them is not available, the order is not processed.
In such a case you might have a separate table with materials set aside.
You just add whatever you plan to take to that table as 'reserved'. Those
reservations have to be tagged under a PO number or such, so that if the PO
is cancelled, you delete all the reserved articles. With this table,
whenever you check for stock you have to check how much there is in the
stock table minus whatever is in the 'reserved' table. When you confirm the
PO a single transaction, a stored procedure, if possible, or a single quite
complex update with multiple dependent tables, gets everything done in hjust
one transaction. Such operation can be done with the tables locked, as they
all are done in a single moment.

Satyam


> You are right though that they don't access the table at the same time,
> but each is doing multiple actions to the table and those can become
> interlaced.
>
> Cheers,
> Rob.
> --
> .------------------------------------------------------------.
> | InterJinn Application Framework - http://www.interjinn.com |
> :------------------------------------------------------------:
> | An application and templating framework for PHP. Boasting |
> | a powerful, scalable system for accessing system services |
> | such as forms, properties, sessions, and caches. InterJinn |
> | also provides an extremely flexible architecture for |
> | creating re-usable components quickly and easily. |
> `------------------------------------------------------------'

[Back to original message]