Click here to Skip to main content
13,861,926 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

5.4K views
68 downloads
Posted 2 Apr 2017
Licenced CPOL

What You See Is What You Update

, 2 Apr 2017
Rate this:
Please Sign up or sign in to vote.
This is an alternative for "What You See Is What You Update"

Introduction

This article is an alternative to SQL Server specific article "What You See Is What You Update" and shows the problem (and the resolution) in an Oracle environment. So if you're working in an SQL Server environment, please use the original article.

For description about the problem, pelase refer to "Introduction, the problem" and also to get a picture about the demo program, have a look at "Demo program"

Contents:

Basic update

Ensuring that you update a correct row is simple, update the record based on a unique key.  Let’s have a look the basic update in the program.

Say you have a table like the following

CREATE TABLE Concurrency1 (
   Id          number        NOT NULL,
   TextColumn  varchar2(100) NULL,
   ValueColumn number        NULL
);

The fetch is done using the following code

/// <summary>
/// Fetch the data from the database and return as a collection
/// </summary>
/// <returns>Collection of data items</returns>
override public ObservableCollection<Data.TheData> FetchData() {
   try {
      using (OracleCommand command = new OracleCommand()) {
         command.Connection = this.connection;
         command.CommandText = @"
SELECT   Id,
         TextColumn,
         ValueColumn
FROM     Concurrency1";
         using (OracleDataReader reader = command.ExecuteReader()) {
            this.CurrentDataCollection = new ObservableCollection<Data.TheData>();
            while (reader.Read()) {
               this.CurrentDataCollection.Add(new Data.TheData() {
                  Id = (decimal)reader["Id"],
                  TextValue = reader["TextColumn"] != System.DBNull.Value 
                              ? (string)reader["TextColumn"] : null,
                  NumberValue = reader["ValueColumn"] != System.DBNull.Value 
                                ? (decimal?)reader["ValueColumn"] : null
               });
            }
         }
      }
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, 
                                     exception.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return this.CurrentDataCollection;
   }

   return this.CurrentDataCollection;
}

And the updates is done as follows

/// <summary>
/// Saves a single data item
/// </summary>
/// <param name="data">Data to save</param>
/// <returns>True if succesful</returns>
override public bool SaveItem(Data.TheData data) {
   int rowsAffected;

   try {
      if (this.transaction == null) {
         this.transaction = this.connection.BeginTransaction();
      }
      using (OracleCommand command = new OracleCommand()) {
         command.Connection = this.connection;
         command.Transaction = this.transaction;
         command.CommandText = @"
UPDATE Concurrency1
SET   TextColumn  = :TextColumn,
      ValueColumn = :ValueColumn
WHERE Id = :Id";
         command.Parameters.Add(":TextColumn", data.TextValue);
         command.Parameters.Add(":ValueColumn", data.NumberValue.HasValue 
                                                ? (object)data.NumberValue.Value 
                                                : System.DBNull.Value);
         command.Parameters.Add(":Id", data.Id);
         rowsAffected = command.ExecuteNonQuery();
         if (rowsAffected != 1) {
            throw new Exception(string.Format("Wrong number of rows ({0}) affected", rowsAffected));
         }
      }
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, 
                                     exception.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return false;
   }
   return true;
}

As in the original article, to demonstrate the problem, do the following:

  • Open two instances of the program
  • Instance 1:
    • Fetch the data
    • Select the first row
  • Instance 2:
    • Fetch the data
    • Select the first row
  • Instance 1:
    • Modify the number field to 123
    • Press save button
  • Instance 2:
    • Modify the number field to 456
    • Press save button. Note that the program freezes because it waits for the lock
  • Instance 1:
    • Press Commit button , instance 2 update now proceeds
  • Instance 2:
    • Press Commit button
  • Instance 1:
    • Fetch the data
  • Instance 2:
    • Fetch the data

When looking at the data in the grid you can notice that, the data updated in instance 2 remains the value of the number field is 456. The data updated in instance 1 is simply overwritten.

Checking row version upon update

The solution to the problem is to ensure that you are updating both correct row and the correct version of the row. While SQL Server has a special data type which is automatically changed during each update, Oracle does not have a direct equivalent. However, it's easy to achieve same type of functionality in Oracle. In this example I've used a trigger, which simply increments the version number on each update. Now the table could look like this

CREATE TABLE Concurrency2 (
   Id             number        NOT NULL,
   CurrentVersion number        NOT NULL,
   TextColumn     varchar2(100) NULL,
   ValueColumn    number        NULL
);

As you can see there’s an additional column called CurrentVersion. This column now stores the current rowversion. The trigger that updates the value could looke like

CREATE OR REPLACE TRIGGER Concurrency2Trigger
   BEFORE INSERT OR UPDATE ON Concurrency2
   FOR EACH ROW
   BEGIN
      :NEW.CurrentVersion := COALESCE(:OLD.CurrentVersion, 0) + 1;
   END;
/

The trigger just takes the value from the old row and increments it by one. During an insert the old row does not exist so the old values are NULL. In this case 0 is used in order to get the "seed" value.

When fetching the data the code is basically the same as previously but also the value of the rowversion column is retrieved.

/// <summary>
/// Fetch the data from the database and return as a collection
/// </summary>
/// <returns>Collection of data items</returns>
override public ObservableCollection<Data.TheData> FetchData() {
   Data.TheData data;

   try {
      using (OracleCommand command = new OracleCommand()) {
         command.Connection = this.connection;
         command.CommandText = @"
SELECT   Id,
         TextColumn,
         ValueColumn,
         CurrentVersion
FROM     Concurrency2";
         using (OracleDataReader reader = command.ExecuteReader()) {
            this.CurrentDataCollection = new ObservableCollection<Data.TheData>();
            while (reader.Read()) {
               data = new Data.TheData();
               data.Id = (decimal)reader["Id"];
               data.TextValue = reader["TextColumn"] != System.DBNull.Value 
                                ? (string)reader["TextColumn"] : null;
               data.NumberValue = reader["ValueColumn"] != System.DBNull.Value 
                                  ? (decimal?)reader["ValueColumn"] : null;
               data.CurrentRowVersion = reader["CurrentVersion"] != System.DBNull.Value 
                                        ? (decimal?)reader["CurrentVersion"] : null;

               this.CurrentDataCollection.Add(data);
            }
         }
      }
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, 
                                     exception.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return this.CurrentDataCollection;
   }

   return this.CurrentDataCollection;
}

So the important thing is that now you always have the value of the CurrentVersion in the program for all the rows you show in the user interface. When you want to update the row, you could have code like the following

/// <summary>
/// Saves a single data item
/// </summary>
/// <param name="data">Data to save</param>
/// <returns>True if succesful</returns>
override public bool SaveItem(Data.TheData data) {
   int rowsAffected;

   try {
      if (this.transaction == null) {
         this.transaction = this.connection.BeginTransaction();
      }
      using (OracleCommand command = new OracleCommand()) {
         command.Connection = this.connection;
         command.Transaction = this.transaction;
         command.CommandText = @"
UPDATE Concurrency2
SET   TextColumn  = :TextColumn,
      ValueColumn = :ValueColumn
WHERE Id             = :Id
AND   CurrentVersion = :CurrentVersion";
         command.Parameters.Add(":TextColumn", data.TextValue);
         command.Parameters.Add(":ValueColumn", data.NumberValue.HasValue 
                                ? (object)data.NumberValue.Value : System.DBNull.Value);
         command.Parameters.Add(":Id", data.Id);
         command.Parameters.Add(":CurrentVersion", data.CurrentRowVersion);
         rowsAffected = command.ExecuteNonQuery();
         if (rowsAffected != 1) {
            throw new System.Exception("Row versions do not match.");
         }
      }
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, 
                                     exception.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return false;
   }
   return true;
}

The code is very similar to the previous one, but in this one, you also check that the CurrentRowversion column still holds the same value as when we read the record from the database.

IMPORTANT! If the underlying rowversion has changed in the database, you won’t get an error from the statement. This happens because in this situation the statement is perfectly valid, it just does not update any rows. Because of this, it is critical to investigate, that correct amount of rows is updated. If you update a single row, the update should affect one and one only row.

Now if you repeat the same test with this code version as you did in the first example you will get an error message like following.

When you fetch the data again to the grid and repeat the modification, it is saved successfully since now the program knows the current rowversion.

Making the check inside a stored procedure

Many people use stored procedures for changing the data. In such case, the principle is the same but you just need to pass the current value of the rowversion to the procedure in order to make the check. The procedure could look like this

------------------------------------------
-- Procedure for saving to concurrency 2
------------------------------------------
CREATE OR REPLACE PROCEDURE procConcurrency2(
      pId             number,
      pCurrentVersion number,
      pTextColumn     varchar2,
      pValueColumn    number) AS
   BEGIN
      UPDATE Concurrency2
      SET   TextColumn  = pTextColumn,
            ValueColumn = pValueColumn
       WHERE Id             = pId
       AND   CurrentVersion = pCurrentVersion;

      IF SQL%ROWCOUNT != 1 THEN
         RAISE_APPLICATION_ERROR(-20001, 'Row versions do not match.');
      END IF;
   END;
/

The statement for updating the row is the same as in previous example and so is the check how many rows have been updated. With this procedure the calling side could look like this

/// <summary>
/// Saves a single data item
/// </summary>
/// <param name="data">Data to save</param>
/// <returns>True if succesful</returns>
override public bool SaveItem(Data.TheData data) {
   int rowsAffected;

   try {
      if (this.transaction == null) {
         this.transaction = this.connection.BeginTransaction();
      }
      using (OracleCommand command = new OracleCommand()) {
         command.Connection = this.connection;
         command.Transaction = this.transaction;
         command.CommandType = System.Data.CommandType.StoredProcedure;
         command.CommandText = @"procConcurrency2";
         command.Parameters.Add(":Id", data.Id);
         command.Parameters.Add(":CurrentVersion", data.CurrentRowVersion.HasValue 
                                ? (object)data.CurrentRowVersion.Value : System.DBNull.Value);
         command.Parameters.Add(":TextColumn", data.TextValue);
         command.Parameters.Add(":ValueColumn", data.NumberValue.HasValue 
                                ? (object)data.NumberValue.Value : System.DBNull.Value);
         rowsAffected = command.ExecuteNonQuery();
      }
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, 
                                     exception.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return false;
   }
   return true;
}

One big difference is that you do not investigate the amount of updated rows at the client side anymore. If the procedure updates a wrong amount of rows then it raises an error and the client receives an exception that can be shown.

Forcing the row version check using a trigger

So now, you know how to check the rowversion but how to ensure that the check is always made. A complex system may have quite a lot of database operations so how to guarantee that each and every one of them is making the check. The answer is that you cannot make such guarantee for all the code, but you can force the check in the database.

The idea is that we have two row version columns in a table, one holding the current value of rowversion and the other one is provided from client side when the update is done. The new table-layout could look like this

CREATE TABLE Concurrency3 (
   Id              number        NOT NULL,
   CurrentVersion  number        NOT NULL,
   PreviousVersion number        NULL,
   TextColumn      varchar2(100) NULL,
   ValueColumn     number        NULL
);

In the previous example the trigger looked like this

CREATE OR REPLACE TRIGGER Concurrency2Trigger
   BEFORE INSERT OR UPDATE ON Concurrency2
   FOR EACH ROW
   BEGIN
      :NEW.CurrentVersion := COALESCE(:OLD.CurrentVersion, 0) + 1;
   END;
/

The same trigger was used for both INSERT and UPDATE operations. Since we need to check the version value only during an update, it's easiest to break this into two separate triggers. The first one handling an INSERT

CREATE OR REPLACE TRIGGER Concurrency3_Insert
   BEFORE INSERT ON Concurrency3
   FOR EACH ROW
   BEGIN
      :NEW.CurrentVersion := 1;
   END;
/

And the second trigger firing on an UPDATE

CREATE OR REPLACE TRIGGER Concurrency3_Update
   BEFORE UPDATE ON Concurrency3
   FOR EACH ROW
   BEGIN
      :NEW.CurrentVersion := :OLD.CurrentVersion + 1;

      IF (:OLD.CurrentVersion != COALESCE(:NEW.PreviousVersion, -1)) THEN
         RAISE_APPLICATION_ERROR(-20001, 'The given row versions do not match old row versions.');
      END IF;
   END;
/

When the update happens the trigger expects that the client code has provided the value of the rowversion to the column PreviousVersion. Since the trigger fires for all updated rows, it simply investigates if the value in CurrentVersion column of the old row is the same as the value in PreviousVersion column in the new row. If the values differ, an error is raised.

Now if you look at the code at the client side

/// <summary>
/// Saves a single data item
/// </summary>
/// <param name="data">Data to save</param>
/// <returns>True if succesful</returns>
override public bool SaveItem(Data.TheData data) {
   int rowsAffected;

   try {
      if (this.transaction == null) {
         this.transaction = this.connection.BeginTransaction();
      }
      using (OracleCommand command = new OracleCommand()) {
         command.Connection = this.connection;
         command.Transaction = this.transaction;
         command.CommandText = @"
UPDATE Concurrency3
SET   TextColumn        = :TextColumn,
      ValueColumn       = :ValueColumn,
      PreviousVersion   = :CurrentVersion
WHERE Id             = :Id";
         command.Parameters.Add(":TextColumn", data.TextValue);
         command.Parameters.Add(":ValueColumn", data.NumberValue.HasValue 
                                ? (object)data.NumberValue.Value : System.DBNull.Value);
         command.Parameters.Add(":CurrentVersion", data.CurrentRowVersion.HasValue 
                                ? (object)data.CurrentRowVersion.Value : System.DBNull.Value);
         command.Parameters.Add(":Id", data.Id);
         rowsAffected = command.ExecuteNonQuery();
         if (rowsAffected != 1) {
            throw new Exception(string.Format("Wrong number of rows ({0}) affected", rowsAffected));
         }
      }
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, 
                                     exception.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return false;
   }
   return true;
}

You can see that there is no additional condition in the WHERE clause anymore, just the condition for the primary key. Instead, the PreviousVersion column is set in the UPDATE statement to have the value of the rowversion you currently have at the client.

Also, note that the check investigating how many rows were updated fails only if the primary key is invalid.

Now if you again repeat the tests listed in the beginning you receive an error like

You also receive the same error if, for example usinq SQL*Plus, you try to update all rows 

-- Try to update several rows at a time
UPDATE Concurrency3 SET ValueColumn = ValueColumn + 1;

You receive the following error

ORA-20001: The given row versions do not match old row versions.
ORA-06512: at "SYSTEM.CONCURRENCY3_UPDATE", line 5
ORA-04088: error during execution of trigger 'SYSTEM.CONCURRENCY3_UPDATE'

Corresponding check with Entity Framework

So now that we've discussed the problem using plain SQL only, what about Entity Framework? How can we enforce the same check when using EF?

The idea is naturally the same, embed the condition to the SQL statement that is executed. In order for the example to be more intuitive, define the Id column as a primary key so that EF generator can correctly pick up the actual key for the table

CREATE TABLE Concurrency5 (
   Id              number        NOT NULL,
   CurrentVersion number        NOT NULL,
   TextColumn     varchar2(100) NULL,
   ValueColumn    number        NULL
);

CREATE OR REPLACE TRIGGER Concurrency5Trigger
   BEFORE INSERT OR UPDATE ON Concurrency5
   FOR EACH ROW
   BEGIN
      :NEW.CurrentVersion := COALESCE(:OLD.CurrentVersion, 0) + 1;
   END;
/

The next step is to generate the model from the database. I won't go into details of that since it's mainly done by clicking Next-button in the wizard... Important! The connection string for the EF is in app.config file so modify it to contain correct TNS alias and user name in your environment.

After the model has been created let's have a look at the methods for fetching and saving

/// <summary>
/// Fetch the data from the database and return as a collection
/// </summary>
/// <returns>Collection of data items</returns>
override public ObservableCollection<Data.IData> FetchData() {
   try {
      if (this.CurrentDataCollection == null) {
         this.CurrentDataCollection = new ObservableCollection<Data.IData>();
         foreach (CONCURRENCY5 item in this.CurrentContext.CONCURRENCY5) {
            this.CurrentDataCollection.Add(item);
         }
      } else {
         foreach (Data.IData item in this.CurrentDataCollection) {
            this.CurrentContext.Entry(item).Reload();
         }
         this.CurrentDataCollection = new ObservableCollection<Data.IData>(this.CurrentDataCollection);
      }
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, 
                                     exception.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return this.CurrentDataCollection;
   }

   return this.CurrentDataCollection;
}

The code above fetches the collection of items from the DbContext. The snippet is simple, just fill the collection with the items from Concurrency5 table. There are few peculiarities in the code. First of all, if the collection already exists, I just reload the items individually from the database. Another thing is that the returned collection is always a new collection. Since I haven't implemented INotifyPropertyChanged interface, this is just a quick workaround to force the grid to react to possible changes.

Important! As in the previous examples, also in this one the lifetime of the context and the transaction is unnecessarily prolonged. The reason is that this way it's easier to test different scenarios, but preserving the context shouldn't be done in real situations.

So what does the save look like? In the simplest form it could be

/// <summary>
/// Saves a single data item
/// </summary>
/// <param name="data">Data to save</param>
/// <returns>True if succesful</returns>
override public bool SaveItem(Data.IData data) {
   int rowsAffected;

   try {
      this.BeginTransaction();
      rowsAffected = this.CurrentContext.SaveChanges();
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, 
                                     exception.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return false;
   }
   return true;
}

As you see, there is no code to enforce the check for the rowversion, no modification to the WHERE clause and the amount of affected rows is stored but it isn't checked since it would make no sense. If you would trace the SQL statement that is executed, you'd notice that the WHERE clause contains only condition for the primary key, nothing else.

So how to make the rowversion check to take place? The key is to alter your EF model. If you open the model and click the CurrentVersion column, you can see that the ConcurrencyMode property is changed to Fixed. This is the way to inform EF that optimistic concurrency check is to be enforced.

So what happens in the code, how to check if correct amount of rows has been updated? The answer is that you don't need to check it. If the update doesn't update the row, EF throws a DbUpdateConcurrencyException. So the complete code could look like this

/// <summary>
/// Saves a single data item
/// </summary>
/// <param name="data">Data to save</param>
/// <returns>True if succesful</returns>
override public bool SaveItem(Data.IData data) {
   int rowsAffected;

   try {
      this.BeginTransaction();
      rowsAffected = this.CurrentContext.SaveChanges();
   } catch (System.Data.Entity.Infrastructure.DbUpdateConcurrencyException concurrencyException) {
      System.Windows.MessageBox.Show("Row versions do not match (" 
                                     + concurrencyException.Message + ")", 
                                     concurrencyException.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return false;
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, 
                                     exception.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return false;
   }
   return true;
}

Now if you repeat the same test described earlier, you should see a message like this "Store update, insert, or delete statement affected an unwxpected amount of rows (0). Entities may have been modified or deleted since entities were loaded..."

 

References

History

  • 2nd April, 2017: Alternative created

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Wendelius
Architect
Europe Europe
This member does have enough reputation to be able to display their biography and homepage.







You may also be interested in...

Pro

Comments and Discussions

 
GeneralThis is a beautiful solution to the wrong approach. Pin
RAND 45586615-Apr-17 22:05
memberRAND 45586615-Apr-17 22:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01 | 2.8.190214.1 | Last Updated 2 Apr 2017
Article Copyright 2017 by Wendelius
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid