Entity Framework Core read un-commited issue

asp.net-core-2.0 c# entity-framework-core

Question

We have a pretty heavy module on one of our internal applications which is using SignalR to check for new notifications. We have been advised that we are creating locks on the database and that we should take an uncommitted read for the method in question.

This is not something I've had to do before, and as far as i'm aware EF by default takes a committed read of records. So i've wrapped the existing query in another using statement like so:

    public IEnumerable<ClientUpdates> GetNotifications(int forPaId)
    {
        using (var transactionScope =
            new TransactionScope(TransactionScopeOption.Required, GetReadUncommitedTransactionOptions()))
        {
            using (var ctx = ContextFactory.CompanyDb)
            {
               // Do stuff

                transactionScope.Complete();

                return objectList;

            }
        }
    }

    private TransactionOptions GetReadUncommitedTransactionOptions()
    {
        var transactionOptions = new TransactionOptions
        {
            IsolationLevel = IsolationLevel.ReadUncommitted
        };

        return transactionOptions;
    }

This results in the following error message:

InvalidOperationException: Warning as error exception for warning 'Microsoft.EntityFrameworkCore.Database.Transaction.AmbientTransactionWarning': An ambient transaction has been detected. Entity Framework Core does not support ambient transactions. See http://go.microsoft.com/fwlink/?LinkId=800142 To suppress this Exception use the DbContextOptionsBuilder.ConfigureWarnings API. ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.

So from that I tried to suppress the warning:

services.AddDbContext<DbContext>(options =>
        {
            options.UseSqlServer(Configuration.GetConnectionString("MyConnectionString"));
            options.ConfigureWarnings(x => x.Ignore(RelationalEventId.AmbientTransactionWarning));
        });

Which results in this error message:

NotSupportedException: Enlisting in Ambient transactions is not supported.

So now I'm a little lost, I can see others have logged this issue on the official issue tracker, but I don't understand what the actual issue is, and how I can force this particular module to do a "Dirty" read of the data, so as to stop the locks happening.

1
2
1/22/2018 2:08:08 PM

Accepted Answer

First, the advice you're getting is a bit antiquated. Since SQL Server 2005, database snapshots have enabled queries without locking. This should be the default, but I suppose it's possible it could have been turned off. To investigate further, see the relevant documentation. Long and short, as long as this is enabled, you don't need to worry about your queries creating locks.

Second, there's a bug in EF Core that currently prevents the code you have from working. However, it has already been fixed, but it's still in upstream (i.e. it hasn't been rolled out yet). Basically, you just can't do this right now, but you soon will be able to. If the first solution doesn't work for, I'd say just wait.

Finally, assuming the first solution doesn't work for you for some reason and you absolutely can't wait for the fix to roll out, you can always simply create one or more stored procs for your queries that utilize NOLOCK. This is of course a more manual endeavor, so I'd start with your heaviest queries first. Profile the query both before and after, and you might also consider doing some load testing, just to ensure you're actually buying yourself something for the effort.

4
1/22/2018 6:26:32 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow