Posted by Evert | Rooftop on 08/29/05 00:34

Reading all this I suddenly realise I have the same problem. I'm using a
modified tree traversal alghoritm to store my tree in the database, but
when I insert a new node I assign new ID's based on a SELECT query.
I think I can deal with it with the combined UPDATE..SELECT query. Right
now an anomaly only occured once and I had a recovery script running to
try to fix the situation (it worked!). But there's a good chance this
was the cause.

Evert

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