Nested (virtual) Transactions

Required Nuget Package:

Motivation

Enables calling BeginTransaction multiple times. On first call a database transaction is started, this transaction is considered to be a root translation. All further calls to BeginTransation are creating virtual child transactions. If and only if all transactions are completed by calling the method Commit then the underlying database transaction is going to be committed as well. In all other cases the database transaction is going to be rolled back if the root transaction is completed (i.e. committed/rolled back/disposed). If BeginTransaction is called with an IsolationLevel to create a child transaction then the provided level is checked for compatibility with the level of the root transaction.

Usage

1. Activate the support for nested transactions

services
   .AddDbContext<DemoDbContext>(builder => builder
                                           //.UseSqlite("...")
                                           .UseSqlServer("...")
                                           .AddNestedTransactionSupport()

2. Use transactions the usual way

// starts a database transaction
using var rootTx = myDbContext.Database.BeginTransaction();
...
// creates a virtual child transaction
using var childTx = myDbContext.Database.BeginTransaction();

// case 1: both transactions are committed
childTx.Commit();
rootTx.Commit(); // commits the database transaction

// case 2: outer transaction is rolled back
childTx.Commit();
rootTx.Rollback(); // the database transaction is rolled back

// case 3: both transactions are rolled back
childTx.Rollback();
rootTx.Rollback(); // the database transaction is rolled back

// case 4: inner transactions is rolled back
childTx.Rollback();
rootTx.Commit(); // throws TransactionAbortedException, later, e.g. on Dispose the database transaction is rolled back