Posted by Robert Cummings on 08/29/05 00:37

On Sun, 2005-08-28 at 15:58, Satyam wrote:
> "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.

If locks were never a good idea then they wouldn't exist. Quite plainly
they are a necessity in some situations.

> 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.

This doesn't allow an action based upon the existence of inventory in
the first place. You don't know until after the query whether there was
inventory which is not a satisfactory solution in all cases.

> 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.

You can lock a table in between two transactions and this is commonplace
and necessary. What isn't commonplace and necessary is the locking of
the data between multiple page requests.

> 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.

Untrue, you may lock the table, check for stock, find that there is
some, then reserve the stock in another table denoted by the user's
session or whatnot. Then in the event the user's session times out you
may return the reserved stock back to the stock table.

> 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.

I thought you said locks were "NEVER" a good idea. At any rate this is
what I had in mind. I never once said anything about locking between
page requests.

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]