错误 - LINQ /与多个数据库连接的TransactionScope [英] Error - LINQ/TransactionScope with multiple database connections

问题描述

我有一个宏大的一次包装相同的SQL Server上的一对夫妇的交易，以2个不同的数据库。我最初在和网络DTC访问麻烦，我解决了。现在，我继续得到错误是与基础事务管理器通信失败。

I'm having a helluva time wrapping a couple transactions to 2 different databases on the same SQL Server. I initially was having trouble with network DTC access and I resolved that. Now, the error that I continue to get is "Communication with the underlying transaction manager has failed."

我们在一个数据库中的一些客户档案，当这些配置文件过时我们希望将其移动到存储的存档数据库。此举仅仅是将其添加到归档数据库，并从主/活动数据库删除它们（幽默斜体）。我对每个数据库一个DataContext。下面的代码执行的添加，然后试图用第二个DataContext的时候沾到删除错误。我只一直在与LINQ几个月，我已经冲刷了过去几天的文章。我想知道如果有什么是错我的代码或者如果还有什么不与我们在VMware上运行的DTC或???

We have some customer profiles in a database and when these profiles become outdated we want to move them to an 'archive' database for storage. The move is simply (italics for humor) adding them to the archive database and deleting them from the main/live database. I have a DataContext for each database. The code below performs the Add and then gets the error on the Delete when trying to use the second DataContext. I've only been working with LINQ for a few months and I've scoured articles for the past couple of days. I'd like to know if anything is wrong with my code or if there is still something not configured properly with the DTC or ???

适当配置对于我的工作站和服务器。
- 工作站是Windows 7的SP1
- 服务器是Windows和SQL Server 2008R2

We're running on VMware for my workstation and the server. - Workstation is Windows 7 SP1 - Server is Windows and SQL Server 2008R2

例行的'移动'：

private int MoveProfileToArchiveDB( int iProfileId )
{
    int rc = RC.UnknownError;

    // get new Archive profile object
    ProfileArchive.ProfileInfo piArchive = new ProfileArchive.ProfileInfo();

    // 'Live' DataContext
    using ( ProfileDataContext dbLive = new ProfileDataContext() )
    {
        // get Live profile
        ProfileInfo piLive = ProfileInfo.GetProfile( dbLive, iProfileId );

        // copy Live data to Archive profile object... including the id
        ProfileArchive.ProfileInfo.CopyFromLive( piLive, piArchive, true );
    }

    bool bArchiveProfileExists = ProfileArchive.ProfileInfo.ProfileExists( piArchive.id );

    // make the move a transaction... 
    using ( TransactionScope ts = new TransactionScope() )
    {
        // Add/Update to Archive db
        using ( ProfileArchiveDataContext dbArchive = new ProfileArchiveDataContext() )
        {
            // if this profile already exists in the Archive db...
            if ( bArchiveProfileExists )
            {
                // update the personal profile in Archive db
                rc = ProfileArchive.ProfileInfo.UpdateProfile( dbArchive, piArchive );
            }
            else
            {
                // add this personal profile to the archive db
                int iArchiveId = 0;
                piArchive.ArchiveDate = DateTime.Now;
                rc = ProfileArchive.ProfileInfo.AddProfile( dbArchive, piArchive, ref iArchiveId );
            }

            // if Add/Update was successful...
            if ( rc == RC.Success )
            {
                // Delete from the Live db
                using ( ProfileDataContext dbLive = new ProfileDataContext() )
                {
                    // delete the personal profile from the Profile DB
                    rc = ProfileInfo.DeleteProfileExecCmd( dbLive, iProfileId );    // *** ERROR HERE ***
                    if ( rc == RC.Success )
                    {
                        // Transaction End (completed)
                        ts.Complete();
                    }
                }
            }
        }

    }

    return rc;
}

注：

NOTES:

1. 我有删除和TransactionScope的以外，他们所有的工作几种不同的方法。


2. ProfileInfo是主个人资料表，大致是两个生活在同和归档数据库。

任何帮助，不胜感激！谢谢了...

Any help is greatly appreciated! Thanks much...

推荐答案

而不是继续十字交叉的意见，我决定发布这个作为一个答案吧。

Rather than continue criss cross comments, I decided to post this as an answer instead.

• 不使用错误代码。这就是例外是。代码流是更难以阅读和错误代码返回邀请被忽略。例外让用户轻松代码来读取和很不容易出错。

• don't use error codes. That's what exceptions are for. The code flow is more difficult to read and error code returns invite to be ignored. Exceptions make the code easier to read and far less error prone.

如果你使用一个TransactionScope，记得要经常设置隔离级别明确。使用是有害的新的TransactionScope（）查看 。 SERIALIZABLE的隐含隔离级别几乎从来没有要求，具有极大的负面影响的规模。

If you use a TransactionScope, remember to always set the isolation level explicitly. See using new TransactionScope() Considered Harmful. The implicit isolation level of SERIALIZABLE is almost never called for and has tremendous negative scale impact.

交易升级。当多个连接在一个事务范围内打开，他们可以升级的事务分布式事务。该行为从版本的不同而不同的版本，一些人已经尝试记录它，例如。 的TransactionScope：交易行为升级：

Transaction escalation. Whenever multiple connections are opened inside a transaction scope they can escalate the transaction to a distributed transaction. The behavior differ from version to version, some have tried to document it, eg. TransactionScope: transaction escalation behavior:

SQL Server 2008是更聪明那么SQL Server 2005中，可以
如果在所有数据库连接自动侦测一定
事务指向同一物理数据库。如果是这种情况，
中的事务仍然是一个本地事务，它不升级了
分布式事务。不幸的是有几个注意事项：

SQL Server 2008 is much more intelligent then SQL Server 2005 and can automatically detect if all the database connections in a certain transaction point to the same physical database. If this is the case, the transaction remains a local transaction and it is not escalated to a distributed transaction. Unfortunately there are a few caveats:

• 如果打开的数据库连接嵌套，该交易仍
  升级为分布式事务。


• 如果在交易中，
  连接到另一台耐用的资源取得，交易
  立即升级为分布式事务。

由于您的连接（从使用的两个数据contextes）指向不同的数据库，甚至在SQL Server 2008中您的TransactionScope将升级到。分布式事务

Since your connection (from the two data contextes used) point to different databases, even on SQL Server 2008 your TransactionScope will escalate to a distributed transaction.

谋取您的应用程序到DTC在至少在两方面危害：

Enlisting your application into DTC is harmful in at least two ways:

• 吞吐量将通过地板下沉。数据库可以支持每秒几千地方事务，但每秒分布式事务的只有几十（也许几百个低）。这主要是因为两相的复杂性，提交。


• DTC需要协调：MSDTC。 [待MSDTC取得安全性增强]进行配置更具挑战性，它肯定是出乎意料的开发者发现，MSDTC在他们的应用程序必需的。 链接的文章中描述的步骤是你缺少的可能就是现在。对于Windows Vista / Windows 7中/ Windows Server 2008中/在Windows Server 2008R2的步骤中的 MSDTC在Windows Vista和Windows Server 2008 中的如何配置Windows 2008 DTC 以及其他类似的文章。

• throughput will sink through the floor. A database can support few thousand local transactions per second, but only tens (maybe low hundreds) of distributed transactions per second. Primarily this is because of the complexity of two phase commit.
• DTC requires a coordinator: MSDTC. The [security enhancements made to MSDTC] make configuration more challenging and it certainly is unexpected for devs to discover that MSDTC is required in their app. The steps described in the article linked are probably what you're missing right now. For Windows Vista/Windows 7/Windows Server 2008/Windows Server 2008R2 the steps are described in MSDTC in Windows Vista and Windows Server 2008, in How to configure DTC on Windows 2008 and other similar articles.

现在。有更好的工具， SSIS 是一个典型的例子。运行SSIS每晚调度作业将更为有效地传递那些未使用的配置文件。

Now if you fix MSDTC communication following the articles mentioned above, your code should be working, but I still believe this archiving should not occur in the client code running EF. There are far better tools, SSIS being a prime example. A nightly scheduled job running SSIS would transfer those unused profiles far more efficiently.

这篇关于错误 - LINQ /与多个数据库连接的TransactionScope的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！