The SQL Standard defines the concept of savepoints, which allow one to partially roll back a transaction; multiple savepoints may be defined, but only within a transaction. These seem to be well-supported in a uniform way across databases - although the SQL syntax is a bit quirky across databases. SqlClient and Npgsql already expose methods for savepoints on their DbTransaction implementations, but there's probably value in exposing these on DbTransaction.

• PostgreSQL. Exposed by Npgsql: Save, Rollback, Release and async counterparts.
• SQL Server. Exposed by SqlClient: Save, Rollback, no Release (apparently not supported by SQL Server), no async methods.
• Sqlite. Supports SAVE/ROLLBACK/RELEASE, but not exposed in Sqlite. Tracking issue: Sqlite: add transaction savepoint API efcore#20228
• MySQL. Supports SAVE/ROLLBACK/RELEASE, but not exposed in MySqlConnector. Tracking issue: Add transaction savepoint API mysql-net/MySqlConnector#775

API Proposal

class DbTransaction
{
    public virtual Task SaveAsync(string savepointName, CancellationToken cancellationToken = default)
        => throw new NotSupportedException();
    public virtual Task RollbackAsync(string savepointName, CancellationToken cancellationToken = default)
        => throw new NotSupportedException();
    public virtual Task ReleaseAsync(string savepointName, CancellationToken cancellationToken = default)
        => Task.CompletedTask;

    public virtual void Save(string savepointName) => throw new NotSupportedException();
    public virtual void Rollback(string savepointName) => throw new NotSupportedException();
    public virtual void Release(string savepointName) {}

    public virtual bool AreSavepointsSupported => false; // Alternate naming: SupportsSavepoints
}

Notes

• RELEASE seems supported everywhere except for SQL Server. However, it has no user-visible behavior - it only frees up database resources associated with the savepoint - so it seems correct to have this be a no-op by default (and on SQL Server), as opposed to the other methods which throw if not supported.
• Release should generally not throw - if the transaction has already completed or the connection has been broken, it should silently return (similar to DbTransaction.Dispose).
• Instead of the above, we could have a CreateSavepoint that returns a disposable DbSavepoint, which has a single Rollback method on it (Dispose releases). Comments:
  • Pro: Better naming (the current Save/Rollback/Release don't even have Savepoint in the method name).
  • Pro: It would "codify" the fact that releasing doesn't throw (since Dispose in general doesn't).
  • Pro: Aligns more cleanly with the transaction API (e.g. DbConnection.BeginTransaction)
  • Con: New API where Save/Rollback/Release already exists in SqlClient and Sqlite.
  • Con: New type (DbSavepoint, conceptual complexity) which is unlikely to add provider-specific functionality.
  • Con: Additional allocation
• All these methods need to do a database roundtrip (i.e. synchronous completion not really possible, except for Sqlite), but they aren't very fine-grained operations, so making them return ValueTask seems unnecessary. For now I'm proposing they return Task (like CommitAsync and RollbackAsync), but guidance may have changed here (/cc @stephentoub)
• At least in theory, savepoint support may vary across database versions/editions, so ideally the AreSavepointsSupported should throw InvalidOperationException if accessed with closed connection. We could introduce a non-virtual user-facing property to check for this, calling a protected virtual property implemented by the provider, but we can't know if a transaction has completed (or has been disposed). So it's probably better to leave this to the provider, documenting the correct behavior (even when the specific provider always supports savepoints).
• To support this, ADO providers would need to cross-target to .NET 5. /cc @David-Engel @cheenamalhotra @bgrainger @bricelam

Edit history