SQL Transactions with a pipeline

[tcartwright]

Would it be possible to use something like this here. Where the transaction is registered as part of the pipeline with this library?

EDIT: I apologize, I looked at your pipeline documentation, but I was unsure which pipeline to pick for this scenario. Or if one would be even applicable at all.

[martinothamar]

Hey, sorry about the reply, thanks for your patience 😄

I couldn't read the whole article since it's on Medium and it want's be to login but... I think I get the gist from the image and first part of the text.

I think it is technically possible, but not sure if it's a good solution in practice. If transaction lifecycle is part of the pipeline, it might be a little unclear what should happen when commands trigger subsequent commands. Is it always the case that transactions should cover the whole graph of commands that are dispatched? That is a little unclear to me, and that would make me nervous. In the applications I've worked on the transaction boundaries are important, and I want the developers to care about them and understand them when interacting with the database, so I'd say my preference would be to not keep transaction lifecycle in the pipeline, but be closer to the specifics of the feature (i.e. in the message handlers)

However, I think if I were to experiment with this kind of abstraction I would go in this general direction:

public interface ITransactionalCommand<TResponse> : ICommand<TResponse> { }

public sealed class TransactionalBoundary<TCommand, TResponse> : IPipelineBehavior<TCommand, TResponse>
    where TCommand : ITransactionalCommand<TResponse>
{
    internal static readonly AsyncLocal<(DbConnection Connection, DbTransaction Transaction)> _currentTransaction = new();

    public async ValueTask<TResponse> Handle(
        TCommand command,
        MessageHandlerDelegate<TCommand, TResponse> next,
        CancellationToken cancellationToken
    )
    {
        var ownsTransaction = false;
        if (_currentTransaction.Value.Connection is null)
        {
            _currentTransaction.Value = (new DbTransaction, new DbConnection());
            ownsTransaction = true;
        }
        try {
            return await next(command, cancellationToken);
        } finally {
            // Handle transaction commit/rollback using catch/finally blocks
            if (ownsTransaction)
            {
                // ...
            }
        }
    }
}

public abstract class TransactionCommandHandler<TCommand, TResponse> : ICommandHandler<TCommand, TResponse>
    where TCommand : ITransactionalCommand<TResponse>
{
    public (DbConnection Connection, DbTransaction Transaction) Db => TransactionalBoundary<TCommand, TResponse>._currentTransaction.Value;

    public abstract ValueTask<TResponse> Handle(TCommand command, CancellationToken cancellationToken);
}

// Now define commands that implement ITransactionalCommand, and handlers that inherit from TransactionCommandHandler

This is completely untested. It uses AsyncLocal which can be kind of dangerous and has some pitfalls. It's unclear what happens if handlers dispatch commands that are not ITransactionalCommand etc.. So I'm not sure if this kind of abstraction is worth it. I'm sure it depends on project requirements, team discipline and skill-level etc 😄