Blocking a record from editing while another user is editing the record - how?

Question

I'm trying to add record locking to a site. This isn't database locking where I have 2 processes editing the same record at the same time, but instead 2 users editing the same record at the same time. Here's the situation:

I need to be able to lock a record while 1 person is editing it so someone else can make changes to record at the same time (and therefore cause confusion). What can happen is user A starts editing a record. In the mean time (before user A saves the record), user B starts editing the record. Now user A saves the record with field_a having a value of "0001". Now user B is seeing and editing an out of date version of the record. Then user B saves the record with a value of "0002". The database ends up with a value of 0002.

I can understand what happened, but it's hard for a user to figure out that the value in the DB will be 0002 instead of 0001. In my case, they are using this number to associate the record in the online database to another record. When this happens, they'll end up with 2 records in the other system for 1 record in the online system.

My thought is to "lock" a record by putting the user's id in field. When 0 it's not locked. >0 is locked.

Here are my thoughts on how things would function this:

• When a user clicks the edit on a record, they will receive the lock for the record.
• They will have this lock for a maximum of X minutes (probably 30). The reason for this is because of clicking the back button instead of save or cancel.
• Once they save, the record will be unlocked.
• To remove the lock without saving, they will need to click the "unlock" (or similar) on the edit page. I would do cancel, but I want them to be able to cancel and still hold onto the lock.
• On the list of records, I'll include the name of the person who's locked the record so everyone will know.
• Admins will be able to remove the lock for a record, just incase it all falls apart.
• Add an area on every page to warn the user that either the current record needs to be unlocked when they are done or just displaying on every page the number of records they have locked.
• Add a script that checks for records that have been locked for more than 30 minutes and unlock them (possibly sending an email to the user that locked the record).

What I'm wondering is if there are any cases I'm missing that are going to cause grief or if you have any suggestions on how to do this?

Note: I am working in PHP and MySQL and it's a web application. I also don't think record locking in the database is what I need.

-- Addition: a suggestion from Dan Hulton was to add a date field which would store the last time it was checked out. This would eliminate the script to check for expired checkouts. Instead the system would just check this date when listing or restricting editing of the records.

Answer 1

Well, you need some locking scheme. For the kind of thing you're talking about, people often use "optimistic locking", which works like this:

• associate a version number with the appropriate record(s)
• when someone opens the file for edit, keep track of the version number

• when anyone attempts to save the file, check the version number. If the version number is the same as the one you retrieved, you save, and then increment the version number. BUT if the version number is not equal to the one you saved when you opened the file, you have a conflict; someone else has changed the record when you weren't looking.

• If you have a conflict, you ask the user to resolve it somehow.

(This is called "optimistic" because you're assuming that most of the time you won't have two users trying to update at the same time.)

The pessimmistic version of this would be to have a field for the lock. When someone opens the record for edit, check that field; if it's 0, you can edit, but then add 1 to the field. When you save, subtract 1 again. If it's not 0, you refuse to let the user edit.