This blog post was moved from the old blog.

One way to learn something new is to write unit tests exploring a new library, that is what I'll be doing in this blog post. For tests, we will use xUnit and for database Npgsql.

Let's explore different use cases of transaction scope. We will see how transactions behave when they are nested and how exceptions are handled on different nesting levels.

Since we are using async/await workflow we need to enable TransactionScopeAsyncFlowOption.Enabled on our transactions.

Enable transaction scope

  1. Include Enlist=true in your connection string.
  2. Call connection.EnlistTransaction(Transaction.Current);

Newer versions of Npgsql have enlisting enabled by default. 

Setup functions and constants used throughout testing

private readonly string ConnectionString = "DATABASE=transaction_test;PORT=5432;USER ID=postgres;PASSWORD=postgres;HOST=localhost;";

private readonly string InsertTableAEntryQuery = @"INSERT INTO public.TestTableA(value) VALUES (@value);";
private readonly string InsertTableBEntryQuery = @"INSERT INTO public.TestTableB(value) VALUES (@value);";

private readonly string TableARowCountQuery = @"SELECT count(*) FROM public.TestTableA;";
private readonly string TableBRowCountQuery = @"SELECT count(*) FROM public.TestTableB;";

private async Task ClearDatabase()
{
    await using var connection = new NpgsqlConnection(this.ConnectionString);

    await connection.ExecuteAsync("DELETE FROM public.TestTableA");
    await connection.ExecuteAsync("DELETE FROM public.TestTableB");
}

private async Task<int> TableARowCount()
{
    await using var connection = new NpgsqlConnection(ConnectionString);
    return await connection.QuerySingleAsync<int>(TableARowCountQuery);
}

private async Task<int> TableBRowCount()
{
    await using var connection = new NpgsqlConnection(ConnectionString);
    return await connection.QuerySingleAsync<int>(TableBRowCountQuery);
}

private async Task ThrowException()
{
    await using var connection = new NpgsqlConnection(this.ConnectionString);
    using var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
    throw new Exception();
}

White smoke test to make sure we have everything setup correctly

[Fact]
public async Task ConnectionIsWorking()
{
    await ClearDatabase();
    await using var connection = new NpgsqlConnection(ConnectionString);

    // Note: no need to open connection
    // Dapper will open it for us

    await connection.ExecuteAsync(InsertTableAEntryQuery, new { value = 123 });
    await connection.ExecuteAsync(InsertTableBEntryQuery, new { value = 321 });

    Assert.Equal(1, await TableARowCount());
    Assert.Equal(1, await TableBRowCount());
}

Throwing exception before transaction complete should roll back transaction

[Fact]
public async Task SimpleTransactionRollbackOnException()
{
    await ClearDatabase();

    using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
    {
        try
        {
            await using var connection = new NpgsqlConnection(ConnectionString);

            await connection.ExecuteAsync(InsertTableAEntryQuery, new { value = 123 });

            await ThrowException();

            scope.Complete();
        }
        catch { }
    }

    Assert.Equal(0, await TableARowCount());
    Assert.Equal(0, await TableBRowCount());
}

Even if we catch exception, we still can't complete transaction since connection state is set to aborted

[Fact]
public async Task SimpleTransactionRollbackOnException2()
{
    await ClearDatabase();
    try
    {
        using var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
        await using var connection2 = new NpgsqlConnection(ConnectionString);

        try
        {
            await using var connection = new NpgsqlConnection(ConnectionString);
            await connection.ExecuteAsync(InsertTableAEntryQuery, new { value = 123 });

            await ThrowException();

        }
        catch { }

        scope.Complete();
    }
    catch (TransactionAbortedException)
    {
        Assert.True(true);
    }
    catch
    {
        Assert.True(false);
    }
}

Forgetting to call Complete will fail to save changes silently

[Fact]
public async Task SimpleTransactionRollbackWithoutComplete()
{
    await ClearDatabase();

    using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
    {
        await using var connection = new NpgsqlConnection(ConnectionString);

        await connection.ExecuteAsync(InsertTableAEntryQuery, new { value = 123 });
        await connection.ExecuteAsync(InsertTableBEntryQuery, new { value = 321 });

        //Forgot to call complete
        //scope.Complete();
    }

    Assert.Equal(0, await TableARowCount());
    Assert.Equal(0, await TableBRowCount());
}

Nesting transaction scopes and having multiple complete functions will work fine

[Fact]
public async Task NestedTransactionsBothHavingComplete()
{
    await ClearDatabase();

    using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
    {
        await using var connection = new NpgsqlConnection(ConnectionString);

        await connection.ExecuteAsync(InsertTableAEntryQuery, new { value = 123 });

        await NestedTransactionWithCompleteInside();

        scope.Complete();
    }

    Assert.Equal(1, await TableARowCount());
    Assert.Equal(1, await TableBRowCount());
}

private async Task NestedTransactionWithCompleteInside()
{
    using var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
    await using var connection = new NpgsqlConnection(ConnectionString);

    await connection.ExecuteAsync(InsertTableBEntryQuery, new { value = 321 });

    scope.Complete();
}

Root transaction will fail when nested transaction isn't completed

[Fact]
public async Task NestedTransactionsOnlyRootWithComplete()
{
    await ClearDatabase();

    try
    {
        using var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
        await using var connection = new NpgsqlConnection(ConnectionString);

        await connection.ExecuteAsync(InsertTableAEntryQuery, new { value = 123 });

        await NestedTransactionWithoutCompleteInside();

        scope.Complete();
    }
    catch (TransactionAbortedException)
    {
        Assert.True(true);
    }
    catch
    {
        Assert.True(false);
    }
}

private async Task NestedTransactionWithoutCompleteInside()
{
    using var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
    await using var connection = new NpgsqlConnection(ConnectionString);

    await connection.ExecuteAsync(InsertTableBEntryQuery, new { value = 321 });
}

Summary

  • If we nest transactions, all of them succeed or all of them will fail.
  • Don't forget to call transaction scope complete method.
  • Transaction complete method will fail if connection is in invalid state due to exception.