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:
- 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()
- 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()
- 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
}
});