Hi--

I've just upgraded to the new version of LLBL 2005.1. I've been using LLBL for the past few months and it has ran like a champ! That is until this new version... I've just noticed some problems with connection timeouts and a lot of connections being kept in our SQL server database. We are using Sql Server 2000, the LLBL Adapter method, ASP.NET 1.1, and C#.

It looks like LLBL is no longer automatically closing the DataAccessAdapter, but leaving them opened. I've been a little bit lazy when writing the code, so I haven't been explicitly calling adapter.CloseConnection(), I've been letting that happen under the covers. Here are two functions that have been chewing up connections and not releasing them:

public void LoadCostCenter()
        {
            if(!_hasLoadedCostCenter && !_costCenterReportEntity.IsNew)
            {
                DataAccessAdapter adp = new DataAccessAdapter();
                CostCenterEntity entity = (CostCenterEntity)adp.FetchNewEntity(new CostCenterEntityFactory(),  _costCenterReportEntity.GetRelationInfoCostCenterEntity());
                _costCenterReportEntity.CostCenterEntity = entity;
                _hasLoadedCostCenter=true;
            }

        }

        public void LoadCostCenterReportDetails()
        {
            if(!_hasLoadedDetails && !_costCenterReportEntity.IsNew)
            {
                DataAccessAdapter adp = new DataAccessAdapter();
                adp.FetchEntityCollection(_costCenterReportEntity.CostCenterReportDetails, _costCenterReportEntity.GetRelationInfoCostCenterReportDetails());
                _hasLoadedDetails=true;
            }

        }

I'm not sure which one of those functions keeps the connection hanging around, but one of them (I think the first one with the adp.FetchNewEntity) does. When I add in an adp.CloseConnection, everything works fine. I'm also having this problem with other projects that I have created.

When I look at the Sql Database under the current activity, there are a bunch (around 30) of active connections for the database with a status of sleeping. I've been getting the following exception:

Exception Information
System.InvalidOperationException: 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 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
   at System.Data.SqlClient.SqlConnection.Open()

I guess my question is, do I need to always explicitly close the connection with the CloseConnection function? Also, how many connections should show up under the current activity in SQL server? When a connection is released, should it still show up under the Current Activity in SQL Server?

Hopefully that is enough information.

Thanks,

-- Chad

WhiteDog wrote:

public void LoadCostCenter()
        {
            if(!_hasLoadedCostCenter && !_costCenterReportEntity.IsNew)
            {
                DataAccessAdapter adp = new DataAccessAdapter();
                CostCenterEntity entity = (CostCenterEntity)adp.FetchNewEntity(new CostCenterEntityFactory(),  _costCenterReportEntity.GetRelationInfoCostCenterEntity());
                _costCenterReportEntity.CostCenterEntity = entity;
                _hasLoadedCostCenter=true;
            }

        }

Use the using statement so you don't have to worry about it:

public void LoadCostCenter()
        {
            if(!_hasLoadedCostCenter && !_costCenterReportEntity.IsNew)
            {
                using (DataAccessAdapter adp = new DataAccessAdapter())
                {
                CostCenterEntity entity = (CostCenterEntity)adp.FetchNewEntity(new CostCenterEntityFactory(),  _costCenterReportEntity.GetRelationInfoCostCenterEntity());
                _costCenterReportEntity.CostCenterEntity = entity;
                _hasLoadedCostCenter=true;
                }
            }

        }

You should follow this pattern for any object which needs to be disposed because it is using unmanaged resources.

If you disable connection pooling in the connection string, what are the results then? What's the type of your app, a webapp or a desktop app and if it's a desktop app, is it run on a lot of machines or not? A pooled connection is still an open connection for sqlserver.

Thanks for your responses.

if you disable connection pooling in the connection string, what are the results then?

It seems to work. Most of the connections that were hanging out in SQL server no longer show up. It is down to just one or two that still show as being active (even though there are not applications using the connections).

What's the type of your app, a webapp or a desktop app and if it's a desktop app, is it run on a lot of machines or not?

It is an ASP.NET 1.1 Web Application(s)

I'll try changing the code to the using syntax that Jim suggested and see if that works.

It seems to be very sparadic with connection timeouts. One time I can go to the site and load test it and everything works great. Then trying it again later, I get timeout errors. It is hard to reproduce the problem consistently. I'm not changing any code between load tests, so I don't know why it is so flaky.

[UPDATE] Interestingly, when I do add in the Using block, it does not seem to give me any timeout errors. I guess I should be following the using block model that Jim suggested for all DataAccessAdapter fetches, updates, and deletes.

Because your app is a website, it can be at a given moment 30 fetches take place in 30 requests. This thus means 30 connections are required but no new one can be created because the poolsize is set to something lower than 30. Try setting the connection pool size to something> 30 and see if that helps.

Using 'using' shouldn't make a difference, as closing a connection (which is done here in every method) on sqlserver is also calling Dispose().

When I run a couple of hundred fetch unittests on sqlserver with adapter and keep open the performance monitor to see what the poolsize is, it will stay 1. So every method is re-using the connection of the previous test and not every test uses 'using'.

Hmmm. I'm not sure why I would be running against the maximum connection limits. I am currently the only person running this website on our development machine. It just limits out after so many requests. It looks to me like it is some kind of memory leak with not closing the connection?

Like I said before, I'm not explicitly calling the CloseConnection method. When I do add this to the function(s), everything works fine and I do not get any timeout errors. I'll keep trying...

Here is the actual Exception [This is actually on a different project than the one previously posted]:

General Information

Additonal Info:
ExceptionManager.MachineName: DEV
ExceptionManager.TimeStamp: 12/2/2005 10:27:25 AM
ExceptionManager.FullName: Microsoft.ApplicationBlocks.ExceptionManagement, Version=1.1.0.0, Culture=neutral, PublicKeyToken=e2d96cf40b9f4409
ExceptionManager.AppDomainName: /LM/W3SVC/1/Root/XpressArrivalClinic-30-127780370953012602
ExceptionManager.ThreadIdentity: USER
ExceptionManager.WindowsIdentity: NT AUTHORITY\NETWORK SERVICE

Exception Information
System.InvalidOperationException: 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 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
   at System.Data.SqlClient.SqlConnection.Open()
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.OpenConnection()
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteSingleRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFields2 fieldsToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityUsingFilter(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfos, IRelationPredicateBucket filter)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntity(IEntity2 entityToFetch)
   at Location..ctor(Int32 locationId) in Location.cs:line 39
   at Webservice.Repost.GetIds(Int32 locationId) in webservice\repost.asmx.cs:line 179
   at Webservice.Repost.GetCurrentScannedInIds(Int32[] locationIds) in webservice\repost.asmx.cs:line 139
   at Repost.RepostClinicPage(Int32[] locationIds, Int32[] clientIds) in webservice\repost.asmx.cs:line 77

The code is doing the following:

Location location = new Location(locationId);

public Location(int locationId)
        {
            _hcLocation = new HCLocationEntity(locationId);
            DataAccessAdapter adp = new DataAccessAdapter();
            if(!adp.FetchEntity(_hcLocation))
            {
                throw new DataNotFoundException("Location ID: " + locationId + " does not exist");
            }
        }

I guess I could see how the thing could timeout if it is waiting for a connection to be freed from the pool. However, all of the stuff being done is realativly very fast (just simple selects from a single table). The connections should hang out and wait for a free connection before they actually timeout. The timeout exception tells me that there is some kind of resource that is not being freed by the DataAccessAdapter.

Thanks!

Well, every fetch call of the adapter (and other calls as well) open AND close the connection. Unless you specify that hte connection has to stay open (with the KeepConnectionOpen flag in the adapter constructor or the property), it is closed when the method is done. There are just a couple of routines which actually do something with the db and they all open and close connections if required. There is therefore no memory leak/resource leak, otherwise I would run into the same thing with my unittests which solely fetch stuff or with the unittests which do saves, reads and deletes. I find it strange as well though, why you get that error, especially if you're the only one on the site.

Edit: do you run the site on an XP box? google groups link

Could you also setup sqlserver performance counters in performance monitor to see how many connections are created and released?

Btw: if there would be a leak, the non-connection pool connection string would wear out the sqlserver as well because connections weren't cleaned up nor re-used.

Otis-

Thanks for being patient and trying to figure this out with me. We are running our website on a Windows 2003 server. This same machine hosts both our database (SLQ 2000) and our website.

Here is some trace information from LLBL

DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.CloseConnection
DataAccessAdapterBase.CloseConnection
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.CloseConnection
DataAccessAdapterBase.CloseConnection
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.FetchNewEntity(4)
DataAccessAdapterBase.FetchNewEntity(3)
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.OpenConnection                                <---- HERE
DataAccessAdapterBase.OpenConnection                                <---- HERE
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.FetchNewEntity(3)
DataAccessAdapterBase.FetchNewEntity(4): no prefetch path.
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.CloseConnection
DataAccessAdapterBase.CloseConnection
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.FetchNewEntity(4)
DataAccessAdapterBase.FetchNewEntity(3)
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.OpenConnection                                <---- HERE
DataAccessAdapterBase.OpenConnection                                <---- HERE
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.FetchNewEntity(3)
DataAccessAdapterBase.FetchNewEntity(4): no prefetch path.
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.CloseConnection
DataAccessAdapterBase.CloseConnection
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.FetchNewEntity(4)
DataAccessAdapterBase.FetchNewEntity(3)
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.OpenConnection                                <---- HERE
DataAccessAdapterBase.OpenConnection                                <---- HERE
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.FetchNewEntity(3)
DataAccessAdapterBase.FetchNewEntity(4): no prefetch path.
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.CloseConnection
DataAccessAdapterBase.CloseConnection
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.FetchNewEntity(4)
DataAccessAdapterBase.FetchNewEntity(3)
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.FetchNewEntity(3)
DataAccessAdapterBase.FetchNewEntity(4): no prefetch path.
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.CloseConnection
DataAccessAdapterBase.CloseConnection
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.FetchNewEntity(4)
DataAccessAdapterBase.FetchNewEntity(3)
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.FetchNewEntity(3)
DataAccessAdapterBase.FetchNewEntity(4): no prefetch path.
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.CloseConnection
DataAccessAdapterBase.CloseConnection
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.FetchNewEntity(4)
DataAccessAdapterBase.FetchNewEntity(3)
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.FetchNewEntity(3)
DataAccessAdapterBase.FetchNewEntity(4): no prefetch path.
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.CloseConnection
DataAccessAdapterBase.CloseConnection
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.FetchNewEntity(4)
DataAccessAdapterBase.FetchNewEntity(3)
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.FetchNewEntity(3)
DataAccessAdapterBase.FetchNewEntity(4): no prefetch path.
DataAccessAdapterBase.FetchEntityCollection(6)
DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.OpenConnection
DataAccessAdapterBase.CloseConnection
DataAccessAdapterBase.CloseConnection

I noticed it is Opening a connection, but it doesn't look like there is a corresponding close. Not sure if that matters or not.

Which performance monitor do you want me to use?

I used the one from SQLServer:General Statistics -->User Connections. When I watch this while I'm running the site, I can see that the user connections starts at about 20, goes to 76, then to 96, then to 100+, then it dies. The user connections never goes back down, it is an upward line. When i do an explicit adp.CloseConnection(), the line goes up by a few, but stays constant (the is no upward trend).

If I turn connection pooling off, it still eventually dies under a heavy load.

DataAccessAdapterBase.FetchNewEntity(4) DataAccessAdapterBase.FetchNewEntity(3) DataAccessAdapterBase.FetchEntityCollection(6) DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery DataAccessAdapterBase.OpenConnection <---- HERE DataAccessAdapterBase.OpenConnection <---- HERE DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery

Thanks for this stacktrace.

This particular code fetches a new entity WITH a prefetch path, so you call FetchNewEntity() with a prefetch path.

In there, it has some optimization which sets the KeepConnectionOpen to true, then fetches the entity, and if it's fetched, fetches the prefetch path. It uses a collection fetch to re-use polymorphic code (not really important, but that's why ExecuteMultiRowRetrievalQuery is there), and the second ExecuteMultiRowRetrievalQuery call is from that prefetch path.

I've to check out if that prefetch path fetch leaves the connection open. THere's an open/close sequence below the snippet I quoted, though it should be pretty clear if I test it here.

As for now, you now know which code is the culprit, if you surround these with using statements, the problem should go away. I'll see if the problem is indeed in that routine and will fix it a.s.a.p. You can proceed with your code with the workaround I just suggested, IF that is indeed the case, but it's likely.

(edit): My FetchNewEntityWithPrefetchPathTest uses using... hence I didn't see it. I'll now test if my assumption is right.

(edit): ok, that's the cause. I'll now fix it and will also check if I have used the same 'pattern' elsewhere. There is more usage of this optimization pattern to keep the connection open during multiple operations like prefetch paths, so I've to check if the connection is left open as well.

Ok I've fixed it. A new build is now created and should be downloadable shortly. Very sorry for this inconvenience. It's an embarrasement I have this bug in the code and I'm sorry you ran into this.

The reason I didn't notice it in my profiling yesterday was that either the tests are all in using statements or in try/finally blocks which call Dispose. So I never would run into a dangling transaction.

(edit) Ok, the fix is now available. Please download the latest runtime libraries archive from the runtime libraries section in the customer area. This should fix the problems.

No problem at all. I'm just glad you found something at it wasn't just me . I'm going to use the USING patern in all of my code just so I don't have this problem in the future anyway. Again, thanks for being so responsive! If only other venders were this helpful!!

I appreciate it.

-- Chad

I am experiencing the same problem using 2004.1 (september edition). Does the issue with not closing the connection in Prefetch Paths scenario exist in 2004.1 or was it introduced in 2005.1 and I should be looking for a different cause of the problem?

Thank you!

It was introduced later on, if I'm not mistaken. To be sure, use using blocks with the adapter, then you won't get dangling connections.

Thanks!