AbleCommerce Shopping Cart Software

Posted: **Fri Mar 14, 2014 12:05 pm**

Get ready for a long post

. Using Gold R5 (WSP).

The Google Feed code that is run on a 60-second timer by Global.asax appears to be leaking an SQL connection sometimes. I will file a bug report via the Feedback button (referring to this post), but I wanted to post here in case someone else runs into the same problem I did. Here is what I went through that led me to blame the Google Feed code.

I was working on my Dev site, and decided to increase the Idle Time-out setting for the Application Pool in IIS to 360 minutes (IIS Manager: choose server on left side, expand tree, select Application Pools, select appropriate pool, click Advanced Settings.. on the right, set Idle Time-out (minutes) to 360, click OK, choose server on left side again, click Restart on the right). I did this because the default is only 20 minutes, and I got tired of waiting for it to load again if I was interrupted by other tasks while working on customizing the site.

After I did this, I started getting these errors on an irregular basis:

2014-03-14 08:08:56,232 WARN ADOExceptionReporter NHibernate.ADOException: While preparing SELECT this_.StoreId as StoreId107_0_, this_.Name as Name107_0_, this_.ApplicationName as Applicat3_107_0_, this_.LoweredApplicationName as LoweredA4_107_0_, this_.LicenseKey as LicenseKey107_0_, this_.DefaultWarehouseId as DefaultW6_107_0_, this_.NextOrderId as NextOrde7_107_0_, this_.OrderIdIncrement as OrderIdI8_107_0_, this_.WeightUnitId as WeightUn9_107_0_, this_.MeasurementUnitId as Measure10_107_0_ FROM ac_Stores this_ an error occurred ---> 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.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at NHibernate.Connection.DriverConnectionProvider.GetConnection()
at NHibernate.AdoNet.ConnectionManager.GetConnection()
at NHibernate.AdoNet.AbstractBatcher.Prepare(IDbCommand cmd)
--- End of inner exception stack trace ---
at NHibernate.AdoNet.AbstractBatcher.Prepare(IDbCommand cmd)
at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session)
2014-03-14 08:08:56,232 ERROR ADOExceptionReporter While preparing SELECT this_.StoreId as StoreId107_0_, this_.Name as Name107_0_, this_.ApplicationName as Applicat3_107_0_, this_.LoweredApplicationName as LoweredA4_107_0_, this_.LicenseKey as LicenseKey107_0_, this_.DefaultWarehouseId as DefaultW6_107_0_, this_.NextOrderId as NextOrde7_107_0_, this_.OrderIdIncrement as OrderIdI8_107_0_, this_.WeightUnitId as WeightUn9_107_0_, this_.MeasurementUnitId as Measure10_107_0_ FROM ac_Stores this_ an error occurred
2014-03-14 08:08:56,232 WARN ADOExceptionReporter 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.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at NHibernate.Connection.DriverConnectionProvider.GetConnection()
at NHibernate.AdoNet.ConnectionManager.GetConnection()
at NHibernate.AdoNet.AbstractBatcher.Prepare(IDbCommand cmd)
2014-03-14 08:08:56,232 ERROR ADOExceptionReporter 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.

I say irregularly because it wasn't every day, sometimes it would be a couple days before it would happen. Once it started though, the same set of 4 messages was repeatedly added to the error log many times per hour. Also, once the error started occurring, I would get similar errors trying to open the site in the browser.
Recycling the Application Pool or restarting the web site or IIS would resolve the issue.
I searched the forums (via ablecommerce.net) for "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". The last post in this thread viewtopic.php?f=42&t=16035 gave me the hint that it had to do with the SQL connection pool, not the IIS Application Pool. After some Googling and reading, the common culprit seemed to be "leaked" or "orphaned" SQL connections (i.e., some code somewhere was opening an SQL connection, but not closing it when it was done). I decided to look at the SQL connections to the AbleCommerce database using the following query:

Code: Select all

declare @who2table as table
(
 SPID int,
 Status nvarchar(20),
 Login nvarchar(50),
 HostName nvarchar(50),
 BlkBy nvarchar(50),
 DBName nvarchar(500),
 Command nvarchar(50),
 CPUTime bigint,
 DiskIO bigint,
 LastBatch nvarchar(50),
 ProgramName nvarchar(500),
 SPID2 int,
 REQUESTID int
);

INSERT INTO @who2table
EXEC ('sp_who2');

select * from @who2table where DBName = 'AbleCommerce' order by LastBatch;
delete @who2table;

The output showed quite a few connections where the LastBatch times were exact minutes apart (the seconds were the same, but the minutes might be 1, 2, 5, etc. different). I double-checked the error log, and most of the sets of 4 error log entries were also timed this way.
I restarted IIS, logged into the web site and logged back out, then started running the query every so often again. After 5 minutes, connections started showing up again, sometimes 1 minute apart, sometimes 2 or more minutes apart. If I refreshed the query just right, I could see that a connection was added to the query results every minute, but only some of the connections were returned with subsequent queries; the rest would be gone after subsequent queries. For example, after about an hour there were still 31 connections showing (the site was not used at all during this time). For example:

Code: Select all

SPID	Status	Login	HostName	BlkBy	DBName	Command	CPUTime	DiskIO	LastBatch	ProgramName	SPID2	REQUESTID
87	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 12:49:32	.Net SqlClient Data Provider                        	87	0
77	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 12:51:32	.Net SqlClient Data Provider                        	77	0
74	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 12:52:32	.Net SqlClient Data Provider                        	74	0
70	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 12:54:32	.Net SqlClient Data Provider                        	70	0
119	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 12:55:32	.Net SqlClient Data Provider                        	119	0
76	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 12:57:32	.Net SqlClient Data Provider                        	76	0
84	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 12:59:32	.Net SqlClient Data Provider                        	84	0
91	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:02:32	.Net SqlClient Data Provider                        	91	0
123	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:03:32	.Net SqlClient Data Provider                        	123	0
137	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:04:32	.Net SqlClient Data Provider                        	137	0
67	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:08:32	.Net SqlClient Data Provider                        	67	0
75	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:12:33	.Net SqlClient Data Provider                        	75	0
97	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:13:33	.Net SqlClient Data Provider                        	97	0
112	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:18:33	.Net SqlClient Data Provider                        	112	0
107	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:19:33	.Net SqlClient Data Provider                        	107	0
115	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:20:33	.Net SqlClient Data Provider                        	115	0
117	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:23:33	.Net SqlClient Data Provider                        	117	0
122	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:24:33	.Net SqlClient Data Provider                        	122	0
88	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:26:33	.Net SqlClient Data Provider                        	88	0
81	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:28:33	.Net SqlClient Data Provider                        	81	0
69	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:29:33	.Net SqlClient Data Provider                        	69	0
140	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:31:33	.Net SqlClient Data Provider                        	140	0
146	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:35:33	.Net SqlClient Data Provider                        	146	0
150	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:36:33	.Net SqlClient Data Provider                        	150	0
104	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:37:33	.Net SqlClient Data Provider                        	104	0
127	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:38:33	.Net SqlClient Data Provider                        	127	0
126	sleeping            	[login]	[dbname]     	  .	AbleCommerce	AWAITING COMMAND	0	0	3/14/14 13:39:33	.Net SqlClient Data Provider                        	126	0

I remembered that at one point I had experimented with putting my own timed task in Global.asax based on this info viewtopic.php?f=42&t=11433. I thought I remembered there was already a timer there, so I looked. Sure enough, the Google Feed timer runs every minute:

Code: Select all

_timer = new System.Threading.Timer(new System.Threading.TimerCallback(AbleCommerce.Code.GoogleFeed.FeedTimerTask), null, 300000, 60000);

Note that even if Marketing > Google Feed > Enable Automatic Feed is not checked (I don't have it checked), this timer still runs the AbleCommerce.Code.GoogleFeed.FeedTimerTask (which just calls CreateFeed, which contains the actual code) every minute.
I commented that line in Global.asax and restarted IIS, and now the connection list doesn't grow any more.
Obviously, there are a lot of things interacting here, so I don't know exactly where the root cause is. It could be AbleCommerce code, .Net libraries, NHibernate libraries, ASP.Net, IIS, or SQL Server. For my Dev site, IIS is on a different server than SQL Server (both are virtual, not sure if they are on the same physical hardware) and SQL Server is handling other databases as well. I did a brief test with IIS and SQL Server on the same virtual server, and the same symptoms occurred, but much slower, maybe 3 or 4 connections added per hour.
I have heavily modified our site, but as far as I know I haven't changed any of the tables or changed anything having to do with the Google Feed code, the CommerceBuilder.Stores.Store object, or the CommerceBuilder.Configuration.ApplicationSettings object (these are referenced in CreateFeed).
Having said all that, I have not tested a fresh, clean, unmodified install of AC Gold to see if I get the same symptoms. Perhaps someone from AC can check this out. Also, I'm not sure how or how often the site maintenance runs (although I know it is much less frequenly), or if it is causing similar leaked SQL connections. Even if it is, it probably isn't noticeable because the default setting for recycling the Application Pools is every 24 hours, and that effectively closes all the leaked connections.
To summarize, if you start getting "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." errors, try commenting out the line in Global.asax that runs the Google Feed timer and see if that helps.

Posted: **Thu Mar 20, 2014 3:14 am**

Thanks for reporting this. We will follow the details to confirm if this issue exist or not.

Posted: **Thu Mar 20, 2014 4:26 am**

I did check this on 3 websites and saw some open connections, but the most I saw were 5- one every minute. Then maybe they closed after that?

Posted: **Thu Mar 20, 2014 6:06 am**

Judy,

In my case, some of them disappear after a while, but some don't. As I mentioned before, since there are so many interconnected components involved, more research would be required to see what is really happening. It seems that the server environment can affect how bad the problem is.

Posted: **Fri Mar 21, 2014 3:31 am**

We did some inspection and were able to confirm that we were missing a required piece of code which may make difference. In Google feed generation we were not releasing the context instances which means it was not being disposed immediately after feed logic execution. Please apply following fix and see if it does make the difference.

Go to Website/Code/GoogleFeed.cs file and locate following method

Code: Select all

public static void CreateFeed()
        {
            // ENSURE APP IS INSTALLED
            Store store = GetInstalledStore();
            if (store != null)
            {
                // SEE IF FEED GENERATION IS ENABLED
                ApplicationSettings appConfig = ApplicationSettings.Instance;
                if (appConfig.GoogleFeedInterval > 0)
                {
                    // SEE IF THE FEED IS OLD ENOUGH TO BE REGENERATED
                    int interval = (int)(60000 * appConfig.GoogleFeedInterval);
                    string googleBaseFile = HostingEnvironment.MapPath("~/Feeds/GoogleFeedData.txt");
                    FileInfo fileInfo = new FileInfo(googleBaseFile);
                    if (!fileInfo.Exists || fileInfo.LastWriteTime.AddMilliseconds(interval) < DateTime.Now)
                    {
                        CreateFeed(googleBaseFile, store.Settings.GoogleFeedIncludeAllProducts, store.Settings.GoogleFeedDefaultBrand, store.Settings.GoogleFeedDefaultCategory);
                    }
                }
            }
        }

and wrap method content in a try catch

Code: Select all

public static void CreateFeed()
        {            
            try
            {
                // ENSURE APP IS INSTALLED
                Store store = GetInstalledStore();
                if (store != null)
                {
                    // SEE IF FEED GENERATION IS ENABLED
                    ApplicationSettings appConfig = ApplicationSettings.Instance;
                    if (appConfig.GoogleFeedInterval > 0)
                    {
                        // SEE IF THE FEED IS OLD ENOUGH TO BE REGENERATED
                        int interval = (int)(60000 * appConfig.GoogleFeedInterval);
                        string googleBaseFile = HostingEnvironment.MapPath("~/Feeds/GoogleFeedData.txt");
                        FileInfo fileInfo = new FileInfo(googleBaseFile);
                        if (!fileInfo.Exists || fileInfo.LastWriteTime.AddMilliseconds(interval) < DateTime.Now)
                        {
                            CreateFeed(googleBaseFile, store.Settings.GoogleFeedIncludeAllProducts, store.Settings.GoogleFeedDefaultBrand, store.Settings.GoogleFeedDefaultCategory);
                        }
                    }
                }
            }
            catch
            {
            }
            finally
            {
                AbleContext.ReleaseInstance();
            }
        }

where in finally block we are releasing current context instance forcing it to dispose the resources. After this fix I was able to confirm using query posted in this thread that connection no longer increase every minute.

Posted: **Fri Mar 21, 2014 4:45 am**

Mazar, what section of code requires having this in the feed?
AbleContext.ReleaseInstance();
Is it this?
ApplicationSettings.Instance

I'm asking to determine if our custom feed file needs it (code is a little different) and what it is actually doing and if there are other places that need it.
Thanks

Posted: **Fri Mar 21, 2014 5:05 am**

jmestep wrote:Mazar, what section of code requires having this in the feed?
AbleContext.ReleaseInstance();
Is it this?
ApplicationSettings.Instance

I'm asking to determine if our custom feed file needs it (code is a little different) and what it is actually doing and if there are other places that need it.
Thanks

Code: Select all

 AbleContext.ReleaseInstance();

In above change we just wrapped complete method code in a try catch and fianlly block. Lastly placed call to AbleContext.ReleaseInstance(); in finally block

like

Code: Select all

try
{
       ************************
       ************************
}
catch
{
}
finally
{
 AbleContext.ReleaseInstance();
}

Posted: **Fri Mar 21, 2014 9:20 am**

Mazhar,

It looks like that fixed the problem. Now (if the store is not in use) there is only ever 1 connection listed.

A few notes for others who may read this thread:

1. The code Mazhar refers to is in Website\App_Code\GoogleFeed.cs
2. The Google feed file is not being generated every minute. The timer that runs every minute just checks to see if you want the feed file generated, and if so, if the time you specified has elapsed. Only then does it actually generate the feed file.

Judy, I'm guessing the release is needed because CreateFeed calls GetInstalledStore, which appears to eventually call (source) code that generates an AbleContext for the timer thread. Mazhar, can you confirm that?

Posted: **Mon Mar 24, 2014 4:49 am**

Judy, I'm guessing the release is needed because CreateFeed calls GetInstalledStore

Thanks, that is what I was thinking but I wasn't sure. One of the sites I had checked was an Able 7 site and they had only one connection and there was code in that feed to get a store instance and at the end it had Token.ResetInstance(). I am glad for the sql query, I've seen code that uses a different DAL to connect to the database and read that you didn't need to close the connection and I want to test that.

Posted: **Tue Apr 01, 2014 5:10 am**

jguengerich wrote:Mazhar,

It looks like that fixed the problem. Now (if the store is not in use) there is only ever 1 connection listed.

A few notes for others who may read this thread:

1. The code Mazhar refers to is in Website\App_Code\GoogleFeed.cs
2. The Google feed file is not being generated every minute. The timer that runs every minute just checks to see if you want the feed file generated, and if so, if the time you specified has elapsed. Only then does it actually generate the feed file.

Judy, I'm guessing the release is needed because CreateFeed calls GetInstalledStore, which appears to eventually call (source) code that generates an AbleContext for the timer thread. Mazhar, can you confirm that?

Yes you are right. When we were loading store object in code it was in turn creating the context. We were suppose to release the context once done but that part was missing. The fix is to simply make sure you release the instance you create.

Posted: **Tue Apr 01, 2014 11:14 am**

Outstanding work Jay, and thanks for posting to the forums. I just ran into this myself and it really saved my bacon !

Posted: **Tue Apr 01, 2014 11:56 am**

BTW, some of the info I came across while figuring this out recommended that you should not rely on a 'using' statment to close an SqlConnection; instead, always explicitly close it:

Code: Select all

using (someConnection)
{
    try
    {
        someConnection.Open();
    }
    catch
    {
    }
    finally
    {
        someConnection.Close();
    }
}

Apparently, even though the SqlConnection's Dispose method (which the 'using' statement calls) contains a Close, it doesn't always really close the connection.
It seems like the NHibernate libraries are taking care of this, but if you write custom code using .Net Sql objects instead of NHibernate, it is something to keep in mind.

Posted: **Tue Apr 01, 2014 4:48 pm**

Jay,

I disagree with that and it's actually discouraged to close a connection when it's in a using block. Where are you hearing otherwise?

Posted: **Tue Apr 01, 2014 8:03 pm**

So Jay/Mazhar...how come you don't just see 1 connection? Isn't that the whole point of connection pooling - the SQL provider is reusing existing connection because the connect string is the same?

Posted: **Wed Apr 02, 2014 2:21 am**

Connection pooling doesn't mean only one connection. Also read below where calling close in a finalizer is incorrect.

http://goo.gl/TNHd4g

Posted: **Wed Apr 02, 2014 8:34 am**

Neal,

The ").aspx" didn't get included in your link, you might want to edit your post.

I found the advice about using Close here:
http://social.msdn.microsoft.com/Forums ... aproviders
In the All Replies section, about half way down the page, look for a post by William Vaughn, a response by someone else 2 posts later, then his reply. It is about 4 years old, so maybe the issue has been resolved.

Joe,

I'm not sure, unless a connection can't be used simultaneously for more than one (or more than "n") requests. Maybe Neal can give a more knowlegeable answer.

Posted: **Wed Apr 02, 2014 9:35 am**

Jay,

Odd, the URL is correct, the forum'ware isn't parsing it, I used a shortened url. The article is about ADO.NET connection pooling. The issue though is this is ADO.NET, I'm not sure NHibernate participates in this or what it uses for its pooling, if anything. I don't have experience with NHibernate but I can tell you Entity Framework's connection string is very different and unique.

All I can suggest is don't over-engineer. Let .NET do its thing. You may see additional connections, you may see additional memory, and you don't know when the garbage collector is going to reclaim disposed objects. I use Developer Express CodeRush add-in for code issues detection and suggestions. VS 2013 has one built in I believe, called Code Analysis. I suggest using those, reviewing them. I'm not a big fan of fxcop personally, to each their own. Don't look at things with a microscope unless a problem is noted in whatever way is my $.02.

Posted: **Wed Apr 02, 2014 9:53 am**

Neal,

I agree with your point about letting .Net do its thing whenever possible, it just seemed like this may be an example where it wasn't doing it correctly in some instances for some people. I don't know much about NHibernate either, when I said NHibernate was taking care of it I just meant that I don't see any orphaned connections, so however they are handling the DB connections doesn't cause this type of problem with the connection pool.

Posted: **Thu Apr 03, 2014 1:45 am**

When making use of 'using' block, explicitly closing the connection in finally block is not needed.

Also when a connection is closed, it is not necessary 'closed'. It is up to the connection pool to decide whether to close the connection completely or just return it to the connection pool. The application code doesn't have to know what the connetion pool is doing. It is only concerned with getting a connection, performing the operation, and returning/closing the connection.

Posted: **Thu Apr 03, 2014 6:10 am**

ForumsAdmin,

When making use of 'using' block, explicitly closing the connection in finally block is not needed.

I know that's what the documentation says, but I was pointing out that I came across information that indicated that at least one person has seen scenarios where the using block was not properly closing (or releasing or whatever term you want to use) a connection, resulting in the eventual exhaustion of the connection pool. However, it sounds like it wasn't easy to replicate that problem, so it is very possible that most people will never have an issue when letting the using block take care of it.

Posted: **Thu Apr 03, 2014 6:58 am**

For everyone's benefit, here is a handy page I built for Able Gold. It shows the sp_who2 results for the current store database and includes a filter for active connections only.

Be sure to load it into your /Admin/ folder so it's automatically protected from anonymous access.

Enjoy!

2014-04-03_10-19-48.jpg

Posted: **Thu Apr 03, 2014 7:28 am**

Thanks Joe. In addition to integrating a troubleshooting tool into the admin pages, it is a good illustration of using an existing custom control to greatly reduce the coding required. I haven't really explored the custom controls AC includes because most of my UI customizations on the admin side are tweaks like showing different or additional columns.

Posted: **Fri Apr 04, 2014 4:43 am**

Another tidbit. If you want to really clean up the sleeping connections, remove this line from the /app_data/nhibernate.config file:

Code: Select all

 <property name="connection.release_mode">on_close</property>

We did it last night and now the sleeping connections disappear within seconds.

We tried setting the connection timeout=5 in the connection string. But that had no effect which surprised me. I could clearly see connections that were minutes old still listed and 'sleeping'.

Posted: **Fri Apr 04, 2014 5:11 am**

Logan????

http://nhforge.org/doc/nh/en/index.html ... on-release

11.7. Connection Release Modes

The legacy (1.0.x) behavior of NHibernate in regards to ADO.NET connection management was that a ISession would obtain a connection when it was first needed and then hold unto that connection until the session was closed. NHibernate introduced the notion of connection release modes to tell a session how to handle its ADO.NET connections. Note that the following discussion is pertinent only to connections provided through a configured IConnectionProvider; user-supplied connections are outside the breadth of this discussion. The different release modes are identified by the enumerated values of NHibernate.ConnectionReleaseMode:

OnClose - is essentially the legacy behavior described above. The NHibernate session obtains a connection when it first needs to perform some database access and holds unto that connection until the session is closed.

AfterTransaction - says to release connections after a NHibernate.ITransaction has completed.

The configuration parameter hibernate.connection.release_mode is used to specify which release mode to use. The possible values:

auto (the default) - equivalent to after_transaction in the current release. It is rarely a good idea to change this default behavior as failures due to the value of this setting tend to indicate bugs and/or invalid assumptions in user code.

on_close - says to use ConnectionReleaseMode.OnClose. This setting is left for backwards compatibility, but its use is highly discouraged.

after_transaction - says to use ConnectionReleaseMode.AfterTransaction. Note that with ConnectionReleaseMode.AfterTransaction, if a session is considered to be in auto-commit mode (i.e. no transaction was started) connections will be released after every operation.

As of NHibernate, if your application manages transactions through .NET APIs such as System.Transactions library, ConnectionReleaseMode.AfterTransaction may cause NHibernate to open and close several connections during one transaction, leading to unnecessary overhead and transaction promotion from local to distributed. Specifying ConnectionReleaseMode.OnClose will revert to the legacy behavior and prevent this problem from occuring.

Posted: **Fri Apr 04, 2014 5:56 am**

Joe,

I wasn't concerned about a few connections remaining in the pool and sleeping as long as AC was reusing them. I think that is the way the connection pooling is supposed to work, otherwise the server has to go through the process of building a connection again the next time the site needs to talk to the database. The problem I was having was that more and more connections were being opened and never getting used or closed.

AbleCommerce Shopping Cart Software

Google Feed code leaking SQL connections

Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections

Re: Google Feed code leaking SQL connections