CODEWITHSUNDEEP
c#oracle-databasetransactionstransactionscopedistributed-transactions
burgen
burgen

Reputation: 144

Attempts to open multiple Oracle connections within context of a TransactionScope result in ORA-12514

I'm using a combination of Dapper and Quartz.Net (scheduler) to save information for a simple object to an 11gR2 database (Quartz is self contained and opens and closes connections by itself, the Dapper call executes a procedure via a connection maintained by a unit of work that also controls the transaction).

I'm using the Microsoft sanctioned method of TransactionScope to initiate a transaction and have connections set to automatically enlist in a distributed transaction if one is already running. I'm using the Managed Oracle driver for 12c so haven't got a 12c client installed.

Here's what's odd. The first connection always opens successfully and gets enlisted to the transaction, whichever way round I do it. The second always fails, and throws an ORA-12514 error.

Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-12514: TNS:listener does not currently know of service requested in connect descriptor ---> OracleInternal.Network.NetworkException (0x000030E2): ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
   at OracleInternal.Network.OracleCommunication.DoConnect(String tnsDescriptor)
   at OracleInternal.Network.OracleCommunication.Connect(String tnsDescriptor, Boolean externalAuth, String instanceName)
   at OracleInternal.ServiceObjects.OracleConnectionImpl.Connect(ConnectionString cs, Boolean bOpenEndUserSession, String instanceName)
   at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)
   at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)
   at OracleInternal.ConnectionPool.PoolManager`3.GetEnlisted(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp)
   at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword)
   at Oracle.ManagedDataAccess.Client.OracleConnection.Open()

I doubt this is a problem with the underlying connectivity, because if I remove the transaction scope and just run the save operation as two individual calls, everything works fine. The below is a rudimentary example of what I'm doing, trimmed down and anonymised.

using (var tx = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions {IsolationLevel = isolationLevel})
{
    connection.Open(); // works, BUT if I move this line under the next one, the calendar save will succeed and this will fail.
    quartzScheduler.AddCalendar(calendar.Name, c1, true, true); // calls underlying Quartz class that opens a connection, saves to the db and closes a connection - *** this line will fail ***
    _repository.Save(_connection);
}

MS DTC service is running locally (Windows 7), Oracle MTS Recovery service (for 11 client) is installed and also running.

I read the help that came with the Oracle driver and as suggested, added both 64 and 32 bit drivers in x86 and x64 folders in the project and set to copy always as part of the build; however I don't think I'm even getting this far - it's like the second connection always fails and I am unable to determine why.

I am running out of ideas, the help in this area seems rather sparse once you go outside of saving to one specific schema and db.

All ideas welcomed, thanks in advance!

Additional Edit

This morning I have also attempted a simple test to promote a standard transaction by opening two connections within a transaction scope. The same problem occurs:

using (var ts = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions {IsolationLevel = IsolationLevel.ReadCommitted}, EnterpriseServicesInteropOption.Full)) {
   var currentTransaction = Transaction.Current;
   var identifier = currentTransaction.TransactionInformation.DistributedIdentifier;
   using (var oc1 = new CustomOracleConnection(_connectionString, 50, 50)) {
      oc1.Open();
      using (var oc2 = new CustomOracleConnection(_quartzString, 50, 50)) {
         oc2.Open();
      }
   }
}

Looking at the trace info I then wondered if this is something to do with pooling. I tried opening the second connection first outside of the transaction scope, then tried it all again. This works.... so it looks like the issue is that the Oracle driver has a bug when attempting to open a second connnection that's not in the pool?

Upvotes: 1

Views: 1341

Answers (1)

burgen
burgen

Reputation: 144

Having consulted with Oracle, I have learned that:
a) This is an acknowledged bug with the Oracle driver, and has been fixed in their code base, but is unlikely to make the 4th release so it may be a while before we see it in release 5.
b) A temporary workaround is to use SERVICE_NAME instead of SID when connecting to the database.

For full details see the Oracle ODP.Net forum https://community.oracle.com/message/13275819

Upvotes: 2

Related Questions