MySQL START Transaction and COMMIT occurring seperately

Codes here !

Moderators: macek, egami, gesf

New php-forum User
New php-forum User
Posts: 2
Joined: Mon Sep 03, 2012 3:52 am

MySQL START Transaction and COMMIT occurring seperately

Postby ShahriarNK » Tue Oct 09, 2012 12:19 am

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/separate scripts.

Can someone please provide some guidelines on how this can be achieved?

php-forum Fan User
php-forum Fan User
Posts: 973
Joined: Mon Oct 01, 2012 12:32 pm

Re: MySQL START Transaction and COMMIT occurring seperately

Postby seandisanti » Tue Oct 09, 2012 8:38 am

I may be wrong, but I don't think that you can do it that way. One thing you can do though, is add a field to your record that says who is editing it. Then your SELECT statement that populates the rows for other visitors would just have to be modified to grab where that field is null, and a check to give them the row already assigned to them if there is one. Then you can have a cron job run every minute or two and null that field out for users not logged in. Your updates should all happen at once, not one at a time where you would need to use a transaction for a single record update.

Return to “mySQL & php coding”

Who is online

Users browsing this forum: No registered users and 2 guests