Concurrent model in ADO.NET, ways that allow to negotiate disconnected model restrictions for Oracle databases

• Download demo project - 35.7 Kb

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 only able to read data and can't work as data source for data-aware components like DataGridView etc.; DataSet provides all interfaces to be a data source but the disconnected model has some restrictions that can become important for some kind of applications, especially for desktop programs that work under a multi-user concurrent environment and change database data actively. Below we consider main restrictions and popular ways to solve them; non standard solutions is considered also.

Prerequisites

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

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 that 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.

In our samples, we have used 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 table "COLORS" from the OracleExplorer panel that is part of the ODT.NET tool and drop it on the application form (fig. 1).

Fig. 1.

The Wizard generates automatically OracleDataAdapter and OracleConnection so we should add an untyped DataSet, and a DataGridView manually as well as a Button to post changes in the database, and a few lines of code to make our 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 we can see how the update SQL that is generated by the wizard looks like. The 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 like our "COLORS" table, but for real tables that contain a significant number of rows, this query can slow down the database.

We should modify this query and make some optimization. As a variant, we 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, we 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, we 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 the index to optimize the query:

create index INDX_COLORS_ID_STAMP on COLORS (color_id, timestamp);

Any time the contents of a row changes, SQL Server will modify the value of the timestamp column for that row. We make our 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 do the extra job, especially if you have to work with the existing database and all the tables were made without a timestamp column and hold extra data in your database.

As we can see, we can choose one of these ways to use in our 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 for all. Except individual disadvantages for each method, there are common disadvantages:

1. if you add a column in the table "COLORS", you should make changes in your UPDATE statement manually and recompile the application;
2. each time, the application must update the entire row even if you have changed only one column value.

Using RDBMS specific mechanisms 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, consider the type of concurrency: optimistic or pessimistic. If you apply lock to a 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 the changes to the database. If you apply the lock as record only when the actual update is attempted, it is optimistic concurrency. Using the ADO.NET DataSet and DataAdatper, we have no choice, you can use only optimistic variant, but the realization of the one is far away from ideal.

OK, but what looks like the 'right' realization? The main scenario of optimistic concurrency:

1. User has changed a record and tries to post changes in the database.
2. Application tries to execute "select for update" for the current record. If record is locked by another user, the first one gets concurrency violation.
3. If record is unlocked, the application checks if 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 posted record.
4. If the record is unchanged, or the user decides to overwrite it, the application updates only columns that were changed by the current user.
5. Application executes "commit" if changes were made successfully, or "rollback" if operation failed to unlock the current record.

"Select for update" is a very important part, because in an Oracle database, the user doesn't see changes made by another user until a "commit" is made, but since we have executed "select for update" for some records, Oracle will throw the appropriate exception if another user tries to change it.

Thank god, Oracle has a unique identifier for each table named ROWID so we can use it as a universal row identifier in any program. To realize the optimistic model for the Oracle database, first we should change CommandText of UpdateCommand as is 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, we should delete parameters higher than four, and add a new one that has the type Varchar2 (see fig. 2 below).

Fig. 2.

Then we 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 erros

                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 original data in column with data from

                        // database

                        if (e.Row[oracleDataReader.GetName(i), 
                            DataRowVersion.Original].ToString() !=
                            oracleDataReader.GetValue(i).ToString())
                        {
                            //We found a difference, inform user about it

                            //and offer to overwrite 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 executes the "commit" command automatically after the update command so we shouldn't worry about it. It's not the ideal solution but at least it checks if the record is locked, checks if it was changed by another user, and allows the user to overwrite the new record if necessary.

Tests

To test our application, we can use the SQLPlus application. When the application is started, the window like in fig. 3 appears.

Fig. 3.

Then connect as scott/tiger using SQLPlus, and execute the command:

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

Then I can try to change some data using our 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. OK, using SQLPlus, I execute the following command:

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

So I change the data row and commit the changes. Now this record should be ready for modifications. I try to post changes using our application:

Fig. 4.

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

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

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

Points of Interest

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