Thousands of connections

[Nils C.]

Hi,

we are using Data Access for a while. Since the last update to the newest version. We are getting a strange behavior. We are using DA with MS Sql and Telerik Data Access connection Pooling. 

Its an ASP.net Application and we are using the request scoped datacontextfactory and disposing the context object on request end. 

The Problem is: DA opens thousands of connection which all ideling after a while. Yesterday we got 15.000 open connections, which had a lastbatch value by calling sp_who2 older than a hour ago. 

I am not sure if thats an DA Bug or if there is code which produces this strange behavior. Is there an event which we can catch to find out which code is opening this thousands of connections?

Thanks in advance..

[Thomas]

Hi Nils,

I'm not aware that OpenAccess got worse wrt. connection handling; I will try to reproduce this here. In order to pinpoint the problem could you please tell us which version you used before and what the most common actions are that could lead to a connection being not reused? Do you call SQL or stored procedures directly, and if so, could you give us an example of such a call (we need to see the code that you really use). Do break enumerations of LINQ query results without disposing the enumerator ?

Regards,
Thomas
Telerik

 

 

[Nils C.]

Hi Thomas,

we are using some direct SQL Code.

Here an example: 

Dim objects = _db.ExecuteQuery(Of Gehoert_zu_Projektvorlage)("SELECT * FROM Gehoert_zu_Projektvorlage where   RolleZuRechtestufeId is not null and RolleZuRechtestufeId NOT IN (SELECT Id From Rolle_zu_Rechtestufe)")
      _db.Delete(objects)
      _db.SaveChanges()

What do you mean with break enumerations? Can you give me an example. Our query results are never disposed. I thought this would do the garbage collector, if the side is unloaded. 

Best

Nils

[Nils C.]

We always update the DA. So the version before was the release before the latest. I am not sure, what could lead to a connection not beeing reused. Can you perhaps give me a hint, in which cases a connection cannot be reused? I couldn't find any documentation about that.

Best

[Thomas]

Hi Nils,

connections will not be reusable if they are still used. This can happen, when you perform a LINQ query that returns more than 50 elements, start the enumeration, and do not fetch all of the data, but break the enumeration without disposing the enumerator. This way, OpenAccess has no chance to know if you are about to further enumerate the result (hence use the connection) or not. This is what I meant by breaking the enumeration. To get around this issue please make sure that the foreach loop is used; a manual for or while loop might have this problem. Throwing an exception while enumerating the result could also lead to such non-terminating behavior. Counting on the GC to terminate the logical reading process is not sufficient here; this is why there is the Disposable pattern.

From looking at our code again, I could see that with the ExecuteQuery we do not have this issue; in cases of exceptions the connection is closed.

Regards,
Thomas
Telerik