Transactions - lobodava/artisan-orm GitHub Wiki

Artisan.Orm offers two transaction patterns — manual commit and auto commit / rollback — plus async variants for both. Pick based on which outcome should be the default when the action returns:

All four APIs have overloads with and without an IsolationLevel; when omitted, SQL Server uses its default (normally READ COMMITTED).

Inside action, commands created through GetByCommand / ExecuteCommand / Read* / Execute* on the same repository attach to its active SqlTransaction. A second repository instance does not implicitly share it. These helpers do not implement nested transactions or savepoints; do not start one inside another on the same repository.

The ownership behavior on this page describes the source being prepared for 4.1.0. In that contract, all four helpers reject an already attached transaction instead of replacing it. Check an older installed package before assuming it includes these fixes.

Construct each participating repository with the existing transaction. The following method uses the base class directly to show resource ownership; derived repositories can expose the same constructor:

public static void RunSharedWork(string connectionString)
{
    using var connection = new SqlConnection(connectionString);
    connection.Open();
    using var transaction = connection.BeginTransaction();
    using (var first = new RepositoryBase(transaction, connectionString))
    using (var second = new RepositoryBase(transaction, connectionString))
    {
        first.Execute("SELECT 1;");
        second.Execute("SELECT 2;");
    }

    // Disposing the repositories leaves both caller-owned resources alive.
    transaction.Commit();
}

Both repositories use transaction.Connection. The constructor string remains stored as ConnectionString; it does not create a second connection or redirect the transaction. The caller must commit or roll back and dispose the transaction and connection. A borrowing repository's Dispose/DisposeAsync only detaches its references. A completed or disposed transaction is rejected by the constructor.

Do not call transaction-starting helpers while that transaction is attached. Complete it and detach it before starting a new one, or create a repository for a new unit of work. Assigning Transaction on a repository that already owns a different connection does not implement this sharing pattern. The constructor determines ownership; repository instances and a shared connection must still be used sequentially.

Use when "do nothing" should mean "rolled back" — typical for read-then-modify flows that may decide partway through to abandon the change.

public void CheckRuleForUser(int userId)
{
    BeginTransaction(IsolationLevel.RepeatableRead, tran =>
    {
        var user = GetByCommand(cmd =>
        {
            cmd.UseProcedure("dbo.GetUserById");
            cmd.AddIntParam("@Id", userId);
            return cmd.ReadTo<User>();
        });

        if (user == null)
            return;

        var roleIds = user.RoleIds ?? Array.Empty<byte>();
        if (Array.IndexOf(roleIds, (byte)2) > -1 && Array.IndexOf(roleIds, (byte)3) == -1)
        {
            var newRoleIds = roleIds.ToList();
            newRoleIds.Add((byte)3);
            user.RoleIds = newRoleIds.ToArray();
        }

        GetByCommand(cmd =>
        {
            cmd.UseProcedure("dbo.SaveUser");
            cmd.AddTableRowParam("@User", user);
            cmd.AddTableParam("@RoleIds", user.RoleIds ?? Array.Empty<byte>());
            return cmd.GetByReader(reader =>
            {
                CheckForDataReplyException(reader);
                return reader.ReadTo<User>();
            });
        });

        // Forgetting tran.Commit() here would roll back — by design.
        tran.Commit();
    });
}

The method opens the connection if it was closed, begins the transaction, runs action, and on exit:

• try — runs action,
• catch — rolls back the transaction and rethrows,
• finally — disposes the transaction (which rolls back if Commit() was not called) and closes the connection if it was opened by this method.

The current sample's dbo.SaveUser returns a DataReply status before its payload. Check that status before committing: executing the procedure without reading its status would not turn a business failure into an exception. The example also passes the separate role-ID TVP; it replaces the user's role assignments. See DataReply.

Use for the typical "do these steps; if any throws, undo all" flow.

public void TransferFunds(int fromAccountId, int toAccountId, decimal amount)
{
    RunInTransaction(IsolationLevel.Serializable, tran =>
    {
        Execute("dbo.MoveFunds",
            new SqlParameter("@From",   fromAccountId),
            new SqlParameter("@To",     toAccountId),
            new SqlParameter("@Amount", amount));

        Execute("dbo.RecordTransfer",
            new SqlParameter("@From",   fromAccountId),
            new SqlParameter("@To",     toAccountId),
            new SqlParameter("@Amount", amount));

        // No tran.Commit() here — RunInTransaction commits automatically
        // on normal return, and rolls back if action throws.
    });
}

If action throws, the transaction is rolled back and the exception is rethrown. The caller must not call Commit or Rollback manually inside action.

Both BeginTransactionAsync and RunInTransactionAsync accept a CancellationToken and provide it to the callback. They use it for their own supported asynchronous connection/transaction operations. The callback must explicitly pass the received token into each operation it starts; the wrapper cannot inject cancellation into arbitrary awaited work. Do not pass an async lambda to the synchronous Action<SqlTransaction> overload.

public async Task TransferFundsAsync(int fromId, int toId, decimal amount,
                                     CancellationToken ct = default)
{
    await RunInTransactionAsync(IsolationLevel.Serializable, async (tran, ct2) =>
    {
        await ExecuteAsync("dbo.MoveFunds", ct2,
            new SqlParameter("@From",   fromId),
            new SqlParameter("@To",     toId),
            new SqlParameter("@Amount", amount));

        await ExecuteAsync("dbo.RecordTransfer", ct2,
            new SqlParameter("@From",   fromId),
            new SqlParameter("@To",     toId),
            new SqlParameter("@Amount", amount));
    }, ct);
}

For the manual-commit async version:

await BeginTransactionAsync(async (tran, ct) =>
{
    // ... reads / writes ...
    await tran.CommitAsync(ct).ConfigureAwait(false);
}, cancellationToken);

To allow an outer transaction (started in C#) to coexist with a procedure that may also want its own when called standalone, write each procedure to detect and respect an existing one:

This complete SQL Server 2012+ procedure example assumes an existing dbo.Items(Id int, Name nvarchar(100)) table:

CREATE PROCEDURE dbo.RenameItem
    @Id int,
    @Name nvarchar(100)
AS
BEGIN
    DECLARE @StartTranCount int = @@TRANCOUNT;
    BEGIN TRY
        IF @StartTranCount = 0 BEGIN TRANSACTION;

        UPDATE dbo.Items SET Name = @Name WHERE Id = @Id;

        IF @StartTranCount = 0 COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @StartTranCount = 0 AND XACT_STATE() <> 0
            ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;

If an outer transaction exists, the error is rethrown to its owner, which must decide whether to roll back. This pattern does not provide partial rollback through a savepoint.

Clustered indexes do not prevent all deadlocks. Two transactions can hold resources needed by each other even when their join columns are indexed. Standardize access order across competing operations, keep transactions short, and choose indexes and isolation that fit the consistency requirements. Inspect the deadlock graph to identify the actual conflict. See SQL Server's deadlock guidance.

Artisan.Orm does not provide an automatic deadlock-retry policy. If the application adds one, bound its attempts and retry the complete transaction after rollback, using a fresh transaction for each attempt. Ensure that external side effects are not duplicated and let cancellation stop the retry loop. Retrying only the last command is not equivalent to repeating the original unit of work. A caller-owned transaction must be coordinated by its owner.

Preserve the error identity when classifying retryable failures. The THROW; template above rethrows the original SQL exception, so a deadlock can retain error number 1205. Several current sample procedures instead catch errors and call RAISERROR with a message, which reports error number 50000. Do not classify every 50000 error as a deadlock. An application adapting those procedures for number-based retries should preserve the original exception; the library has not rewritten those sample wrappers or added retries. See RAISERROR numbers and THROW rethrowing.

Transaction statement order, row insertion order, identity allocation, and result order are distinct. The graph-saving guidance explains why OUTPUT key mapping preserves relationships and why an application ordering value still needs an explicit ORDER BY.

In 4.1.0, synchronous and asynchronous transaction helpers preserve the original callback/commit exception when cleanup also fails. Cleanup disposes unfinished transactions without using the canceled operation token and closes connections opened by the helper. It cannot undo a transaction that was already committed, and it does not add retries. Validate the complete retry policy, including external side effects and procedure error-number preservation; see RepositoryBase.

See also:

• RepositoryBase
• Using Artisan.Orm in ASP.NET Core — sharing transactions across services in one HTTP request
• SQL function dbo.GetErrorMessage()