Understanding Concurrency Issues
Concurrency is the ability to have multiple users accessing the database and seeing a consistent view of the data. You ensure a consistent view by locking rows, tables, and/or the database as necessary to keep users from accessing data that might be inconsistent. For example, let's say Mary is in the middle of a transaction that will transfer funds by debiting checking account A and crediting checking account B. At the same time, Joe might want to withdraw money from checking account A. What should Joe see as the current balance for account A if Mary is in the middle of her transaction? What should happen to Joe's transaction if Mary's transaction cannot be completed?
The database server addresses such situations by implementing database locking while a transaction is executing. If Mary is in the middle of a transaction that affects two checking accounts, both accounts should be locked until the transaction is complete. This means Joe's transaction should wait until Mary's transaction has been completed. The goal is to keep transactions as short as possible and to keep the lock wait time to a minimum.
Database locking allows concurrent access to the database in a connected environment where users are looking at and modifying live data, but what happens if you want to copy data to the client application, work on the data for a period of time, and then send all of the changes back to the database? If you start a transaction that lasts until you save your changes back to the database, you might cause severe locking issues for other users who need to access the database. On the other hand, if you don't start a transaction, the data won't be locked and another user might modify the data before you have a chance to save your changes. This latter approach is the least obtrusive and more desirable approach, but you have to deal with conflicts that arise from multiple updates taking place without locking.
To deal with concurrency conflicts, the wizards in Microsoft Visual Studio implement concurrency checking by default when you create DbDataAdapter objects. To understand the default operation of the DbDataAdapter, consider the following scenario.
The database contains a table named TblBookList that contains columns for the ISBN number (primary key), BookName, and Quantity. Joe and Mary have retrieved a complete list of all rows from the table and are making changes while offline. The following sequence of events takes place.
-
Joe and Mary read data into a DataTable. The data is in the CurrentVersion and Original-Version of the DataRow. At this point, Joe and Mary have started with the same data.
-
Joe changes the BookName of the book whose ISBN is 123 to "Test Book 123". Mary changes the Quantity of the same book to 999.
-
Joe updates his changes to the database, which performs an update only if the original version of the DataRow matches the data that is currently in the database (column for column). There is a match, so the update succeeds.
-
Mary updates her changes to the database, which again performs an update only if the original version of the DataRow matches the data that is currently in the database (column for column). There is no match because the BookName that is currently in the database is "Test Book 123", which Joe saved, so the update fails with a Concurrency-Exception.
Should a concurrency error be thrown in this case? After all, Joe changed one column and Mary changed a different column. This is the default behavior of the DbDataAdapter object's update command, which you can modify to suit your needs. If you examine the SQL update command, it looks like the following:
SQL Update Command
UPDATE [dbo].[TblBookList] SET [ISBN] = @ISBN, [BookName] = @BookName, [Quantity] = @Quantity WHERE (([ISBN] = @Original_ISBN) AND ([BookName] = @Original_BookName) AND ([Quantity] = @Original_Quantity))
The SQL update command's WHERE clause dictates that the update takes place only if all of the current database column values are equal to the original column values. This is probably the safest and easiest generic approach to identifying concurrency conflicts. Also, notice that all of the columns will be set, regardless of the actual columns that have changed.
Resolving Concurrency Conflicts
When a concurrency conflict occurs, how should it be resolved? Should Joe's change override Mary's change? How you address concurrency conflicts is a business decision. Here are the main choices.
-
Prioritized on time; first update wins Otherwise known as "first in wins." In this scenario, because Mary updated last, her changes are not persisted and Joe's changes are maintained. This approach is easy to implement because it is the default behavior of the DataAdapter Wizard.
-
Prioritized on time; last update wins Otherwise known as "last in wins." In this scenario, because Mary updated last, her changes are persisted, which means Joe's changes are lost. This approach is easy to implement because it involves removing all of the extra conditions from the WHERE clause. In other words, the WHERE clause specifies only the primary key of the row to be updated.
-
Prioritized on role Salespeople win over order entry people. If Joe is a salesperson and Mary is an order entry person, Joe has priority because it is assumed that salespeople are more knowledgeable about their customers. Thus, Joe's changes are kept and Mary's changes are rejected. This approach is a bit more difficult to implement because your application must know the role of each user. Also, if Joe and Mary have the same role, you still need to provide a fallback mechanism, such as prioritized on time.
-
Prioritized on location Headquarters wins over branch offices. If Joe is in a branch office and Mary is at headquarters, Mary's changes are persisted and Joe's changes are overwritten. This approach is also a bit more difficult to implement because your application must know each user's location. Don't forget, if Joe and Mary are in the same location, you still need to provide a fallback mechanism, such as prioritized on time.
-
User resolves the conflict When a conflict occurs, the user is presented with a conflict resolution screen with choices for how to resolve the conflict. Joe saves first, and at the time he saves there is no conflict. When Mary saves, a conflict is identified and she is presented with a conflict resolution screen that shows the original data that was retrieved from the database, the current data that is in the database (Joe's changes), and the current data that is in the application (Mary's changes). Mary can decide which data should be persisted.
How you should resolve concurrency conflicts depends entirely on the goals of your application and might also depend on the data you are working with. For example, with customer data the priority might be based on role, but with accounts receivable data the priority might be based on location. The automatic prioritization methods are rather straightforward; but allowing the user to resolve the conflict can be challenging to implement. I will show an implementation of this approach later in the chapter.
