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.

Concurrent Model in ADO.NET

Tests

To test your application, you can use the sqlplus application. When the application is started, a window like that shown in Figure 3 appears.

Figure 3

Then, use sqlplus to connect as scott/tiger and execute the following command:

select * from colors where color_id=1 for update nowait;

Then, I can try to change some data by using the application. For example, I change the red column from 0 to 5. When I try to post changes, I get ORA-00054: resource busy and acquire with NOWAIT specified. Okay; I use sqlpus to execute the following command:

update colors set red=1 where color_id=1;
commit;

So, I changed the data row and commited changes. Now, this record should be ready for modifications. I try to post changes by using the application:

I see that the record is ready for modifications and it was changed by another user (see Figure 4). I decided to rewrite it and push OK but, of course, I can cancel. If you select data from this table, you can see that this row was changed by your application and that the concurrency violation was handled correctly.

Figure 4

As was shown here, handling optimistic concurrency violations gracefully can be done with some coding effort.

ADO.NET problems concerning the concurrent model can be solved by another way. Obviously, one can write a visual component that would be able to set a suitable concurrent model, but not only optimistic and like DataSet be data source for data-aware components.

Snotra Tech Oracle Data Components for .NET

For the third sample, I have used Snotra Tech Oracle Data Components for .NET that contain the STOraDataTable component. Unlike DataTable, it is connected to the dataset and checks all Oracle constraints "on the fly" and posts all changes—those are have made by the user in the database automatically—so you shouldn't call a command like colorsOracleDataAdapter1.Update. The component allows both pessimistic and optimistic concurrency and offers a good Oracle-specific realization of them. When the user has made some changes in DataGrid and is going to another row, STOraDataTable validates data and posts changes to the database. Unlike DataAdapter, it posts only columns those were changed by the user. You can download Snotra Tech Oracle Data Components from here. Close the Visual Studio environment and install it. It takes odp.net 2.102.2.20 or 9.2.0.700 as well as an Oracle client installed on your PC.

After installation, a SNOC.NET tab should appear on your Visual Studio Toolbar (see Figure 5).

Figure 5

To make a simple application, drop STOracleConnection and STOraDataTable to the application form. DataGridView also should be on the form.

STOracleConnection takes a connection string, so write this string as shown in Figure 6.

Figure 6

STOraDataTable also takes some options should be defined. See Figure 7.

Figure 7

Here, you need to set up four properties correctly:

  • The BindingControl property allows STOraDataTable to control DataGridView; the user can't leave the record if it contains some errors.
  • The SQL property allows STOraDataTable to determine from where to select data; All other SQLs (insert, update, delete) components generate automatically.
  • The STOracleConnection property sets the Oracle connection component.
  • The ThrowException property defines exception propagation. If an Oracle error occurred and ThrowException is false, an exception will be handled by STOraDataTable only.

Also put in three lines of code and the application is ready to start:

public Form1()
{
   InitializeComponent();
   dataGridView1.DataSource = stOraDataTable1;
   stOracleConnection1.Open();
   stOraDataTable1.Open();
}

See how to set a suitable concurrent model by using the STOraDataTable component. STOraDataTable has a property named LockingMode that can be on four variants:

  • CheckImmediate: When the user starts editing a record, it is locked and a check is performed to see whether it has been changed. The lock remains until the user posts or cancels the changes.
  • LockDelayed: When the user starts editing a record, a check is performed to see whether it has been changed, but the record is not locked. Therefore, when the user posts the record, it is locked and checked again.
  • LockImmediate: When the user posts an edited record, it is locked and a check is performed to see whether it has been changed. After this, the lock is released.
  • None: No locking or checks are performed. This should only be used in single-user applications.

By default, LockingMode is set to CheckImmediate; this mode works similarly to your last realisation of optimistic concurrency. But here, you have a good choice and realisation that is very suitable for an Oracle database.

Points of Interest

I came to .NET from Delphi and was a bit frustrated about those possibilities that disconnected ADO.NET components offer, so I decided to write my own solution that allows me to use concurrent models—those I need and some other extra features such as single record refreshing, partial data selection, and so on.

Conclusion

To write effective SQL code, I suggest that you do not make a cure all; the solution that is offered nowadays is suitable for Oracle database only, but this restriction is temporary. Snotra Tech Oracle Data Components is shareware licensed, but there are a number of free licenses and can deliver this software for interested persons for free. It welcomes any suggestions and opinions and is glad to answers your questions in the forum or by mail: info@snotratech.com.



About the Author

Michael Milonov

PhD in computer science, founder of Snotra Tech Company (http://www.snotratech.com), participant of FusionSoft project (http://fusionsoft-online.com). .net, oracle, java addicted person.

Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • On-demand Event Event Date: December 18, 2014 The Internet of Things (IoT) incorporates physical devices into business processes using predictive analytics. While it relies heavily on existing Internet technologies, it differs by including physical devices, specialized protocols, physical analytics, and a unique partner network. To capture the real business value of IoT, the industry must move beyond customized projects to general patterns and platforms. Check out this webcast and join industry experts as …

  • On-demand Event Event Date: October 29, 2014 It's well understood how critical version control is for code. However, its importance to DevOps isn't always recognized. The 2014 DevOps Survey of Practice shows that one of the key predictors of DevOps success is putting all production environment artifacts into version control. In this webcast, Gene Kim discusses these survey findings and shares woeful tales of artifact management gone wrong! Gene also shares examples of how high-performing DevOps …

Most Popular Programming Stories

More for Developers

RSS Feeds