事务无法通过Task.WhenAll处理并行命令 [英] Transaction can't handle parallel commands via Task.WhenAll

问题描述

我的Postgres DB中有一些主表（例如Companies）和许多从属表（例如CompanyAddresses，CompanyPaymentInfos等）：

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),
...);

我在我的C＃代码中使用标准库中的事务：

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();
    }
}

每个插入命令仅生成SQL代码执行而没有任何内部事务。

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

在执行DoChange之后，出现此错误：

And after DoChange execution I get this error:

Npgsql.PostgresException（0x80004005）：23503：在
表 companyaddresses上插入或更新违反了外键约束
companyaddresses_companyid_fkey

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:

• 为什么会出现错误？


• 为什么在插入CompanyAddress而不是CompanyPaymentInfo时会出现错误？

如果我将 DoChange 更改为顺序执行，则一切正常：

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:

• 我使用Net Core 2.0


• 我在标准设置下使用Postgres 10.4（ ReadCommitted作为隔离级别，依此类推）。另外，我在连接字符串中添加了 enlist = true 来使事务正常工作。


• 我使用Npgsql 3.2.5和Dapper 1.50 .2我的 Insert 命令

• 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

推荐答案

这里没有什么魔术，您会收到错误消息，因为插入CompanyAddress时使用的连接不是您认为的那个。

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.

这是一个新的连接。运行ComapnyPaymentInfo插入后，您使用的是已经与您的交易绑定的连接。由于您已经等待了上一步，因此它正在等待新命令。

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.

使用Task.WhenAll（）会尝试使用多个线程。如果某个连接正忙于运行一个命令，则该命令将不被使用，并且将产生一个新命令。

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.

这篇关于事务无法通过Task.WhenAll处理并行命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！