Understanding how transaction.complete function?

Question

Hi All we have started to use transaction scope and below is the code snippet. What we need to understand is based on our understading after each of the using for connection the particular connection will be disposed/closed right? So since its closed how does the transaction.complete works?

using (TransactionScope transScope = new TransactionScope())
{
   try
   {
      string myConnStringLocal = "User Id=***;Password=****;Host=" + globalSettings.settingLocalIP + ";Database=" + globalSettings.settingLocalDB;
      using (MySqlConnection connectionLocal = new MySqlConnection(myConnStringLocal))
      {
         try{
         connectionLocal.Open();
         }
         Catch(Exception e)
         {

         }
         finally{
         connectionLocal.Close();
         }
      }

      string myConnStringCentral = "User Id=***;Password=*****;Host=" + globalSettings.settingCentralIP + ";Database=" + globalSettings.settingCentralDB;
      using (MySqlConnection connectionCentral = new MySqlConnection(myConnStringCentral))
      {
         try{
         connectionCentral.Open();
         }
         Catch(Exception e)
         {

         }
         finally{
         connectionCentral.Close();
         }

      }
      string myConnStringCentralCopy = "User Id=*****;Password=*****;Host=" + globalSettings.settingCentralCopyIP + ";Database=" + globalSettings.settingCentralCopyDB;
      using (MySqlConnection connectionCentralCopy = new MySqlConnection(myConnStringCentralCopy))
      {
         try{
         connectionCentralCopy.Open();
         }
         Catch(Exception e)
         {

         }
         finally{
         connectionCentralCopy.Close();
         }
      }
      transScope.Complete();
      Console.WriteLine("Transaction is completed");
   }
   catch (Exception)
   {
      Console.WriteLine("Transaction is rolled back");
   }
}

Answer 1

TransactionScope.Complete tells all of the Transaction Managers that they are good to commit this transaction. It is not a guarantee that everything will actually commit. After the Complete method is called all the transaction managers initiate a commit individually and if all of the transaction Managers succeed then the transaction is considered as completed successfully. You may refer to this link for further details

Answer 2

When you connect to multiple databases in a single TransactionScope, the transaction is escalated to a distributed transaction and coordinated by MSDTC using 2-phase commit.

Regarding connection close - this is special case when connection is closed inside a TransactionScope, internally it is managed by System.Transactions infrastructure, so db session may still be open even though connection closed from your end.

See this note on MSDN:

Pending transactions started using Transact-SQL or BeginTransaction are automatically rolled back when the connection is reset if connection pooling is enabled. If connection pooling is off, the transaction is rolled back after SqlConnection.Close is called. Transactions started through System.Transactions are controlled through the System.Transactions infrastructure, and are not affected by SqlConnection.Close

EDIT Based on your comments, here is what you can do:

try
{
   using (TransactionScope transScope = new TransactionScope())
   {
      string myConnStringLocal = ...;
      using (var connectionLocal = new MySqlConnection(myConnStringLocal))
      {
         connectionLocal.Open();
         // do your DB update 

      } //no need to close connection explicitly, the using() {..} statement does that for you

      string myConnStringCentral = ...;
      using (var connectionCentral = new MySqlConnection(myConnStringCentral))
      {
         connectionCentral.Open();
         // do your DB update

      } //no need to close connection explicitly, the using() {..} statement does that for you

      string myConnStringCentralCopy = ...;
      using (var connectionCentralCopy = new MySqlConnection(myConnStringCentralCopy))
      {         
         connectionCentralCopy.Open();
         // do your DB update

      } //no need to close connection explicitly, the using() {..} statement does that for you

      transScope.Complete();

      Console.WriteLine("Transaction is completed");

   } //no need to dispose transactionScope explicitly, the using() {..} statement does that for you
}
catch (Exception)
{
      // If any exception occurs in the try block above transScope.Complete() line will be caught here
      // and will automatically cause the transaction to rollback.
      Console.WriteLine("Transaction is rolled back");
}

// You can then start new TransactionScope if you want to further update more than one DB in a transactional manner.
try
{
   using (TransactionScope transScope = new TransactionScope())
   {
      //...

   }
}
catch (Exception)
{
     //...
}