Handle Concurrency Conflicts in ASP.NET Core and Oracle

Concurrency conflicts arise due to concurrent access to a shared resource. This article explores ways to handle concurrency conflicts using ASP.NET Core and Oracle. To connect to Oracle, dotConnect for Oracle will be used.

Prerequisites

You’ll need the following tools to deal with code examples:

• Visual Studio 2019 Community Edition (download)
• Oracle Express Edition (download)
• dotConnect for Oracle (download)

What is Concurrency Handling?

Concurrency handling is a technique for detecting and resolving problems caused by two concurrent requests to the same resource. It is a mechanism that enables numerous users to access the same data at the same time while ensuring that the data stays consistent throughout all future requests.

Concurrency handling is a proven technique of identifying and resolving conflicts generated by concurrent requests to the same resource. In essence, it may be used to help enforce data integrity and consistency when several concurrent users attempt to access the same resource at the same time.

Concurrency violations might occur when you’ve interdependent transactions, that is, transactions that rely on one another and attempt to access the same resource. A transaction comprises a collection of statements bundled together that is either guaranteed to be executed in its entirety or rolled back. Transactions contribute to data integrity and security.

There are two approaches to handling concurrency conflict: optimistic concurrency and pessimistic concurrency strategies. Let’s now discuss how each of these works.

Pessimistic Concurrency

Pessimistic concurrency entails locking rows to prevent other users from changing the data and avoiding data inconsistency. This strategy makes the records inaccessible from the time they were last fetched until it is updated in the database. Hence, when a record is modified, all other concurrent changes on the same record are placed on hold until the current operation is completed, and the lock on the record is released.

This approach is a good choice in environments with high data contention. You can take advantage of pessimistic concurrency in scenarios with short lock durations. However, pessimistic concurrency does not scale well when users interact with data, causing records to lock in longer periods.

Optimistic Concurrency

In the optimistic concurrency model, records are not locked; when a user attempts to edit a row, the application detects whether another user has modified the row since it was read last in the memory. Optimistic concurrency is often used in scenarios where data is scarce. 

Optimistic concurrency enhances efficiency by eliminating the need for record locking, which consumes extra server resources. Optimistic concurrency enhances performance and scales better since it allows the server to serve more clients in less time.

The optimistic concurrency option follows the “last saved wins” policy, which means that the most recently changed value is stored in the database. In other words, the most recently saved record “wins.” It should be noted that the optimistic concurrency management technique assumes that resource conflicts caused by concurrent accesses to a shared resource are improbable, but not impossible. You do not need to check for concurrent modifications to the same resource (i.e., a record in your database table) using this method; the record is simply rewritten.

Concurrency violation

In the event of a concurrency violation, the most recent data in the database will be re-read and the update will then be attempted again. To check for concurrent violations, you would need to ascertain the changes to the record since the previous time the application read it. For optimistic concurrency control to work properly, your application must first check the row version before proceeding with an update activity.

Create a new ASP.NET Core Web API Project

Earlier, we mentioned the necessary tools to proceed to the practical scenarios. The time has come to use those tools.

First, we need to create a new ASP.NET Core Web API project: 

1. Open Visual Studio 2019.
2. Click Create a new project.
3. Select ASP.NET Core Web Application and click Next.
4. Specify the project name and location to store that project in your system. Optionally, checkmark the Place solution and project in the same directory checkbox.
5. Click Create.
6. In the Create a new ASP.NET Core Web Application window, select API as the project template.
7. Select ASP.NET Core 5 or later as the version.
8. Disable the Configure for HTTPS and Enable Docker Support options (uncheck them).
9. Since we won’t use authentication in this example, specify authentication as No Authentication.
10. Click Create to finish the process.

We’ll use this project in this article.

Implement Concurrency Handling in ASP.NET Core and Oracle

When several users attempt to alter the same piece of data simultaneously, you must have a way to avoid one user’s modifications from interfering with other users’ changes. Concurrency Control is the process of detecting and resolving database changes performed by many users concurrently.

Create a new database table.

First off, let’s create a new database table in Oracle. The following code snippet can be used to create a new table called product in Oracle.

create table product
  (
    product_id number primary key,
    product_name varchar2(50) not null,
    price double precision not null
  );

Next, insert a few records in the product table using the following script:

insert into product values(1,'Lenovo Laptop',1000.00);
insert into product values(2,'HP Laptop',1000.00);
commit;

Install NuGet Packages

To get started you should install the dotConnect for Oracle package in your project. You can install it either from the NuGet Package Manager tool inside Visual Studio or, from the NuGet Package Manager console using the following command:

PM> Install-Package Devart.Data.Oracle

If the installation is successful, you’re all set to get started using dotConnect for Oracle.

Creating an OracleConnection

Now provide the Oracle database credentials in your application to establish a connection to the database. You can save this information configurable by storing it in the application’s config file as well. 

The code snippet given below illustrates how you can create an instance of OracleConnection:

String connectionString = "User Id=Your user Id; Password=Your password; Server = localhost; License Key = Specify your license key here; ";
OracleConnection oracleConnection = new OracleConnection(connectionString);

You should include the following namespace in your program:

using Devart.Data.Oracle;

Updating Data

Let’s now examine how to simulate optimistic concurrency in ADO.NET using dotConnect for Oracle. We’ll take advantage of the disconnected mode of operation here.

The following code snippet illustrates how you can use the OracleDataAdapter to simulate optimistic concurrency in ADO.NET.

OracleDataAdapter oracleAdapter = new OracleDataAdapter();
oracleAdapter.SelectCommand = new OracleCommand("select * from product", oracleConnection);

DataSet dataSet = new DataSet();
oracleAdapter.Fill(dataSet);

oracleAdapter.UpdateCommand = new OracleCommand("UPDATE product Set id = :id, " + 
"name = :name WHERE id = :oldid AND name = :oldname", oracleConnection);
oracleAdapter.UpdateCommand.Parameters.Add(":id", OracleDbType.Integer, 5, "id");
oracleAdapter.UpdateCommand.Parameters.Add(":name", OracleDbType.VarChar, 50, "name");

OracleParameter parameter = oracleAdapter.UpdateCommand.Parameters.Add(":oldid", OracleDbType.Integer, 5, "id");
parameter.SourceVersion = DataRowVersion.Original;
parameter = oracleAdapter.UpdateCommand.Parameters.Add(":oldname", OracleDbType.NVarChar, 50, "name");
parameter.SourceVersion = DataRowVersion.Original;
dataSet.Tables[0].Rows[0]["name"] = "DELL Laptop";

oracleAdapter.Update(dataSet);

The above code snippet shows how you can set the UpdateCommand of the OracleDataAdapter to test for optimistic concurrency. Note how the “where” clause of the update command has been used to check for any optimistic concurrency violations that might have occurred.

Create a class named Product a shown below:

public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public double Price { get; set; }                
    }

We’ll use this class as our model in this example.

Create an Action Method in ProductController Class

Create a new controller class in your project named ProductController. The following code listing illustrates how you can write a controller action method to update a record in the product table.

[HttpPut]
public IActionResult Put([FromBody] Product product) {
  string connectionString = "User Id=Your user Id;Password=Your password; Server = localhost; License Key = Your License Key";

  try {
    using(OracleConnection oracleConnection = new OracleConnection(connectionString)) {
      try {
        OracleDataAdapter oracleAdapter = new OracleDataAdapter();
        oracleAdapter.SelectCommand = new OracleCommand("select * from product where id  
        = :id", oracleConnection);
        oracleAdapter.SelectCommand.Parameters.Add("id", product.Id);

        DataSet dataSet = new DataSet();
        oracleAdapter.Fill(dataSet);

        if (dataSet.Tables[0].Rows.Count == 0) return NotFound();

        oracleAdapter.UpdateCommand = new OracleCommand("update product set name = 
        :name "+
        "WHERE id = :oldid AND name = :oldname", oracleConnection);
        oracleAdapter.UpdateCommand.Parameters.Add(":name", OracleDbType.VarChar, 50, 
        "name");

        OracleParameter parameter = 
        oracleAdapter.UpdateCommand.Parameters.Add(":oldid", product.Id);
        parameter.SourceVersion = DataRowVersion.Original;

        parameter = oracleAdapter.UpdateCommand.Parameters.Add(":oldname", 
        OracleDbType.VarChar, 50, "name");
        parameter.SourceVersion = DataRowVersion.Original;

        dataSet.Tables[0].Rows[0]["name"] = product.Name;

        oracleAdapter.Update(dataSet);
      } catch (DBConcurrencyException ex) {
        Debug.WriteLine(ex.Message);
        return BadRequest("Db Concurrency Violation");
      }

      if (oracleConnection.State != ConnectionState.Closed) oracleConnection.Close();
    }
  } catch (Exception ex) {
    Debug.WriteLine(ex.Message);
    return BadRequest("Error...");
  }

  return Ok("1 record updated...");
}

Remember to include the following namespace in your ProductController.cs file:

using Devart.Data.Oracle;

Execute the Application

Launch the Postman tool and execute the endpoint after specifying the URL as shown below.

Note the text message “1 record updated…” in the response. 

Set a breakpoint in the call to the Update method of the OracleDataAdapter class in your action method. Now, hit the same endpoint once again.

Run the following commands to update the record explicitly:

update product set name = 'DELL Laptop' where id = 1;
commit;

Now when you click on F10, you’ll be presented with the following exception:

Once you click on F5, you’ll be able to see the response returned in Postman as shown below:

Summary

In this post, we’ve implemented concurrency handling using OracleDataAdapter in disconnected mode. You can also implement concurrency in connected mode and take advantage of the row version by using a Timestamp column in your database. Once a record is updated, the row version will change, and you can check if the row version of a record has changed during an update operation on the database.