Implicit and Explicit Transactions In Entity Framework – Andrey Zavadskiy

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

opens a connection
starts an implicit transaction
then it generates single T-SQL command for each record to be inserted/updated/deleted
after all it commits the transaction
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.

List<Client> list = new List<Client>
{
    new Client { Name = "Mary" },
    new Client { Name = "Nigel" },
    new Client { Name = "Oliver" }
};

using (var ctx = new ClientContext())
{
    ctx.Database.Log = x => System.Diagnostics.Debug.WriteLine(x);

    ctx.Clients.Add(new Client { Name = "Jeff" });
    ctx.Clients.Add(new Client { Name = "Karla" });
    ctx.Clients.Add(new Client { Name = "Linda" });
    ctx.SaveChanges();

    ctx.Clients.AddRange(list);
    ctx.SaveChanges();
}

List<Client> list = new List<Client>

{

new Client { Name = "Mary" },

new Client { Name = "Nigel" },

new Client { Name = "Oliver" }

};

using (var ctx = new ClientContext())

{

ctx.Database.Log = x => System.Diagnostics.Debug.WriteLine(x);

ctx.Clients.Add(new Client { Name = "Jeff" });

ctx.Clients.Add(new Client { Name = "Karla" });

ctx.Clients.Add(new Client { Name = "Linda" });

ctx.SaveChanges();

ctx.Clients.AddRange(list);

ctx.SaveChanges();

}

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:

using (var ctx = new ClientContext())
{
    ctx.Database.Log = x => System.Diagnostics.Debug.WriteLine(x);

    using (var dbContextTransaction = ctx.Database.BeginTransaction())
    {
        try
        {
            ctx.Clients.Add(new Client { Name = "Jeff" });
            ctx.Clients.Add(new Client { Name = "Karla" });
            ctx.Clients.Add(new Client { Name = "Linda" });
            ctx.SaveChanges();

            ctx.Clients.AddRange(list);
            ctx.SaveChanges();

            dbContextTransaction.Commit();
        }
        catch (Exception)
        {
            dbContextTransaction.Rollback();
        }
    }
}

using (var ctx = new ClientContext())

{

ctx.Database.Log = x => System.Diagnostics.Debug.WriteLine(x);

using (var dbContextTransaction = ctx.Database.BeginTransaction())

{

try

{

ctx.Clients.Add(new Client { Name = "Jeff" });

ctx.Clients.Add(new Client { Name = "Karla" });

ctx.Clients.Add(new Client { Name = "Linda" });

ctx.SaveChanges();

ctx.Clients.AddRange(list);

ctx.SaveChanges();

dbContextTransaction.Commit();

}

catch (Exception)

{

dbContextTransaction.Rollback();

}

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.

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.

SELECT cp.usecounts, cp.cacheobjtype, cp.objtype, t.[text], qp.query_plan
FROM sys.dm_exec_cached_plans cp 
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) t
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE t.[dbid] = DB_ID('Clients');

SELECT cp.usecounts, cp.cacheobjtype, cp.objtype, t.[text], qp.query_plan

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) t

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

WHERE t.[dbid] = DB_ID('Clients');

Leave a Reply Cancel reply