Using isolation level with MSSQL and ADO.Net

 We had a task to start the transaction in Entity Framework Core Middleware and after await _next(context); commit the transaction. In the middle of the process there is an insert into the table and a call to "SaveChanges"

It looks like a very easy task. Unfortunately, it doesn't work. Or should I say it doesn't work out of the box as I expect it. As I mentioned I do an insert into some table. Let's call it "SF". If after "SaveChanges" there is some long-running process and it takes about a minute before we commit the transaction, the "SF" table is blocked also for select!!!

I worked a lot with Oracle DB and this sounds like nonsense for me that select statements can be blocked, but what can I say about the MSSQL database: "Ooops ... we did it again".

The core of the problem is the default isolation level of the database. 

To demonstrate the problem I create a small program that I will attach to the post.

Here is the snapshot of the code

Note that I put "wait" of 1 minute to get a chance to show you the problem. 

When "ExecuteNonQuery" executed meaning that 1 record is inserted into the DB, do a simple select from the table

You will see that "Executing Query" never ends. Not something you expect , because you do expect that select statement will always bring you only committed data.

This is related to the DB isolation level. You are most invited to read about it, but I will focus on how to solve it.

The solution is very easy.

Run on your DB

ALTER DATABASE YOUR_DB SET READ_COMMITTED_SNAPSHOT ON 

WITH ROLLBACK IMMEDIATE;

It will force the DB to return you only the committed snapshot of the data.

Project data is here