In this post, we will cover how to handle concurrency for a resource in an ASP.NET Core Web API. The endpoint we will focus on is updating a product resource. The product lives in a SQL Server database and we access it with Dapper. We will handle the case when requests are trying to update the product at the same time.

The code is deliberately simple and focused on the handling of concurrency.

An existing action method

Here’s our existing action method for handling requests to update a product:

[HttpPut()]
public async Task<ActionResult<Product>> Put([FromBody] Product product)
{
  using (var connection = new SqlConnection(_configuration["ConnectionStrings:DefaultConnection"]))
  {
    await connection.OpenAsync();
    var existingProduct = await connection.QueryFirstOrDefaultAsync<Product>("SELECT * FROM Product WHERE ProductId = @ProductId", new { ProductId = product.ProductId });
    if (existingProduct == null)
    {
        return new NotFoundResult();
    }
    await connection.ExecuteAsync(@"UPDATE Product
                                SET ProductName=@ProductName,
                                    UnitPrice=@UnitPrice,
                                    UnitsInStock=@UnitsInStock
                                WHERE ProductId = @ProductId",
                                product);
    return Ok(product);
  }
}

The Product model is as follows:

public class Product
{
  public Guid ProductId { get; set; }
  public string ProductName { get; set; }
  public decimal UnitPrice { get; set; }
  public int UnitsInStock { get; set; }
}

The problem with this is that a request can wipe over changes from another request. This might be fine in some APIs, but some APIs might want to stop this from happening.

We also have an action method to get a product:

[HttpGet("{productId}")]
public async Task<ActionResult<Product>> GetById(Guid productId)
{
  using (var connection = new SqlConnection(_configuration["ConnectionStrings:DefaultConnection"]))
  {
    await connection.OpenAsync();
    var product = await connection.QueryFirstOrDefaultAsync<Product>("SELECT * FROM Product WHERE ProductId = @ProductId", new { ProductId = productId });
    if (product == null) return NotFound();
    return Ok(product);
  }
}

So, an app consuming this API would:

• get the product from the API
• display it on a page
• allow the user to make changes to the product
• submit the updated product to the API

A solution

A solution is to check that the product hasn’t changed between an app getting it and submitting changes to it. If the product isn’t up-to-date, then another user has been making changes at the same time, and the changes can be rejected.

How can we check whether the product has changed though? Well, if the product is persisted in SQL Server, we can use rowversion. rowversion is a mechanism for automatically version-stamping table rows. If we add a field of type rowversion, SQL Server will automatically change the value of this field every time a change occurs in that row.

If we include the value from this rowversion field in the GET request for a product and require it in the PUT request, we can check if the product has changed before making the database update.

Adding a product version

We are going to add a Version field to the Product table so that we can enforce a request must have the latest version of the product to change it:

ALTER TABLE Product
ADD Version rowversion

Before we implement the additional code in the web API, let’s experiment with this new field:

If we select a product, we see that SQL Server has given Version an initial value:

If we update and select the product again, we see that SQL Server has updated the Version:

Nice!

So, let’s add Version to our Product model in our web API which needs to be a byte array:

public class Product
{
  ...
  public byte[] Version { get; set; }
}

It is worthing noting that ASP.NET core automatically converts the byte array to a base64 encoded string during model binding. We can see this if we make a GET request for a product:

Cool!

Checking the PUT request contains the latest product

The last change we need to make is to check the changed product is the latest version in the PUT action method:

[HttpPut()]
public async Task<ActionResult<Product>> Put([FromBody] Product product)
{
  using (var connection = new SqlConnection(_configuration["ConnectionStrings:DefaultConnection"]))
  {
    await connection.OpenAsync();
    var existingProduct = await connection.QueryFirstOrDefaultAsync<Product>("SELECT * FROM Product WHERE ProductId = @ProductId", new { ProductId = product.ProductId });
    if (existingProduct == null)
    {
      return new NotFoundResult();
    }
    if (Convert.ToBase64String(existingProduct.Version) != Convert.ToBase64String(product.Version))
    {
      return StatusCode(409); // conflict
    }
    await connection.ExecuteAsync(@"UPDATE Product

                                SET ProductName=@ProductName,

                                    UnitPrice=@UnitPrice,

                                    UnitsInStock=@UnitsInStock

                                WHERE ProductId = @ProductId",
                                product);
    var savedProduct = await connection.QueryFirstOrDefaultAsync<Product>("SELECT * FROM Product WHERE ProductId = @ProductId", new { ProductId = product.ProductId });
    return Ok(savedProduct);
  }
}

Let’s test this out.

First, we’ll try to update an out-of-date product:

We get a conflict as expected.

Now let’s try to update an up-to-date product:

The product is updated successfully as expected. The new version for the product is also returned.

A more thorough check

There is still a chance that simultaneous requests will update the same product. We can deal with this edge case by adding Version to the WHERE clause and checking that only one row is updated:

var rowsUpdated =
  await connection.ExecuteAsync(@"UPDATE Product

                                  SET ProductName=@ProductName,

                                      UnitPrice=@UnitPrice,

                                      UnitsInStock=@UnitsInStock

                                  WHERE ProductId = @ProductId

                                      AND Version = @Version",
                                  product);
if (rowsUpdated != 1)
{
    return StatusCode(409);
}

The code for this post can be found in GitHub at https://github.com/carlrip/asp-net-core-web-api-concurrency.

Wrap up

If a resource for a web API endpoint is persisted in SQL Server, then using a rowversion field is an efficient way of helping manage concurrency. This field maps to a byte array in .NET. The request/response model binding in ASP.NET Core automatically converts it into a base64 string.