Data consistency in Entity Framework Core

What are we going to discuss?

  • A little bit of theory about data consistency and sql transactions.
  • How does EF Core converts our code into SQL queries and transactions?
  • What happens with data consistency when we combine several operations with single SaveChange()?
  • When should we use explicit transactions in EF Core?
  • and something else

Theory part

What is transactions

Some well known SQL transaction statements:

  • Begin transaction;
  • Commit transaction;
  • Rollback transaction;

Properties of database transactions (ACID)

  • Atomic — all or nothing
  • Consistent — no constraints violated
  • Isolation — sessions don’t effect each other
  • Durable — once data is committed, it is permanent

“A” and “D” are more or less simple to understand and implemented out of the box by DB server. But “C” and “I” requires more developers attention because this is our responsibility to implement that correctly. In this article we would like to cover “C “— consistency. “I” is out of scope now (I will try to cover that in one of my future articles and to add a link here)

Why transaction can fail?

  • Constrains violated
  • Datatype mismatch
  • etc

Examples of SQL constrains

  • NOT NULL - Ensures that a column cannot have a NULL value
  • UNIQUE - Ensures that all values in a column are different
  • PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
  • FOREIGN KEY - Prevents actions that would destroy links between tables
  • CHECK - Ensures that the values in a column satisfies a specific condition
  • DEFAULT - Sets a default value for a column if no value is specified
  • CREATE INDEX - Used to create and retrieve data from the database very quickly

We can start SQL transaction in one of these modes

  • Autocommit transactions
  • Explicit transactions
  • Implicit transactions
  • Batch-scoped transactions (Out of scope for us here)

SQL Practice part

I will be using SQL Server and SSMS here. My current SSMS version

My current SQL Server version (to check yours run “Select @@version”):

Preconditions:

  • You should have SQL server
  • Create database called FishingLog
  • Download my github repo
  • Run EF Core data migration
  • Run the DatabaseTransactions console app. By default Program.cs entry point should have only these methods uncommented
  • everything else should be commented
  • We are all set!

Now Lets take a closer look on each transaction mode

Here is mind map I created to understand transactions mode and data consistency in EF Core.

Mind map: Data consistency in EF Core

JFYI — To view the current setting for IMPLICIT_TRANSACTION run this SQL

  • Autocommit transactions: Each individual statement is a transaction

Run this SQL

Result should be:

Why did we get this error? Because we are in autocommit mode and each statement is a transaction which is commuted right after SQL statement finishes

  • Implicit transactions: A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.

Run console app to apply data seed and then run this SQL

Result

Data before we’ve run our script:

And after:

Summery: We can run rollback successfully and both update statements were rolled back. Please notice that we did not execute BEGIN TRANSACTION explicitly. That is how implicit mode works.

  • Explicit transactions: Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.

Run console app to apply data seed and then run this SQL

Data before update

And after

Summery: I intentionally have set autocommit mode in the beginning to demonstrate that “begin transaction“ overrides it.

EF Core part

Theory

  1. Based on official documentation SaveChanges() applies as a transaction
  2. Default EF Core transaction isolation level is set to default value for the database server. In SQL Server it is READ COMMITTED
  3. Use explicit transactions when: need several SaveChanges(), need to use different IsolationLevel but not default one.
  4. Each Entity has EntityState to track all changes inMemory before we call SaveChanges()
  5. EF Core runs sql via sp_executesql extended storage procedure (such sp can be only in master db). Located under master -> Programmability → Extended Stored Procedures → System Extended Stored Procedures. Selects statement could be executed directly (without sp)

Practice

  • Go to DatabaseTransactions project Program.cs
  • Uncomment SingleUpdateWithSaveChanges method and run application

Main questions here are

  1. What is going on behind the scene in runtime when we go though these 3 lines of code?
  2. When DB is actually called and how does an actual SQL request look like?
  3. Is it wrapped via transaction and how?

Lets run our app in Debug->Performance Profiler->Database mode (Select Target as a Startup project)

But before clicking start I also would like to use SQL Server Profiler to doublecheck what are actual SQL queries that come to SQL server.

Go to SSMS -> Tools -> SQL Server Profiler -> Connect to our DB and run new trace.

Now we click start in VS Performance profiler. Here is the result:

Some interesting finding we observe here:

  • We don’t see any “Begin transaction” here
  • We can see “TransactionCommitted” word here
  • EF Core update operation is displayed as this SQL Code
  • There is no “TransactionCommitted” word after our update operation

After this info I got even more questions than answers. So lets take a look on a result of SQL Profiler:

Some interesting findings we observe here:

  • When EF Core established a connection some SQL parameters were set. Please pay attention to 2 of them which I pointed with red arrows:

set implicit_transactions off — means that transaction autocommit mode was set.

set transaction isolation level read committed — isolation levels are out of scope here but I added this JFYI to know why EF Core uses “read committed” isolation level by default.

  • Select statement was executed as a SQL select statement

Now lets investigate EF Core logs. First make sure you enabled them:

A little bit of theory: How does EF Core Modified Entity State behave?

This is what we can see in logs for this piece of code:

Some of logs (this is not a full list, some of logs I skipped as they are not so important in out case):

  • SaveChanges starting for ‘FishingLogDbContext’.
  • DetectChanges starting for ‘FishingLogDbContext’
  • The unchanged property ‘Account.Name’ was detected as changed and will be marked as modified.
  • An entity of type ‘Account’ tracked by ‘FishingLogDbContext’ changed state from ‘Unchanged’ to ‘Modified’.
  • DetectChanges completed for ‘FishingLogDbContext’
  • Opening connection to database ‘FishingLog’ on server ‘localhost’
  • Began transaction with isolation level ‘ReadCommitted’
  • [some logs to run SQL]
  • Committing transaction…
  • Closing connection..

Now you can continue and investigate these for remaining methods:

Just uncomment them one by one, run the program and then check SQL Server Profiler logs and EF Core console logs (as we did for SingleUpdateWithOneSaveChanges method).

If you don't want to do it by your own you can check my video here where I covered and tried to explain all examples:

Summary:

  1. In most cases there is no need to explicit EF Core BeginTransaction. It could help only if you need some other isolation level (not SQL default one)or you want to combine several SaveChanges into single transaction.
  2. EF Core runs select statements immediately if you use something like this var a = dbContext.Accounts.First(a => a.Id == 1);
  3. EF Core runs create, update, delete sql scripts only when we call SaveChanges();
  4. SaveChanges() applies as a transaction (if we dont wrap it with explicit transaction)
  5. EF Core default isolation level is the level which set on DB provider side. Usually Read Commited.
  6. EntityState is used to track Entities changes in memory before SaveChanges() is called.
  7. Despite the fact that EF Core uses autocommit transaction mode by default — it overrides that behavior because each SaveChanges() act as a transaction.

Some useful links

How does EF Core Modified Entity State behave?

My repo with code samples

.Net technical leader