Concurrent Updates - Solutions

[blyxx86]

Good Morning Everyone,

I am making a PHP web frontend for a MySQL database. I have finally started to create the UPDATE portions of the website and am wanting some opinions/feedback as to what I'm trying to do.

Since it is a webpage and not a direct, always connected, link to the database the DBMS will assume an optimistic locking method. I am using InnoDB.

What I am thinking of doing to ensure that concurrent SQL UPDATE statements don't mess with the same record I was going to create hidden form variables of the "old" values and then when the form is submitted compare those "old" values to the ones currently in the database when the submit button is clicked. If they are the same (all of them), then I will allow the UPDATE command to take place. If not, an error message would be thrown.

Is there a better way to handle the situation with concurrent (or someone updating a record in one tab, and thinking they didn't update it in another) updates?

Thanks again!
Kyle

PS. I'm pretty sure this belongs in the MySQL section and not PHP, but please let me know if I'm wrong.

[debasisdas]

why not lock the database table once in edit mode .

[blyxx86]

I thought about doing that as well, but what happens if they close the window while they are updating it? I know InnoDB supports row-level locking and that is ideal, but I was worried that if the user cancels out without pressing a "cancel" button then the row will remain locked until the dbms decides it shouldn't be anymore.

Is there an ideal way of locking that you would suggest that works well for web-based apps like the one I'm developing?

[r035198x]

Good Morning Everyone,

...
What I am thinking of doing to ensure that concurrent SQL UPDATE statements don't mess with the same record I was going to create hidden form variables of the "old" values and then when the form is submitted compare those "old" values to the ones currently in the database when the submit button is clicked. If they are the same (all of them), then I will allow the UPDATE command to take place. If not, an error message would be thrown.

....
PS. I'm pretty sure this belongs in the MySQL section and not PHP, but please let me know if I'm wrong.

What if the database values are changed just after you checked that they are OK and then someone else updates them just before you set the new values?

[blyxx86]

Perhaps instead of doing the initial preliminary SELECT, I can do the UPDATE with a lengthy WHERE statement.

Would that be ideal?

[r035198x]

No use. Your where clause would presumably contain a select after which an update can be done from elsewhere. Better synchronize the parts of the code that change the database in a possibly inconsistent manner.
Also have a look at internal-locking.

[blyxx86]

So running:

UPDATE table(columna, columnb)
VALUES ('A', 'B')
WHERE (columna=oldvaluea)
   AND (columnb=oldvalueb)
   AND (id=updateid)

So instead of updating based only off of the id column, it would match the entire contents before finding whether or not it can update the row.

I don't think it would be wise to lock a row/table when someone chooses to "Edit" a record with a PHP based webpage.

I suppose locking the table/row when the "Submit" button is pressed and still performing the extended WHERE statement in the UPDATE sql would still be necessary.

How else would be ideal to cope with this type of situation?