Recommended course of action in handling concurrency conflicts

[dee-u]

For instance, if you determined that a pending Edit operation would cause concurrency problem, that is another user has already modified the record you are trying to edit, what is the recommend course of action in such cases?

[d3gerald]

well in this case, the changes made by the last user who edited the record will be stored in the database. supposing there are two of you who are editing the same record. then he saved it with just a few milliseconds before you saved your work, the changes you made will be stored in the database overiding the changes he made. as long as he did not edit the identifier or the primary key of the record which is the basis for your update, everything will be fine.

but if he changes the identifier of the record, the changes you made will be useless and will not be save to the database

[d3gerald]

thats just according to my experience in interacting with databases

[RobDog888]

The unique identifieer of a record usually is never changed or you will break any foreign key references and orphan records.

Depending on the db and how your either connected to it or in it, you may get an OLE Multi-Step Error which indicates that the recordset was modified outside of your current operation.

[szlamany]

For instance, if you determined that a pending Edit operation would cause concurrency problem, that is another user has already modified the record you are trying to edit, what is the recommend course of action in such cases?

That question is a bit vague.

It's a big problem if the user was presented with the "contents" of the record on the screen - with values that they thought were on file - and made change to those values and were about to save them to the database.

If another user has already changed that row then the "big problem user" is working under a misconception.

Whether this is really a "big problem" or not is up for debate. Really has to do with the type of data and expectations of the user.

When we post SCHOOL-WIDE DAILY ATTENANCE in our system we prompt for the STUDENT ID/NAME. At that moment we "re-query" the DB for all prior entries for that student and display them in a grid. The user is expected to walk down a column in the grid either accepting or rejecting that attendance and then they arrive back at the row in the grid that they initially entered.

It is possible for another user to post attendance for this student at the exact same moment - but the expectation in my opinion - if that really happened - would be for an inaccurate entry to possibly be loaded. The reality is that two people are trying to post about the same student at the same time - that's not a DB or UI issue - that's a office procedural issue.

When our teachers get into the CLASS-ROOM ATTENDANCE module and call up a class of students we display a column of what the "SCHOOL-WIDE DAILY ATTENDANCE" is for every kid. If the teacher posts an absence from the class for a student we immediately re-query the DB to see if the "SCHOOL-WIDE ATTENDANCE" in the GRID for that student has changed - if so we flash a message and correct the grid value - the teacher gets the most up-to-date information regarding the student so that a proper closure for the CLASS-ROOM ATTENDANCE is possible. In that case the "accurate display of current data" about a student was important - so we dealt with it in the design of the application.

[dee-u]

I happen to have this experience, one user of our program asked me to edit a record for her (since only system admins like me is allowed to do so), she has the record loaded in her UI, I complied with her request and edited the record, of course I am sure I was able to make the changes, but the problem is the old record is still the one being shown to her UI and then comes the real trouble, she press edit then modified some fields then saved it, voila, the changes I made was overridden, I just want to handle such cases such that cases, do you happen to have a recommendation on how to handle such scenarios?

Thanks!

[szlamany]

The fault seems to be with her UI - as it did not check that the record was in it's original state prior to update.

That can be done many different ways - first one that comes to mind is to update on WHERE col1=origcol1 and col2=origcol2...and so on...

[d3gerald]

i think there is a problem with the refreshing of records in her UI. maybe after you edited her records, her UI is not refreshed or did not get the latest recordset from your db.

as what i have observed with my previous applications, the recordset is just a temporary storage of data that comes from your database and is being stored in your application. if you dont refresh it or requery it, it wont give you the exact updated result commiing from your database. so i think there should be a requery event in her UI or i suggest it should be in both ways. and to prevent overusage of bandwidth, it is advisable, for my own opinion, to do the refreshing of recordset at 1 minute interval or just wait for the user to click a refresh button.

[dee-u]

The fault seems to be with her UI - as it did not check that the record was in it's original state prior to update.

That can be done many different ways - first one that comes to mind is to update on WHERE col1=origcol1 and col2=origcol2...and so on...

That's what I am after, after determining that the record is not the latest one, what should be the appropriate logic that should be done, tell the user that the record is outdated already then show her the latest version of the record?

[szlamany]

That's what I am after, after determining that the record is not the latest one, what should be the appropriate logic that should be done, tell the user that the record is outdated already then show her the latest version of the record?

That was my point in the prior post - it's all up to you. If it's important for this user to know that the row changed during the time she had it on screen for edit, then you have to tell her...

"Data has been changed by another user - do you want to see those changes or apply your change?" (I hate the wording of this question, btw - it's just an example!).

If it's not important for the user to know that the change was made then simply have her update go into place. I think that the consideration for this option would be: What if she came along 20 minutes later and wanted to make the same change to the record that she made during the "concurrency conflict moment? Would she have made the change anyway?.

Or are you concerned that she is effectively resetting the change made during the concurrency conflict moment? If that "damage" to a prior update is what is concerning you then her update cannot take place. If it's allowed to take place then it's breaking the ACID rule - basically an "open transaction" is started by user 1 that is "broken" by an update from user 2 - and update with data read during the "open transaction" of user 1. In a tightly held scenario two users cannot start a transaction against the same data. Although I dislike holding transactions open on a DB from a UI...

[FunkyDexter]

SZlamany's basically describing an optimistic aproach to concurrency management - you assume that there'll be no conflicts then, if any occur, you sort them out. That's great if you're going to see conflicts rarely - on a low user system for example.

If you've got a high user system you might want to consider a more pessimistic aproach where you assume that concurrency conflict's will occur and you prevent them from happening in the first place. Place a row lock on the record when the user 1 selects edit mode (you can use SQLServers transaction functionality to do this but most places I've worked at have maintained their own 'locks' table to avoid problems with lock escalation etc). You can then let user 2 view the record but prevent them from moving it into update mode issueing whatever error message you see fit. Once user 1 has finished you can let user 2 enter update mode but they must refresh the record first. This is similar to the behaviour you see from word or excel if a second user tries to open a document that another user already has open.