In this Article

A few months ago, I had to implement a Web API for a relatively simple use case, but there was a challenge. The web endpoint must read and update a specific record using Entity Framework Core 5 and be capable of handling thousands of requests per second. Another requirement was the data integrity, so a transaction was a must. With high concurrency and Microsoft SQL Server as the database, we can end up in a deadlock if the SQL Server locks the records or rather the pages in an unfavorable way. Let's see how table hints can help us.

Links:

Sample Application

First, we will create a sample application for the reproduction of the issues and for measuring the throughput. For that, we create an ASP.NET Core application with one endpoint and a DbContext with one entity, Product.

Demo DbContext

The entity Product has two properties: an ID and a DateTime, which will be changed on every call of the web API.

public class Product
{
   public Guid Id { get; set; }
   public DateTime LastProductAccess { get; set; }
}

public class DemoDbContext : DbContext
{
   public DbSet<Product> Products { get; set; }

   public DemoDbContext(DbContextOptions<DemoDbContext> options)
      : base(options)
   {
   }
}

Later, during the startup of the application, we will create one Product with a hard-coded identifier. This ID will be used for load tests.

var id = new Guid("06D6E029-320B-4347-9E47-03CCFF153652");

if (!await dbContext.Products.AnyAsync(p => p.Id == id))
{
   dbContext.Products.Add(new() { Id = id, LastProductAccess = DateTime.UtcNow });
   await dbContext.SaveChangesAsync();
}

Demo Controller

The ASP.NET controller has one action, which reads a Product with the provided ID and updates the property LastProductAccess.

[Route("[controller]")]
public class DemoController : Controller
{
   private const IsolationLevel _ISOLATION_LEVEL =
         IsolationLevel.ReadCommitted // Violates our business requirements,
                                      // but just for testing purposes
      // IsolationLevel.RepeatableRead
      // IsolationLevel.Serializable
      ;

   private readonly DemoDbContext _ctx;

   public DemoController(DemoDbContext ctx)
   {
      _ctx = ctx;
   }

   [HttpPost("{id}")]
   public async Task DoAsync(Guid id)
   {
      await using var tx = await _ctx.Database.BeginTransactionAsync(_ISOLATION_LEVEL);

      var product = await _ctx.Products.FirstAsync(p => p.Id == id);

      // do more or less complex stuff

      product.LastProductAccess = DateTime.UtcNow;

      await _ctx.SaveChangesAsync();

      await tx.CommitAsync();
   }
}

Choosing the Transaction Isolation Level

For consistency, a transaction is the first that comes to mind. The question is, what isolation level should we choose, and what are the implications?

Isolation Level: ReadCommitted

The isolation level ReadCommitted is usually the default one. Although it is not sufficient for our purposes because it allows others to modify the data we are currently working on, still, it is interesting to see how it will perform.

I'm using hey for the execution of multiple concurrent requests. We execute 10k requests with a concurrency level of 50.

After the execution of the load tests, we see that all requests have responded with the status code 200, and we are able to handle 5k requests per second.

Please note: the absolute values are not important and will vary from machine to machine. What matters are the differences between measurements.

> hey -n 10000 -c 50 -m POST http://localhost:5000/demo/06D6E029-320B-4347-9E47-03CCFF153652

Summary:
  Total:        1.9819 secs
  Slowest:      0.0303 secs
  Fastest:      0.0052 secs
  Average:      0.0099 secs
  Requests/sec: 5045.5772

Status code distribution:
  [200] 10000 responses

The numbers are more than acceptable for our use case, but the isolation level is not.

Isolation Level: RepeatableRead

The first isolation level that suffices our requirements is RepeatableRead. We change the isolation level in the controller and rerun the load tests.

[Route("[controller]")]
public class DemoController : Controller
{
   private const IsolationLevel _ISOLATION_LEVEL =
      // IsolationLevel.ReadCommitted // Violates our business requirements,
                                      // but just for testing purposes
         IsolationLevel.RepeatableRead
      // IsolationLevel.Serializable
      ;

Shortly after the start of the load test, our application drowns in exceptions due to deadlocks.

System.InvalidOperationException: An exception has been raised that is likely due to a
  transient failure. Consider enabling transient error resiliency by adding
  'EnableRetryOnFailure()' to the 'UseSqlServer' call.
  --> Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating 
      the entries. See the inner exception for details.
     --> Microsoft.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 102)
         was deadlocked on lock resources with another process and has been chosen as the
         deadlock victim. Rerun the transaction.

Most of the requests ended up with the status code 500, and the performance turned out very bad. Sure, we could try to catch the exception and retry or activate EnableRetryOnFailure() as suggested in the error message, but it will make everything worse.

> hey -n 10000 -c 50 -m POST http://localhost:5000/demo/06D6E029-320B-4347-9E47-03CCFF153652

Summary:
  Total:        56.0751 secs
  Slowest:      8.3635 secs
  Fastest:      0.0957 secs
  Average:      0.2801 secs
  Requests/sec: 178.3322

Status code distribution:
  [200] 400 responses
  [500] 9600 responses

Isolation Level: Serializable

The isolation level Serializable is the strictest one, so it may help us to prevent the deadlocks - but it did not. We still get as many deadlocks as with RepeatableRead.

> hey -n 10000 -c 50 -m POST http://localhost:5000/demo/06D6E029-320B-4347-9E47-03CCFF153652

Summary:
  Total:        55.2665 secs
  Slowest:      7.5805 secs
  Fastest:      0.0133 secs
  Average:      0.2756 secs
  Requests/sec: 180.9414

Status code distribution:
  [200] 401 responses
  [500] 9599 responses

Consequently, switching the isolation level alone won't help to improve the throughput unless we are willing to sacrifice the data integrity.

Table Hints

The table hints aren't supported by Entity Framework by default, i.e., we have to implement this feature ourselves or use a 3rd party library. In this article, I use the library Thinktecture.EntityFrameworkCore.SqlServer.

We change the LINQ query so that the Product is loaded with a UpdLock and RowLock, and retry the load test.

var product = await _ctx.Products
                        .WithTableHints(SqlServerTableHint.UpdLock,
                                        SqlServerTableHint.RowLock)
                        .FirstAsync(p => p.Id == id);

The output of the load test shows that the application is stable now; we have no deadlocks anymore. The throughput is not as high as with IsolationLevel.ReadCommitted and without table hints, but it is the worst-case scenario we have here. In production, we won't be hammering on the one and only record over and over again but use different records.

> hey -n 10000 -c 50 -m POST http://localhost:5000/demo/06D6E029-320B-4347-9E47-03CCFF153652

Summary:
  Total:        4.6708 secs
  Slowest:      0.0446 secs
  Fastest:      0.0160 secs
  Average:      0.0233 secs
  Requests/sec: 2140.9711

Status code distribution:
  [200] 10000 responses

Let's see whether we can improve the performance a bit more.

Bonus: Compiled Queries

In this section, we will replace the only LINQ query with a compiled query.

[Route("[controller]")]
public class DemoController : Controller
{
   // definition and "caching" of the compiled query
   private static readonly Func<DemoDbContext, Guid, Task<Product>> _loadProductAsync
      = EF.CompileAsyncQuery((DemoDbContext ctx, Guid id) 
               => ctx.Products
                     .WithTableHints(SqlServerTableHint.UpdLock, SqlServerTableHint.RowLock)
                     .First(p => p.Id == id));

    ...
    
   [HttpPost("{id}")]
   public async Task DoAsync(Guid id)
   {
      await using var tx = await _ctx.Database.BeginTransactionAsync(_ISOLATION_LEVEL);

      // replacement of the LINQ-query with compiled query
      var product = await _loadProductAsync(_ctx, id);

      // do more or less complex stuff

      product.LastProductAccess = DateTime.UtcNow;

      await _ctx.SaveChangesAsync();

      await tx.CommitAsync();
   }

The results are not bad. We almost got 500 requests per second more, for free.

>hey -n 10000 -c 50 -m POST http://localhost:5000/demo/06D6E029-320B-4347-9E47-03CCFF153652

Summary:
  Total:        3.8418 secs
  Slowest:      0.0295 secs
  Fastest:      0.0091 secs
  Average:      0.0192 secs
  Requests/sec: 2602.9580

Status code distribution:
  [200] 10000 responses

Summary

The table hints may have been forgotten when working with Entity Framework. Still, they shouldn't be underestimated when it comes to performance and stability of the application.

If you want to stay up to date, sign up for our newsletter, and we will inform you about new articles, webinars, and screencasts by our experts.

Related Articles

 | Pawel Gerr

It has been a while since I released my article about the usage of temp tables in Entity Framework (v6). Meanwhile, Microsoft has released a completely rewritten version of its O/R mapper so my old approach is no longer applicable. But before we learn about a new one, let us…

Read article
 | Pawel Gerr

Both a LINQ query and an SQL statement are descriptions that state which data should be fetched, but not how.. Sure, when reading LINQ or SQL, we can make assumptions about the performance but not in every case. Some queries are either too fancy or too big to grasp, so our…

Read article
 | Pawel Gerr

After talking about performance issues like N+1 Queries and the Cartesian Explosion that made its comeback in Entity Framework Core 3, we will today look at a performance issue that is not tied to any Entity Framework version but is rather a general one. What do I mean by…

Read article