Concurrent Model in ADO.NET

Introduction

The most popular instrument to access database data for .NET applications is ADO.NET. This set of components contains three main classes those are used to manipulate and store data: DataReader, DataSet, and DataAdapter. DataReader is able only to read data and can’t work as data source for data-aware components such as DataGridView; DataSet provides all interfaces to be a data source but the disconnected model considers some restrictions that can become important for some kind of applications, especially for desktop programs that work under multiuser concurrent environment and change database data actively. Learn about the main restrictions and popular ways to solve them; non-standard solutions are also considered.

Prerequisites

All code samples are intended for Microsoft Visual Studio 2005; as a database, I used Oracle 10i and Oracle Development Tools (ODT.NET) to generate sample code to access data; the last version is available here, but the main principles those are shown also are suitable for other RDBMS.

Concurrent Model

One of the serious problems of ADO.NET is the concurrent model; it’s not a secret that ADO.NET uses optimistic concurrency. The disconnected nature of the DataSet makes it quite powerful. However, optimistic concurrency can lead to problems, as in the case of frequent data updates. Optimistic concurrency means that you assume no one will be making edits to a record while you are making your edits. Because you are “optimistic” that two people will not edit the same record simultaneously, you do not apply a lock to the record as soon as a user starts to edit it. Instead, you apply the lock only when the actual update is attempted.

To check for optimistic concurrency violations, the Data Adapter Configuration Wizard writes SQL statements that verify the record you are about to update or delete has not changed since you originally loaded it into your DataSet. The Wizard does this by adding a rather large WHERE clause to the SQL statement to verify that it is updating or deleting an exact match of what was downloaded and placed in the DataSet.

The samples use a simple table named “COLORS”.

create table COLORS
(
   COLOR_ID    NUMBER not null,
   COLOR_NAME  varchar2(100) not null,
   RED         number not null,
   GREEN       number not null,
   BLUE number not null,
   CONSTRAINT PK_COLORS PRIMARY KEY (COLOR_ID)
)

// ------------- fill table-------------
insert into colors(color_id, color_name, red, green, blue)
   values(1, 'black', 0, 0, 0);
insert into colors(color_id, color_name, red, green, blue)
   values(2, 'white', 254, 254, 254);
insert into colors(color_id, color_name, red, green, blue)
   values(3, 'red', 254, 0, 0);
insert into colors(color_id, color_name, red, green, blue)
   values(4, 'green', 0, 254, 0);
insert into colors(color_id, color_name, red, green, blue)
   values(5, 'blue', 0, 0, 254);
insert into colors(color_id, color_name, red, green, blue)
   values(6, 'yellow', 0, 254, 254);
commit;

To generate the program, simply drag the “COLORS” table from the OracleExplorer panel that is part of the ODT.NET tool and drop it on an application form (see Figure 1).

Figure 1

A wizard automatically generates OracleDataAdapter and OracleConnection, so you should add an untyped DataSet, DataGridView, manually as well as a Button to post changes in the database and a few lines of code to make your program work:

public Form1()
{
   InitializeComponent();
   colorsOracleDataAdapter1.Fill(dataSet1, "COLORS");
   dataGridView1.DataSource = dataSet1.Tables["COLORS"];
}

private void button1_Click(object sender, EventArgs e)
{
   try
   {
      colorsOracleDataAdapter1.Update(dataSet1, "Colors");
      // Display confirmation message
      MessageBox.Show("Changes saved successfully !");
   }
   catch (Exception ex)
   {
      MessageBox.Show(ex.ToString(), "Exception Occured");
   }
}

Now, you can see what the UPDATE SQL that the wizard generated looks like. Update Command text:

UPDATE "COLORS" SET "COLOR_ID"=:current_COLOR_ID_param0,
   "COLOR_NAME"=:current_COLOR_NAME_param1,
   "RED"=:current_RED_param2,
   "GREEN"=:current_GREEN_param3,
   "BLUE"=:current_BLUE_param4
   WHERE "COLOR_ID"=:original_COLOR_ID_param5
   AND "COLOR_NAME"=:original_COLOR_NAME_param6
   AND "RED"=:original_RED_param7 AND "GREEN"=:original_GREEN_param8
   AND "BLUE"=:original_BLUE_param9

The default behavior of the Data Adapter Configuration Wizard is to include all the columns in the WHERE clause. This prevents your code from overwriting changes made by other users between the time your code retrieves the row and the time your code attempts to submit the pending change in the row. Because the value of another user’s updated column for a row of data has changed in the database, no row in the table satisfies all the criteria in the query’s WHERE clause. Thus, the database does not modify the row.

This update query works, but obviously it can’t be optimal because the RDBMS has to select non-indexed parameters. So, it is suitable for small tables such as the “COLORS” table, but for real tables—those that contain a significant number of rows—this query can slow down the database seriously.

You should modify this query and make some optimization. As a variant, you can include only the primary columns in the SQL UPDATE queries:

UPDATE "COLORS" SET "COLOR_ID"=:current_COLOR_ID_param0,
   "COLOR_NAME"=:current_COLOR_NAME_param1,
   "RED"=:current_RED_param2,
   "GREEN"=:current_GREEN_param3,
   "BLUE"=:current_BLUE_param4
   WHERE "COLOR_ID"=:original_COLOR_ID_param5

This creates a “last in wins” updating scenario. Both update attempts will succeed. Obviously, the database is not going to maintain both sets of changes. The changes made by the last update will override the previous changes. The “last in wins” scenario is not appropriate if you want to prevent users from unwittingly overwriting other users’ changes.

Anyway, you can improve this situation and add timestamp columns to the “COLORS” table and include the Primary Key and Timestamp Columns in the WHERE clause:

alter table COLORS add TIMESTAMP date;

Also, you should create a trigger after the insert or update to generate a new value in this column:

create or replace trigger TBIU_COLORS
   before insert or update on COLORS
   for each row
declare
begin
   select sysdate into :new.timestamp from dual;
end TBIU_COLORS;

And create an index to optimize the query:

create index INDX_COLORS_ID_STAMP on COLORS (color_id, timestamp);

Anytime the contents of a row changes, SQL Server will modify the value of the timestamp column for that row. Make your query look like this:

UPDATE "COLORS" SET "COLOR_ID"=:current_COLOR_ID_param0,
   "COLOR_NAME"=:current_COLOR_NAME_param1,
   "RED"=:current_RED_param2,
   "GREEN"=:current_GREEN_param3,
   "BLUE"=:current_BLUE_param4
   WHERE "COLOR_ID"=:original_COLOR_ID_param5
   AND "TIMESTAMP"=:original_TIMESTAMP_param6

Because the server will generate a new value for the timestamp column each time it updates a row, you can use a combination of the primary key and timestamp columns in the WHERE clause of your query-based updates to ensure that you don’t overwrite another user’s changes. The disadvantage of this method is the necessity to make an extra job, especially if you have to work with an existing database and all tables were made without a timestamp column and hold extra data in your database.

As you can see, you can choose one of these ways to use in your ADO.NET applications. A big plus of those methods is universality, but universality doesn’t mean optimality and convenience and of course it’s not a cure all. Except for individual disadvantages for each method, there are common disadvantages:

  • If you add a column or columns in the “COLORS” table, you should make changes in your UPDATE statement manually and recompile the application.
  • Each time, the application must update the entire row even if you have changed only one column’s value.

Using a RDBMS-Specific Mechanism to Handle Optimistic Concurrency Violations

Each database has a mechanism that maintains different concurrent models. The important part of this mechanism is record locking. The moment you lock a database record, it considers the type of concurrency: optimistic or pessimistic. If you apply a lock record as soon as a user has started to edit it, it is pessimistic concurrency and all other users can’t change this record until the first user has finished editing it and posted changes to the database. If you apply to lock the record only when the actual update is attempted, it is optimistic concurrency. If you use ADO.NET DataSet and DataAdatper, you have no choice; you can use only optimistic variant, but the realization of the one is far away from ideal.

Okay, but what is the ‘right’ realization? The main scenario of optimistic concurrency are as follows:

  1. The user has changed a record and tries to post changes to the database.
  2. The application tries to execute “select for update” for the current record. If the record is locked by another user, the first one gets a concurrency violation.
  3. If the record is unlocked, the application checks whether the record was changed by another user. If the record was changed, the application informs the user about it and offers to overwrite or cancel the post record command.
  4. If the record is unchanged, or the user decided to overwrite it, the application updates only columns that were changed by the current user.
  5. The application executes “commit” if changes were made successfully or “rollback” if the operation failed to unlock the current record.

“Select for update” is a very important part because, in Oracle, a database user doesn’t see changes that were made by another user until the “commit” was made. However, because you have executed “select for update” for some record, Oracle will throw an appropriate exception if another user tries to change it.

Fortunately, Oracle has an unique identifier for each table, named ROWID, so you can use it as an universal row identifier in any program. To realize an optimistic model for Oracle database, first you should change CommandText of UpdateCommand as shown below:

UPDATE "COLORS" SET "COLOR_ID"=:current_COLOR_ID_param0,
   "COLOR_NAME"=:current_COLOR_NAME_param1,
   "RED"=:current_RED_param2,
   "GREEN"=:current_GREEN_param3,
   "BLUE"=:current_BLUE_param4 WHERE "ROWID"=:original_ROWID_param5.

Also, you should delete parameters higher than 4 and add a new one that has type Varchar2 (see Figure 2).

Figure 2

Then, you should write a RowUpdating event handler for OracleDataAdapter1.

private void colorsOracleDataAdapter1_RowUpdating(object sender,
   Oracle.DataAccess.Client.OracleRowUpdatingEventArgs e)
{
   OracleCommand oracleDMLCommand    = null;
   OracleDataReader oracleDataReader = null;
   if (e.StatementType != StatementType.Update)
      return;
   try
   {
      // clear row errors
      e.Row.ClearErrors();
      // create command
      oracleDMLCommand = colorsOracleConnection1.CreateCommand();
      oracleDMLCommand.CommandType = CommandType.Text;
      // select for update nowait
      oracleDMLCommand.CommandText = "select * from colors c where
         c.rowid= :cur_rowid for update nowait";
         oracleDMLCommand.Parameters.Add("cur_rowid",
         OracleDbType.Varchar2,
         e.Row["ROWID"].ToString().Length,
      e.Row["ROWID"].ToString(), ParameterDirection.Input);
      // execute command
      oracleDataReader = oracleDMLCommand.ExecuteReader();
      // read data from database reader
      while (oracleDataReader.Read())
      {
         //iterate all fields from datareader
         for (int i = 0; i < oracleDataReader.FieldCount; i++)
         {
            // compare the original data in the column with data
            // from the database
            if (e.Row[oracleDataReader.GetName(i),
               DataRowVersion.Original].ToString() !=
               oracleDataReader.GetValue(i).ToString())
               {
                  // We found a difference. Inform the user about
                  // it and offer to overwrite the record
                  if (DialogResult.Cancel == MessageBox.Show("Row
                     was changed by another user, rewrite anyway?",
                     "Warning", MessageBoxButtons.OKCancel,
                        MessageBoxIcon.Question))
                     throw new Exception("Row was changed by another
                                          user");
                  else
                     break;
            }
         }
      }

   }
   catch (OracleException ee)
   {
      //set row error
      e.Row.SetColumnError("COLOR_ID", ee.ToString());
      throw ee;
   }
   catch (Exception ex)
   {
      // set row error
      e.Row.SetColumnError("COLOR_ID", ex.ToString());
      throw ex;
   }
   finally
   {
      if(oracleDataReader !=  null)
                  oracleDataReader.Close();
      if (oracleDMLCommand != null)
         oracleDMLCommand.Dispose();
   }
}

OracleDataAdapter automatically executes a “commit” command after the update command so you shouldn’t worry about it. It’s not an ideal solution, but at least it checks whether a record is locked, checks whether it was changed by another user, and allows the user to overwrite the new record if necessary.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read