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
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 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
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
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
- 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”):
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)
- 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.
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
Data before we’ve run our script:
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
Summery: I intentionally have set autocommit mode in the beginning to demonstrate that “begin transaction“ overrides it.
EF Core part
- 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: need several SaveChanges(), need to use different IsolationLevel but not 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). Located under master -> Programmability → Extended Stored Procedures → System Extended Stored Procedures. Selects statement could be executed directly (without sp)
- Go to DatabaseTransactions project Program.cs
- Uncomment SingleUpdateWithSaveChanges method and run application
Main questions here are
- What is going on behind the scene in runtime when we go though these 3 lines of code?
- When DB is actually called and how does an actual SQL request look like?
- 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:
- 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.
- 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 we call SaveChanges();
- SaveChanges() applies as a transaction (if we dont wrap it with explicit transaction)
- EF Core default isolation level is the level which 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 behavior because each SaveChanges() act as a transaction.
Some useful links