Transactions in EF Core: SaveChanges, BeginTransaction and Savepoints

Version note: The code in this post is written for EF Core 6 and runs unchanged on EF Core 10.

Guess First: Was the Order Written?

The method below creates an order, charges the payment and reduces the stock. The payment service throws right in the middle. Before reading on, write down your answer: when the method ends, how many orders are in the database, and what is the stock of Dune?

static async Task PlaceOrder()
{
    using var db = new BookstoreContext();
    var book = await db.Books.SingleAsync(b => b.Title == "Dune");

    var order = new Order { CustomerName = "Ada" };
    order.Items.Add(new OrderItem { BookId = book.Id, Quantity = 2, UnitPrice = book.Price });
    db.Orders.Add(order);
    await db.SaveChangesAsync(); // 1st SaveChanges: the order is written, order.Id is now known

    ChargePayment(order.Id); // what if the payment service throws?

    book.Stock -= 2;
    await db.SaveChangesAsync(); // 2nd SaveChanges: we may never get here
}

static void ChargePayment(int orderId)
{
    // the payment provider did not respond
    throw new InvalidOperationException($"Payment failed for order {orderId}");
}

In the previous post we saw that SaveChanges sends all pending changes in one go. Here there are two separate SaveChanges calls and an exception between them. We will run it shortly and see. That is exactly what this post is about: which pieces of work must either happen completely or not at all, and how EF Core guarantees that with transactions.

The Sample Model and Transaction Logs

We use just enough of the Bookstore model for this post: a Book with stock, an Order with its items, and a Tag with a unique name. We also add a logging setup to the context. We pass LogTo a list of events so it prints only transaction start, commit, savepoints and executed SQL commands. Transaction events are logged at Debug level, so we set the minimum level to LogLevel.Debug.

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.Extensions.Logging;

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public decimal Price { get; set; }
    public int Stock { get; set; }
}

public class Order
{
    public int Id { get; set; }
    public string CustomerName { get; set; } = "";
    public List<OrderItem> Items { get; set; } = new();
}

public class OrderItem
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public int BookId { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
}

public class Tag
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
}

public class BookstoreContext : DbContext
{
    public DbSet<Book> Books => Set<Book>();
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<Tag> Tags => Set<Tag>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseSqlite("Data Source=bookstore.db")
            .LogTo(Console.WriteLine,
                new[]
                {
                    RelationalEventId.TransactionStarted,
                    RelationalEventId.TransactionCommitted,
                    RelationalEventId.CreatedTransactionSavepoint,
                    RelationalEventId.RolledBackToTransactionSavepoint,
                    RelationalEventId.CommandExecuted
                },
                LogLevel.Debug,
                DbContextLoggerOptions.Level);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Tag>().HasIndex(t => t.Name).IsUnique();
    }
}

DbContextLoggerOptions.Level drops the timestamp, category and event id noise from the log and keeps only the level. The package is the usual Microsoft.EntityFrameworkCore.Sqlite. At the start the database holds Dune with 10 copies in stock and one tag named sci-fi.

SaveChanges Is Already a Transaction

First let's see what happens in a single SaveChanges call. We add an order and update the stock at the same time:

using var db = new BookstoreContext();
var book = await db.Books.SingleAsync(b => b.Title == "Dune");

var order = new Order { CustomerName = "Ada" };
order.Items.Add(new OrderItem { BookId = book.Id, Quantity = 2, UnitPrice = book.Price });
db.Orders.Add(order);
book.Stock -= 2;

await db.SaveChangesAsync(); // one call: UPDATE + 2 INSERTs

Running it on EF Core 10, the SaveChanges part of the log looks like this:

dbug: 
      Began transaction with isolation level 'Serializable'.
info: 
      Executed DbCommand (0ms) [Parameters=[@p1='?' (DbType = Int32), @p0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      UPDATE "Books" SET "Stock" = @p0
      WHERE "Id" = @p1
      RETURNING 1;
info: 
      Executed DbCommand (0ms) [Parameters=[@p0='?' (Size = 3)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "Orders" ("CustomerName")
      VALUES (@p0)
      RETURNING "Id";
info: 
      Executed DbCommand (0ms) [Parameters=[@p1='?' (DbType = Int32), @p2='?' (DbType = Int32), @p3='?' (DbType = Int32), @p4='?' (DbType = Decimal)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "OrderItem" ("BookId", "OrderId", "Quantity", "UnitPrice")
      VALUES (@p1, @p2, @p3, @p4)
      RETURNING "Id";
dbug: 
      Committed transaction.

We never opened a transaction, yet EF Core put all three commands between a BEGIN and a COMMIT. This is the basic promise of SaveChanges: either every change in that call is written or none is. A half-finished state where the order exists but the stock did not drop never happens. SQLite's default isolation level is Serializable, which is why you see it in the log; on SQL Server this line says ReadCommitted.

The same log on EF Core 6 is almost identical. The only difference is how the SQL is written: EF Core 6 reads the new row's id with a separate SELECT ... WHERE changes() = 1 AND "rowid" = last_insert_rowid() instead of RETURNING. The transaction behavior is the same in both.

What if SaveChanges only has one command to send? Let's add a single tag:

using var db = new BookstoreContext();
db.Tags.Add(new Tag { Name = "classic" });
await db.SaveChangesAsync(); // a single INSERT

This is where the versions differ. EF Core 6 wraps this single INSERT in Began transaction and Committed transaction lines as well. On EF Core 10 the log is only this:

info: 
      Executed DbCommand (0ms) [Parameters=[@p0='?' (Size = 7)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "Tags" ("Name")
      VALUES (@p0)
      RETURNING "Id";

A single SQL statement is already atomic from the database's point of view; it cannot run halfway. Starting with EF Core 7, EF knows this and skips the unnecessary transaction. The behavior does not change, there are just two fewer round trips to the database. We will look at this and similar version differences in the version posts.

What If One Fails? All or Nothing

Let's test the "all or nothing" promise. We add two tags in the same SaveChanges. The first is new, the second already exists and will hit the unique index:

using (var db = new BookstoreContext())
{
    db.Tags.Add(new Tag { Name = "fantasy" });
    db.Tags.Add(new Tag { Name = "sci-fi" }); // already exists, will hit the unique index

    try
    {
        await db.SaveChangesAsync();
    }
    catch (DbUpdateException ex)
    {
        Console.WriteLine(ex.InnerException?.Message);
    }
}

using (var db = new BookstoreContext())
{
    var names = await db.Tags.Select(t => t.Name).ToListAsync();
    Console.WriteLine(string.Join(", ", names));
}

SQLite Error 19: 'UNIQUE constraint failed: Tags.Name'.
sci-fi

The INSERT for fantasy actually succeeded, but when the second command failed, EF Core rolled the transaction back. The database still contains only sci-fi. The real cause of the error lives in the InnerException of the DbUpdateException; make it a habit to log that too.

One more detail: after the failure, both Tag entities in the change tracker are still in the Added state. If you call SaveChanges again on the same context, it retries the same two INSERTs. That is why the sample checks the result with a new context.

Multiple SaveChanges Calls: BeginTransaction

Now back to the opening question. Running PlaceOrder prints:

Payment failed for order 1
Orders: 1, Items: 1, Tags: 1, Stock: 10

The order is written and the stock is untouched. Each SaveChanges opens and closes its own transaction, so the first call was committed and there is no way back. We now have an order that was never paid for and never reduced the stock, yet shows up in the system. The reason for splitting into two SaveChanges calls is understandable too: order.Id has to exist before we can send the order number to the payment service.

The fix is to open a transaction ourselves that spans both calls:

static async Task PlaceOrderInTransaction()
{
    using var db = new BookstoreContext();
    using var transaction = await db.Database.BeginTransactionAsync();

    var book = await db.Books.SingleAsync(b => b.Title == "Dune");

    var order = new Order { CustomerName = "Ada" };
    order.Items.Add(new OrderItem { BookId = book.Id, Quantity = 2, UnitPrice = book.Price });
    db.Orders.Add(order);
    await db.SaveChangesAsync();

    ChargePayment(order.Id);

    book.Stock -= 2;
    await db.SaveChangesAsync();

    await transaction.CommitAsync(); // without Commit, everything is rolled back on Dispose
}

This time the output is:

Payment failed for order 1
Orders: 0, Items: 0, Tags: 1, Stock: 10

The relevant lines of the log tell the story as well:

dbug:
      Began transaction with isolation level 'Serializable'.
info:
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "b"."Id", "b"."Price", "b"."Stock", "b"."Title"
dbug:
      Created transaction savepoint.

This time the Began transaction line was written by the BeginTransactionAsync call, not by SaveChanges. When SaveChanges sees an open transaction, it does not start a new one; it runs inside the existing one. There is no Committed transaction line at all. When the exception was thrown, CommitAsync was never reached, the transaction was disposed at the end of the using block, and a transaction that is disposed without a commit is rolled back. So you do not need to call Rollback by hand in a catch. The rule is simple: open the transaction with using, Commit at the very end, and leave the rest to Dispose.

Keep this in mind too: as long as the transaction is open, the connection stays open and database locks are held. Calling an external service inside a transaction, as in the example above, is a debatable decision in real life. If the payment service takes 30 seconds to answer, the locks are held for 30 seconds. Keep transactions short; move external calls outside the transaction when you can.

Savepoints: A Checkpoint Inside a Transaction

You may have noticed it in the log above: Created transaction savepoint. Since EF Core 5, when SaveChanges runs inside an open transaction, it first creates a savepoint named __EFSavePoint. If SaveChanges fails, it rolls back only to that point. That way your transaction is not ruined; the work of earlier SaveChanges calls stays in place, and you can catch the error and decide to carry on.

You can use the same mechanism by hand. After saving the order we set a checkpoint. The "extras" that follow consist of two separate SaveChanges calls, and if either fails we want to undo both:

using var db = new BookstoreContext();
using var transaction = await db.Database.BeginTransactionAsync();

var book = await db.Books.SingleAsync(b => b.Title == "Dune");
var order = new Order { CustomerName = "Ada" };
order.Items.Add(new OrderItem { BookId = book.Id, Quantity = 1, UnitPrice = book.Price });
db.Orders.Add(order);
await db.SaveChangesAsync();

await transaction.CreateSavepointAsync("BeforeExtras");
try
{
    order.Items.Add(new OrderItem { BookId = book.Id, Quantity = 1, UnitPrice = 0m }); // free gift book
    await db.SaveChangesAsync();

    db.Tags.Add(new Tag { Name = "sci-fi" }); // hits the unique index
    await db.SaveChangesAsync();
}
catch (DbUpdateException)
{
    await transaction.RollbackToSavepointAsync("BeforeExtras");
    db.ChangeTracker.Clear(); // forget the rolled-back entities
}

await transaction.CommitAsync();

Orders: 1, Items: 1, Tags: 1, Stock: 10

The order and its first item are there. The gift book item had been written successfully, but RollbackToSavepointAsync("BeforeExtras") undid it as well. Do not forget the ChangeTracker.Clear() line. You rewound the database, but the change tracker still thinks the gift item is Unchanged and the failed tag is Added. If you do not clear it, the next SaveChanges tries that tag again.

SQL Server, PostgreSQL and SQLite all support savepoints; we just ran the sample on SQLite. There is one exception: on SQL Server, savepoints cannot be used when MARS (MultipleActiveResultSets=True) is enabled, and EF Core stops creating its own savepoint in SaveChanges.

A Note on TransactionScope

Someone coming from the .NET Framework days will most likely ask about System.Transactions.TransactionScope. You wrap a block of code in a scope, and connections inside it enlist in this "ambient" transaction automatically. Let's try it with SQLite:

using var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
using var db = new BookstoreContext();

db.Tags.Add(new Tag { Name = "classic" });
await db.SaveChangesAsync();

scope.Complete();

InvalidOperationException: An error was generated for warning 'Microsoft.EntityFrameworkCore.Database.Transaction.AmbientTransactionWarning': An ambient transaction has been detected, but the current provider does not support ambient transactions. See https://go.microsoft.com/fwlink/?LinkId=800142. This exception can be suppressed or logged by passing event ID 'RelationalEventId.AmbientTransactionWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'.

The output is from EF Core 10; EF Core 6 throws exactly the same exception. The SQLite provider does not support ambient transactions, and instead of silently ignoring that, EF Core throws. Good thing it does; otherwise the changes would be written even without a call to scope.Complete(). The SQL Server provider supports TransactionScope, but watch out for two things: in async code, if you do not pass TransactionScopeAsyncFlowOption.Enabled, the scope is lost after an await; and if a second connection is opened inside the scope, the transaction tries to become distributed, which .NET 6 does not support. If you work with a single database, BeginTransaction is both more explicit and less surprising.

Common Pitfalls

Mixing retries with your own transaction. On SQL Server, turning on EnableRetryOnFailure() against transient errors is a common setting:

public class SqlServerBookstoreContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlServer(
            "Server=localhost;Database=Bookstore;User Id=sa;Password=Your_password123;TrustServerCertificate=True",
            sql => sql.EnableRetryOnFailure());
}

With this setting in place, if you open your own transaction with BeginTransaction and call SaveChanges, EF Core throws an InvalidOperationException with the message "The configured execution strategy 'SqlServerRetryingExecutionStrategy' does not support user-initiated transactions". The reason makes sense: when the connection drops, EF can retry a single SaveChanges, but it cannot know what your transaction has done since it began. The fix is to hand the whole transaction to the execution strategy, so the entire block runs again if something fails:

var strategy = db.Database.CreateExecutionStrategy();

await strategy.ExecuteAsync(async () =>
{
    using var transaction = await db.Database.BeginTransactionAsync();

    db.Tags.Add(new Tag { Name = "classic" });
    await db.SaveChangesAsync();

    await transaction.CommitAsync();
});

We tried this on SQLite with a custom retry strategy; the message and the fix behave exactly the same. Remember that the block may run again from the start: it should not contain work that must not be repeated, such as sending an email.

Forgetting to commit. If you call BeginTransaction and never write CommitAsync, the code does not fail, every SaveChanges looks successful, but when the context is disposed everything is rolled back. This is the classic bug where people search for hours because "it saves, but it is not in the database".

Long transactions. Waiting for user input, processing a large file or calling a slow external service inside a transaction means holding locks for that long. Open the transaction as late as possible and close it as early as possible.

Unnecessary transactions. A single SaveChanges is already atomic. Wrapping every SaveChanges in BeginTransaction by reflex gains nothing and clutters the code. Open a transaction by hand only when you need to turn several SaveChanges calls, raw SQL, or other code sharing the same connection (for example the queries we saw in the Dapper series) into a single unit.

A Small Experiment

★☆☆ In PlaceOrderInTransaction, comment out the ChargePayment line, but also delete the CommitAsync line. Run the program and look at the Orders count. Then find which line is missing from the log.

★★☆ Write a CancelOrder(int orderId) method: it should delete the order and add the item quantities back to the stock. First do the delete and the stock update in a single SaveChanges, then split them into two SaveChanges calls, put an exception between them, and try protecting them with a transaction.

★★★ Write a loop that saves three orders one after another and make the second one fail on purpose (for example by adding a tag that already exists). Create a savepoint for each order and roll back only the order that fails. When the loop ends, write the successful orders with a single Commit. Figuring out where the ChangeTracker.Clear() call has to go is the real work.

Transactions keep the work inside a single request together. But what happens when two different users try to buy the last copy of the same book at the same moment? Two separate transactions, each flawless on its own, and the result is still wrong. In the next post we will look at optimistic concurrency, concurrency tokens and DbUpdateConcurrencyException.