Multiple Connections and DTC

Marjon1 posted on Thursday, May 07, 2009

I know that this problem has been asked many times in the past, but can't remember this specific scenario and I wanted to throw it out there for the communities input.

If I open two connections within a single transaction to two seperate databases,
with the following process:

- Open connection to DB1
- Do some stuff on DB1
- Close connection on DB1

- Open conneciton on DB2
- Do some stuff and then close DB2

Will this cause the dreaded MS-DTC issue to popup?

RockfordLhotka replied on Thursday, May 07, 2009

Yes, I think that triggers the use of the DTC.

In this case it isn't even dreaded, because you are interacting with two different databases, so the only way your data access can be transactionally protected is by using the DTC. That's the only technology that can transactionally protect any operations that span 2+ databases.

The dreaded problem with DTC is where you open two connections to the same database, because there are less expensive ways to transactionally protect the single database scenario.

tmg4340 replied on Thursday, May 07, 2009

Maybe I'm missing/misunderstanding something, but wouldn't closing the first database connection without completing the transaction cause the DB1 activity to roll back? If so, I would think that would also potentially eliminate the DTC.

- Scott

RockfordLhotka replied on Thursday, May 07, 2009

If this entire bit of work is enclosed in a single TransactionScope then
both connections should be managed by that same transaction, regardless of
whether the connections are closed or not prior to the transaction
committing or rolling back.

Remember, all the data portal does is wraps your code in this basic
structure:

using (var tr = new TransactionScope())
{
// call your data method (DataPortal_XYZ)
tr.Commit();
}

Nothing fancy, and nothing you couldn't do yourself. So the normal rules
associated with a TransactionScope apply.

Rocky

Marjon1 replied on Friday, May 08, 2009

Thanks for the fast response Rocky, greatly appreciated.