Concurrency Checks and Scenarios in business apps
This whole blog is based on some notes I collected on concurrency checks in a web application.
While designing a persistent object framework, or designing a web application (say, a Task Assignment and Management app), one of the tricky questions is the handling of concurrency while saving data.
Definition
For the purposes of this essay, a concurrency issue comes up when user A loads data from the app database; user B also loads data from the app database. User A makes some changes and saves back to the database. Now, when user B also makes changes and tries to save, how should the application handle it? User B’s data has become stale (because user A has changed it AFTER user B loaded it).and therefore his save may result in a wrong decision.
In our task management app, let us say User A sees a task with task id 111. This task has a description, status and a person it Is assigned to. It may also have task notes.
The database table looks like this:
task_id task_desc task_status task_assignedto
It has the following values for task id 111:
task_id task_desc task_status task_assignedto
111 Fix error Pending User A
Now user A sees the above record in his screen and decides to change the status to Completed. He changes the status and saves the record.
Meanwhile, at the same time, user B, who is a project manager is also looking at task id 111 in her screen. She thinks the task has been pending for a long time. So she changes the status to “Escalate to Supervisor” and saves the task.
But, her data is stale – user A has already changed the status to Completed. If she now escalates, it would only result in confusion all around.
This is a common scenario in applications (web or desktop) ; what just happened was a concurrency issue or a “save collission”. There are different techniques to handle save collission and we will discuss a few here.
Concurrency Check Approaches
1- Last Save Wins approach
A simple application may just choose to avoid concurrency altogether – the designer may think that two users editing the same record is unlikely. Sometimes, even if two users edit the same record, the designer may choose a “Last Save Wins” approach. In this approach, there are no concurrency checks in the database layer. If user B saves after making changes, those changes overwrite user A’s changes.
2 - Concurrency Check using all original data
ADO.NET and some frameworks have a default concurrency check based on all the columns in a table. In the Save stored procedure, the query will look like this:
The stored procedure with the above query takes the original data that an user (User B) loaded. Then it checks if ALL the columns are identical ie, that no user has changed the data after User B loaded the data. If ANY of the columns have new data, the above query fails to update. The stored procedure gets the number of rows updated and if the number is zero, it raises a concurrency exception.
In a web application, the original data has to be cached in the client side or in session storage in the web server. Every save sends back the original data.
Now, this approach helps in the case of auto-generated queries – you do not have to code different rules for different tables (and objects). There is another, simpler approach for such concurrency checks.
3 - Concurrency check using Timestamp
Every table in the database should have a “LastModified” datetime or timestamp field. When User B loads her data, the LastModified timestamp goes back to her session. Now, when User A modifies the same task, the timestamp is modified for that task record. [This requires that any update to the Task table should always also update the LastModified column].
Finally, when User B tries to modify the same record, the stored procedure runs the following query:
Let us stop at this point and consider the effects of a concurrency error. What should happen in application logic when a concurrency exception is thrown from the database or persistence layer?
Concurrency Usecases
The answer depends on the business user’s requirements. Generally the use case is that the application:
- notifies the user (User B) that her save failed because someone else had modified the data after she loaded it.
- prompts for a refresh of the record(s) so that User B sees the new changes, thus losing her changes
- Or asks User B if they want to save in spite of the concurrency error. At this point, in complicated scenarios, User B may actually be shown the modified record(s) in a new window User B now has enough information to make a decision to overwrite another user’s modifications or cancel the save and lose her changes.
For handling these more refined usecases, in which the user B is shown the modified record, you may have to return the new recordset from the save stored procedure.
Note: - Notice that in the above description, there is a possibility of multiple records being saved and retrieved. Till now we have been considering a single task record, for the sake of simplicity. In an non-trivial application, there would be an object map that is getting saved back. An example is when all tasks assigned to a certain person are being modified for moving a deadline. In a persistence framework, such a transaction would be handled as a parent-child relation and a series of stored procedures may be called. Handling concurrency in such “units of work” is more complicated – we will return to that later in this essay.
An Ideal approach to Concurrency
The above three approaches are standard in most business applications but they are not ideal. The reason frameworks deal with such approaches is because it is easier to generate code for stored procedures when you adopt a single consistent approach for concurrency issues.
In reality, a business application may need specific, tailor-made concurrency checks for each object. Let us consider the Task Management scenario above again.
Let us say User A loads the task 111. User B also loads the same task. Now User A modifies just the description of the task and saves it back. Now, when User B saves her data, should the stored procedure throw a concurrency error? It depends but in general users may not prefer a concurrency error for something as simple as a description change. In this case, User B’s changes may be allowed to overwrite User A’s description change.
Thus the stored procedure should check for specific columns instead of all columns based upon business rules set by a business analyst. This is painful and tedious, which is why you see general approaches such as using the last modified timestamp for any column updates.
Note:- Please keep in mind that an user could also have deleted a record while another is editing that same record. It is ideal that the concurrency check in that case specifically inform User B that their changes were not saved because the underlying record was deleted.
Note 2:- Please also keep in mind that multiple users may have changed the record in between. Thus, the change may be more than a single update.
Thus, an ideal approach to concurrency issues in an application would follow the below checklist:
- collect concurrency rules for important tables and columns in those tables.
- create stored procedures that would allow certain columns to be overwritten while other column changes should throw concurrency errors.
- While throwing a concurrency error, attach a list with the offending fields’ user friendly display names; the new values; the users who made changes in between; the different times when changes were made (an entire change history after load).
- prompt the user to ovewrite individual changes.
- When the user chooses to overwrite, run the save procedure again and overwrite specific columns
It is just impossible to do all of the above – the closest systems that come to handling concurrency checks in such detail are version control systems such as Visual Source Safe or SVN. I have never seen a business app do all of this.
The most common approach I have seen uses the below checklist:
- create a last modified timestamp column
- any update would modify that column
- that column would be loaded along with the table data
- on a save, the stored procedure would check if the timestamp column has changed. If yes, throw a concurrency error.
- The application would handle the concurrency error by informing the user that the save failed and prompt them to reload the data.
Parent Child Object Graphs and Concurrency
If you are saving multiple records that are associated with each other, at the same time, then there are two common scenarios:
1 - Middle Tier Transaction – multiple stored procedures are called in the context of a middle tier transaction. If three records have concurrency errors ( that is they are stale and have been modified since load), and another ten records go through without concurrency errors, then the decision is again upto business.
Business may prefer that a single concurrency error in any one record causes the entire transaction to fail; or they may prefer that the ten successful saves not be rolled back. The ideal scenario again dictates that the user be prompted with a list of records that were not saved. The user chooses to overwrite or not.
2 - Database Transaction – in the case of rich internet applications, sometimes the middle tier simply serves as a proxy and all the batches of data is routed to the database, in the form of Xml. The database stored procedure parses the Xml and creates new records, modifies existing or deletes. All of these happen (generally) in the context of a single database transaction.
I have worked with such systems and handling concurrency is easier. In the case of an error, you assemble a new Xml document with the changed records and send them back to the browser client.
Rich Internet Scenario
Let us consider such a rich internet scenario:
User B sees a tree control of security groups. In the client, User B drags and drops a few branches, modifies some descriptions and deletes a few groups. Then she save the tree structure back.
Meanwhile User A (as usual) has done a few changes himself and already saved the structure back.
What should the concurrency rules be?
When the stored procedure gets User B’s Xml data, it parses the Xml and saves individual records. While saving each record, it checks a bunch of things:
- If this is a deleted record, then mark all child branches as deleted and so on recursively.
- if this is a new record, then check if its parent exists and has not been deleted by another user. If the parent branch has been deleted, mark the new record as having a concurrency exception.
- If this is a existing record that has been modified, then check if its immediate parent exists and has not been deleted. If the parent branch has been deleted, mark the modified record as a concurrency exception.
- If this is a existing record that has been modified, then check if its immediate parent or any of its ancestors have been modified (by using the last modified timestamp). If yes, then mark the modified record as a concurrency exception
- Description changes do not cause the last modified timestamp to be changed.
Following these rules, the stored procedure creates a set of records that were not saved and returns them to User B. The application prompts User B on the next course of action – overwrite (which may not make much sense); or cancel your changes.
The above is just one set of algorithms you can use to implement concurrency checks in a hierarchical structure.
Merge Issues
One big problem in rich internet applications is that the user could be modifying a lot of records. In case of a concurrency error, they may not want to lose the changes they made. One complicated usecase is to do a client side merge of the new data in the server along with User B’s changes. The merge rules may be complicated sometimes but may also be simple.
Consider that a set of security groups is being modified by a system admin. The admin is modifying security groups but is also adding a few new ones. Now, when she saves, a concurrency error occurs. The admin may choose to merge her data - in which case, the tree control in the client will still contain the new records, plus the reloaded data. Setting a merge policy would require much custom code.
Summary
When you start designing an application consider the concurrency checks needed. Design your stored procedures upfront with the checks built in. Design your middle tier, client and stored procedures considering the overwrite and merge rules. The closest model to think about concurrency is in terms of version control systems.
While designing a persistent object framework, or designing a web application (say, a Task Assignment and Management app), one of the tricky questions is the handling of concurrency while saving data.
Definition
For the purposes of this essay, a concurrency issue comes up when user A loads data from the app database; user B also loads data from the app database. User A makes some changes and saves back to the database. Now, when user B also makes changes and tries to save, how should the application handle it? User B’s data has become stale (because user A has changed it AFTER user B loaded it).and therefore his save may result in a wrong decision.
In our task management app, let us say User A sees a task with task id 111. This task has a description, status and a person it Is assigned to. It may also have task notes.
The database table looks like this:
task_id task_desc task_status task_assignedto
It has the following values for task id 111:
task_id task_desc task_status task_assignedto
111 Fix error Pending User A
Now user A sees the above record in his screen and decides to change the status to Completed. He changes the status and saves the record.
Meanwhile, at the same time, user B, who is a project manager is also looking at task id 111 in her screen. She thinks the task has been pending for a long time. So she changes the status to “Escalate to Supervisor” and saves the task.
But, her data is stale – user A has already changed the status to Completed. If she now escalates, it would only result in confusion all around.
This is a common scenario in applications (web or desktop) ; what just happened was a concurrency issue or a “save collission”. There are different techniques to handle save collission and we will discuss a few here.
Concurrency Check Approaches
1- Last Save Wins approach
A simple application may just choose to avoid concurrency altogether – the designer may think that two users editing the same record is unlikely. Sometimes, even if two users edit the same record, the designer may choose a “Last Save Wins” approach. In this approach, there are no concurrency checks in the database layer. If user B saves after making changes, those changes overwrite user A’s changes.
2 - Concurrency Check using all original data
ADO.NET and some frameworks have a default concurrency check based on all the columns in a table. In the Save stored procedure, the query will look like this:
Update Task
Set task_desc=@ desc
task_status = @status
task_assignedto =
@assignedto
Where
task_id=@id
And task_desc =
@original_desc
And task_status =
@original_status
And task_assignedto = @original_assignedto
The stored procedure with the above query takes the original data that an user (User B) loaded. Then it checks if ALL the columns are identical ie, that no user has changed the data after User B loaded the data. If ANY of the columns have new data, the above query fails to update. The stored procedure gets the number of rows updated and if the number is zero, it raises a concurrency exception.
In a web application, the original data has to be cached in the client side or in session storage in the web server. Every save sends back the original data.
Now, this approach helps in the case of auto-generated queries – you do not have to code different rules for different tables (and objects). There is another, simpler approach for such concurrency checks.
3 - Concurrency check using Timestamp
Every table in the database should have a “LastModified” datetime or timestamp field. When User B loads her data, the LastModified timestamp goes back to her session. Now, when User A modifies the same task, the timestamp is modified for that task record. [This requires that any update to the Task table should always also update the LastModified column].
Finally, when User B tries to modify the same record, the stored procedure runs the following query:
Update Task
Set task_desc=@ desc
task_status = @status
task_assignedto =
@assignedto
task_lastmodified = getdate()
Where
task_id=@id
And task_lastmodified = @original_timestamp
Let us stop at this point and consider the effects of a concurrency error. What should happen in application logic when a concurrency exception is thrown from the database or persistence layer?
Concurrency Usecases
The answer depends on the business user’s requirements. Generally the use case is that the application:
- notifies the user (User B) that her save failed because someone else had modified the data after she loaded it.
- prompts for a refresh of the record(s) so that User B sees the new changes, thus losing her changes
- Or asks User B if they want to save in spite of the concurrency error. At this point, in complicated scenarios, User B may actually be shown the modified record(s) in a new window User B now has enough information to make a decision to overwrite another user’s modifications or cancel the save and lose her changes.
For handling these more refined usecases, in which the user B is shown the modified record, you may have to return the new recordset from the save stored procedure.
Note: - Notice that in the above description, there is a possibility of multiple records being saved and retrieved. Till now we have been considering a single task record, for the sake of simplicity. In an non-trivial application, there would be an object map that is getting saved back. An example is when all tasks assigned to a certain person are being modified for moving a deadline. In a persistence framework, such a transaction would be handled as a parent-child relation and a series of stored procedures may be called. Handling concurrency in such “units of work” is more complicated – we will return to that later in this essay.
An Ideal approach to Concurrency
The above three approaches are standard in most business applications but they are not ideal. The reason frameworks deal with such approaches is because it is easier to generate code for stored procedures when you adopt a single consistent approach for concurrency issues.
In reality, a business application may need specific, tailor-made concurrency checks for each object. Let us consider the Task Management scenario above again.
Let us say User A loads the task 111. User B also loads the same task. Now User A modifies just the description of the task and saves it back. Now, when User B saves her data, should the stored procedure throw a concurrency error? It depends but in general users may not prefer a concurrency error for something as simple as a description change. In this case, User B’s changes may be allowed to overwrite User A’s description change.
Thus the stored procedure should check for specific columns instead of all columns based upon business rules set by a business analyst. This is painful and tedious, which is why you see general approaches such as using the last modified timestamp for any column updates.
Note:- Please keep in mind that an user could also have deleted a record while another is editing that same record. It is ideal that the concurrency check in that case specifically inform User B that their changes were not saved because the underlying record was deleted.
Note 2:- Please also keep in mind that multiple users may have changed the record in between. Thus, the change may be more than a single update.
Thus, an ideal approach to concurrency issues in an application would follow the below checklist:
- collect concurrency rules for important tables and columns in those tables.
- create stored procedures that would allow certain columns to be overwritten while other column changes should throw concurrency errors.
- While throwing a concurrency error, attach a list with the offending fields’ user friendly display names; the new values; the users who made changes in between; the different times when changes were made (an entire change history after load).
- prompt the user to ovewrite individual changes.
- When the user chooses to overwrite, run the save procedure again and overwrite specific columns
It is just impossible to do all of the above – the closest systems that come to handling concurrency checks in such detail are version control systems such as Visual Source Safe or SVN. I have never seen a business app do all of this.
The most common approach I have seen uses the below checklist:
- create a last modified timestamp column
- any update would modify that column
- that column would be loaded along with the table data
- on a save, the stored procedure would check if the timestamp column has changed. If yes, throw a concurrency error.
- The application would handle the concurrency error by informing the user that the save failed and prompt them to reload the data.
Parent Child Object Graphs and Concurrency
If you are saving multiple records that are associated with each other, at the same time, then there are two common scenarios:
1 - Middle Tier Transaction – multiple stored procedures are called in the context of a middle tier transaction. If three records have concurrency errors ( that is they are stale and have been modified since load), and another ten records go through without concurrency errors, then the decision is again upto business.
Business may prefer that a single concurrency error in any one record causes the entire transaction to fail; or they may prefer that the ten successful saves not be rolled back. The ideal scenario again dictates that the user be prompted with a list of records that were not saved. The user chooses to overwrite or not.
2 - Database Transaction – in the case of rich internet applications, sometimes the middle tier simply serves as a proxy and all the batches of data is routed to the database, in the form of Xml. The database stored procedure parses the Xml and creates new records, modifies existing or deletes. All of these happen (generally) in the context of a single database transaction.
I have worked with such systems and handling concurrency is easier. In the case of an error, you assemble a new Xml document with the changed records and send them back to the browser client.
Rich Internet Scenario
Let us consider such a rich internet scenario:
User B sees a tree control of security groups. In the client, User B drags and drops a few branches, modifies some descriptions and deletes a few groups. Then she save the tree structure back.
Meanwhile User A (as usual) has done a few changes himself and already saved the structure back.
What should the concurrency rules be?
When the stored procedure gets User B’s Xml data, it parses the Xml and saves individual records. While saving each record, it checks a bunch of things:
- If this is a deleted record, then mark all child branches as deleted and so on recursively.
- if this is a new record, then check if its parent exists and has not been deleted by another user. If the parent branch has been deleted, mark the new record as having a concurrency exception.
- If this is a existing record that has been modified, then check if its immediate parent exists and has not been deleted. If the parent branch has been deleted, mark the modified record as a concurrency exception.
- If this is a existing record that has been modified, then check if its immediate parent or any of its ancestors have been modified (by using the last modified timestamp). If yes, then mark the modified record as a concurrency exception
- Description changes do not cause the last modified timestamp to be changed.
Following these rules, the stored procedure creates a set of records that were not saved and returns them to User B. The application prompts User B on the next course of action – overwrite (which may not make much sense); or cancel your changes.
The above is just one set of algorithms you can use to implement concurrency checks in a hierarchical structure.
Merge Issues
One big problem in rich internet applications is that the user could be modifying a lot of records. In case of a concurrency error, they may not want to lose the changes they made. One complicated usecase is to do a client side merge of the new data in the server along with User B’s changes. The merge rules may be complicated sometimes but may also be simple.
Consider that a set of security groups is being modified by a system admin. The admin is modifying security groups but is also adding a few new ones. Now, when she saves, a concurrency error occurs. The admin may choose to merge her data - in which case, the tree control in the client will still contain the new records, plus the reloaded data. Setting a merge policy would require much custom code.
Summary
When you start designing an application consider the concurrency checks needed. Design your stored procedures upfront with the checks built in. Design your middle tier, client and stored procedures considering the overwrite and merge rules. The closest model to think about concurrency is in terms of version control systems.
Labels: Concurrency Checks, Save Collission
posted by Ramiah Ariya at
12:51 AM
|
0 comments
