I'm developing a php / mysql application that handles multiple simultaneous users. I'm thinking of the best approach to take when it comes to locking / warning against records that are currently being viewed / edited.
The scenario to avoid is two users viewing the record, one making a change, then the other doing likewise - with the potential that one change might overwrite the previous.
In the latest versions of WordPress they use some method to detect this, but it does not seem wholly reliable - often returning false positives, at least in my experience.
I assume some form of ajax must be in place to 'ping' the application and let it know the record is still being viewed / edited (otherwise, a user might simply close their browser window, and then how would the application know that).
Another solution I could see is to check the last updated time when a record is submitted for update, to see if in the interim it has been updated elsewhere - and then offer the user a choice to proceed or discard their own changes.
Perhaps I'm barking up the wrong tree in terms of a solution - what are peoples experiences of implementing this (what must be a fairly common) requirement?
I would do this: Store the time of the last modification in the edit form. Compare this time on submission with the time stored in the database. If they are the same, lock the table, update the data (along with the modification time) and unlock the table. If the times are different, notify the user about it and ask for the next step.