Skip to main content
GameDev.net gamedev.net
🔒 Locked

[web] Websites and connection pooling

Started by geo2004 Nov 3, 2009 at 3:11 PM 6 replies 1.1k views
Original Post
geo2004
geo2004
We are having some Timeout issues at work related to our connection pool, so I was wondering if anyone could assist me in figuring out exactly how the connections are pooled (can't remember the error exactly, but something along the lines of 'Connection timedout before a connection could be obtained from the connection pool'). We are using SQL Server and SIS. Our system is kind of weird so I have several questions, and hopefully its not too confusing. So from what I have read online, sql server will pool connections based off of the credentials of the connection string. So connection strings with the same username, password, server and database will pull from the same connection pool. However I'm not sure how connection strings being used on different domains/app pools are pooled. I have several different environments and am curious how each would pull from the connection pool: (Assume both environments on same server) Environment 1 (2 domains, 1 App Pool): Site A: www.Domain1.com. Uses App Pool: ABC. Connection Used:"Data Source=ServerName;Initial Catalog=DatabaseName;UID=User;PWD=Password;" Site B: www.Domain2.com. Uses App Pool: ABC. Connection used:"Data Source=ServerName;Initial Catalog=DatabaseName;UID=User;PWD=Password;" Environment 2 (2 domains, 2 App Pools): Site C: www.Domain3.com. Uses App Pool: RST. Connection Used:"Data Source=ServerName;Initial Catalog=DatabaseName;UID=User;PWD=Password;" Site D: www.Domain4.com. Uses App Pool: XYZ. Connection used:"Data Source=ServerName;Initial Catalog=DatabaseName;UID=User;PWD=Password;" How would these pull from the connection pool? Are they all using separate pools? A and B same pool, C and D different? Hopefully this makes sense. Thanks.
Codeka
Codeka
There is one pool per AppDomain, so since each website is running in it's own AppDomain, it'll get it's own connection pool.
geo2004
geo2004
OK, that's kind of what I assumed. I'm going to need to scour my code and look for any connections not getting closed.

I have another question.

Lets say I have the below code:

private void MyTestFunction()        {            SqlConnection conn = null;            string strconn = "Data Source=server;Initial Catalog=db;User ID=name;Password=pass;";            conn = new SqlConnection(strconn);            conn.Open();            Cleanup(conn);            // After calling Cleanup(), the connection is closed             // and cleared from the call to Dispose().            // However it is not null?        }        private void Cleanup(SqlConnection conn)        {            if (conn != null)            {                conn.Dispose();                conn = null;    // Connection is closed, cleared, and null.            }        }


I stepped thru this code and it didn't quite work as I thought it would. I thought that conn would be null after returning from the Cleanup function, but it is just the closed and cleared connection.
I thought about using a 'cleanup' function like this just to save a little time, but maybe its not worth it. I was going to use it like the above, but also pass commands and DataAdapters to it:
private void Cleanup(SqlConnection conn, SqlCommand cmd, SqlDataAdapter da){Dispose all here...}

So A) why isn't conn null after returning from Cleanup? Apparently it isn't passed quite the way I thought.
B) Is having a generic Cleanup function a good idea? Is there any gain/loss in it?
mattd
mattd
Quote:
Original post by geo2004
So A) why isn't conn null after returning from Cleanup? Apparently it isn't passed quite the way I thought.

Because you are passing by value. Check this page - it has an example which is doing exactly what you're doing.

Quote:
B) Is having a generic Cleanup function a good idea? Is there any gain/loss in it?

Perhaps try using the using (:]) statement instead. See here, in the remarks section.
Codeka
Codeka
Quote:
Original post by geo2004
OK, that's kind of what I assumed. I'm going to need to scour my code and look for any connections not getting closed.

I have another question.

Lets say I have the below code:

*** Source Snippet Removed ***
The code you posted is not exception-safe. If an exception occured between the call to conn.Open() and the call to Cleanup(), your connection would be leaked. As mattd pointed out, liberal use of the using statement is always a good idea.
geo2004
geo2004
Quote:
Original post by Codeka
The code you posted is not exception-safe. If an exception occured between the call to conn.Open() and the call to Cleanup(), your connection would be leaked. As mattd pointed out, liberal use of the using statement is always a good idea.


Yea I know, that was a bad example. I always put code like that in a try..catch..finally block (putting the Cleanup() in the finally), but just left it out of that example because I wrote it up fast. Sorry about that.
I also don't use using statements often, just because the error's are handled internally, and they can't be handled. I'd rather do the try/catch myself.

As for my Cleanup function, that makes sense now. I always forget that it is a reference, passed by value.

Now my manager said the reason you can call Dispose() on the connection in the cleanup is because it will dereference the object, therefore actually modifying the original object. Is that why that works as I thought it would?
mattd
mattd
Quote:
Original post by geo2004
Now my manager said the reason you can call Dispose() on the connection in the cleanup is because it will dereference the object, therefore actually modifying the original object. Is that why that works as I thought it would?

Correct. Like you said, it's the reference that's getting passed by value. Operations on it still refer to the original object, but re-seating the reference doesn't change it in whatever invoked Cleanup, because the reference was passed by value.
ID Merlin
ID Merlin
Quote:
Original post by geo2004
So from what I have read online, sql server will pool connections based off of the credentials of the connection string. So connection strings with the same username, password, server and database will pull from the same connection pool.


I'm fairly sure I've read that the pooling is based on the whole string, so differences in the string such as "Server" and "server" will not be treated as the same connection.
Aragon Online

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.

Sign in to reply to this topic.