CODEWITHSUNDEEP
c#sql-servertransactionstransactionscopemsdtc
DooDoo
DooDoo

Reputation: 13487

TransactionScope and 2-phase commit In two different SQL Server server

Please consider this code from MSDN:

Implementing an Implicit Transaction using Transaction Scope

and this comment:

To test this code, you can connect to two different databases on the same server by altering the connection string, or to another 3rd party RDBMS by altering the code in the connection2 code block.

and I was surprised. I was surprised by all my knowledge. I learned in Advanced Database course that:

  1. Run commands on two separate DBMS by default isn't transaction unless we have transaction manager on each RDBMS and a distributed transaction manager that handle 2-phase commit.

  2. Each transaction manager should have logging ability to manage transactions.

So I have some questions:

  1. Does TransactionScope do distributed transaction management?
  2. If so what is the logging mechanism?

Please consider this code:

using (TransactionScope scope = new TransactionScope())
{
    using (SqlConnection connection1 = new SqlConnection(connectString1))
    {
         //Do Some work 1
    }

    using (SqlConnection connection2 = new SqlConnection(connectString2))
    {
         //Do Some Work 2
    }
    scope.Complete();
}

and consider these steps:

A commit transaction is issued to Server 1, which gets it and successfully commits the transaction. Commit transaction is issued to Server 2 and suddenly after it, Server 1 becomes unavailable. Because of some network error Server 2 cannot commit the transaction and sends back an exception to TransactionScope. Now Server 1 is in correct status but it isn't available to rollback the transaction. I want to know how TransactionScope handles this situation?

Upvotes: 0

Views: 2619

Answers (1)

Kit
Kit

Reputation: 21769

1) Does TransactionScope do distributed transaction management? If so what is the logging mechanism?

Yes. The first data source starts a new local transaction unless there was a previous ambient transaction. If the ambient was with a different data source the TransactionScope elevates the transaction to a distributed transaction by enlisting the local transaction. The TransactionScope then starts communicating with the distributed transaction coordinator (DTC), which communicates with each source's transaction manager.

The logging mechanism is that of each data source's transaction manager, which you sort of mentioned:

2) Each transaction manager should have logging ability to manage transactions.

Now in your scenario where server 1 goes offline after successfully committing the transaction and can't rollback because server 2 couldn't commit... well that will likely allow server 1 to still recover and rollback. I don't know the specifics, but each transaction manager has a number of mechanisms to correctly rollback, and yes the log does come into play here.

Consider this rough sequence of steps including a two-phase commit:

  1. Server 1 and Server 2 both vote to commit in phase 1 (the voting phase) and inform the DTC and thus TransactionScope of these votes.
  2. Server 1 prior to voting records what it will do in the log.
  3. The DTC then tells Server 1 and 2 to commit (the commit phase).
  4. Server 1 commits, records this fact, and informs the DTC.
  5. Server 2 can't commit so rolls back and informs the DTC.
  6. DTC attempts to tell server 1 to abort and informs TransactionScope.
  7. TransactionScope comes back with an exception.

Depending on the timing, let's say server 1 drops out somewhere after 5, 6, or 7. At this point server 1's log A) may or B) may not have enough information to know what to do.

If server 1 is still running or gets restarted) it checks the log for issues and in case A sees that it needs to run a compensating transaction. In case B it doesn't have enough information, so it's up to something (or someone) to fix the problem out of band.

Upvotes: 2

Related Questions