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.

  1. 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.

  2. 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.

  3. 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.

  4. 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:

image from book

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)) 
image from book

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.

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.


Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics
Linux Application Development (2nd Edition)
ISBN: 735621411
EAN: 2147483647
Year: 2004
Pages: 85
Authors: Michael K. Johnson, Erik W. Troan

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net