connection pool problem after devart povider renewal

[Peter Koch]

We have made the following changes for our application:
- from dotconnect for oracle 6.50.244 to 9.4.299
- Visual Studio 2010 to VS2015
- from DotNet 4.0 to DotNet 4.6
- from EF 1 (?) to EF 5
we are using linq and edmx.

We did the go live last saturday.

We experience now the problem that within one hour all 400 connections of the connection pool are in use and we get the Error in the Event-Viewer:
The underlying provider failed on Open.
...
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
at Devart.Common.DbConnectionFactory.b(DbConnectionBase A_0)

We did not change the connection string.

It seems that open connections are not always freed and so all connections are used within short period.

With the old application we have 25 open conections after hours of use.

What can be the cause?
How can it be examined, why a new connection is created (and not an existing one is used)?
How can the status (in use?) of a connection be detected?

Thank you very much!

[Shalex]

1. Please localize the issue (if possible) and upload a test project with the corresponding DDL/DML script to ftp://ftp.devart.com (credentials: anonymous / yourEmail) for reproducing the issue in our environment.

2. As a temporary workaround, turn off the Devart pooling ("pooling=false;" in connection string). If you are connecting in OCI mode (via Oracle Client), try using Oci Session Pooling.

[Peter Koch]

We have put the devart provider which we used until Saturday (6.50.244) in the application which is changed as described above and our application seems to work, i.e. after one day of use we have 12 open connections in the pool.

Are there any dependencies of the devart provider 6.50.244 to the dotnet framework 4.6, entity framework 5 or visual studio 2015 wich will lead to problems?

Especially generating or updating EDMX with the VS2015 Entity data model wizard and the edmx mode "Legacy ObjectContext"?

[Shalex]

Please refer to https://www.devart.com/dotconnect/oracl ... story.html. We think that the issue may be caused by one of the following fixes:

8.4.333 15-Jan-15
The bug with throwing System.InvalidOperationException by Devart connection pooling in multithreaded applications under high loads is fixed

7.1.40 17-Jul-12
The bug with NullReferenceException under the transaction rollback of a connection placed into the pool is fixed

We need a test project to reproduce the problem in our environment and investigate it.

[Peter Koch]

Thank you very much for your hints.

It's hard to build a test project because we currently do not know which situation causes a connection to have the status "in use" and so it cannot be reused for other statements and a new connection is raised.

We tried yesterday the "Oci Session Pooling=True;" setting in the connection string, I,e OCI pooling as you suggested, and the behavior of the application in which we tested it with user load was much better.

But today in the morning it seems that there are still some open connections which probably shouldn't be open.
And the list of open connections was only growing and not shrinking as far as we can see.

As I understand it's not possible to detect for what a connection is waiting to finish a statement, i.e. why it is not closing, isn't it?

we have checked the status of the connections with the following oracle statement:

select v$session.sid, osuser, logon_time, status, LAST_ACTIVE_TIME, SQL_TEXT
from v$session, V$SQLSTATS
where username in ('APPLICATION_USER')
and v$session.prev_sql_id = V$SQLSTATS.sql_id
order by osuser, username, last_active_time desc;

and we see a lot of statements with the same sql_id and sql_fulltext and the same LAST_ACTIVE_TIME timestamp.

One statement which often occurs is a procedure call which has a raise_application_error statements:

PROCEDURE PROC_XXX IS
BEGIN
...
if some_condition = true then
RAISE_APPLICATION_ERROR (-20101, 'PROC_XXX: parameter is not valid');
end if;
...
EXCEPTION
WHEN others THEN
begin
--rollback;
RAISE_APPLICATION_ERROR (-20104, SUBSTR(SQLERRM,1,200));
end;
...
END PROC_XXX;

Has this something to do with the fixes you mentioned?
I cannot really identify how these fixes influence the behavior of the connection pooling and under which conditions they work.
Do you have some more explanation for them?

Will the connection string parameter "Default Command Timeout" be helpful in this situation?
Will there be a specific error which will show which statement had the timeout and why?

Thank you very much.

[Shalex]

As I understand it's not possible to detect for what a connection is waiting to finish a statement, i.e. why it is not closing, isn't it?

That is correct.

Will the connection string parameter "Default Command Timeout" be helpful in this situation?
Will there be a specific error which will show which statement had the timeout and why?

The "Default Command Timeout" connection string parameter will not help to localize the connection pooling issue.

Has this something to do with the fixes you mentioned?
Do you have some more explanation for them?

1. Please try to localize the issue and upload a test project with the corresponding DDL/DML script to ftp://ftp.devart.com (credentials: anonymous / yourEmail) for reproducing the issue in our environment.
2. Tell us your current versions of Oracle Server and Oracle Client.
3. Your current versions of Oracle Server and Oracle Client are the same you used before upgrade of dotConnect for Oracle, aren't they?

[Peter Koch]

Hello,
We cannot provide the DML and DDL scripts easily, it is a big, complex database with company data.
Is there a chance, that we analyze he issue together via an online meeting?

What is actually the criteria for returning a connection to the connection pool?
By the way, our issue occurs in the Devart connection pool as well as in the Oracle connection pool (parameter: oci ...)

[Shalex]

What is actually the criteria for returning a connection to the connection pool?

conn.Close() returns the connection to a pool. But the connection, which resides in the pool, can be reused by your application only in case if it doesn't participate in any opened global transaction. Otherwise, the connection waits until global transaction is commited (or rolled back).

Connection may be enlisted in a distributed transaction by your code or by EF code.

By the way, our issue occurs in the Devart connection pool as well as in the Oracle connection pool (parameter: oci ...)

If the problem persists with both Devart (implemented in dotConnect for Oracle) and OCI (implemented in Oracle Client) poolings, most likely the issue is caused either by Oracle Server (it hangs sessions) or by the code (connection is not closed or the corresponding global transaction is not finished).

[Peter Koch]

Thanks for your answer.
But the strange thing is: this problem doesn't not occur, if we are using Devart version 6.50.244, where we actually reverted to.

[Shalex]

We need a test project with the corresponding DDL/DML script (upload it to ftp://ftp.devart.com, credentials: anonymous / yourEmail) for reproducing the issue in our environment so that we can investigate the case.