PHP MYSQL Limit Row for User to Update [closed]
up vote
1
down vote
favorite
What is the best practice to keep multiple users from updating a row in a database at the same time?
I have a php form page with a lot of boxes that the user will update. There's a lot of javascript "conditional type" formatting going on so the user will be spending several minutes on this page. I need to lock the row in the database so another user doesn't try editing the same row. Other users will need to access other rows so locking the entire table isn't an option.
If I set a bit to know if the user is editing the row what happens if the user doesn't save, update or delete the row through the functions on the page so I can unset that bit? They may just close the browser unexpectedly, crash, have the session timeout etc.
I've tried "Select For Update" but that seems to reset as soon as the row is retrieved from the DB. The row is made available almost instantly. I'm testing with 2 different browsers so 2 different sessions.
I'm using PHP 7.027, MySql
Just looking for a general direction to go. Can't seem to find anything online or in any of my books. Thanks for any input.
php mysql
asked Nov 10 at 19:24
S55
63
closed as too broad by Madhur Bhaiya, Vega, Sotirios Delimanolis, Pearly Spencer, Steve Nov 10 at 22:19
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
up vote
1
down vote
favorite
What is the best practice to keep multiple users from updating a row in a database at the same time?
I have a php form page with a lot of boxes that the user will update. There's a lot of javascript "conditional type" formatting going on so the user will be spending several minutes on this page. I need to lock the row in the database so another user doesn't try editing the same row. Other users will need to access other rows so locking the entire table isn't an option.
If I set a bit to know if the user is editing the row what happens if the user doesn't save, update or delete the row through the functions on the page so I can unset that bit? They may just close the browser unexpectedly, crash, have the session timeout etc.
I've tried "Select For Update" but that seems to reset as soon as the row is retrieved from the DB. The row is made available almost instantly. I'm testing with 2 different browsers so 2 different sessions.
I'm using PHP 7.027, MySql
Just looking for a general direction to go. Can't seem to find anything online or in any of my books. Thanks for any input.
php mysql
asked Nov 10 at 19:24
S55
63
closed as too broad by Madhur Bhaiya, Vega, Sotirios Delimanolis, Pearly Spencer, Steve Nov 10 at 22:19
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
1
If using theInnoDBstorage engine, you could use transactions.
– Paul T.
Nov 10 at 19:38
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
What is the best practice to keep multiple users from updating a row in a database at the same time?
I have a php form page with a lot of boxes that the user will update. There's a lot of javascript "conditional type" formatting going on so the user will be spending several minutes on this page. I need to lock the row in the database so another user doesn't try editing the same row. Other users will need to access other rows so locking the entire table isn't an option.
If I set a bit to know if the user is editing the row what happens if the user doesn't save, update or delete the row through the functions on the page so I can unset that bit? They may just close the browser unexpectedly, crash, have the session timeout etc.
I've tried "Select For Update" but that seems to reset as soon as the row is retrieved from the DB. The row is made available almost instantly. I'm testing with 2 different browsers so 2 different sessions.
I'm using PHP 7.027, MySql
Just looking for a general direction to go. Can't seem to find anything online or in any of my books. Thanks for any input.
php mysql
asked Nov 10 at 19:24
S55
63
What is the best practice to keep multiple users from updating a row in a database at the same time?
I have a php form page with a lot of boxes that the user will update. There's a lot of javascript "conditional type" formatting going on so the user will be spending several minutes on this page. I need to lock the row in the database so another user doesn't try editing the same row. Other users will need to access other rows so locking the entire table isn't an option.
If I set a bit to know if the user is editing the row what happens if the user doesn't save, update or delete the row through the functions on the page so I can unset that bit? They may just close the browser unexpectedly, crash, have the session timeout etc.
I've tried "Select For Update" but that seems to reset as soon as the row is retrieved from the DB. The row is made available almost instantly. I'm testing with 2 different browsers so 2 different sessions.
I'm using PHP 7.027, MySql
Just looking for a general direction to go. Can't seem to find anything online or in any of my books. Thanks for any input.
php mysql
php mysql
asked Nov 10 at 19:24
S55
63
asked Nov 10 at 19:24
S55
63
asked Nov 10 at 19:24
S55
63
asked Nov 10 at 19:24
S55
63
asked Nov 10 at 19:24
S55
63
63
closed as too broad by Madhur Bhaiya, Vega, Sotirios Delimanolis, Pearly Spencer, Steve Nov 10 at 22:19
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as too broad by Madhur Bhaiya, Vega, Sotirios Delimanolis, Pearly Spencer, Steve Nov 10 at 22:19
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
1
If using theInnoDBstorage engine, you could use transactions.
– Paul T.
Nov 10 at 19:38
add a comment |
1
If using theInnoDBstorage engine, you could use transactions.
– Paul T.
Nov 10 at 19:38
1
1
If using the
InnoDB storage engine, you could use transactions.– Paul T.
Nov 10 at 19:38
If using the
InnoDB storage engine, you could use transactions.– Paul T.
Nov 10 at 19:38
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
The answer is already described here: https://softwareengineering.stackexchange.com/a/184098/317637.
The main gist is you should use optimistic locking. An exerpt from the answer mentioned above:
Basically, you need to timestamp all your editable records with the last update time. When you try to save the record, you first verify that the record in the database has the same last-updated timestamp as your record. If not, then someone has updated the record and you now have to either get the updated record and inform the user that they need to enter their changes again, or you can try to merge the user's changes into the updated record (which usually turns out to be either simple or impossible).
answered Nov 10 at 19:38
AWhitePelican
714
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
The answer is already described here: https://softwareengineering.stackexchange.com/a/184098/317637.
The main gist is you should use optimistic locking. An exerpt from the answer mentioned above:
Basically, you need to timestamp all your editable records with the last update time. When you try to save the record, you first verify that the record in the database has the same last-updated timestamp as your record. If not, then someone has updated the record and you now have to either get the updated record and inform the user that they need to enter their changes again, or you can try to merge the user's changes into the updated record (which usually turns out to be either simple or impossible).
answered Nov 10 at 19:38
AWhitePelican
714
add a comment |
up vote
0
down vote
The answer is already described here: https://softwareengineering.stackexchange.com/a/184098/317637.
The main gist is you should use optimistic locking. An exerpt from the answer mentioned above:
Basically, you need to timestamp all your editable records with the last update time. When you try to save the record, you first verify that the record in the database has the same last-updated timestamp as your record. If not, then someone has updated the record and you now have to either get the updated record and inform the user that they need to enter their changes again, or you can try to merge the user's changes into the updated record (which usually turns out to be either simple or impossible).
answered Nov 10 at 19:38
AWhitePelican
714
add a comment |
up vote
0
down vote
up vote
0
down vote
The answer is already described here: https://softwareengineering.stackexchange.com/a/184098/317637.
The main gist is you should use optimistic locking. An exerpt from the answer mentioned above:
Basically, you need to timestamp all your editable records with the last update time. When you try to save the record, you first verify that the record in the database has the same last-updated timestamp as your record. If not, then someone has updated the record and you now have to either get the updated record and inform the user that they need to enter their changes again, or you can try to merge the user's changes into the updated record (which usually turns out to be either simple or impossible).
answered Nov 10 at 19:38
AWhitePelican
714
The answer is already described here: https://softwareengineering.stackexchange.com/a/184098/317637.
The main gist is you should use optimistic locking. An exerpt from the answer mentioned above:
Basically, you need to timestamp all your editable records with the last update time. When you try to save the record, you first verify that the record in the database has the same last-updated timestamp as your record. If not, then someone has updated the record and you now have to either get the updated record and inform the user that they need to enter their changes again, or you can try to merge the user's changes into the updated record (which usually turns out to be either simple or impossible).
answered Nov 10 at 19:38
AWhitePelican
714
answered Nov 10 at 19:38
AWhitePelican
714
answered Nov 10 at 19:38
AWhitePelican
714
answered Nov 10 at 19:38
AWhitePelican
714
714
add a comment |
add a comment |
1
If using the
InnoDBstorage engine, you could use transactions.– Paul T.
Nov 10 at 19:38