469,908 Members | 2,213 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,908 developers. It's quick & easy.

Multiple Update - Record Locking advice

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
1 1720
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.

Similar topics

5 posts views Thread by jayson_13 | last post: by
5 posts views Thread by rdemyan via AccessMonster.com | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.