0

Hi I have problem with enlist to distributed transaction after database restart.

My environment:

  • Windows 7 x64 with SP1
  • Oracle Database 11g Express Edition
  • ODP.NET 4.112.3.0

My program:

const string connectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=LOCALHOST)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));User Id=system;Password=sasa;";

static void Main(string[] args)
{
    using (TransactionScope scope = new TransactionScope())
    {
        while (true)
        {
           using (OracleConnection connection = new OracleConnection(connectionString))
           {
               try
               {
                   connection.Open();
                   Console.WriteLine("Connection opened");
               }
               catch (Exception ex)
               {
                   Console.WriteLine(ex.Message);
               }
           }
           Thread.Sleep(1000);
        }
    }
}

All works great after application start. When I start stopping database I get NRE and some exception telling me that database shutdown is in progress. After start it again i'm receiving error - Unable to enlist in a distributed transaction. Connection opened is no longer printed.

Output:

Connection opened
Connection opened
Connection opened
Connection opened
Connection opened
-- here I'm stopping database
ORA-12518: TNS:listener could not hand off client connection
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
-- here database is stopped I suppose
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
-- here I'm starting database again
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
ORA-1033: ORACLE initialization or shutdown in progress
Unable to enlist in a distributed transaction
Unable to enlist in a distributed transaction
Unable to enlist in a distributed transaction
Unable to enlist in a distributed transaction
Unable to enlist in a distributed transaction
...
  • What is the reason of that behavior?
  • How to diagnose what's happen?
Sławomir Rosiek
  • 4,038
  • 3
  • 25
  • 43

1 Answers1

1

You have an invalid test. You are looping inside the context of a single transaction. When the database goes down any in-progress distributed transaction is aborted. Your loop is trying to bring up a new connection under that already-dead transaction.

I'm not sure exactly what you are trying to test but moving the TransactionScope inside of the while loop should fix it.

b_levitt
  • 7,059
  • 2
  • 41
  • 56
  • Any reference supports your made statements "When the database goes down any in-progress distributed transaction is aborted. ...."? Besides, if you have any good reference talking about transactions and multiple dbcontext(s) in different scenarios single scope vs multiple scopes, single thread vs multiple threads,....? – Abdulkarim Kanaan Jun 29 '17 at 23:38
  • I guess how could it be anything else? The default is to abort, and if the db is not available to complete the 2PC, it's going to rollback. System.Transactions has a DependentTransaction class for use in multithreading. – b_levitt Jun 30 '17 at 13:55
  • I got your point and it seems logical, but I wanted to get more details about these stuff. However, so in case of a single transaction is used, when it is aborted, so the application will fail to open a new connection under the scope because of the inability to enlist its connections. What's about the scenario of dependent transaction. I mean in case one of the dependent transaction went down, what would happen? In this case, you can also cancel the process and dispose the main scope so nothing would be saved into DB? am I right? or is there another behavior should be considered? – Abdulkarim Kanaan Jul 04 '17 at 00:03