php-general Digest 29 Aug 2005 05:20:59 -0000 Issue 3651
Topics (messages 221485 through 221491):
Re: just a php/mysql logic question
221485 by: Robert Cummings
221487 by: Satyam
221488 by: Evert | Rooftop
221489 by: Robert Cummings
221490 by: Jasper Bryant-Greene
<kein Betreff>
221486 by: Jan Broermann
Re: Getting queries from files FYI
221491 by: Raj Shekhar
Administrivia:
To subscribe to the digest, e-mail:
[EMAIL PROTECTED]
To unsubscribe from the digest, e-mail:
[EMAIL PROTECTED]
To post to the list, e-mail:
[email protected]
----------------------------------------------------------------------
--- Begin Message ---
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.
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. |
`------------------------------------------------------------'
--- End Message ---
--- Begin Message ---
"Robert Cummings" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> 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. |
> `------------------------------------------------------------'
--- End Message ---
--- Begin Message ---
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" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
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. |
`------------------------------------------------------------'
--- End Message ---
--- Begin Message ---
On Sun, 2005-08-28 at 15:58, Satyam wrote:
> "Robert Cummings" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > 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. |
`------------------------------------------------------------'
--- End Message ---
--- Begin Message ---
Evert | Rooftop wrote:
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.
That's kind of what I meant in my initial reply with the comment about
transactions. If you need the lock on the table rows to extend over
several SQL statements, and you couldn't fix this by using, for example,
an UPDATE..SELECT query, your best bet would be to wrap the statements
that *had* to be done together in a transaction [1].
Then you would write your SELECT statements as
SELECT .. FOR UPDATE
which locks the rows selected until the end of the current transaction [2].
So your SQL looks like:
START TRANSACTION
SELECT ... FOR UPDATE
[...]
UPDATE ...
COMMIT
[1] http://dev.mysql.com/doc/mysql/en/transactional-commands.html
[2] http://dev.mysql.com/doc/mysql/en/select.html
--
Jasper Bryant-Greene
Freelance web developer
http://jasper.bryant-greene.name/
If you find my advice useful, please consider donating to a poor
student! You can choose whatever amount you think my advice was
worth to you. http://tinyurl.com/7oa5s
--- End Message ---
--- Begin Message ---
_________________________________________________________________________
Mit der Gruppen-SMS von WEB.DE FreeMail können Sie eine SMS an alle
Freunde gleichzeitig schicken: http://freemail.web.de/features/?mc=021179
--- End Message ---
--- Begin Message ---
Robin Vickery <[EMAIL PROTECTED]> writes:
> On 8/23/05, Jay Blanchard <[EMAIL PROTECTED]> wrote:
> > You may (or may not) remember me posting to the list a couple of weeks
> > ago asking about using REGEX to get queries out of PHP files for a
> > migration project. I had to let it go for several days, but started
> > working on it again yesterday, here is the code (no REGEX was used in
> > the making of this code);
>
> Just a thought - and I know it's a little late, sorry.
>
> Have you considered writing a wrapper for mysql_query() that logs its
> parameters?
(I cannot locate the original post, hence replying to this mail)
You can enable logging in mysql server itself to log all queries
http://dev.mysql.com/doc/mysql/en/binary-log.html ( there are ways to
restrict logging to only some databases) . Later when you want to replay
the sql statements, you can use mysqlbinlog
--
Raj Shekhar
blog : http://rajshekhar.net/blog home : http://rajshekhar.net
Disclaimer : http://rajshekhar.net/disclaimer
--- End Message ---
