Web front-end design for concurrent modification

Question

I am creating a web application which has a few management screens, where a user can go to edit records (for example, changing the contact details of a user). Access to these managements screens are controlled by roles, with multiple users possibly having access. The problem now arises of what to do if two users simultaneously try to edit the same record.

My problem is with the front-end, not with the back-end. What are some of the patterns I can use in order to design my pages so as to be both user-friendly and prevent concurrent modifications? The only two options I can think of are these:

• Pessimistic locking: Difficult in a web environment where I'd need to wait for the session to timeout before being able to release the lock in most cases.
• Optimistic locking: Not that good in terms of user-friendliness, as a user might fill in a large form only to be greeted with a "cannot save" message at the end.

Any suggestions?

Answer 1

How about a timed reservation? This is a model used in many on-line booking systems.

An example based on trivial single table case (I hope that extending to more tables should be obvious) Add a column to the table called RESERVATION_TIME. All records are initially populated with a reservation time of "many years ago".

Use this in conjunction with optimistic locking. At the point you go into Edit mode you

• Make a reservation for the record you want

  UPDATE RESERVATION_TIME to NOW where KEY = id and RESERVATION_TIME more than 30 mins ago

This will only work if no one has already set the up update to recently. The idea is that we don't even allow the edit screen to be populated if some other user is already working. But we set that reservation to expire after 30 mins (or whatever) so that nothing is "locked" permenently. But note that we are not (in database terms) holding a pessimistic lock.

• Now retrieve the data (maybe inn the same tran as took the reservation)

• on write back check the optimistic predicate and clear the reservation back to a long time ago.

Now this mediates between two users of any applications that honour the reservation system. The optimistic lock mediates between all optimistic users of the system, so it's still possible for the user to be irritated, but assumig that out-of-band updates are rare then you should get acceptable usability.

Some other ideas:

1. Last save wins - for some data there's no point in locking. You were going to allow user Bill to change Alice's work anyway and vice-versa, so allow either to win.
2. Detect conflict and merge. Like the CVS source control system. Use an optimistic scheme and in the event of conflict present the intervening update and the proposed updat and ask the user to resolve the conflict.

Answer 2

We implement a first-write-wins mechanism in similar situations (or Optimistic Locking)

BACKEND:

Create a timestamp field in the database (this is automatically updated in MSSQL on an INSERT and UPDATE)

When you load the objects for editing, including a timestamp property, only allow saving in your stored procedures if the timestamp is the same else throw an error, handle this in your application indicating the record has changed and give them the opportunity to re-load the page. This works well on the web/disconnected environments.

FRONT END

The page needs to poll the database (using ajax) to detect a change. If there is a change prompt the user indicating the record has changed and option to reload/merge. On the page postback display labels or secondary form fields (readonly) with the new entered values on the right that can be copied, eg arrow button pointing left that copies the values back to the original form fields.