Multiple Update - Record Locking advice

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

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

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
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?

Re: Multiple Update - Record Locking advice

oldandgrey wrote:

Quoted text here. Click to load it

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

Site Timeline