Issue with connection pool. Getting Underlying provider failed error after max pool size reached

Issue with connection pool. Getting Underlying provider failed error after max pool size reached

Postby sandeshkoli » Fri 05 Oct 2012 16:40

Hi,
I am using dotConnect for Oracle. I am getting "Underlying provider failed" eror after my connection pool reaches 100 since the default setting of the connection pool is 100. The connections from the connection pool are not getting reused. Below is my sample code. Can somebody please let me know what is wrong here?.

Connection string -
<add name="MyNamespace.EntityFrameWorkConnectionString"
connectionString="metadata=res://*/MyNamespace.EntityFrameWork.csdl|res://*/MyNamespace.EntityFrameWork.ssdl|res://*/MyNamespace.EntityFrameWork.msl;provider=Devart.Data.Oracle;provider connection string='User Id=db_user;Password=db_pwd;Server=ora_server;Persist Security Info=True';"
providerName="System.Data.EntityClient" />

using (DBEntity dbEntity = new DBEntity())
{
MyTableRec tableRec = new tableRec()
{
ResponsibilitieId = GenericHelper.GetNextSequenceValue(GenericHelper.TABLE_NAME),
Name = "Some Name",
CreatedBy = "User1"
};
try
{
dbEntity.AddToMyTable(tableRec);
dbEntity.SaveChanges();
}
catch
{
isSuccess = GenericHelper.isNotSucessfull;
}
}
sandeshkoli
 
Posts: 1
Joined: Fri 05 Oct 2012 16:01

Re: Issue with connection pool. Getting Underlying provider failed error after max pool size reached

Postby Shalex » Thu 11 Oct 2012 14:58

1. The possible reason of opening (and not closing) too much connections can be usage of TransactionScope in a wrong way. We recommend using TransactionScope only for saving data (not for multiple read/save-s):
a) use
Code: Select all
using (var firstConext = new FirstContext()) {
using (var secondConext = new SecondContext()) {
// ... multiple reading 1
// ... multiple reading 2
using (var transactionscope = new TransactionScope()) {
// ... saving 1
// ... saving 2
transactionscope.Commit();
}
}
}
instead of
Code: Select all
using (var transactionscope = new TransactionScope()) {
using (var firstConext = new FirstContext()) {
// ... multiple reading 1
// ... saving 1
}
using (var secondConext = new SecondContext()) {
// ... multiple reading 2
// ... saving 2
}
transactionscope.Commit();
}

b) also take into account the peculiarities of a particular ORM. For example, if you are using multiple read/save-s before applying ts.Commit(), all used connections are accumulated (they will be released only after ts.Commit()). It makes sense to open connection manually so that EF would not open/"close" them for each database interoperation - in this case EF context would use only this single connection.
Use
Code: Select all
using (var transactionscope = new TransactionScope()) {
using (var firstConext = new FirstContext()) {
firstConext.Connection.Open();
// ... multiple reading 1
// ... saving 1
}
using (var secondConext = new SecondContext()) {
secondConext.Connection.Open();
// ... multiple reading 2
// ... saving 2
}
transactionscope.Commit();
}
instead of
Code: Select all
using (var transactionscope = new TransactionScope()) {
using (var firstConext = new FirstContext()) {
// ... multiple reading 1
// ... saving 1
}
using (var secondConext = new SecondContext()) {
// ... multiple reading 2
// ... saving 2
}
transactionscope.Commit();
}
This is a sample for ObjectContext.

In case of DbContext, it has to be cast to IObjectContextAdapter:
Code: Select all
((IObjectContextAdapter)сonext).ObjectContext.Connection.Open();


2. You are employing XML-mapping and obtaining sequence.nextval directly from the database. Be aware that this can be done by EF-provider to optimize usage of connections: http://blogs.devart.com/dotconnect/set-identity-and-computed-properties-in-entity-framework-without-triggers.html

3. If these recommendations don't help, send us a test project to reproduce the problem in our environment.
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle