I am uncertain how to ask this concisely, so I will explain it through situational-context.
User-A decides he wants to modify a MySQL-row via an HTML-form. Simultaneously, User-B decides he also wants to modify the same MySQL-row in the same way.
We will say the MySQL-row is a text-field which contains the text, 'Can\'t touch this'
The two users have no knowledge of eachothers intentions.
User-A makes a significant change to the MySQL-row and saves it to the database. The MySQL-row becomes 'Can\'t touch this was a popular track by the Hip-Hop artist M.C. Hammer.'
User-B makes a minor-change to the MySQL-row and saves it to the database. The MySQL-row becomes 'Cannot touch this. Please avoid contractions.'
Because User-B decided to modify the MySQL-row before User-A finished his modification, the Modification User-A made gets overwritten by the modification made by User-B.
How do scripts which allow simultaneous editing of database-rows (such as MediaWiki, or any other wiki-software) deal with this?
Source: Tips4all, CCNA FINAL EXAM
You can use row-level locking to force edits to be done in a series (as opposed to simultaneously). This is often what you'll want to do over devising a way to merge the changes, it'll save you some hassle later.
ReplyDeleteIt also helps if you use a timestamp and sanity variable in the form that sends submission changes to your database / processing script. Then you can check this against the database before writing to it, and if not; give the user a message that there were posts or changes before his/her own.. and let htem see it before commiting their own.
The simplest solution is to add at least one extra field to any tables which can be edited in this manner. Something to indicate that the row's locked. For added usefulness, you should record when then the row was locked and by whom.
ReplyDeleteA timed job to unlock records which were abandoned would also be needed, otherwise someone could just edit every record in sequence, abandon the edit, and lock up the entire database.
Something similar happens here on SO. If you're editing someone's question and someone else saves an edit while you're working away, you get a notice that the question's been changed and your edits are no longer valid.
One way to do this is to add a hidden input to your form with the current timestamp. When the user submits the form, check if the date last modified of the row in your database is greater than the time the form was generated. If it is older, then take appropriate action (up to you what that is). This of course involves storing a date_modified field in your table and updating it with each revision.
ReplyDeleteYou see a great implementation of this when editing or answering questions on Stack Overflow which I assume does some ajax polling to check the time last modified:
Be advised, this post has been edited 2 times
But you can handle this after the form submission as well, although it's probably more annoying to the user.
I liked the Wesley Murch method.
ReplyDeleteAlso you can allow multiple users to change the same document, and employ a merge while the second commit is done. You will need to hold the original document before editing.
One of the easiest methods I know is to add a version field to the table. When you read a record you fetch the version field and add it as a hidden field to your edit form. When the data is posted back after editing, you fetch the record and check if the version field in the db still matches the one of your form. If not, then the record has been edited by someone else in the meantime (how you handle this is up to you). If it matches, you alter the data and increment the version number. Or in other words, a basic implementation of optimistic locking...
ReplyDelete