Is there a way to lock a record in a MySQL table for a specific period of time?

Question

Friends,

I have a table that contains data on the two parents of students at a college. Each parent will be sent an email with a link to a web page that will display the parent data that we currently have on record (names, email addresses, mailing addresses, employment information, etc.), and will be able to edit the data in order to update our records.

Since each parent will receive a link to the same data, and will be able to update the same fields, there is the potential for both parents opening the data at the same time, and then one parent submitting changes, then the other submitting changes which would overwrite those submitted by the first parent.

In order to avoid this, I have thought of using the method I've read about in which a timestamp field exists in the parent data record, and that timestamp is used as a hidden field on the form. Then, if both parents load the form, they'll both have the same timestamp stored in the form. When the first parent submits her/his updates, though, the timestamp field will update, and when the form is submitted by the second parent, the timestamp from her/his form will not be the same as the timestamp in the table, and the program (a Perl CGI) would alert the 2nd parent to this fact, and tell them to reload the form or risk overwriting the data submitted by the first parent.

That will work, but the person for whom I'm creating this form has asked if, instead, there's a way to lock the record in the table as soon as the first parent loads the form, and if the second parent tries to load the form while the lock exists, the form will tell them to wait until later (or words to that effect). The lock would be in place either until the form is submitted by parent one, or until one hour (or some specific period of time) has passed. Is this even do-able? I've been Googling, and don't see specific examples of this having been done.

Is there some better solution to this issue of needing to prevent two people from updating the same record, and the second submitter overwriting data submitted by the first.

Thanks for any help you can provide!

     Doug

*******to address the comment by "inspiredcoder," here are some more details about what I'm concerned with here:

What I'm trying to avoid having happen is that parent 1 opens the form and starts making changes to the data. Before parent 1 submits those changes, parent 2 opens the form and also starts making different changes to the same fields being edited by parent 1. Parent 1 then submits her/his changes. Parent 2 then submits her/his changes, overwriting the changes made by parent 1.

What I would prefer is that parent 2 would not be able to even begin making changes if parent 1 has opened the form. The changes made by both parents need to be captured, and not overwritten.

The method of using the timestamp as I describe in my initial post can be used to prevent parent 2 from overwriting the data, but it also will mean that they'd have to reload the form to see the changes submitted by parent 1, and in doing so, would lose any of the edits they'd made in the form prior to them trying to submit it and getting the notice to reload. I'd like to avoid them having to re-enter their changes, and the only way to accomplish this seems to be to prevent them from even opening the form if it is already being edited, but I'd want that "lock" on the form/data to timeout after an hour or so in case parent 1 walks away with the form open but unsubmitted.

*****To answer a question by "ThisSuitIsBlackNot": Each parent can edit the same fields. One field asks for activities in which the parents are involved. Let's say Parent 1 enters five activities. If Parent 2 sees the form before Parent 1's edits have been submitted, he/she may enter completely different items, which upon submission would overwrite the activities submitted by Parent 1. If, on the other hand, Parent 2 could be stopped from accessing the form until after Parent 1 has finished her/his edits, then when Parent 2 can load the form, she/he will see everything that Parent 1 entered, rather than an empty form field, and may choose to modify what Parent 1 submitted, overwrite it completely, or not make any changes.

Answer 1

There's a reason you're not finding any info on how to do this. It is a very tough problem that no one has a good solution for regardless of which tech stack you're using. In your case, I'm not convinced that it is actually a terribly important issue to solve because the data does not seem crucial or mission critical. And besides, if there are changes they will likely be the same.

I've been in many design discussions where this issue came up. After hours of arguing the result is always the same: Last one in wins.

That said, here are a couple of simpler ideas you could try:

• Simply email both parents (or whoever's registered as a guardian) whenever data on that page changes. This solution is stupid simple and easy to implement. If you're already using email services in other parts of the app then it becomes nearly trivial.
• Not so simple: Whenever a request is made to edit the data, create a hash of the data as is to send back with the response to the client. When the edited data is sent in to update the row, check the data against the hash. If the hashes don't match it means that someone else has modified the data while the other parent was looking at it. The trouble with this solution is that you have to create these hashes and lug them around through several layers of the app making your programming non-trivial.

This statement caught my eye in a later edit of your OP:

The changes made by both parents need to be captured, and not overwritten.

That single business rule actually makes things quite simple for you. All you need to do is to ALWAYS create objects when they do not have a unique identifier (probably 0 or -1). When objects do have an ID, meaning they have already been created, you simply update.

There is an assumption here that edits will likely be performed non-destructively on the same data. e.g. One parent creating an activity and the other parent editing it. There is a chance of duplicate activities but that's a situation easily resolved with a delete.

This way, no one parent can overwrite the other's data blindly and unknowingly.

Regardless of what you do though, do not try to find a perfect solution. It just doesn't happen. I know, I've been writing line of business apps for over 15 years. Apply your time and talents to something that you can get right, which the application and its business rules.

Answer 2

I would suggest reading up on database isolation levels. I believe MySQL defaults to repeatable read. You can confirm your isolation level at the DB level by running "SHOW GLOBAL VARIABLES LIKE 'tx_isolation';" Each transaction in this configuration is already placing a lock. Whether it is getting a row level or escalating depends on factors such as how indexes are being hit etc, by the query. If you fire off transaction A to update a record then subsequently fire off transaction B, transaction B is already in a holding pattern until transaction A completes its work in this configuration. If you set this to read commited, reads no longer block each other with locking (updates, etc still place locks). In lieu of implicit locks on reads you can be explicit using the select for update to try and force a lock on the read.

I mention brushing up on locking mechanics as trying to brute force locking without extreme knowledge of the back end DB mechanics can lend itself to deadlock central.

It seems like in your scenario this is more about user perception that what they are reading is up to date when they submit the changes. The DB is really doing it's job as designed. I have seen architecture to address this user perception issue by only allowing one user in a record at a time (locking out other users from the record while someone is it) handled in some middle ware code, etc. Or by using SOA architecture to push notifications to users in the record that a changed occurred by another user.