Here is a question that no-one has ever been able to answer for me.

I want to write a web application in ASP (possibly ASP.NET) that allows several people access to the same data, and allows them each to edit that data.

Major problem: while Bill is editing the data, Bob is editing it too. Therefore, when they have both submitted their edits, the last one to submit will overwrite the first, thus wiping out (potentially) the edits made by the first.

This is totally unacceptable. Is there a way to avoid it?

I have thought about creating flags in the database to indicate that a piece of data is currently being edited (thus preventing others from doing so) but this is no good, since the flags will not be properly cleared if the user simply closes his browser (or loses connection) before submitting the edit.

Please, please, please..... this is SUCH an obvious issue. Surely SOMEONE knows the definitive answer to it?

Thanks.

Lets all take a deep breath and relax =] First, in so far as a user simply closing the browser you have 2 choice IE has an event OnClosing that you can capture with JavaScript (slightly buggy in other browsers) then have this javascript raise an event to release your locks.

On the .NET side, I have a class that I wrote that releases resources right before a user session expires so if a user just closed a browser, when their session expires on the server this class handles all of the resource release's (of course this can take 15mins or more depending on what you have your application set to timeout as)

You can write a 'Merge' function in SQL that, when Bob edits data it inserts a new row and when Rose edits data it inserts a new row then your SQL Procedure would go through and merge the 2 records to the source document, but my question to you is, what data is the correct data to keep or discard?

Some food for thought: I work in the Health Care Industry and wrote an Electronic Records Management application for our agency and we have about 700 clients and just about everyone of our 300 employees can edit data in some regard. (Obviously employees are restricted to what client information they can see but we also have power users that can see everyone) Since this systems inception I have not had an issue of employee A overwriting employee B's data or vice versa.

I am not saying it can't happen but the probablility of it happening is extremely low.


-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature

This issue is commonly called Data Concurrency, concurrency control, or simply Concurrency.

It IS a big deal, and the probability is VERY HIGH in a multi-user environment that "collision" will occur, and often. An enterprise system that does not consider and mitigate concurrency issues would not be considered professional. I have never worked on an enterprise app that didn't deal with concurrency in a more or less robust manner, and have seen lots of various solutions. No matter what anyone tells you, in a real enterprise environment this is a critical issue that must be considered and dealt with.

There are several ways to deal with this. The basic requirement is to be able to determine if the data in the row you are editing has changed since you last read it. There are at least 4 common ways to do this.

1 - The data from the row is stored locally in its original form until the attempt is made to update the data. At that time, the row is retrieved from the database and the current values are compared with the original data. If they are the same, then you can go ahead and make the Update. If they are different, you can handle it based on some rule that you follow - the typical one being to let the user know that the data has been changed by another user and their save didn't go through. Sometimes it is appropriate to allow them to have their change override the original change. You have to decide what rules best fit the application you are writing. The stored original data can be in the form of XML, discrete variables, or whatever works for you. It can be stored in session, or part of a hidden field in the html.

2 - Add a datetime stamp field to every table. That field holds the datetime stamp of when the field was last updated. You always retrieve this field when reading data and keep it for checking when you return to do your save. If the stamp is different, you handle it in the same way as option 1. Again, the datetime stamp can be stored in session, or part of a hidden field in the html - this goes for the remaining options as well.

3 - Add a guid that does the same thing as the timestamp verion - it acts as a value that is changed each time the data in the row is changed and can be checked to see if the read you are working with has the same guid that is currently in the database.

4 - "Hash" the data in the row you are working with using some simple (and hopefully very fast) algorithm. You store the hash number and then, like the other options, retrieve and rehash the data - if the hashed values are the same, then proceed, and if different, inform the user like the other options. This method is useful when you aren't given the option of adding fields to the tables as in option 2 and 3.

There are other ways as well, but they mostly fall into the above pattern. You can look up things like DiffGrams or concurrency control on the interent to find a starting point for gathering more info. For example, here is a link that came up when I search on concurrency control:

http://www.agiledata.org/essays/concurrencyControl.html

I almost always use the first option. In a typical client/server or n-tier windows app it is very simple to serialize the original data, and it gives you the benefit of checking what data actually changed so you could inform the user if that is useful for your user.

When you have a lot of data - such as huge text fields, then the hashing or datetime stamp or guid methods can be better so you don't have to pass around or store all sorts of data.

There are numerous other variations to consider. In some cases you might allow changes to fields that have not been changed. For example, in this scheme: If the other user has changed the LastName field, but not the FirstName field, and your user is changing the FirstName field but not the LastName field, you would allow that second change to go through.

A note: I am a bit surprised at how, in the past, little information has been out on the internet about dealing with this issue. Lately, there seems to be more out there that you can track down. This is such a fundamental issue, and as far as I am concerned is on a par with transactions in being something that enterprise programmers must know about.

One last thing:
The flags idea is a difficult solution to implement well for the concurrency problem, but it shows you are thinking. This sort of locking will be troublesome to deal with. I've worked on several projects where this sort of thing had been implemented to solve various locking needs and caused endless bugs and a poor user experience. It can be done, but it isn't hardly worth the effort unless there is no other solution.


Woody Z http://www.learntoprogramnow.com

Woody, your post is without question the most professional and helpful answer to a programming question I have ever received on a forum. Thank you. I am just sorry that it has taken so long to reply. I have, in fact, only just seen your reply, as I had not checked back here for ages. I totally agree with you that this issue is critical and I have been (like you) gobsmacked that I have not been able to find anyone who would talk about this issue and provide the professional approach to it. All your suggestions are brilliant, and the strange thing is that - although they are not rocket science - not one of them has previously occurred to me. So now I have an approach I can use in practice, in order to create a professional application, and the proper name "concurrency control" to describe it. Fortunately, I still have time to implement this in my current project. Best answer ever - thanks!

Hey Jim,

You are too kind. I am very glad if I have provided some help.

You are right that this isn't Rocket Science, but it isn't a simple thing either. And I do want to make it clear that I didn't invent any of the techniques that I use to deal with concurrency issues, I just happen to have gathered a few of them up so that I have some choices when I need to deal with it.

If you come upon other solutions in your searches and in dealing with it, I'd love hear about them.


Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems

You will have to forgive me in my original response as I did not mean to imply (or shrug off) this problem I was speaking from the perspective of my current position. The need to get at the same data at the same time within my agency is extremely low and there is only 1 set of people that have the ability to update information within our database, each with their own caseloads.

Of course there exceptions of power users whom have access to the entire database but, again, in my situation the possiblity of a "Last in Wins" situation is very small.

As Woody so meticulously pointed out, there are various methodologies that can be employed to work around this problem but you have somewhat of a trade off (not necessarily with the examples he provided but in general). For example, you can't use locks (Pesimistic Concurrency) on a row in a disconnected environment and, if locks are employed, an application that holds onto locks to long simply isn't scaleable. =\

Like woody I would also like to know what kinds of solutions you come across as you deal with this problem. ^^


================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========