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
Based on official documentation SaveChanges() applies as a transaction
Default EF Core transaction isolation level is set to default value for the database server. In SQL Server it is READ COMMITTED
Use explicit transactions when you need several SaveChanges() or you need to use different IsolationLevel but not a default one.
Each Entity has EntityState to track all changes inMemory before we call SaveChanges()
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:
What is going on behind the scene in the runtime when we go though these 3 lines of code?
When is DB actually called and what does an actual SQL request look like?
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:
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:
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:
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:
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.
EF Core runs select statements immediately if you use something like this: var a = dbContext.Accounts.First(a => a.Id == 1);
EF Core runs create, update, delete sql scripts only when SaveChanges() is called;
SaveChanges() applies as a transaction (if we don’t wrap it with explicit transaction)
EF Core default isolation level is the level which is set on DB provider side. Usually Read Commited.
EntityState is used to track Entities changes in memory before SaveChanges() is called.
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