What Happens to an EF Core Transaction When the Client Disconnects

A client gives up and closes the connection while your transaction is halfway through. Does the work stop? Is half of it saved? I tried it both ways. The transaction is safe either way, and one case doesn't behave the way the CancellationToken suggests.

Prerequisites

• An ASP.NET Core API that passes a CancellationToken to EF Core. A minimal API endpoint gets one by adding the parameter, and it's cancelled when the client disconnects. I used ASP.NET Core 10, EF Core 10.0.14, MySql.Data 26.10.0 and MySQL 9.7.4.

Step 1: Hang Up Between Two Statements

This endpoint makes a change, waits 5 seconds, and makes another:

An endpoint

g.MapPost("/hangup/{id:int}", async (int id, InventoryDbContext db, ILogger<InventoryDbContext> log, CancellationToken cancellationToken) =>
{
    var strategy = db.Database.CreateExecutionStrategy();

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

            await db.Products.Where(p => p.Id == id)
                .ExecuteUpdateAsync(s => s.SetProperty(p => p.ReorderLevel, p => p.ReorderLevel + 1), ct);

            await Task.Delay(5000, ct);

            await db.Products.Where(p => p.Id == id)
                .ExecuteUpdateAsync(s => s.SetProperty(p => p.ReorderLevel, p => p.ReorderLevel + 1), ct);

            await transaction.CommitAsync(ct);
        },
        cancellationToken);

    return Results.Ok();
});

curl gives up after 2 seconds:

Terminal

curl -s -m 2 -X POST http://localhost:5190/demo/hangup/1

Output (curl's exit code, and how long it took)

curl exit 28 2.006707s

Step 2: See What Happened

MySQL's log:

Output

+--------------------------------------------------------------------+
| statement                                                          |
+--------------------------------------------------------------------+
| SET TRANSACTION ISOLATION LEVEL REPEATABLE READ                    |
| BEGIN                                                              |
| UPDATE `products` AS `p` SET `ReorderLevel` = `p`.`ReorderLevel` + |
| ROLLBACK                                                           |
+--------------------------------------------------------------------+

ROLLBACK, and the first change is gone. The token cancelled Task.Delay, an exception left the block, and await using rolled back.

The API's log:

API log

dbug: Microsoft.EntityFrameworkCore.Database.Transaction[20209]
      Beginning transaction with isolation level 'Unspecified'.
dbug: Microsoft.EntityFrameworkCore.Database.Transaction[20200]
      Began transaction with isolation level 'Unspecified'.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@id='?' (DbType = Int32)], CommandType='Text', CommandTimeout='0']
      UPDATE `products` AS `p`
      SET `ReorderLevel` = `p`.`ReorderLevel` + 1
      WHERE `p`.`Id` = @id
dbug: Microsoft.EntityFrameworkCore.Database.Transaction[20204]
      Disposing transaction.

No error, which is right, because a client leaving isn't one of your bugs.

Step 3: Hang Up During a Blocked Statement

Now the hang-up comes while a statement is waiting for a row lock that another session holds:

Output (curl's exit code, and how long it took)

curl exit 28 2.006210s

MySQL still had the statement, waiting for its lock:

Output

+-------------------+------------------------------------------+
| PROCESSLIST_STATE | statement                                |
+-------------------+------------------------------------------+
| updating          | UPDATE `products` AS `p`
SET `ReorderLev |
+-------------------+------------------------------------------+

When the other session let go, the statement ran. ReorderLevel went from 1 to 2:

Output

+--------------+
| ReorderLevel |
+--------------+
|            2 |
+--------------+

So the driver's async call doesn't stop a statement that's already at the server. That single change was applied, for a client that had already left.

Step 4: Know What to Rely On

When the client leaves	What happens
Between statements in a transaction	The next await throws, and it rolls back
During a statement in a transaction	That statement finishes, and the rest of the work then rolls back
During a single statement with no transaction	It's applied
After the commit	The work is done, and the client just doesn't hear

A client that leaves can't know which of these happened, so it should try again, with an Idempotency-Key.

Common Mistakes

Mistake	What happens	Do this instead
Not passing the token	The whole transaction finishes for nobody	Pass it to every call
Expecting the token to stop a running statement	It finishes on the server	Limit it on the server
Treating this as an error in your logs	Noise	Let it go, as ASP.NET Core does

Conclusion

A transaction is safe when a client disconnects: leaving without a commit rolls it back. But a statement already at the server will finish, so a change with no transaction around it can still be applied after the client has gone.

Additional Resources

• HttpContext.RequestAborted — the token.
• KILL Statement — stopping a statement on the server.

Related

• How to Use a Transaction in EF Core with BeginTransactionAsync
• How to Add an Idempotency-Key Header to a POST Endpoint in ASP.NET Core
• Why You Should Not Do Slow Work Inside a Database Transaction