How To: Handle Database Concurrency Issues

Concurrency issues are common in multi-user applications that have a database on the backend. Suppose you read a table record and then another user comes along and makes changes to the same record. Unless you re-read (e.g. refresh) your record, the data becomes stale. If you then attempt to make changes to the stale record, the application must detect and handle any data conflicts that may have occurred. For example, the other user may have modified the exact same field or may have even deleted the entire record. Clearly, the task of detecting and handling concurrency issues is not only important, but also quite complicated.

The good news is that LightSwitch makes concurrency very easy to handle. In fact, you might say that LightSwitch turns concurrency issues into concurrency non-issues. Specifically, LightSwitch provides a couple of different mechanisms for handling concurrency:

• When a conflict is detected, a “Data Conflict” screen is automatically displayed to the user allowing them to choose how to resolve the conflict
• Or, for a more customized solution, code can be written so that the application resolves the conflict without requiring interaction from the user

To see how to use these mechanisms, let’s continue with the Expense Report Tracker application that I discussed in my previous blog posts.

Display “Data Conflict” Screen

First off, let’s look at what we need to do in order to have LightSwitch display the “Data Conflict” screen when a concurrency error occurs in the Expense Report Tracker application. The answer to this is…nothing! LightSwitch automatically does this for you without having to write any additional code.

Since Expense Report Tracker is a multi-user application, it’s possible that two different managers might be reviewing the same expense report. If one of the managers decides to reject the expense report while the other decides to approve it, LightSwitch will detect the conflict. As a result, LightSwitch will display the following screen to the manager who was slowest to save their change:

Or, if instead the first manager deletes an expense report while the second manager attempts to approve it, the second manager will see the following screen when they save:

Write Code to Automatically Handle Conflict Resolution

In some cases you may want your application to automatically handle conflict resolution so that the user never sees the “Data Conflict” screen. Again, LightSwitch makes this very simple by exposing several programming objects that can be used to resolve the conflict.

When a data conflict is detected by LightSwitch’s server, it will throw a ConcurrencyException.

You can then write code in the client that catches this exception and uses it to get the collection of entities that have data conflicts. From there, you have access to all of the entity property values and have the ability to indicate whether a change should be saved or discarded.

With this in mind, suppose in the Expense Report Tracker that when two managers update an expense report to have the same status, that we want the code to automatically resolve the conflict. For example, if both managers set the status for the same expense report to “Approved”, then we don’t want the application to display the “Data Conflict” screen. Instead, we want our application to handle this automatically for the user.

To do this, we will need to write code in the screen’s “Saving” method. This method allows us to override the default behavior that occurs when the user clicks the Save button on the screen. You can add code to this method by opening the screen’s designer and choosing the method from the “Write Code” menu:

Next, let’s add the following code to the “Saving” method. Notice that our code first attempts to save the changes. If no data conflicts are detected, the changes will be saved as normal.

However, if a data conflict is detected, the server will throw a ConcurrencyException. Our code catches this exception and uses it to determine whether a conflict occurred with the Status property. In the case that two different users set the Status property to the exact same value, our code will automatically resolve the conflict by choosing to save the client’s value.

Otherwise, if some other type of data conflict occurred, the user will still be prompted to resolve the conflict by displaying the “Data Conflict” screen.

C#:

   partial void ExpenseReportsListDetail_Saving(ref bool handled)

  {

       //Indicates that our code will call SaveChanges()

       handled = true;

       try

       {

            this.DataWorkspace.ApplicationData.SaveChanges();

       }

       catch (ConcurrencyException e)

       {

            foreach (IEntityObject entityConflict in e.EntitiesWithConflicts)

              {

                    EntityPropertyConflict statusProperty =

                       entityConflict.Details.EntityConflict.ConflictingProperties.Where(

                       property => property.Property.Name == "Status").Single();

                    if (statusProperty.Property.Value.ToString() == statusProperty.ServerValue.ToString())

                    {

                        statusProperty.EntityConflict.ResolveConflicts(ConflictResolution.ClientWins);

                        //Resave once we’ve resolved the conflicts

                        this.DataWorkspace.ApplicationData.SaveChanges();

                    }

                }

          }

    }

 VB: 

   Private Sub ExpenseReportDetail_Saving(ByRef handled As Boolean)

       handled = True

       Try

            Me.DataWorkspace.ApplicationData.SaveChanges()

       Catch e As ConcurrencyException

            For Each entityConflict As IEntityObject In e.EntitiesWithConflicts

                Dim statusProperty As EntityPropertyConflict =

                    (From prop In   entityConflict.Details.EntityConflict.ConflictingProperties

                      Where prop.Property.Name = "Status"

                        Select prop).Single()

                If (statusProperty.Property.Value.ToString() = statusProperty.ServerValue.ToString()) Then

                    statusProperty.EntityConflict.ResolveConflicts(

                    ConflictResolution.ClientWins)

                    Me.DataWorkspace.ApplicationData.SaveChanges()

                End If

            Next

        End Try

   End Sub
 

There are a few things worth highlighting in the above code:

• Notice that this method accepts a “handled” parameter. When this parameter is set to true, this indicates that the code is responsible for calling SaveChanges() to save any changes that the user has made. Otherwise, if this parameter is false, LightSwitch automatically calls SaveChanges() for us. Since we want to handle saving on our own, the “handled” property is set to true. Also, notice that when a concurrency exception does occur, we must call SaveChanges() again to save the changes after we’ve resolved the concurrency conflicts.
• You may have wondered why we didn’t try to catch and resolve the concurrency issue on the server, such as in the SaveChanges_ExecuteFailed method. The issue with this is that it is not valid to re-initiate a new Save from within the save pipeline once a failure has occurred. Instead, you must fix the concurrency issues and re-initiate the save from the client.

Summary

LightSwitch makes concurrency extremely easy to implement. In most cases, you don’t even need to worry about concurrency issues when creating your application because LightSwitch will handle it all for you. However, if there are situations where you want to write code to automatically resolve the data conflicts for the user; LightSwitch provides an easy to use programming model that is accessible through the ConcurrencyException that is thrown from the server.