Error - LINQ/TransactionScope with multiple database connections

Question

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."

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 ???

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

Routine for the 'Move':

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:

I have a few different methods for the Delete and they all work outside the TransactionScope.
ProfileInfo is the main profile table and is roughly the same for both Live and Archive databases.

Any help is greatly appreciated! Thanks much...

Does the account running MSDTC have the correct permissions to access the database? — Kane, Jun 27 '12 at 04:32
Your archiving is going row-by-row on the client side and does DTC between two collocated DBs? When all you have is a hammer... You do not have to use EF for **everything**. Try doing it server side. `DELETE ... OUTPUT ... INTO targetdb... FROM sourcedb ... WHERE` would be a grate starting point. An SSIS job is another great alternative. Even a T-SQL cursor based solution. Just don't route every row through the client... — Remus Rusanu, Jun 27 '12 at 08:04
@Kane - I believe so. There were 2 other errors related to MSDTC that I've overcome. - "Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool." and "The partner transaction manager has disabled its support for remote/network transactions." Now it makes the call to the Delete method and gets the error I posted - "Communication with the underlying transaction manager has failed." — TravelDev, Jun 27 '12 at 16:43
@Remus - I've been told by another person that LINQ has it's place but its not for everything. I'm not much of a SQL scripter, but if that makes more sense then that's what I'll do. I was leaning that way yesterday and will likely do that if that's what makes the best sense. I would like to learn from this if there is a limitation, bug, etc. From what I've read this 'should' work as is. — TravelDev, Jun 27 '12 at 16:49
@TravelDev: it will work. But it will be one or two orders of magnitude less performant than it could. An you will have to fix issues like DTC coordination when you could be instead coding something useful. — Remus Rusanu, Jun 27 '12 at 17:06
@RemusRusanu - Ok. So you're confident that the code should work and this is a DTC issue? And, you're also agreeing that when it comes to processing a significant amount of data its best to leave this out of LINQ's hands? — TravelDev, Jun 27 '12 at 18:52

score 8 · Accepted Answer · edited Aug 13 '21 at 14:46

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.
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.
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 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:

If the open database connections are nested, the transaction is still escalated to a distributed transaction.

If in the transaction, a connection is made to another durable resource, the transaction is immediately escalated to a distributed transaction.

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.

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

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.

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.

This is very helpful. I would take it as the answer – voddy May 19 '14 at 23:47 — voddy, May 19 '14 at 23:47

Error - LINQ/TransactionScope with multiple database connections

1 Answers1

Linked