Connection pool with possible disconnected connections

Question

I have several threads accessing the same database (with the same connection string). Each thread:

• creates its own instance of SqlConnection using the same connection string

• uses the code below to open his own connection instance when he needs

  try { wasOpened = connection.State == ConnectionState.Open; if (connection.State == ConnectionState.Closed) { connection.Open(); } } catch (Exception ex) { throw new Exception(string.Format("Connection to data source {0} can not be established! Reason: {1} - complete stack {2}", connection.Database, ex.Message, ex.StackTrace == null ? "NULL" : ex.StackTrace.ToString())); } 

We have tested this code on 2 servers so far, and one server sometimes throws an exception in the SqlConnection.Open method. Here is the exception message that we get from the catch block:

Unable to connect to xyz data source! Reason: Invalid operation. The connection is closed. - full stack

in System.Data.SqlClient.SqlConnection.GetOpenConnection ()
in System.Data.SqlClient.SqlConnection.get_Parser ()
in System.Data.SqlClient.SqlConnection.Open ()

Checking the SqlConnection.GetOpenConnection method shows that innerConnection is null:

 internal SqlInternalConnection GetOpenConnection() { SqlInternalConnection innerConnection = this.InnerConnection as SqlInternalConnection; if (innerConnection == null) { throw ADP.ClosedConnectionError(); } return innerConnection; } 

It remains unclear to me: why does the connection pool sometimes give me a disconnected connection (innerConnection == null)?

Change # 1 : there are no static properties in the code - we always close the connection, if necessary, wasOpened is used in our Close method and means: if the connection was already open when Open was called, just leave it open when closing, otherwise close it. However, this is not related to the problem described in this question (innerConnection == null).

Edit # 2 : Server: SQL Server 2008 R2, Windows Server 2003. Client: Windows Server 2003 (code is executed in the custom component of the SSIS package). Connection string: Data Source=server_name;Initial Catalog=db_name;Integrated Security=SSPI;Application Name=app_name

Answer 1

Read carefully first: SQL Server Connection Pool (ADO.NET)

I will quote the most important part for you (I think):

A connection pool is created for each unique connection string. When a pool is created, several pool connection objects are created and added to meet the minimum pool size requirements. Connections are added to the pool as needed, up to a maximum size pool (100 by default) . Connections are dropped back to the pool when they are closed or located .

When a SqlConnection object is requested, it is obtained from the pool if an accessible connection is available. To be useful, the connection must be unused, have an appropriate transaction context, or be unrelated to any transaction context, and have a valid server link.

The connection pool satisfies connection requests by redistributing the connections as they are released back to the pool. If the maximum pool size is reached and no usable connection is available, the request is queued . Then the pool tries to return before the timeout expires (the default value is 15 seconds). If the pool cannot satisfy the request before connecting out, an exception is thrown .

We strongly recommend that you always close the connection when you finish using it, so that the connection will be returned to the pool. You can do this using the Close or Dispose methods of the Connection object or opening all connections inside a C # statement or Using using in Visual Basic. links that are not explicitly closed cannot be added or returned to the pool. For more information see Using Statement (C # Reference)

In short : do not reject the territory of the connection pool and close the connections as soon as you finish with them (fe via using-statement ).

Since you do not want to throw your DB-Class in the garbage bin , I would suggest either increasing the maximum pool and / or timeout, or disabling the merge and see what happens.

 <add name="theConnectionString" connectionString="Data Source=(local); Database=AdventureWorks; Integrated Security=SSPI; Max Pool Size=200; Pooling=True; Timout=60" /> 

You should also try to catch this particular error and clear all connection pools :

 System.Data.SqlClient.SqlConnection.ClearAllPools(); 

Or take a look at these questions that look promising:

• SQL Server connection pool does not detect closed connections?
• http://forums.asp.net/t/1729521.aspx/1

Answer 2

I have several threads accessing the same database (with the same connection string). Each thread:

• creates its own instance of SqlConnection using the same connection string
• uses the code below to open his own connection instance when he needs it.

If you have a problem that appears by accident, in your case, given the code you provided, you can:

• Problem with the connection pool on the server
• Race condition somewhere in your code

All that was said ...

You must wrap your SqlConnection in a using statement. Thus, the connection will be closed when your code or thread is executed with it.

 using (SqlConnection connection = new SqlConnection(connectionString)) { //... stuff } 

Thus, the connection is guaranteed using the Dispose() method (which internally calls Close() ). Thus, the connection can be returned to the pool if it is used (which is likely to be).

Answer 3

The answers of Tim Schmelter and Brian Crosby are very valuable in terms of recommendations, recommendations, best practices and so on. Unfortunately, this was not enough for me because I cannot make changes to the legacy code.

The solution to this particular problem was to encapsulate the SqlConnection Open and Close methods with the same lock. Please note that this is consistent with our scenario, this may not match the others.

I'm really sorry that I can’t delve deeper into it now and find out if our code or the connection pool was not completely thread safe. I know that, most likely, the source is in our code. With this in mind, this answer is more costly than the real solution.

Before anyone else applies this workaround, read the other answers as well.