|
Home > Archive > MySQL Server Forum > March 2005 > Multiple Update - Record Locking advice
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Multiple Update - Record Locking advice
|
|
| oldandgrey 2005-03-30, 9:40 am |
| I'm looking at producing an application that will allow multiple users to
order multiple items/ parts from what would effectively be an online store.
These users could be ordering several hundred items/ parts at a time, each
item/ part ordered representing one box or several hundred.
Each part record will require the "total stock" to be down dated and each
unit will require updated to show it as having been picked (plus other
info).
I'm looking at programming this in php 4.3 or php 5 using a current version
of mysql. Looking around etc. I believe there are various options options
open to me :
1 Create a pick list in a database table and have a "pick" routine pick the
stock. This would ensure that the actual pick could only be carried out by
a single process and the result of the pick request could be emailed to the
user. This approach would provide the user with a very fast, responsive
acceptance of an order request (but not real time - albeit almost).
2 Use Table Locks - This I understand may be the fastest but potentially
could leave users with apparently hung screens, eg. if three picks were
issued each taking 10 secs, then pick 3 would not start until the first two
picks completed (20 secs).
3 Record Locks - I'm concerned that doing this would increase the time taken
to pick the stock. But if the times were not excessive this would provide
the user with an immediate "real time" response.
What would you reccomend?
| |
| oldandgrey 2005-03-30, 7:03 pm |
| oldandgrey wrote:
> I'm looking at producing an application that will allow multiple users to
> order multiple items/ parts from what would effectively be an online
> store.
>
> These users could be ordering several hundred items/ parts at a time, each
> item/ part ordered representing one box or several hundred.
>
> Each part record will require the "total stock" to be down dated and each
> unit will require updated to show it as having been picked (plus other
> info).
>
> I'm looking at programming this in php 4.3 or php 5 using a current
> version of mysql. Looking around etc. I believe there are various options
> options open to me :
>
> 1 Create a pick list in a database table and have a "pick" routine
> pick the stock. This would ensure that the actual pick could only be
> carried out by a single process and the result of the pick request could
> be emailed to the user. This approach would provide the user with a very
> fast, responsive acceptance of an order request (but not real time -
> albeit almost).
>
> 2 Use Table Locks - This I understand may be the fastest but
> potentially could leave users with apparently hung screens, eg. if three
> picks were issued each taking 10 secs, then pick 3 would not start until
> the first two picks completed (20 secs).
>
> 3 Record Locks - I'm concerned that doing this would increase the
> time taken to pick the stock. But if the times were not excessive this
> would provide the user with an immediate "real time" response.
>
> What would you reccomend?
Another Alternative would be to add an additional field to each record
called "Rel" then perhaps the process flow would be :
1 Read record to be updated
2 Update Record
a) UPDATE TABLE SET Flag = 'Y', Rel = Rel + 1 WHERE id = .. AND Rel = ..
b) if mysql_affected_rows(
)<>1 generate error
|
|
|
|
|