Stop users overwriting each other

Question

Hey,

I'm wanting to stop two users accidently overwriting each other when updating a record. That is to say two users load a page with record A on it. User one updates record to AB and user two updates it to AC.

I don't just want the last to hit the database to override. I need a mechanism to say the record has been updated so yours can't be saved.

Now the two ideas I have is to time stamp the records and check that. If it doesn't match up don't allow the update. The second method is to GUID the record each time an update is performed, check the GUID and if it doesn't match don't update.

Are either of these methods valid, if so, which is best. If not, what do you suggest. This is in C# if it makes a difference

Thanks

Answer 1

The two methods you've mentioned are effectively equivalent - either way you've got a unique identifier for "the record at the checkout time" effectively. I don't have any particular view on which is better, although a timestamp obviously gives you the added benefit of data about when the record was valid.

An alternative is to remember what the previous values were, and only allow an update if they match - that way if user A started editing a record, then user B goes in and changes something, then changes it back, user A's edits are still valid.

The term for these techniques is optimistic locking (or optimistic concurrency control).

Answer 2

I am using the first option. Update the timestamp on each update. So at the time of update we check the equality of the timestamp.

Answer 3

You're describing Optimistic Locking, a valid and useful technique.

See references here.

Answer 4

There is actually a third method. To do the update, issue an update statement of this form:

UPDATE table SET update_field = new_value
WHERE db_pk = my_pk       // assume primary key immutable
AND update_field = original_field_value

where original_field_value is the value of the field before the update was attempted. This update will fail if someone else has modified update_field, unless they have changed it to the same value that you have.

Answer 5

Hi,

Either method is valid for checking.

As to which is the best you have to look at the size of your app and how long it will take to implement each one. So if this is only ever going to happen occasionally then I'd prob go for the quicker solution and implement the timestamp option.

If you want something more detailed google concurrency - heres an article to start with - concirrency

Answer 6

Do the terms optimistic and pessimistic locking ring a bell. These are the two recognised approaches to the problem you are describing. It sounds like you are working in a web environment. In this case the former option (optimistic locking) is more appropriate. You have gone on to describe how this would generally be implemented. It is common to use a timestamp or a version number to check if the record has been updated since the record was retrieved. One other thing to consider is to let your users know that there has been a change to the underlying data and potentially give them the option to choose between what they have attempted to save and what was save by another user. This choice really depends on what the business rules are.