By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,527 Members | 988 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,527 IT Pros & Developers. It's quick & easy.

Multiple Update - Record Locking advice

P: n/a
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?
Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a
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
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.