Setting Transaction Isolation Level

[eriksendc]

Hi All,

I'm using EF7 (rc1-final). The application I'm working on does some querying that really doesn't need to be done read committed. Dirty reads would suffice, but not always.

Our application started out as a standard ASP.NET 5 one that injects a DbContext anywhere we need it by putting a parameter on the public constructor (of, say, a service or controller). EF is configured in Startup.cs using the following boilerplate code:

// Add Entity Framework services to the services container.
services.AddEntityFramework()
    .AddSqlServer()
    .AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(Configuration["Data:DefaultConnection:ConnectionString"]));

What is the EF team's recommendation on affecting a change in the isolation level? I know I can create a System.Data.SqlClient.SqlConnection and pass it to UseSqlServer() instead of passing the connection string, but there isn't an isolation level property directly on SqlConnection as far as I can tell. It does have a BeginTransaction() method, but I don't want to add transaction overhead... I'm trying to reduce overall overhead by doing uncommitted reads. And I don't understand how calling BeginTransaction in Startup.cs would be the right thing to do, anyways.

I was thinking I could derive another class from ApplicationDbContext, (ReadOnlyApplicationDbContext) and basically add nothing to it. Just wire it up to the ASP.NET app by chaining in another call to AddDbContext. If I had a way to configure all db access via ApplicationDbContext as read committed, and all db access via ReadOnlyApplicationDbContext as read uncommitted, I could simply inject one or the other into a particular service or controller depending on the kind of processing I was expecting to do.

Another thought I had was that if I could set the connection's transaction isolation level to read uncommitted, then, in the case where I needed read committed, if there was something analogous to the TransactionScope object, I could use that to escalate from read uncommitted to read committed (at least the old TransactionScope object used to allow that, I think).

I'm happy to dig / research more myself, but looking through things I'm just not figuring out a way to get started (or to get any further).

Thanks in advance!

Regards,
-Brian Eriksen

[divega]

@eriksendc Sorry for the very long delay in answering your question. Currently the simplest way to do this is to open the connection and to issue a SQL command to set the isolation level, e.g.:

    context.Database.EnsureCreated();
    context.Database.OpenConnection();
    context.Database.ExecuteSqlCommand(
        "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");

    // now do some work in read uncommitted

Note that EF Core normally closes and re-opens the connection automatically between database operations but you can override that by opening the connection explicitly like in this example. Once you do that the connection will remain open (and the isolation level you have set will remain in force) until you close the connection explicitly or until the context is disposed.

In the future we may support other ways of doing this, e.g. we have been talking about the ability to register code that executes any time the connection is open by EF, but still if we do you may prefer to avoid the extra database round-trips and open the connection explicitly anyway.

Also note that while you may choose to do this every time a DbContext instance is created or configured, but this has a few disadvantages, e.g.:

1. You won't be able to use the async version of the APIs
2. You may end up performing unnecessary work/database round-trips if e.g. the DbContext got injected into the controller but the controller action executed in a particular request ended up not needing to perform any data access.

For that reason I would recommend just waiting until the last chance and only opening the connection and executing the command when you actually know you will be using the database.

Finally, we don't support TransactionScope or any other feature of System.Transactions in EF Core. This is something we may reconsider in the future (see #5595) but for the time being we only use local database transactions.

[divega]

Closing because I believe I have answered the question but feel free to reopen or create a new issue of something isn't clear.

[codearoo]

Hi.. Why do I not see commands like "ExecuteSqlCommand" or "OpenConnection" or "EnsureCreated"?
When I see my context class that was created from the db scaffolding, it's "Database" member is a "DatabaseFacade" which implements IInfrastructure and all it has is this:

public virtual IDbContextTransaction BeginTransaction();
public virtual Task BeginTransactionAsync(CancellationToken cancellationToken = default(CancellationToken));
public virtual void CommitTransaction();
public virtual bool EnsureCreated();
public virtual Task EnsureCreatedAsync(CancellationToken cancellationToken = default(CancellationToken));
public virtual bool EnsureDeleted();
public virtual Task EnsureDeletedAsync(CancellationToken cancellationToken = default(CancellationToken));
public virtual void RollbackTransaction();

[codearoo]

OH! I just tried typing it and using VS2015's quick fixes and it included the
using Microsoft.EntityFrameworkCore;
and now I see that stuff!

[jemiller0]

I'm using Entity Framework to query a table that has close to 8 million rows in it. I'm using .NoTracking() and I'm not calling .ToArray() or .ToList() because I want to stream the data and not load everything into memory. I'm iterating over the results using a foreach statement. Also, I'm using a separate DbConnection with a prepared command to execute UPDATE statements to update the rows that I'm iterating over. The reason I'm using the second connection and prepared statements is for performance. I'm running into a problem where the connection is timing out. My guess is that the problem is a deadlock between the connection with the query on it that EF is using and the second connection that I'm using for the UPDATE statements. However, the exception being thrown doesn't say that it's a deadlock. It is a connection timeout. As far as I know, EF is using SQL Server's default isolation level of READ COMMITTED? I thought I read a blog post stating that the default changed from EF to EF Core to make locking less of an issue. However, I don't recall exactly where I read that. I would prefer to use EF Core, but, I ran into a different problem where it appears to not be streaming the data and is reading the entire result set into memory. It's only doing that with the SQL Server provider. I submitted a separate bug report on that. Can anyone recommend how I can get this to work? I.e. iterate over a large result set, not reading everything into memory and at the same time, have the ability to execute UPDATEs as I go along? Also, I tried using the same connection that EF is using for my prepared statements, but, I received an error about other threads using the session. I'm assuming this is because it is not valid to execute another command while a reader is open. I think that error happened when I had MARS enabled. Also, worth noting is that I'm able to do the UPDATEs usign MySQL just fine. I have the default isolation level set to READ COMMITTED in my MySQL server. I'm wondering if I should do something like open the DbConnection explicitly, setting the isolation level, possibly to something like SNAPSHOT and pass that in to EF?

[jemiller0]

Hmmm, I just tried setting it to READ UNCOMMITTED like divega mentioned above and it worked. So, I guess it is a locking issue. Although, I don't want to do dirty reads. Not sure what the best way to do this is. I guess the query has a row-level lock on the current row, so the UPDATE won't work on the other connection.

[divega]

@jemiller0 just to address some of your specific questions:

As far as I know, EF is using SQL Server's default isolation level of READ COMMITTED? I thought I read a blog post stating that the default changed from EF to EF Core to make locking less of an issue.

The default for current versions of EF6 is the same as EF Core: at database creation time we set READ_COMMITTED_SNAPSHOT to minimize locking and deadlocks for the default isolation level READ COMMITTED. This is a database-level setting and won't be used unless you create the database using Code First/Migrations (it is also worth noting that if you are using Azure SQL DB this setting is not necessary to achieve the same locking behavior).

Also, I tried using the same connection that EF is using for my prepared statements, but, I received an error about other threads using the session. I'm assuming this is because it is not valid to execute another command while a reader is open. I think that error happened when I had MARS enabled.

AFAIR this occurs because even with MARS enabled it is not legal to start a new transaction on a SqlConnection while any reader is open. Using a separate connection (and/or a separate DbContext instance) for updates is a reasonable workaround.

[divega]

@jemiller0 my answers above do not explain why you are getting timeouts, but if you happen to know the database was not created using any version of EF you may want to try setting READ_COMMITTED_SNAPSHOT on it yourself as a possible mitigation. I recommend you do read the documentation to understand the implications.

[jemiller0]

Thanks @divega, I greatly appreciate your response. That is very helpful to know about READ_COMMITTED_SNAPSHOT. I did some reading up on that last night. It's amazing I've made it this far through my career without thoroughly reading into the isolation levels. I was planning on setting that in my databases by default from now on. I tested it last night and it cured the problem I've been having. I need to read up on what kind of negative performance impacts enabling that might be. The documentation mentioned it writing extra information to tembdb, so, I'm unsure if that would slow things down at all. I figured there must be some kind of gotcha to it, or it would be enabled by default in SQL Server. But, if EF Core enables it by default, that makes me more comfortable doing it myself as I'm assuming you guys know more than me about that. Until recently, I was using EnsureCreated() to generate my database. So, I may not have ran into that problem. But, last week I started generating the schema myself so that I could generate the column ordering in the proper order and so that I could set some extra properties on the CREATE TABLE statements for MySQL. I've seen exceptions thrown at times where it mentions that a deadlock occurred. I don't recall if that was with SQL Server, or MySQL. In this most recent case, it just timed the connection out and didn't say anything about a deadlock. I don't know if you would officially call it a deadlock, but, the problem did appear to have to do with lock contention. I'm going to read up more on the ramifications of enabling READ_COMMITTED_SNAPSHOT and do some test loads of a decent sized database that I have. If I don't find any problems with it, I plan on enabling that on my databases by default. The fact that EF Core sets that on the database by default makes me more confident in doing that. So, I greatly appreciate you telling me about that.

[he-dev]

@codearoo you need to install the Microsoft.EntityFrameworkCore.Relational package that contains the necessary extensions. See also RelationalDatabaseFacadeExtensions.ExecuteSqlCommand Method

[stevendarby]

@divega if I open the connection manually and run the SET TRANSACTION ISOLATION LEVEL statement, the logs still state that the following transactions are begun with the default ReadCommitted isolation level. Is this accurate, or is it simply opening transactions and the logger is assuming that they’re the default because an isolation level wasn’t provided for that transaction?

At the moment, after running that statement I’m also calling context.Database.BeginTransaction in order to specify the isolation level. This gives me some peace of mind because the logs now state the transactions are begun with my specified isolation level. But it means I have to manage the transaction commit and ideally I would like to just leave that to the framework.

Is there any plan to simply be able to set the default isolation level so the framework does this all for you?

[divega]

@Snappyfoo in EF Core 3.0 you could set the isolation level in the ConnectionOpened or ConnectionOpenedAsync of a DbConnectionInterceptor (see https://devblogs.microsoft.com/dotnet/announcing-entity-framework-core-3-0-preview-7-and-entity-framework-6-3-preview-7/#other-major-new-features for a very brief intro to interception in EF Core) to make sure every connection opened automatically by EF Core will have the isolation level you want. Note that setting the isolation level will require an additional server roundtrip every time the connection is opened. You can combine this with open the connection explicitly to avoid some roundtrips.

Re EF Core's logging not recording the actual isolation level in force, I don't know of a reason to change this, but maybe @AndriySvyryd will know.

[stevendarby]

@divega thanks, the connection interceptor seems like a good addition, but given it’s still recommended to open the connection manually to avoid round trips, arguably in this case it’s still simpler to run the isolation statement after the manual open, especially if the lines are wrapped in a public method on a base context class as I have done.

I suppose my main concern now is how sure I can be that this isolation level is respected given that the logging suggests otherwise. I’m not clear from your comment what you mean by not knowing ”of a reason to change this” - change what? The logging? Wouldn’t a reason be the one I’ve given, i.e. the fact it’s confusing? Sorry if I’ve misunderstood.

I suppose what I was actually asking before is if there any plans to provide an abstracted way to set the isolation level, because then EF Core would be aware of the level and perhaps be able to manage transactions and logging better around that. With the current method of running raw SQL I’m not surprised there is some uncertainty around the behaviour.

If you happen to know of a way to check the isolation level actually used, perhaps on the SQL Server side, that would be great, but I appreciate that discussion is probably moving away from EF Core.

[divega]

@Snappyfoo You can use the connection interception approach without manually opening the connection. Think about opening the connection manually as an optimization. Being able to open the connection in a desired transaction isolation level without having an extra database round-trip requires special support from the database driver...

Which leads me directly to another option: creating a System.Transactions.TransactionScope with the desired isolation level before you perform operations using EF Core.

I just realized that my only mention of this approach in this thread was 3 years ago, when EF Core still didn't have support for System.Transactions, so I had to say that then it wasn't an option, but now it actually is.