0

I don't use transactions in my C# .NET Core v3.1 with EFCore v3 code explicitly and all works fine. Except for my Azure Webjob. It listens to a queue. When multiple messages are on the queue and thus the function gets called multiple times in parallel I get transaction errors.

My webjob reads a file from the storage and saves the content to a database table. I also use the Sharding mechanism: each client has its own database.

I tried using TransactionScope but then I get other errors. Examples I found use the TransactionScope and opening the connection and doing the saving in one method. I have those parts split into several methods making it unclear to me how to use the TransactionScope.

Here's some code:

ImportDataService.cs:
  //using var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
  await using var tenantContext = await _tenantFactory.GetContextAsync(clientId, true);
  await tenantContext.Foo.AddRangeAsync(dboList, cancellationToken);
  await tenantContext.SaveChangesAsync(cancellationToken);
  //scope.Complete();

TenantFactory.cs:
public async Task<TenantContext> GetContextAsync(int tenantId, bool lazyLoading = false)
{
    _tenantConnection = await _sharding.GetTenantConnectionAsync(tenantId);
    var optionsBuilder = new DbContextOptionsBuilder<TenantContext>();
    optionsBuilder.UseLoggerFactory(_loggerFactory);
    if (lazyLoading) optionsBuilder.UseLazyLoadingProxies();
    optionsBuilder.UseSqlServer(_tenantConnection,
        options => options.MinBatchSize(5).CommandTimeout(60 * 60));
    return new TenantContext(optionsBuilder.Options);
}

This code results in SqlConnection does not support parallel transactions.
When enabling TransactionScope I get this error: This platform does not support distributed transactions.

In my ConfigureServices I have

services.AddSingleton<IImportDataService, ImportDataService>();
services.AddTransient <ITenantFactory, TenantFactory>();
services.AddTransient <IShardingService, ShardingService>();

I also tried AddScoped but no change.

Edit: Additional code

ShardingService.cs
public async Task<SqlConnection> GetTenantConnectionAsync(int tenantId)
{
    SqlConnection tenantConnection;
    try
    {
        tenantConnection = await _clientShardMap.OpenConnectionForKeyAsync(tenantId, _tenantConnectionString, ConnectionOptions.Validate);
    }
    catch (Exception e)
    {
        _logger.LogDebug($"Error getting tenant connection for key {tenantId}. Error: " + e.Message);
        throw;
    }

    if (tenantConnection == null) throw new ApplicationException($"Cannot get tenant connection for key {tenantId}");
    return tenantConnection;
}

When the WebJob gets triggered it reads a record from a table. The ID of the record is in the queue message. Before processing the data it first changes the status to processing and when the data is processed it changes the status to processed or error:

var fileImport = await _masterContext.FileImports.FindAsync(fileId);
fileImport.Status = Status.Processing;
await _masterContext.SaveChangesAsync();

if (await _fileImportService.ProcessImportFile(fileImport))
                    fileImport.Status = Status.Processed;

await _masterContext.SaveChangesAsync();
Paul Meems
  • 3,002
  • 4
  • 35
  • 66
  • Can you post the code in `GetTenantConnectionAsync`? Also, what is the lifetime of the service containing the code you've posted above? – DvS Nov 17 '20 at 14:36
  • From what it sounds like, you have another set of code which is making a query to you database in an async fashion which. Entity framework has a limitation which doesn't allow code to be executed in parallel or fully async (e.g ef will free the thread but another request cannot be made using the same context ) – johnny 5 Nov 17 '20 at 14:47
  • I've updated my post. I'm not sure how to get the lifetime of the service @DvS is requesting. – Paul Meems Nov 17 '20 at 15:09
  • By lifetime I meant verifying transient, scoped, etc.but also to make sure you don't have a 'captive dependency' (https://blog.ploeh.dk/2014/06/02/captive-dependency/) which might result in a connection being shared, which means multiple transactions will be started on it before the original one has been committed or rolled back. The latter is the root of your issue, but may not be due to a captive dependency. It's likely because you're using async and the lifetime of the connection along your code path is not being correctly managed. – DvS Nov 18 '20 at 10:58

0 Answers0