错误:底层提供程序在打开时失败.如何解决? [英] Error: The underlying provider failed on Open. How to resolve it?

问题描述

我有类似这样的代码的功能:

I have function that with code similar to this:

using(var db = new MyDbContext())
{
    //a whole bunch of code, db is working.

    //then I try opening another DbContext like so
    using(var dba = new AnotherDbContext())
    {
        //about 2 lines of code just to get something from a database
    }
}

然后当我到达第二个 DbContext 时收到 2 条错误消息:

And then I get 2 error messages when I get to the second DbContext:

底层提供程序在 Open 上失败."&服务器‘myserver’上的 MSDTC 不可用."

"The underlying provider failed on Open." & "MSDTC on server 'myserver' is unavailable."

有人知道发生这种情况的原因吗?我可以一次打开 2 个 DbContext 吗?

Does anybody know the reason why this is happening? Can I open 2 DbContexts at once?

推荐答案

在第一个场景中，您正在嵌套 AnotherDbContext.为每个数据库打开一个连接.当您在 using 块中调用服务方法时，会在 MyDbContext 中打开一个新连接，而另一个连接已打开.这会导致您的事务被提升为分布式事务，并且部分提交的数据(服务中 dba.SaveChanges 调用的结果)无法从您的外部连接中获得.另请注意，分布式事务是慢得多，因此会降低性能.

In the first scenario, you are nesting AnotherDbContext. A connection to the database is opened for each on of them. When you call your service method within the using block, a new connection is opened within the MyDbContextwhile there is another one already open. This causes your transaction to be promoted to a distributed transaction, and partially committed data (the result of the dba.SaveChanges call in the service) not being available from your outer connection.Also note that distributed transactions are far slower and thus, this has the side effect of degrading performance.

private void btnTwoConnectionsNested_Click(object sender, EventArgs e)
{
    string connectionString = @"Data Source=" + tbServer.Text
        + @";Initial Catalog=master;Integrated Security=True; timeout=0";

    using (TransactionScope transactionScope = new TransactionScope())
    {
        SqlConnection connectionOne = new SqlConnection(connectionString);
        SqlConnection connectionTwo = new SqlConnection(connectionString);

        try
        {
            //2 connections, nested
            connectionOne.Open();
            connectionTwo.Open(); // escalates to DTC on 05 and 08
            connectionTwo.Close();
            connectionOne.Close();

            MessageBox.Show("Success");
        }
        catch (Exception ex)
        {
            MessageBox.Show("ERROR: " + ex.Message);
        }
        finally
        {
            connectionOne.Dispose();
            connectionTwo.Dispose();
        }
    }
}

在一个事务范围内，它将打开两个嵌套的连接.结果:

Within one Transaction Scope, it will open two connections, nested. The result:

针对 SQL Server 2005 实例:服务器SERVERNAME"上的 MSDTC 不可用.

Against SQL Server 2005 Instance: MSDTC on server ‘SERVERNAME’ is unavailable.

针对 SQL Server 2008 实例:
服务器SERVERNAME"上的 MSDTC 不可用.

Against SQL Server 2008 Instance:
MSDTC on server ‘SERVERNAME’ is unavailable.

SQL Server 确实并且应该在两个版本中升级为嵌套连接的 DTC.

SQL Server does, and should, escalate to DTC for a nested connection in both versions.

在 SQL Server 2005 和 2008 中，嵌套连接将升级为 DTC.一次打开一个连接将在 2005 年升级为 DTC，但不会在 2008 年升级.

Nested connections will escalate to DTC in SQL Server 2005 and 2008. Opening one connection at a time will escalate to DTC in 2005, but WILL NOT in 2008.

这篇关于错误:底层提供程序在打开时失败.如何解决?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！