Transaction can't handle parallel commands via Task.WhenAll

Question

I have some main table (like Companies) and a lot of dependent tables (like CompanyAddresses, CompanyPaymentInfos, etc.) in my Postgres DB:

CREATE TABLE Companies (
Id uuid NOT NULL PRIMARY KEY,
...);

CREATE TABLE CompanyAddresses(
CompanyId uuid NOT NULL PRIMARY KEY REFERENCES Companies(Id),
...);

CREATE TABLE CompanyPaymentInfos(
CompanyId uuid NOT NULL PRIMARY KEY REFERENCES Companies(Id),
...);

I use transactions from standard library in my C# code:

private TransactionScope GeTransactionScope()
{
    return new TransactionScope(
        TransactionScopeOption.RequiresNew,
        new TransactionOptions
        {
            IsolationLevel = IsolationLevel.ReadCommitted
        },
        TransactionScopeAsyncFlowOption.Enabled);
}

private async Task DoChange(...)
{
    using (var scope = GeTransactionScope())
    {
        await Insert(Company);

        await Task.WhenAll(
            Insert(CompanyPaymentInfo),
            Insert(CompanyAddress),
            Insert(CompanyTags),
            // so on
        );

        scope.Complete();
    }
}

Each Insert command produces only an execution of SQL code without any inner transactions.

And after DoChange execution I get this error:

Npgsql.PostgresException (0x80004005): 23503: insert or update on table "companyaddresses" violates foreign key constraint "companyaddresses_companyid_fkey"

And of course, I have a lot of questions like:

• Why do I get an error?
• Why do I get the error in time of the CompanyAddress insertion, not CompanyPaymentInfo?

If I change DoChange to sequential execution everything works fine:

private void DoChange()
{
    using (var scope = GeTransactionScope())
    {
        await Insert(Company);
        await Insert(CompanyPaymentInfo);
        await Insert(CompanyAddress);
        await Insert(CompanyTags);
        // ...

        scope.Complete();
    }
}

---

Maybe it helps:

• I use Net Core 2.0
• I use Postgres 10.4 with standard settings (ReadCommitted as the isolation level and so on). Also, I have added enlist=true to my connection string to make transactions work.
• I use Npgsql 3.2.5 and Dapper 1.50.2 inside my Insert command

Answer 1

There is nothing magic here, you get the error because the connection you're using when inserting CompanyAddress is not the one you think it is.

It is a new connection. When ComapnyPaymentInfo insert is ran, you're using the connection that is already tied to your transaction. It is pending new commands because you've awaited in the previous step.

The use of Task.WhenAll() on the other hand will try to use multiple threads. If a connection is busy running a command it won't be used and a new one will be spawn.

Remember that when using Transactions, you have only one connection available, you can't benefit from parallelism.