Any time you have two users editing the same record, there is the potential for a collision. At least one of them has to be the victim, and I'm a firm believer that "first one wins" is the correct way to go.
Locking will accomplish this, but I think this introduces complexity and may impact overally scalability.
What we did was come up with a concurrency model that reduces the chances of a collision. There are several parts to this.
(1) Only changed fields are tested. If two users change different fields of the same record, then both will go through. Yes, you can contrive a situation where this doesn't work well, but in practice we've found it works very well.
(2) Where numeric values are involved, we have an optional capability to update these fields with deltas rather than absolute values (e.g. add/subtract from the field rather than assign a specific value).
(3) In some cases "last one wins" is OK with regards to specific fields. For example, if you are auditing and recording the last user and update date/time for a record, then you shouldn't use these fields to enforce concurrency, and it's quite OK if the second of two updates to these specific fields "wins" -- it in fact reflects what you want recorded.
meierk:
Unfortunately, for my users this issue has only one outcome. After a brief meeting with them, the demand was that I find a way to inform the users that another user *may potentially be* editing the same data, as soon as they launch the edit form. Additionally, they would prefer that I show them which user may be editing the data. They are NOT demanding that I lock all users out after the first user opens the form, but they do what to know that there is a risk of losing their edits.
Personally, I don't think this is an unreasonable request,
I think it is unreasonable. Please explain why someone would want to edit a record knowing that their changes would not be saved? Do they even know what they are asking for? Or did you omit some information in your post that makes this request reasonable?
The database "flag" idea is called Application Locking. You may have a small app now and you could make the "mistake" of adding the flag to the table where your data resides. That may work today but when your app grows you could wind up regretting it.
For example say you only have Orders to deal with today. In the future you may have to deal with Requests, Invoices, etc. The Admin locking feature needs to be able to see all of them. So I use a separate table to store the locks. Each row has a GUID, a RecordType (PO, Invoice, Req), and a RecordKey (the PK of the document we are referring to). I also have LockType which allows me to distinguish the various locks for the Admin. e.g. the PO is locked because it is being edited in Purchasing, or Receiving Or Invoicing. I also trap the userID.
With all of this information available, if a user tries to edit a PO and can't acquire the lock, then they get a message saying "John Smith is editing the PO in Receiving as of 3/13/08 2:15 PM." - or something like that.
I use a CSLA BO to manage the lock for a PO (or invoice or ...).
It adds some complexity but the design is scalable.
The Admin screen is now easy to display since all the locks are in a single table. It makes deleting stale locks simple. Also, in my web app we have code to detect to since if a user abandons a object with a lock and we try to release it. This succeeds 90% of the time or more. This reduces the burden on an Admin to release stale locks.
Joe
I think it is unreasonable. Please explain why someone would want to edit a record knowing that their changes would not be saved?
It is not my place to tell the user what is reasonable or not. I merely provide them with my professional opinion and recommendation and then implement what they are willing to pay for. I stand by my comment about their request being reasonable. If the application *must* allow multiple users to open the edit form for the same data, the users should know the risks when they open the form. It is not fool proof because user 1 may have numerous edits leaving time for user 2 to enter the form, make a quick change and then exit leaving user 1 in a position of failure. I also suggested that instead of just failing the save, that I re-acquire the data and allow the user to continue making changes and save. That is still being considered, but was initially rejected by the users stating that it was too complex. Remember when your parents got their first computer and trying to explain to them what a click was or asking if the application was minimized?
What happens in many cases is that a user opens the edit form to view the data without any intent on saving it. That user however, may get up to get a coffee, end up in the washroom, or even wonder down to the plant. When finally return to their desk 2-3 hours later, they decide to actually make a change. The client simply does not want to stop users from making necessary changes to the data because the first users ethics (or culture depending on your view) is questionable. They have unanimously agreed that in a case like this, if the first user returns to their desk and decides to change the data, that it is acceptable for those edits to be lost.
The database "flag" idea is called Application Locking. You may have a small app now and you could make the "mistake" of adding the flag to the table where your data resides. That may work today but when your app grows you could wind up regretting it.
If this was a concern I would agree, however, this is a re-write of an application that has been in production for over 8 years. In that 8 years, I have not had to touch the source. In theory I agree with you, but I do not expect this application to ever grow in size.
Application locking was my first consideration, but before I implemented anything I wanted to ask the community if there was alternatives that I haven't thought of. It appears from the responses that I was probably on the same track that others would have ventured.meierk:
It is not my place to tell the user what is reasonable or not. I merely provide them with my professional opinion and recommendation and then implement what they are willing to pay for. I stand by my comment about their request being reasonable.
What happens in many cases is that a user opens the edit form to view the data without any intent on saving it. That user however, may get up to get a coffee, end up in the washroom, or even wonder down to the plant. When finally return to their desk 2-3 hours later, they decide to actually make a change. The client simply does not want to stop users from making necessary changes to the data because the first users ethics (or culture depending on your view) is questionable. They have unanimously agreed that in a case like this, if the first user returns to their desk and decides to change the data, that it is acceptable for those edits to be lost.
Ahh. This is a bit different. Would they accept needing to click a button to "unlock" the edit form. That is, by default the form opens, but everything is readonly. They may click a button that allows editing. At that point you can get an exclusive lock, or a non-exclusive lock (which is your warning message).
If this was a concern I would agree, however, this is a re-write of an application that has been in production for over 8 years. In that 8 years, I have not had to touch the source. In theory I agree with you, but I do not expect this application to ever grow in size.
Well, it sounds to me you're adding a feature that wasn't there before, correct? I think an independant locking is the best way to go; you can add support for similar locking on other forms in the future, and, moer imporantly, your locking logic is seperated more from your business logic. That will be a pretty significant benefit.
Application locking was my first consideration, but before I implemented anything I wanted to ask the community if there was alternatives that I haven't thought of. It appears from the responses that I was probably on the same track that others would have ventured.
Ya, I think there's pretty much only one way to handle this. Recording a lock in the database. Whether or not the lock prevents other users from editing or not is another matter, but trying to get clients talking to each other directly is going to be a HUGE PITA.
Ahh. This is a bit different. Would they accept needing to click a button to "unlock" the edit form. That is, by default the form opens, but everything is readonly. They may click a button that allows editing. At that point you can get an exclusive lock, or a non-exclusive lock (which is your warning message).
That is an interesting idea. I'll pass that option on to my client.
Thanks,
Kevin
We have had the same issues with other apps we have built. It is not an easy one to overcome. There is no perfect solution without implamenting a ton of architecture, including client to client notifaction of record lock releases.
One word of warning: If you use this scheme, don't for get that the user that first started editing the data thinks they have exclusive access to the data since they were not warned that the record was locked. You will want to be sure they fully underssand the posible impact.
Like I said, there is no perfect solution.
including client to client notifaction of record lock releases.
Have you considered having the forms time out? Kind of like Enterprise Manager does with queries. The form can either close, be made read only, or re-load with fresh data.
In your 'washroom' use case above, what do your users want to happen to the first user? Are they not allowed to make changes if changes have been made since they opened the form? Will their changes still overwrite, but now the subsequent users were 'warned' about that possibility and will have to check back later to see if their changes were overwritten?
Have you considered having the forms time out? Kind of like Enterprise Manager does with queries. The form can either close, be made read only, or re-load with fresh data.
Have you considered having the forms time out? Kind of like Enterprise Manager does with queries. The form can either close, be made read only, or re-load with fresh data.
I offered that solution already, but it was rejected as there as times the users will be entering data and will get a sales call that preoccupies them for extended periods of time.
But will the system still overwrite intervening changes when the user returns? I would be most worried about maintaining data integrity and avoiding data loss.
I also see a lot of extra work/wasted time with users trying to track down other users who (inadvertantly) leave forms open.
I see leaving forms open as the action/behavior to be avoided and subsequently 'punished' (by having their changes discarded if the underlying data changed). At least they are the only user who has to recreate their changes and they are informed of that fact when they try to save it, rather than (possibly) multiple users losing their changes (even if they were warned beforhand) and also not having a practical way to know if they lost their changes.
In the application I work with we have had to address this same issue and honestly any concurrent business app will probably need to deal with it in one way or another.
First the ideal which in our next version we may try to attempt: The source control model, whereby a business entity treated just like source code, that is when opened for editing it is "checked out" and "checked in" when saved, if someone else checks in between another users check in that last check in goes through a "merge" presenting the differences and letting the user resolve anything that could not be automatically merged. This of course is not trivial and what difference/merging means would most likely be different per entity and need to be implemented differently for each with perhaps some similar rules for properties and child objects etc. Honestly this goes with version control for the business entity which our users want as well to see a history of changes and be able to rollback, again basically the same functions we as developers demand for our prized source code business users demand of their prized business data, not unreasonable but not easy. This model also solves many issues of offline editing.
Now for the way we have implemented currently, which is the "exclusive locking". First our app has a concept of "Session" in the database, this is table that maintains a user session just like and instead of an ASP session. We use our own session management so that it may be used directly in our database with foreign keys etc. This is key to doing application level locks reliably and makes it trivial to monitor user activity. You may question performance of maintaining a session table in your apps database, but so far with SQL 2005 we have successfully tested with nearly 2500 concurrent users under load. Each entity that needs locks gets a SessionID (GUID) foreign key to the Session table, if another session attempts to open the entity for editing they are denied. In our experience users DO NOT really want to be allowed to edit and overwrite while another user is making changes unless it would be handled properly with merging, this causes way to many issues especially as the system grows in size, they would rather be denied update with the ability to go read only until the lock is freed. If a session is orphaned from the system due to say a network disconnection our session timeout mechanism clears the locks, the timeout is adjustable and is usually 15 to 60 minutes just like an ASP session with the app doing a "heartbeat" to keep the session alive that is slightly less than the timeout. The administration area of our app has a place to manually clear locks should the need arise and sessions themselves can be cleared forcefully kicking a user( and consequently clearing any locks). Note with a reliable session mechanism integrated other things start to become more trivial such as security checks, since the SessionID is basically a secure token representing an authenticated user.
Just my input based on a system that has been working for years and proven to scale up to a few thousand concurrent users.
Copyright (c) Marimer LLC