This is the scenario: I have multiple users using a PHP page/form to edit data concurrently. The table has rows of data and each user is allowed to edit any one row at a time. The system selects the row to be edited; so, the user is simply given a row to edit; he does not decide which row he will edit.
The first time a user visits the page with form, it load a row. Also, so that the system can decide which row to provide to any other concurrent user, it sets a flag in the database table indicating that the row has been loaded for edit (to another concurrent user).
Now, if the user refreshes the page or quits the page without clicking the Submit button, I want to roll-back the flag change so that the row that was loaded can be made available again for edit.
For this, I am planning to use MySQL transaction. - At the start of the transaction the flag will be set. - Then, the data to be edited will be loaded into the form.
Once, the user edits the data and submits it (by clicking a button), only then Commit will be issued.
So, my understanding is that the START transaction and the Commit will be occurring separately.
Can someone please provide some guidelines on how this can be achieved?