.NET Concurrency Model (Default)

Concurrency is critical in any multiuser environment where data is to be updated. Concurrency, as you can see in Figure 46.4, is best described as "multiple users vying to update data without affecting each other as they update it." In this illustration, each client application (Windows Client A, Web Client B, and Web Client C) has read the same customer data values at approximately the same time. They all see the same customer data, and some will choose to update this data and will expect their updates to be successful. The type of concurrency model you utilize will directly determine how these data resources are treated (whether they are held, locked, or enqueued), what type of performance to expect, and how scalable your application will be.

Figure 46.4. A typical multiuser data access in a .NET architecture. Here, three clients are accessing customer data stored in Microsoft SQL Server 2000.

In classic client/server architectures, most programming languages and database servers support multiple types of concurrency models ”optimistic, pessimistic, and everything in between (different isolation levels). In the multitiered .NET architecture, the focus is on utilizing a "disconnected" mode of data retrieval to minimize data concurrency issues and to increase scalability. This correlates well with using the optimistic concurrency approach and is the default approach used in the .NET Framework.

Optimistic concurrency enables multiple users to read the same data row at the same time without having to lock it. Any one of the users can change (update) the data values. An optimistic concurrency violation will occur when a user tries to update the previously read data if another user has already updated it since the last time the first user read it. This can really be a disaster if not understood well or not handled properly.

What is so significant here is that not locking the data resource (the data row) for update leads to an improvement in the overall performance of your system due to the major reduction of additional server resources needed to lock data. For an application to hold a lock on a data resource, it must also maintain a persistent connection to the database server that houses the data. Because this is not happening in optimistic concurrency (no persistent connection is needed and no data lock is used), connections to the database server are available to handle many more clients in less time. This directly achieves the scalability and performance goals of multitiered architectures.

Coding for Optimistic Concurrency

In general, there are two basic coding techniques you can use to implement the optimistic concurrency approach within ADO.NET. The default approach is to compare each column of data in the database table to the original data values you read into your DataSet as part of your UPDATE statement. This will detect any optimistic concurrency violations. If any of the database data values have changed since you last read the data row, your UPDATE statement will fail (as you would probably want it to). At a minimum, this forces you to reread the data values from the database, see what they are, and see whether you want to update some data value further.

Another, quicker method of doing the same thing is to utilize a timestamp column that may be available in the data table you are working with. (Such a column is available in MS SQL Server tables that have defined one.) This technique allows you to read a timestamp value that is part of the database data row and then compare timestamps at the time you want to update the data in the database. If the timestamp value has changed since the last time you read the data values, your UPDATE statement will fail (again, as you would want it to do).

The following is a short piece of Visual Basic code that you can execute for practice. This example uses the ADO.NET DataSet fill-and-update approach from the Customers table in the Northwind database that comes with Microsoft SQL Server:

 CustomerAdapter.Fill(CustomerDataSet, "Customers")  CustomerAdapter.Update(CustomerDataSet, "Customers") 

It will save the original data row values as they were originally read from the database:

 OldParms = CustomerAdapter.UpdateCommand.Parameters.Add("@oldCustomerID",          SqlDbType.NChar, 5, "CustomerID")   OldParms.SourceVersion = DataRowVersion.Original 

These will be used in a comparison ( WHERE clause) that will be part of the UPDATE statement:

 CustomerAdapter.UpdateCommand = New SqlCommand(  "UPDATE Customers (CustomerID, CompanyName, ContactName) " & "VALUES(@CustomerID, @CompanyName, @ContactName) " & _ "WHERE CustomerID = @oldCustomerID AND CompanyName = @oldCompanyName " & " AND ContactName = @oldContactName", nwindConn) 

By doing this comparison of the original data values read from the database with what's in the database at the time the update is issued, you will guarantee that no other user has slipped in and updated something before you. This is optimistic concurrency, and is shown in its complete form in Listing 46.1.

Listing 46.1 Optimistic Concurrency (File 46OptCon.vb)

 Imports System Imports System.Data Imports System.Data.SqlClient Imports Microsoft.VisualBasic namespace HowTo.ADONET24.Samples Public Class OptConSample   Public Shared Sub Main()     Dim nwindConn As SqlConnection = New SqlConnection   ("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")     Dim CustomerAdapter As SqlDataAdapter = New SqlDataAdapter         ("SELECT CustomerID, CompanyName, ContactName " &          " FROM Customers ORDER BY CustomerID", nwindConn)   CustomerAdapter.UpdateCommand = New SqlCommand         ("UPDATE Customers (CustomerID, CompanyName, ContactName) " &          "VALUES(@CustomerID, @CompanyName, @ContactName) " & _         "WHERE CustomerID = @oldCustomerID AND CompanyName = @oldCompanyName " &         " AND ContactName = @oldContactName", nwindConn)   CustomerAdapter.UpdateCommand.Parameters.Add         ("@CustomerID", SqlDbType.NChar, 5, "CustomerID")   CustomerAdapter.UpdateCommand.Parameters.Add         ("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName")   CustomerAdapter.UpdateCommand.Parameters.Add         ("@ContactName", SqlDbType.NVarChar, 30, "ContactName")  'Set up OldParms to hold the rows original values  'These are then used in the WHERE clause for the  'optimistic concurrency comparison   Dim OldParms As SqlParameter   OldParms = CustomerAdapter.UpdateCommand.Parameters.Add         ("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID")   OldParms.SourceVersion = DataRowVersion.Original   OldParms = CustomerAdapter.UpdateCommand.Parameters.Add         ("@oldCompanyName", SqlDbType.NVarChar, 40, "CompanyName")   OldParms.SourceVersion = DataRowVersion.Original   OldParms = CustomerAdapter.UpdateCommand.Parameters.Add         ("@oldContactName", SqlDbType.NVarChar, 30, "ContactName")   OldParms.SourceVersion = DataRowVersion.Original   Dim CustomerDataSet As DataSet = New DataSet()   Console.Writeline ("Go get some customer data - Fill")   CustomerAdapter.Fill(CustomerDataSet, "Customers")   Console.Writeline ("Update the rows")   CustomerAdapter.Update(CustomerDataSet, "Customers")   Dim CustRow As DataRow   Console.Writeline ("Look for optimistic concurrency violations")   For Each CustRow In CustomerDataSet.Tables("Customers").Rows     Console.Writeline ("Looking for errors for row with CustomerID of " &         CustRow(0) )     If CustRow.HasErrors Then Console.WriteLine(CustRow(0) &         vbCrLf & CustRow.RowError)     if not CustRow.HasErrors then Console.Writeline         ("No optimistic concurrency error found")   Next   Console.Writeline ("Show contents of DataSet")   For each CustRow in CustomerDataSet.Tables("Customers").Rows       Console.Writeline("Customer Contacts Selected: "         + CustRow("ContactName").ToString())   Next  End Sub End Class End namespace