How to Use Savepoints in EF Core

A savepoint lets part of a transaction fail while the rest carries on. In EF Core, you create one with CreateSavepointAsync, and EF Core also creates its own around each SaveChanges inside a transaction. If you've seen __EFSavePoint in MySQL's log, that's where it comes from.

Prerequisites

• A transaction. How to Use a Transaction in EF Core with BeginTransactionAsync. I used EF Core 10.0.14 and MySQL 9.7.4.
• What a savepoint does in MySQL. How to Use Savepoints in MySQL.

Step 1: Create One, and Roll Back to It

This saves one category, then marks a savepoint, then tries a second that fails on the unique index. The first category should survive:

An endpoint

g.MapPost("/savepoint", async (InventoryDbContext db, TimeProvider clock, ILogger<InventoryDbContext> log, CancellationToken cancellationToken) =>
{
    var strategy = db.Database.CreateExecutionStrategy();
    var now = clock.GetUtcNow().UtcDateTime;

    return await strategy.ExecuteAsync(
        async ct =>
        {
            db.ChangeTracker.Clear();

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

            db.Categories.Add(new Category { Name = "Stands", CreatedAtUtc = now });
            await db.SaveChangesAsync(ct);

            await transaction.CreateSavepointAsync("BeforeMounts", ct);

            try
            {
                db.Categories.Add(new Category { Name = "Cables", CreatedAtUtc = now }); // This name already exists.
                await db.SaveChangesAsync(ct);
            }
            catch (DbUpdateException exception) when (exception.IsDuplicateKey())
            {
                await transaction.RollbackToSavepointAsync("BeforeMounts", ct);
                db.ChangeTracker.Clear();
            }

            await transaction.CommitAsync(ct);

            return Results.Ok(new { supportsSavepoints = transaction.SupportsSavepoints });
        },
        cancellationToken);
});

• ChangeTracker.Clear() in the catch removes the failed entity. Otherwise, the next save tries it again.
• SupportsSavepoints is true for this driver, as the response shows.

Output

{
  "supportsSavepoints": true
}

Step 2: See What MySQL Got

Output (MySQL's log)

+--------------------------------------------------------------+
| statement                                                    |
+--------------------------------------------------------------+
| SET TRANSACTION ISOLATION LEVEL REPEATABLE READ              |
| BEGIN                                                        |
| SAVEPOINT `__EFSavePoint`                                    |
| INSERT INTO `categories` (`CreatedAtUtc`, `Name`) VALUES (ti |
| SELECT `Id` FROM `categories` WHERE ROW_COUNT() = 1 AND `Id` |
| RELEASE SAVEPOINT `__EFSavePoint`                            |
| SAVEPOINT `BeforeMounts`                                     |
| SAVEPOINT `__EFSavePoint`                                    |
| INSERT INTO `categories` (`CreatedAtUtc`, `Name`) VALUES (ti |
| ROLLBACK TO `__EFSavePoint`                                  |
| RELEASE SAVEPOINT `__EFSavePoint`                            |
| ROLLBACK TO `BeforeMounts`                                   |
| COMMIT                                                       |
+--------------------------------------------------------------+

My savepoint is the `BeforeMounts` one. All the `__EFSavePoint` lines are EF Core's own.

Step 3: Understand the Automatic Ones

The EF Core docs explain: "When SaveChanges is invoked and a transaction is already in progress on the context, EF automatically creates a savepoint before saving any data." If the save fails, "it automatically rolls the transaction back to the savepoint, leaving the transaction in the same state as if it had never started."

You can see that in the log: ROLLBACK TO __EFSavePoint came first, from EF Core, and then ROLLBACK TO BeforeMounts, from my catch.

To turn them off:

An endpoint (excerpt)

db.Database.AutoSavepointsEnabled = false;

Output (MySQL's log, with them off)

+--------------------------------------------------------------+
| statement                                                    |
+--------------------------------------------------------------+
| SET TRANSACTION ISOLATION LEVEL REPEATABLE READ              |
| BEGIN                                                        |
| INSERT INTO `categories` (`CreatedAtUtc`, `Name`) VALUES (ti |
| SELECT `Id` FROM `categories` WHERE ROW_COUNT() = 1 AND `Id` |
| ROLLBACK                                                     |
+--------------------------------------------------------------+

There's rarely a reason to, except to save the round trips in a tight loop.

Step 4: Remember That Locks Stay

Rolling back to a savepoint undoes the changes, but MySQL doesn't release locks taken before it. So it isn't a way out of a lock conflict.

Common Mistakes

Mistake	What happens	Do this instead
Using one to fake a nested transaction	It's one transaction, and a later failure loses everything	Keep them for partial retries
Forgetting to clear the tracker	The next save tries the failed entity again	Clear it, or detach it
Rolling back to a name that doesn't exist	MySQL raises an error	Check the name

Troubleshooting

Symptom	Likely cause	Fix
__EFSavePoint in the log	A SaveChanges inside a transaction	That's normal
The same failure again on the next save	The entity's still in the tracker	Clear it
Row locks remain after the rollback	They do, until the commit	Keep transactions short

Conclusion

Use CreateSavepointAsync and RollbackToSavepointAsync when part of a transaction may fail and the rest should still commit. Clear the tracker after rolling back, and don't be surprised by EF Core's own __EFSavePoint.

Additional Resources

• Using Transactions — savepoints in EF Core.
• SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT — MySQL's side.

Related

• How to Use Savepoints in MySQL
• How to Use a Transaction in EF Core with BeginTransactionAsync
• How to Use ChangeTracker.Clear in EF Core