1 Reply Latest reply on Oct 22, 2019 3:33 PM by Alex Keh - Product Manager-Oracle

    Oracle.ManagedDataAccess driver throws a SemaphoreFullException under heavy load

    4116299

      In a multi-threaded environment under heavy load the Oracle connection opening/closing fails with a SemaphoreFullException (The expected behavior would be getting an Connection timeout or Connection Pool timeout exception which are all subclasses of OracleException).

      After the below mentioned exception happens, sometimes the connection pool gets into an invalid state, where opening/closing connections fails until the application is restarted.

      We got exceptions with three different stacktraces:

      1. System.Threading.SemaphoreFullException: Adding the specified count to the semaphore would cause it to exceed its maximum count.

         at System.Threading.Semaphore.Release(Int32 releaseCount)

         at System.Threading.Semaphore.Release()

         at OracleInternal.ConnectionPool.PoolManager`3.GetIdleConnectionToKill(TimeSpan ts, List`1 instancesToSkip)

         at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)

         at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)

         at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword, OracleConnection connRefForCriteria)

         at Oracle.ManagedDataAccess.Client.OracleConnection.Open()

       

      1. System.Threading.SemaphoreFullException: Adding the specified count to the semaphore would cause it to exceed its maximum count.

         at System.Threading.Semaphore.Release(Int32 releaseCount)

         at System.Threading.Semaphore.Release()

         at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)

         at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)

         at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword, OracleConnection connRefForCriteria)

         at Oracle.ManagedDataAccess.Client.OracleConnection.Open()

       

      1. System.Threading.SemaphoreFullException: Adding the specified count to the semaphore would cause it to exceed its maximum count.

         at System.Threading.Semaphore.Release(Int32 releaseCount)

         at OracleInternal.ConnectionPool.PoolManager`3.Put(PR pr, OracleConnection connRefForCriteria)

         at OracleInternal.ConnectionPool.OraclePoolManager.Put(OracleConnectionImpl con, OracleConnection connRefForCriteria)

         at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Put(PR pr, OracleConnection connRefForCriteria)

         at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.PutFromApp(PR pr, OracleConnection connRefForCriteria)

         at Oracle.ManagedDataAccess.Client.OracleConnection.Close()

       

      Steps to reproduce:

      We found no deterministic way of reproducing the issue (most likely a race condition) but executing the application below, a SemaphoreFullException is thrown in a couple of minutes.

      We could reproduce the issue with NuGet versions 19.5.0, 19.3.1, 19.3.0, 18.6.0 but *not* with 18.3.0.

      Tested environments: 

      • Windows 10 and Windows Server 2012
      • .NET 4.6.2
      • Debug and Release builds
      • Hosting in IIS and Console application
      • Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

      The issue occurs with greater Min/Max Pool sizes and thread numbers, but it is easier to reproduce with smaller numbers.

       

       

      string connectionString = "user id=XXX;password=XXX;Pooling=true;Min Pool Size=1;Max Pool Size=3;data source=(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=on)(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=XXX)(PORT=1621)))(CONNECT_DATA=(SERVICE_NAME= XXX)(FAILOVER_MODE=(METHOD=BASIC)(TYPE=SELECT)(RETRIES=18)(DELAY=1))));";

       

      //fill the connection pool. This is not needed, but speeds up connection creation

      for (int i = 0; i < 3; i++)

      {

          using (OracleConnection conn = new OracleConnection(connectionString))

          {

              conn.Open();

          }

      }

       

      long count = 0;

      CancellationTokenSource cts = new CancellationTokenSource();

      Enumerable.Range(1, 200000).AsParallel().WithDegreeOfParallelism(50).WithCancellation(cts.Token).ForAll(i =>

      {

          try

          {

              using (OracleConnection conn = new OracleConnection(connectionString))

              {

                  conn.Open();

                  //simulate work

                  Thread.Sleep(1);

       

                  //calling conn.Close(); here doesn't fix it

              }

       

              var localCount = Interlocked.Increment(ref count);

              if (localCount % 1000 == 0)

              {

                  Console.WriteLine(localCount);

              }

          }

          catch (System.Threading.SemaphoreFullException sfe)

          {

              Console.WriteLine(sfe);

              cts.Cancel();

          }

          catch

          {

              //Ignore anything else

          }

      });

       

        • 1. Re: Oracle.ManagedDataAccess driver throws a SemaphoreFullException under heavy load
          Alex Keh - Product Manager-Oracle

          Thanks for the complete test case. Are you using managed ODP.NET or ODP.NET Core as either can run in .NET 4.6.2?

           

          If possible, can you turn on tracing and send the trace file to dotnet_us(at)oracle.com? We can take a look to better understand how the semaphores are being consumed. Using either managed or Core 19.5 versions, you can add the following code to turn on tracing at the start of your app:

           

          OracleConfiguration.TraceFileLocation = @"D:\traces";

          OracleConfiguration.TraceLevel = 7;

           

          Just set the TraceFileLocation to a directory you want the traces outputted to.