Error - LINQ / TransactionScope with multiple database connections

Question

I have helluva time wrapping a pair of transactions in two different databases on the same SQL Server. I initially had problems accessing the DTC network, and I solved it. Now the error that I keep getting is "Communication with the underlying transaction manager failed."

We have some customer profiles in the database, and when these profiles become obsolete, we want to move them to the archive database for storage. It's easy (italics for humor), adding them to the archive database and removing them from the main / live database. I have a DataContext for each database. In the code below, Add is executed, and then an error occurs while deleting when trying to use a second DataContext. I have only been working with LINQ for several months, and I have been looking at articles over the past couple of days. I would like to know that something is wrong with my code or if something is not configured properly with DTC or ???

We work on VMware for my workstation and server. - Workstation - Windows 7 SP1 - Server - this is Windows and SQL Server 2008R2

Routine for "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 several different methods for Delete, and they all work outside of TransactionScope.
• ProfileInfo is the main profile table and is approximately the same for Live and Archive databases.

Any help is much appreciated! Many thanks...

Answer 1

Instead of continuing to cross-comment, I decided to post this as an answer instead.

• Do not use error codes. What are the exceptions for? The code stream is harder to read, and the error code returns an ignore prompt. Exceptions make code easier to read and much less error prone.

• If you use TransactionScope, be sure to specify the isolation level explicitly. See using the new TransactionScope (), which is considered malicious . The implicit isolation level of SERIALIZABLE is almost never called and has a huge negative effect on scale.

• Escalation of transactions. When multiple connections are opened in the transaction area, they can escalate the transaction into a distributed transaction. The behavior is different from version to version, some tried to document it, for example. TransactionScope: transaction escalation behavior :

SQL Server 2008 is much more intelligent than SQL Server 2005, and can automatically detect that all database connections in a specific transaction point to the same physical database. If so, the transaction remains a local transaction, and it is not a distributed transaction. Unfortunately, there are a few caveats:

• If open database connections are nested, the transaction is still escalated into a distributed transaction.
• If the transaction connects to another long-term resource, the transaction immediately moves to the distributed transaction.

Since your connection (from the two data contexts used) points to different databases, even on SQL Server 2008 your TransactionScope will grow into a distributed transaction.

Including your application in DTC is harmful in at least two ways:

• bandwidth will fall through the floor. A database can support several thousand local transactions per second, but only tens (maybe several hundreds) of distributed transactions per second. This is primarily due to the complexity of two-phase fixation .
• DTC requires a coordinator: MSDTC. [Security enhancements made in MSDTC] make the configuration more complex, and of course, it is unexpected for developers to discover that MSDTC is required in their application. The steps described in the related article are probably missing you 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 in Windows 2008 and other similar articles.

Now, if you fix the MSDTC connection in accordance with the articles mentioned above, your code should work, but I still think that this should not happen in the client code with EF running. There are much better tools, SSIS is a prime example. Scheduled night work running SSIS will transfer these unused profiles much more efficiently.