Forum Stats

  • 3,769,008 Users
  • 2,252,898 Discussions
  • 7,874,840 Comments

Discussions

Attempts to open multiple connections within a Transaction result in ORA-12514

I'm using a combination of Dapper and Quartz.Net to save information for a calendar object to an 11gR2 database (Quartz is self contained, the Dapper call is calling a procedure).

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 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.

<snip>

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 and saves to the db - this line will fail

     _repository.Save(_connection);

}

</snip>

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

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.

It looks like I may also need a Oracle MTS service installed but not sure where to get this now that 12c doesn't have a client install any longer.

Any ideas?

Tagged:

Answers

  • 7475ccc0-5d51-42dd-955a-0753a878eb9d
    edited Aug 28, 2015 11:34AM

    This morning I scaled the code right back to basics to add a simpler test:

    <span class="kwd" style="color: #00008b;">using</span><span class="pln" style="color: #000000;"> </span><span class="pun" style="color: #000000;">(</span><span class="kwd" style="color: #00008b;">var</span><span class="pln" style="color: #000000;"> ts </span><span class="pun" style="color: #000000;">=</span><span class="pln" style="color: #000000;"> </span><span class="kwd" style="color: #00008b;">new</span><span class="pln" style="color: #000000;"> </span><span class="typ" style="color: #2b91af;">TransactionScope</span><span class="pun" style="color: #000000;">(</span><span class="typ" style="color: #2b91af;">TransactionScopeOption</span><span class="pun" style="color: #000000;">.</span><span class="typ" style="color: #2b91af;">RequiresNew</span><span class="pun" style="color: #000000;">,</span><span class="pln" style="color: #000000;"> </span><span class="kwd" style="color: #00008b;">new</span><span class="pln" style="color: #000000;"> </span><span class="typ" style="color: #2b91af;">TransactionOptions</span><span class="pln" style="color: #000000;"> </span><span class="pun" style="color: #000000;">{</span><span class="typ" style="color: #2b91af;">IsolationLevel</span><span class="pln" style="color: #000000;"> </span><span class="pun" style="color: #000000;">=</span><span class="pln" style="color: #000000;"> </span><span class="typ" style="color: #2b91af;">IsolationLevel</span><span class="pun" style="color: #000000;">.</span><span class="typ" style="color: #2b91af;">ReadCommitted</span><span class="pun" style="color: #000000;">},</span><span class="pln" style="color: #000000;"> </span><span class="typ" style="color: #2b91af;">EnterpriseServicesInteropOption</span><span class="pun" style="color: #000000;">.</span><span class="typ" style="color: #2b91af;">Full</span><span class="pun" style="color: #000000;">))</span><span class="pln" style="color: #000000;"> </span><span class="pun" style="color: #000000;">{</span><span class="pln" style="color: #000000;"><br/>   </span><span class="kwd" style="color: #00008b;">var</span><span class="pln" style="color: #000000;"> currentTransaction </span><span class="pun" style="color: #000000;">=</span><span class="pln" style="color: #000000;"> </span><span class="typ" style="color: #2b91af;">Transaction</span><span class="pun" style="color: #000000;">.</span><span class="typ" style="color: #2b91af;">Current</span><span class="pun" style="color: #000000;">;</span><span class="pln" style="color: #000000;"><br/></span><span class="pln" style="color: #000000;">   </span><span class="kwd" style="color: #00008b;">using</span><span class="pln" style="color: #000000;"> </span><span class="pun" style="color: #000000;">(</span><span class="kwd" style="color: #00008b;">var</span><span class="pln" style="color: #000000;"> oc1 </span><span class="pun" style="color: #000000;">=</span><span class="pln" style="color: #000000;"> </span><span class="kwd" style="color: #00008b;">new</span><span class="pln" style="color: #000000;"> </span><span class="typ" style="color: #2b91af;">OracleConnection</span><span class="pun" style="color: #000000;">(</span><span class="pln" style="color: #000000;">_connectionString</span><span class="pun" style="color: #000000;">))</span><span class="pln" style="color: #000000;"> </span><span class="pun" style="color: #000000;">{</span><span class="pln" style="color: #000000;"><br/>         oc1</span><span class="pun" style="color: #000000;">.</span><span class="typ" style="color: #2b91af;">Open</span><span class="pun" style="color: #000000;">();</span><span class="pln" style="color: #000000;"><br/>         </span><span class="kwd" style="color: #00008b;">using</span><span class="pln" style="color: #000000;"> </span><span class="pun" style="color: #000000;">(</span><span class="kwd" style="color: #00008b;">var</span><span class="pln" style="color: #000000;"> oc2 </span><span class="pun" style="color: #000000;">=</span><span class="pln" style="color: #000000;"> </span><span class="kwd" style="color: #00008b;">new</span><span class="pln" style="color: #000000;"> <span style="color: #2b91af; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13.3333330154419px;">OracleConnection</span></span><span class="pun" style="color: #000000;">(</span><span class="pln" style="color: #000000;">_quartzString</span><span class="pun" style="color: #000000;">))</span><span class="pln" style="color: #000000;"> </span><span class="pun" style="color: #000000;">{</span><span class="pln" style="color: #000000;"><br/>            oc2</span><span class="pun" style="color: #000000;">.</span><span class="typ" style="color: #2b91af;">Open</span><span class="pun" style="color: #000000;">();</span><span class="pln" style="color: #000000;"><br/>         </span><span class="pun" style="color: #000000;">}</span><span class="pln" style="color: #000000;"><br/>   </span><span class="pun" style="color: #000000;">}</span><span class="pln" style="color: #000000;"><br/></span><span class="pun" style="color: #000000;">}</span>


    The same issue occurred.  I then downloaded ILSpy to view the C# for the Oracle driver, and this was where I noted that connection pooling seemed to be at the heart of my issue.

    I then re-attempted the same test, but opened a copy of oc2 outside the test like so:

    var oc3 = new CustomOracleConnection(_connectionString, 50, 50);

    oc3.Open();

    oc3.Close();

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

    }


    and guess what - it works.... looks like there's an issue with connection pooling.  All values are defaulted, I don't specify a minimum pool size, etc.  I then tried playing with the connection pool attributes but no cigar.

    Looks to me like it's to do with the context of the connections available at the point the transaction begins enlisting connections, but surely this isn't right.

This discussion has been closed.