Implicit and Explicit Transactions In Entity Framework

When you call SaveChanges() method on Entity Framework database context, EF makes the following steps:

  1. opens a connection
  2. starts an implicit transaction
  3. then it generates single T-SQL command for each record to be inserted/updated/deleted
  4. after all it commits the transaction
  5. closes connection

Based on my experiment model (I use it through all Quering Entity Framework posts), I’ve created a simple test with two save operations.

The log shows two sequences of operations described above (log content is not included).

Entity Framework also allows to use explicit transactions. They are described in MSDN article Working with Transactions (EF6 Onwards). My code would be transformed to the following:

Now there’s only one transaction.

I’ve noticed a couple of interesting moments.
1. When EF uses an implicit transaction, transaction starts at the moment when you call SaveChanges() method and commits when all commands sent to SQL Server would finish. The connection opens/closes just before and after this implicit transaction.
When you start explicit transaction, EF opens the connection and starts transaction right when you call BeginTransaction() method. Connection is closed when you go out of the scope of explicit transaction, i.e. out of using statement. So don’t make large calculations when the transaction is opened. It would better to have a transaction as short as possible.

2. EF does not use direct T-SQL commands like INSERT or UPDATE. Instead it wraps those commands as a dynamic SQL and executes them with sp_executesql stored procedure.

ef-saves-data

This wrapping creates execution plan on the first insert (SQL Server needs some time and CPU to compile it), and that execution plan would be reused for any subsequent identical inserts. It’s a very good behavor!

You can run the following query to see what’s in the SQL Server plan cache.

ef-execution-plan-reuse

Leave a Reply

Your email address will not be published. Required fields are marked *