Implement Optimistic Concurrency
Choose the appropriate level of protection to prevent update anomalies.
by Bob Beauchemin
August 2003 Issue
Technology Toolbox: VB.NET, SQL Server 2000
A database must serve up to tens of thousands users simultaneously while presenting a façade to each user that he or she is the only one accessing the database. SQL Server, DB2, and Sybase maintain this façade by locking data when one user updates it, making all other users wait until the original user is finished before they can "see" the data. Oracle maintains the single-user façade by versioning the data—that is, allowing the user to see a consistent view of it as of a specific point in time. Whether your database uses locking or versioning, serving multiple users as though each one owns the database can raise concurrency issues—most often when more than one user tries to read and update the same row at approximately the same time. I'll explain the options you have for using the DataSet class in VB.NET to avoid these problems in your applications.
You must decide what your app will do when a user checks a row out of the database to view and possibly update it. The row might change between the time the user checks it out and the time he or she completes the update. If you simply overwrite the row in the database when the user clicks on the Update button, updates that other users made in the meantime will be lost. Take the simple example of multiuser access to a variable (see Figure 1). User A retrieves the value of the variable X and sees that it's 0. In the meantime, User B changes the value to 3. When User A adds 1 to the variable's supposed value of 0 and writes it back to the variable, the variable's value is 1. However, if User B's changes were taken into consideration, the value would be 4. User B has lost his update. This is known as "last writer wins." Imagine how simple it would be for your bank account to get out of balance if it worked like this.
You can use concurrency checking to protect against lost updates and other update anomalies. The two main types of concurrency checking are optimistic concurrency and pessimistic concurrency. Pessimistic concurrency ties locks in the database to user input: You lock the rows when you fetch them. You don't implement optimistic concurrency by locking rows. Instead, you fetch rows initially without taking locks, then check during the update to see if the rows are still unchanged. You're optimistic that no one else will update a row while your user views it. You can implement various degrees of optimism in the way you construct the UPDATE statement.
The DataSet class is one of .NET's vehicles for fetching rows for presentation and updating in user interfaces. The DataSet can hold one or more sets of rows. You can use a class called a DataAdapter to populate the DataSet with data from the database. DataAdapter.Fill fills a single DataTable in the DataSet with rows from a SQL statement. The user can peruse the rows, update some or all of the data, and push the updates back to the database. You use the DataAdapter.Update method call to let the user update the database. The connection to the database is usually open only for the short time that Fill and Update execute; the rest of the time, the data in the DataSet is disjoint from the data in the database. Concurrency problems can occur when you update through the DataSet, but you can address them by using optimistic or pessimistic concurrency.
Back to top
|