Transactions

Transaction handling is part of the pipeline itself, so it behaves the same for Raw SQL, Entity and Repository operations and for stored procedures. All of them use the context's one connection and its current transaction.

Begin, commit, roll back#

db.BeginTransaction();
try
{
    await db.Insert(new Order { CustomerId = 42, TotalAmount = 99m }).ExecuteAsync();
    await db.ExecuteAsync("UPDATE {{T: customers}} SET {{C: CreditLimit}} = {{C: CreditLimit}} - @amt WHERE {{C: CustomerId}} = @id",
                          new { amt = 99m, id = 42 });
    db.CommitTransaction();
}
catch
{
    db.RollbackTransaction();
    throw;
}

CommitTransaction(bool commitTransaction = true) commits only when the flag is true, which lets a caller that did not start the transaction leave the decision to the outer scope.

Nested scopes: who owns the transaction?#

BeginTransaction() returns true only if it started a new transaction; if one is already open it returns false. Use that to write helpers that join an existing transaction instead of committing someone else's work:

bool owner = db.BeginTransaction();
try
{
    // ... work ...
    db.CommitTransaction(owner);   // commits only when this scope started the transaction
}
catch
{
    if (owner) db.RollbackTransaction();
    throw;
}

Savepoints#

Pass a name to create a savepoint inside the current transaction:

db.BeginTransaction("after_header");

RollbackTransaction() rolls back to the most recent savepoint if there is one, otherwise it rolls back the whole transaction. Savepoint syntax differs by database, and the pipeline generates the right statements for the engine in use.

Isolation level#

using System.Data;

db.BeginTransaction(IsolationLevel.Serializable);
db.BeginTransaction(IsolationLevel.ReadCommitted, "sp1");   // with a savepoint

If you do not specify one, the driver's default applies.

Notes#

• Connect() must have opened the connection first. Calling BeginTransaction() on a context that is not connected throws an InvalidOperationException.
• Commit and rollback also clear the savepoint stack.
• In Kiwi Flow, a database connection activity can open a transaction that commits when the run succeeds and rolls back when it fails, using this same mechanism.