| re: Multiple Update - Record Locking advice
oldandgrey wrote:
[color=blue]
> 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?[/color]
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
| 
