top of page

Data consistency in Entity Framework Core

Theoretical

What is a transaction?

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.


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 easy to understand and are implemented out of the box by DB server. But “C” and “I” require more attention because this is our responsibility to implement them correctly. In this article, I will cover “C “— consistency.


Why can a transaction 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 a SQL transaction in one of these modes

  • Autocommit transactions

  • Explicit transactions

  • Implicit transactions

  • Batch-scoped transactions


SQL Practice

SQL Server and SSMS are used here. My current SSMS version is:


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

Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) — 15.0.2080.9 (X64) Nov 6 2020 16:50:01 Copyright © 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 19042: ) (Hypervisor)

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

  • Everything is set now!


Now Let’s take a closer look at each transaction mode

Here is a mind map I created to understand transaction modes and data consistency in EF Core.


Mind map: Data consistency in EF Core


FYI — This is hot to view the current setting for IMPLICIT_TRANSACTION run this SQL



  • Autocommit transactions: Each individual statement is a transaction

Run this SQL


Result:


Why did we get this error? Because we are in autocommit mode and each statement is a transaction which is committed right after the 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:


After:


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

  • Explicit transactions: Each transaction explicitly starts with BEGIN TRANSACTION statement and explicitly ends with COMMIT or ROLLBACK statement.

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


Data before the update:


After:


Summary: I intentionally 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 you need several SaveChanges() or you need to use different IsolationLevel but not a 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). It is 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


Pay attention to the following questions:

  1. What is going on behind the scene in the runtime when we go though these 3 lines of code?

  2. When is DB actually called and what does an actual SQL request look like?

  3. Is it wrapped via transaction and how?

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

But before clicking “Start” I used SQL Server Profiler to doublecheck what actual SQL queries that come to SQL server are.


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 findings we observe here are:

  • We don’t see any “Begin transaction”

  • We can see “TransactionCommitted”

  • EF Core “update” operation is:

SET NOCOUNT ON; UPDATE [Accounts] SET [Name] = @p0 WHERE [Id] = @p1; SELECT @@ROWCOUNT;
  • There is no “TransactionCommitted” after the “update” operation

After these findings I had even more questions than answers. So let’s take a look at the result of SQL Profiler:



Some interesting findings:

  • When EF Core established a connection some SQL parameters were set. Please, pay attention to those 2 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 FYI to inform you why EF Core uses “read committed” isolation level by default.

  • EF Core executed “select” with such SQL statement



Now let’s investigate EF Core logs. First, make sure you enabled them:




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



Some of logs:

  • 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 (like we did for SingleUpdateWithOneSaveChanges method).

Check my video where all the examples are covered and explained:


Summary:

  1. In most cases there is no need for explicit EF Core BeginTransaction. It helps only if you need some other isolation level (not SQL default one) or want to combine several SaveChanges into a 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 SaveChanges() is called;

  4. SaveChanges() applies as a transaction (if we don’t wrap it with explicit transaction)

  5. EF Core default isolation level is the level which is 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 functionality because each SaveChanges() acts as a transaction.



Source: Medium - Oleg Kikhtov


The Tech Platform

Comentarios


bottom of page