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@xxxxxxxxxxxxx> wrote in message
news:1125243084.29396.15.camel@xxxxxxxxxxxxxxx
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. |
`------------------------------------------------------------'
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
