An introduction to ADO.NET concluded - Concurrency and the disconnected data architecture
(Page 4 of 4 )
Although the disconnected data architecture has advantages, it also has some disadvantages. One of those is the conflict that can occur when two or more users retrieve and then try to update data in the same row of a table. This is called a
concurrency problem. This is possible because once a program retrieves data from a database, the connection to that database is dropped. As a result, the database management system can’t manage the update process.
To illustrate, consider the situation shown in figure 2-8. Here, two users have retrieved the Vendors table from a database, so a copy of the Vendors table is stored on each user’s PC. These users could be using the same program or two different programs. Now, suppose that user 1 modifies the address in the row for vendor 123 and updates the Vendors table in the database. And suppose that user 2 modifies the phone number in the row for vendor 123 and then tries to update the Vendors table in the database. What will happen? That will depend on the concurrency control that’s used by the programs.
When you use ADO.NET, you have two choices for concurrency control. By default, a program uses optimistic concurrency, which checks whether a row has been changed since it was retrieved. If it has, the update or deletion will be refused and a concurrency exception will be thrown. Then, the program should handle the error. For example, it could display an error message that tells the user that the row could not be updated and then retrieve the updated row so the user can make the change again.
In contrast, the "last in wins" technique works the way its name implies. Since no checking is done with this technique, the row that's updated by the last user overwrites any changes made to the row by a previous user. For the example above, the row updated by user 2 will overwrite changes made by user 1, which means that the phone number will be right but the address will be wrong. Since errors like this corrupt data in a database, optimistic concurrency is used by most programs, which means that your programs have to handle the concurrency exceptions that are thrown.
If you know that concurrency will be a problem, you can use a couple of programming techniques to limit concurrency exceptions. If a program uses a dataset, one technique is to update the database frequently so other programs can retrieve the current data. The program should also refresh its dataset frequently so it contains the recent changes made by other programs.
Another way to avoid concurrency exceptions is to retrieve and work with just one row at a time. That way, it’s less likely that two programs will update the same row at the same time. In contrast, if two programs retrieve the same table, they will of course retrieve the same rows. Then, if they both update the same row in the table, even though it may not be at the same time, a concurrency exception will occur when they try to update the database.
Of course, you will understand and appreciate this more as you learn how to develop your own database applications. As you develop them, though, keep in mind that most applications are multi-user applications. That’s why you have to be aware of concurrency problems.
Two users who are working with copies of the same data
Figure 2-8. Concurrency and the disconnected data
architecture
What happens when two users try to update the same row
- When two or more users retrieve the data in the same row of a database table at the same time, it is called concurrency. Because ADO.NET uses a disconnected data architecture, the database management system can’t prevent this from happening.
- If two users try to update the same row in a database table at the same time, the second user’s changes could overwrite the changes made by the first user. Whether or not that happens, though, depends on the concurrency control that the programs use.
- By default, ADO.NET uses optimistic concurrency. This means that the program checks to see whether the database row that’s going to be updated or deleted has been changed since it was retrieved. If it has, a concurrency exception occurs and the update or deletion is refused. Then, the program should handle the exception.
If optimistic concurrency isn’t in effect, the program doesn’t check to see whether a row has been changed before an update or deletion takes place. Instead, the operation proceeds without throwing an exception. This is referred to as “last in wins” because the last update overwrites any previous update. And this leads to errors in the database.
How to avoid concurrency errors
- For many applications, concurrency errors rarely occur. As a result, optimistic concurrency is adequate because the users will rarely have to resubmit an update or deletion that is refused.
- If concurrency is likely to be a problem, a program that uses a dataset can be designed so it updates the database and refreshes the dataset frequently. That way, concurrency errors are less likely to occur.
- Another way to avoid concurrency errors is to design a program so it retrieves and updates just one row at a time. That way, there’s less chance that two users will retrieve and update the same row at the same time.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter two of the book VB.NET Database: Database Programming with ADO.NET, written by Anne Prince and Doug Lowe (Murach Publishing; ISBN: 1890774197). Check it out today at your favorite bookstore. Buy this book now.
|
|
