C# - MSSQL Random Timeouts

30. August 2011 21:44

 

If you have ever come across a c# application or any asp.net application for that matter that appears to work perfectly then without warning starts to fail with database timeout issues. There is one of two things normally wrong. Either it is a database problem. Or another rather common problem is actually in the code inside the application. This is when the developers and the database guys normally start screaming at each other. This normally happens because the developers cannot reproduce any problems in their enviroment and the dba's know that there database is in perfect working order and see little or no load on the sql server.

This can happen if you have the following code ...

 

 

SqlConnection Conn = new SqlConnection(Args);
SqlCommand cmd = new SqlCommand("SELECT * FROM TABLE WHERE x = y");
Conn.Open();

cmd.Connection = Conn;
cmd.ExecuteNonQuery();
cmd.Dispose();

Conn.Dispose();
Conn.Close();

 

 

It looks like some pretty simple code. It works too ... Or does it?

Lets have a look for a minute at the same code which handles exceptions.

 

while (true)
{
    try
    {

        SqlConnection Conn = new SqlConnection(Args);
        SqlCommand cmd = new SqlCommand("SELECT * FROM TABLE WHERE x = y");
        Conn.Open();

        cmd.Connection = Conn;
        cmd.ExecuteNonQuery();
        cmd.Dispose();

        Conn.Dispose();
        Conn.Close();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

 

 

The above looks pretty good to. However there is a pretty nasty bug in it. To a developer in a test enviroment it looks really good and appears to work. So what happens when something invalid happens in the sql command? You are probably thinking that the sql works and is correct. The sql may be correct but what about a bug / issue about a key or a constraint violation? Well the sql is valid but it will still produce an error.

 

The error of course is being logged. However the Conn.Dispose / Conn.Close will never be called this will mean that you are now holding an open connection to the database until the c# garbage collector decides to collect the database connection. This actually creates a problem when you have a number of errors before connection is destroyed in some way. The SqlConnection class uses a "pool" of connections in the background by default which is used to hold open connections to the database server. This is done to improve the performance and latency of opening and closeing database connections to a server. After an application has been running for a while and there is a number of opened connections to the server the SqlConnection open / close functions don't actually open and close connections. It is more like they are only getting and returning a spare connection from the pool in the background. So if you have an unmatch number of open and close calls being made specificly when you call open without calling close then the pool will run out of connections.

 

Try running the following code

 

 

class Program
{
    static void Main(string[] args)
    {
        string Args = "Data Source=SQL;Initial Catalog=Mess;Integrated Security=True;Connect Timeout=10;Timeout=10;Max Pool Size=10;";

        while (true)
        {
            try
            {
                Console.WriteLine("Trying");
                SqlConnection Conn = new SqlConnection(Args);
                SqlCommand cmd = new SqlCommand("SELECT INVALID");
                Conn.Open();

                cmd.Connection = Conn;
                cmd.ExecuteNonQuery();
                cmd.Dispose();

                Conn.Dispose();
                Conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

 

The output however will be something like this....

 

Invalid column name 'INVALID'.
Trying
Invalid column name 'INVALID'.
Trying
Invalid column name 'INVALID'.
Trying
Invalid column name 'INVALID'.
Trying
Timeout expired.  The timeout period elapsed prior to obtaining a connection fro
m the pool.  This may have occurred because all pooled connections were in use a
nd max pool size was reached.
Trying
Timeout expired.  The timeout period elapsed prior to obtaining a connection fro
m the pool.  This may have occurred because all pooled connections were in use a
nd max pool size was reached.
Trying

 

Note: there will be a delay of the "Connect Timeout" for each of the timeout messages. In this case to trigger the bug I have limited the connection pool to 10 to produce the error quickly. The default maximum connections in .net is 100 connections. This can explain why a bug like this can hide for a long period of time.

 

Fixing the issue is simple. Move the Conn.Close / Conn.Dispose outside of the exception handler. It sems however lots of developers seem to think this is enough. Then they copy and paste the same sql code all over there application instead of creating a supporting class for running all of their sql. I made a previous post about code organization and database connections.

 

E-mail Kick it! DZone it! del.icio.us Permalink


Comments (2) -

9/8/2011 12:31:35 PM #

Would wrapping your command and connection into a using statement have the same effect? Or do you feel like calling dispose explicitly is more efficient?

Kevin Upchurch United States |

9/8/2011 6:24:34 PM #


The using statement is the same. But you don't always have the same chance to clean up / log error and such things. It will call the dispose and other things if you want it to. The way I do is really habit from writing c/c++ which had no using statement.

However in some situation I want the sqlconnection to stay alive and only the close to be called. Or only the sql statement to be killed off.

james United Kingdom |