6

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
Rustam Salakhutdinov
  • 774
  • 7
  • 25
  • I would say that it has nothing to do with Npgsql/Transaction or Dapper. If you need to guarantee inserts order then you can't use Task.WhenAll. This is why it works awaiting each insert in order. –  Jun 03 '18 at 07:41
  • @OlivierMATROT. I await company insertion as my first command. I insert data in dependent tables only AFTER company was inserted. And the order of inserting INSIDE Task.WhenAll is not important for me, because the company should be already existent at this moment. – Rustam Salakhutdinov Jun 03 '18 at 07:55
  • Right. Then the problem is that using Task.WhenAll() is spawning new connections to the database. All your commands must be run inside the same transaction, thus the same connection. Take a look at the PostgreSQL logs to confirm. –  Jun 03 '18 at 08:47

1 Answers1

7

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.