Welcome to today’s post.
In today’s post I will be discussing how to execute database queries and transactions and maintain concurrency within a data context.
In applications that are worker services or web API methods we will encounter situations where requests to a method are running concurrently, and in contention to access common resources such as database queries that share a common data context.
A database server with resources such as a table, supports multiple reads concurrently, however multiple writes are not supported. Moreover, an object relational mapping provider such as Entity Framework Core supports one concurrent SQL request per data context.
Explaining Data Context Service Lifetimes
When we configure a SQL data context within Entity Framework Core within a .NET Core application, the recommended lifetime for each dependency that uses a data context is scoped.
The reason why a data context has a scoped lifetime is so that the data context instance is maintained during the request, including when it is injected into every dependent class or middle ware that is created within the request. The data context instance is disposed and goes out of scope once the request completes. Refer to one of my previous posts where I discussed the differences between singleton, transient and scoped lifetimes.
Below is the recommended configuration for a data context within a worker process service that can have multiple threads running each with its own data context instance within a shared worker process:
services.AddDbContext<ApplicationDbContext>(options =>
options.UseSqlServer(config.GetConnectionString("AppDbContext")
), ServiceLifetime.Transient, ServiceLifetime.Scoped);
In the above we have the context lifetime set to Transient, which means the data context instance is different for each worker thread. The connection option lifetime is Scoped and is the same for each request.
As I will show later, the custom data service is injected with the application data context which is transient scoped:
public class CustomDBService: ICustomDbService
{
private readonly ILogger _logger;
private readonly ApplicationDbContext _db;
...
In the following example case, we have a background queue work item that is executed periodically.
public void QueueBackgroundWorkItem(
Func<CancellationToken, Task> workItem)
{
if (workItem == null)
{
throw new ArgumentNullException(nameof(workItem));
}
_workItems.Enqueue(workItem);
_logger.LogInformation(
$"Enqueue(): Number of Queued Items = {_workItems.Count}.");
_customDbService.RetrieveNextAuditEntries();
_signal.Release();
}
In the next section, I will show how the above task within a polled worker thread can fail due to data concurrency access.
When Data Concurrency Errors will Occur
In most cases we would not be concerned with concurrency, however if the polling interval is reduced to as low as 1-3 seconds, then the duration of the call to the custom data service can cause a backlog of requests to accrue with the call:
_customDbService.RetrieveNextAuditEntries();
Should this situation occur, then we observe an error from Entity Framework Core that resembles the following:
Microsoft.EntityFrameworkCore.Query: Error: An exception occurred while iterating over the results of a query for context type 'BackgroundTasksSample.Data.ApplicationDbContext'.
System.InvalidOperationException: A second operation started on this context before a previous operation completed. This is usually caused by different threads using the same instance of DbContext. For more information on how to avoid threading issues with DbContext, see https://go.microsoft.com/fwlink/?linkid=2097913.
at Microsoft.EntityFrameworkCore.Internal.ConcurrencyDetector.EnterCriticalSection()
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
The above error will occur in the scenario when the have multiple request threads attempting to run queries within the same instance of DB context.
When we use the above dependency-injected data context, the data context instance is re-used across different threads that run concurrently. This causes a critical section error on the Entity Framework Core library.
A more detailed screenshot of the concurrency error is shown below:
In the next section, I will show an example of when the SQL concurrency error will occur and an example of how to avoid it.
How we Avoid Data Concurrency Errors
To avoid this situation, running the shared queries in different threads will have data contexts that are created in their own scope.
In web API applications, each HTTP request call a web API method has its own thread, and the equivalent data context instance is injected into the service collection objects with a unique data context instance. In this case, different requests into an API method that accesses data context queries is thread safe with select queries but requires to be gated with the await keyword for CRUD operations (insert, update and delete).
Even use of the async keyword to run parallel queries or using the task parallel library Task.Run commands that run in a background thread do not protect data from invalid concurrent access. The following command sequence will give the above concurrent data context access error:
var taskResult = Task.Run(() =>
{
_customDbService.RetrieveNextAuditEntries();
return true;
}).Wait(10000);
if (taskResult)
_logger.LogInformation(
$"RetrieveNextAuditEntries(): Completed Successfully.");
else
_logger.LogInformation(
$"RetrieveNextAuditEntries(): Completed UnSuccessfully.");
The Wait() command does not block another concurrent thread from accessing the same RetrieveNextAuditEntries() method within the _customDbService instance.
However, the following will work as expected, maintaining consistency and serialization of the data base calls:
var taskResult = Task.Run(() =>
{
_customDbService.RetrieveNextAuditEntries();
return true;
}).GetAwaiter();
taskResult.OnCompleted(() =>
{
_logger.LogInformation(
$"RetrieveNextAuditEntries(): Completed Successfully.");
});
This is because the GetAwaiter() method forces current task commands within the Task.Run() delegate block to wait and block all subsequent calls until the commands complete.
In our console output, we will see the following sequence of calls showing each request being executed and then completed. Below is the log corresponding to the record retrieval request commence within the Task.Run() block:
info: BackgroundTasksSample.Services.CustomDBService[0]
RetrieveNextAuditEntries() .. start-date=4/07/2020 11:55:57 PM, end-date=4/07/2021 11:55:57 PM.
info: BackgroundTasksSample.Services.CustomDBService[0]
RetrieveNextAuditEntries() .. start-date=4/07/2020 11:56:57 PM, end-date=4/07/2021 11:56:57 PM.
The next part of the console output corresponds to the completion of the SQL command logged in the OnCompleted() delegate block:
info: BackgroundTasksSample.Services.BackgroundTaskQueue[0]
RetrieveNextAuditEntries(): Completed Successfully.
info: BackgroundTasksSample.Services.BackgroundTaskQueue[0]
RetrieveNextAuditEntries(): Completed Successfully.
With the parametrized form of actual completed SQL command logged as shown:
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (5ms) [Parameters=[@__startDateTime_0='?' (DbType = DateTime2), @__endDateTime_1='?' (DbType = DateTime2)], CommandType='Text', CommandTimeout='30']
SELECT [l].[ID], [l].[Country], [l].[UserLogin], [l].[WhenLoggedIn]
FROM [LoginAudits] AS [l]
WHERE ([l].[WhenLoggedIn] >= @__startDateTime_0) AND ([l].[WhenLoggedIn] < @__endDateTime_1)
The following sequence of commands is equivalent to the above:
var taskResult = Task.Run(async () =>
{
await _customDbService.RetrieveNextAuditEntries();
return true;
}).GetAwaiter();
taskResult.OnCompleted(() =>
{
_logger.LogInformation(
$"RetrieveNextAuditEntries(): Completed Successfully.");
});
The async keyword within the Task.Run() ensure the commands within the block run asynchronously. It usually has benefits if there are numerous commands that require being run concurrently, however the await call waits for the current thread to complete the data request and protect the integrity of the database resource being accessed.
Testing Concurrency Access of the Custom Data Service
To thoroughly test a concurrent request, place a breakpoint on the target line.
Then run the application.
Wait until the breakpoint is hit several times by multiple processes.
Now remove the breakpoint and let the application run.
Check the console output for errors. If there are no errors, this means a unique data context is used to access the database for different threads. If there are errors, then we know the data context is the same across different threads, causing the access violation.
To avoid the critical section error with Entity Framework Core, I create another version of the data service, the difference being how the data context is instantiated and disposed of.
I will show how this instantiation is implemented in the next section.
Instantiation of the Data Service with IServiceProvider
Below is the custom data class code properties constructor section:
public class CustomScopedDBService: ICustomScopedDbService
{
private readonly ILogger _logger;
private readonly IServiceProvider _serviceProvider;
private int _result;
public int GetResult => _result;
public CustomScopedDBService(
IConfiguration configuration,
ILogger<CustomDBService> logger,
IServiceProvider serviceProvider)
{
this._logger = logger;
this._serviceProvider = serviceProvider;
}
…
What I have changed from the other custom data service is the use of the IServiceProvider instead of the dependency injected data context ApplicationDbContext, which is normally injected with a Scoped lifetime within startup and with the AddDbContext extension method.
In our custom data method, we use the IServiceScopeFactory service to create the scope for the data context as shown:
public async Task<List<Models.LoginAuditViewModel>> RetrieveNextAuditEntries()
{
DateTime startDateTime = DateTime.Now.AddYears(-1);
DateTime endDateTime = DateTime.Now;
using (var serviceScope = _serviceProvider
.GetService<IServiceScopeFactory>()
.CreateScope())
{
var _db = serviceScope
.ServiceProvider
.GetRequiredService<ApplicationDbContext>();
_logger.LogInformation(
$"RetrieveNextAuditEntries(): Db Context = {_db.ContextId}.");
Console.WriteLine(
"Running RetrieveNextAuditEntries() ..in Background Worker Service.");
_logger.LogInformation(
$"Running RetrieveNextAuditEntries() ..in Background Worker Service.");
Console.WriteLine($"RetrieveNextAuditEntries() .. start-date={startDateTime.ToString()}, end-date={endDateTime.ToString()}.");
_logger.LogInformation($"RetrieveNextAuditEntries() ..
start-date={startDateTime.ToString()}, end-date={endDateTime.ToString()}.");
var auditEntries = _db.LoginAudits
.Where(a =>
a.WhenLoggedIn >= startDateTime && a.WhenLoggedIn < endDateTime)
.ToList();
return auditEntries;
}
}
We then call the above scoped data service from within our background task:
private readonly ICustomDbService _customDbService;
private readonly ICustomScopedDbService _customScopedDbService;
…
public BackgroundTaskQueue(
ILogger<BackgroundTaskQueue> logger,
ICustomDbService customDbService,
ICustomScopedDbService customScopedDbService)
{
_customDbService = customDbService;
_customScopedDbService = customScopedDbService;
_logger = logger;
}
public void QueueBackgroundWorkItem(
Func<CancellationToken, Task> workItem)
{
if (workItem == null)
{
throw new ArgumentNullException(nameof(workItem));
}
_workItems.Enqueue(workItem);
_logger.LogInformation(
$"Enqueue(): Number of Queued Items = {_workItems.Count}.");
var taskResult = Task.Run(async () =>
{
await _customScopedDbService.RetrieveNextAuditEntries();
return true;
}).GetAwaiter();
taskResult.OnCompleted(() =>
{
_logger.LogInformation(
$"RetrieveNextAuditEntries(): Completed Successfully.");
});
…
}
In the next section, I will show how the data context identifiers, ContextId, vary for each data service instantiation within the background task.
Observing Data Context Uniqueness within the Instantiated Data Service
When we run the application again and observe the data contexts being output below:
BackgroundTasksSample.Services.CustomDBService: Information: RetrieveNextAuditEntries(): Db Context = 7c82b4ae-f936-4ebe-8b7a-3935464f62f5:0.
…
BackgroundTasksSample.Services.CustomDBService: Information: Running RetrieveNextAuditEntries() ..in Background Worker Service.
BackgroundTasksSample.Services.CustomDBService: Information: RetrieveNextAuditEntries() .. start-date=6/07/2020 2:30:47 AM, end-date=6/07/2021 2:30:47 AM.
Microsoft.EntityFrameworkCore.Infrastructure: Information: Entity Framework Core 3.1.11 initialized 'ApplicationDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
BackgroundTasksSample.Services.CustomDBService: Information: Running
Microsoft.EntityFrameworkCore.Infrastructure: Information: Entity Framework Core 3.1.11 initialized 'ApplicationDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
BackgroundTasksSample.Services.BackgroundTaskQueue: Information: RetrieveNextAuditEntries(): Completed Successfully.
…
BackgroundTasksSample.Services.CustomDBService: Information: RetrieveNextAuditEntries(): Db Context = 9a1efb31-4b16-4a9b-8ab2-1fb8f8b42244:0.
BackgroundTasksSample.Services.CustomDBService: Information: Running RetrieveNextAuditEntries() ..in Background Worker Service.
Notice that within each call to RetrieveNextAuditEntries(), the data context ID changes with a new data context instance:
Context = 7c82b4ae-f936-4ebe-8b7a-3935464f62f5:0.
Context = 9a1efb31-4b16-4a9b-8ab2-1fb8f8b42244:0.
After each scope is created, the following output shows the initialization of the Entity Framework Core:
Microsoft.EntityFrameworkCore.Infrastructure: Information: Entity Framework Core 3.1.11 initialized 'ApplicationDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
With the creation of a different data context for each request, the ability to synchronize the data contexts across multiple threads now works without synchronization errors.
You have seen how to use Entity Framework data contexts across single threads and multiple threads without causing threading conflicts. You have also seen the scenarios where the data context is not thread safe and could lead to potential data corruption.
That is all for today’s post.
I hope you found this post useful and informative.
Andrew Halil is a blogger, author and software developer with expertise of many areas in the information technology industry including full-stack web and native cloud based development, test driven development and Devops.
