Тайм-ауты подключения к SQL Server и использование инструкций с объектами команд и соединений

Пожалуйста, прочитайте весь вопрос, прежде чем отвечать. И я извиняюсь, я никогда не пишу короткие вопросы...

Я поддерживаю внутреннее веб-приложение C#, которое работает с SQL Server 2008 R2 в Windows Small Business Server 2011 SP1.

В последнее время мы получаем много тайм-аутов SQL, вот пример исключения:

System.Web.HttpUnhandledException: возникло исключение типа «System.Web.HttpUnhandledException». ---> System.InvalidOperationException: время ожидания истекло. Время ожидания истекло до получения соединения из пула. Это могло произойти из-за того, что все соединения в пуле использовались и был достигнут максимальный размер пула. в System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) в System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection externalConnection, DbConnectionFactory connectionFactory) в System.Data.SqlClient.SqlConnection.Open()

Я проверил несколько вещей, одна из которых — то, как код обрабатывает соединения и закрывает соединения. Я читал в других потоках, что использование оператора Using с вашим соединением является адекватным, поскольку оно «... оборачивает соединение, созданное в попытке ... наконец, и помещает вызов удаления соединения внутри, наконец». Соединение закрывается даже в случае исключения.

Итак, я согласен и использую этот метод в течение многих лет. Другие рекомендуют явно закрывать соединения даже при использовании инструкции Using с вашим соединением. Думаю, это было бы лишним...

Однако мой вопрос касается объекта команды. Кто-то еще написал большую библиотеку методов базы данных для этого приложения, и они (во всех методах базы данных) объявили объект SqlCommand ПЕРЕД оператором использования объекта SqlConnection. Они также присвоили объект соединения объекту команды перед оператором соединения using.

Лучше ли объявлять и использовать объект команды внутри оператора соединения, используя оператор, и может ли это делать другим способом, чтобы вызвать тайм-ауты соединения sql (за исключением других причин тайм-аутов соединения sql)? Возьмите этот код, например:

    public Musician GetMusician(int recordId)
    {
        Musician objMusician = null;
        SqlConnection con = new SqlConnection(_connectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "selectMusician";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@id", recordId);

        using (con)
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();
                objMusician = new Musician((int)reader["id"]);
                objMusician.Name = (string)reader["name"];
            }
        }

        if objMusician != null)
        {
            objMusician.Albums = Albums.GetAlbums((int)objMusician.ID);
            objMusician.Tours = Tours.GetTours((int)objMusician.ID);
            objMusician.Interviews = Interviews.GetInterviews((int)objMusician.ID);
        }
        return objMusician;
    }

Также знайте, что на вызывающих страницах есть перехваты попыток, и это страница, которая регистрирует ошибку в нашей базе данных журналов. Мы позволяем исключению подниматься до вызывающего метода на странице, и там оно обрабатывается.


sql c# sql-server connection-timeout
person Kershaw    schedule 29.02.2016    source источник

Ответы (3)


arrow_upward
0
arrow_downward

Вы должны явно закрыть соединение, когда закончите с ним. Вы никогда не закрываете какие-либо соединения, поэтому после того, как вы достигнете предела пула соединений, вы будете получать ошибки, пока вы не перезапустите пул вручную или он не запустится сам по себе. Переместите блок назначения свойства внутрь блока использования и выполните con.Close(); cmd.Расположить(); перед возвратом вашего objMusician:

using (con)
{
    con.Open();
    SqlDataReader reader = cmd.ExecuteReader();
    if (reader.HasRows)
    {
        reader.Read();
        objMusician = new Musician((int)reader["id"]);
        objMusician.Name = (string)reader["name"];
    }

    if objMusician != null)
    {
        objMusician.Albums = Albums.GetAlbums((int)objMusician.ID);
        objMusician.Tours = Tours.GetTours((int)objMusician.ID);
        objMusician.Interviews = Interviews.GetInterviews((int)objMusician.ID);
    }

    con.Close();
    cmd.Dispose();        

    return objMusician;
}
person mjw    schedule 29.02.2016
comment
Предполагается, что оператор Using закрывает соединение. Разве это не правильно? - person Kershaw; 29.02.2016
comment
Кроме того, главный вопрос заключается в том, следует ли объявить объект команды и использовать его внутри соединения с использованием блока? Не проблематично ли это сделать? - person Kershaw; 29.02.2016
comment
Dispose закрывает соединение и возвращает его в пул. - person Chris Dunaway; 01.03.2016
comment
Нет, командный объект может быть объявлен внутри или снаружи блока подключения... ваша проблема в том, что вы не избавляетесь от объектов, поэтому вы просто оставляете их там в памяти и надеетесь, что сборщик мусора решит проблему до того, как вы работаете в тайм-ауты. Судя по описанному вами поведению, сборщик мусора не обрабатывает вещи до того, как это произойдет. - person mjw; 01.03.2016

arrow_upward
0
arrow_downward

Не знаю, поможет ли это вашей проблеме с тайм-аутом, но я всегда структурировал свой код следующим образом, и у меня не было этой проблемы:

using(var cmd = new SqlCommand())
{
    using(var con = new SqlConnection(ConnectionString))
    {
        con.Open();
        cmd.Connection = con;
        cmd.CommandText = "selectMusician";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@id", recordId);
        ...
    }
}

Только что читал в MSDN, он сказал: «Вызовите Dispose, когда вы закончите использовать компонент. Метод Dispose оставляет компонент в непригодном для использования состоянии. После вызова Dispose вы должны освободить все ссылки на компонент, чтобы сборщик мусора мог освободить память который занимал Компонент." Это означает, что для того, чтобы GC мог немедленно собрать соединение, вы должны удалить соединение перед удалением команды, в противном случае соединение зависнет, пока GC не успеет вызвать для него Finalize.

person Kevin    schedule 29.02.2016
comment
Но разве не это делают операторы Using? Они должны справиться с утилизацией для вас. Некоторые говорят, что в любом случае явно вызывать Dispose, даже если вы используете оператор Using с вашим соединением? Просто надеюсь получить исчерпывающий ответ. - person Kershaw; 01.03.2016
comment
Да, оператор using вызывает Dispose. Моя точка зрения выше состоит в том, чтобы получить их в правильном порядке, чтобы, когда оператор использования соединения удалял соединение, команда (которая имеет ссылку на соединение) удалялась после. Таким образом, сборщик мусора сразу соберет соединение, а не будет ждать, пока он не вызовет для него Finalize (что может занять некоторое время). - person Kevin; 01.03.2016

arrow_upward
0
arrow_downward

Рефакторинг вашего метода следующим образом. Вероятно, вы столкнулись с ситуацией, когда средство чтения данных имеет ссылку на соединение, и оно еще не удалено.

public Musician GetMusician(int recordId)
{
    Musician objMusician = null;

    using(SqlConnection con = new SqlConnection(_connectionString))
    {
        con.Open();
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = con;
            cmd.CommandText = "selectMusician";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@id", recordId);

            using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                if (reader.HasRows)
                {
                    reader.Read();
                    objMusician = new Musician((int) reader["id"]);
                    objMusician.Name = (string) reader["name"];
                }

                if objMusician != null)
                {
                    objMusician.Albums = Albums.GetAlbums((int)objMusician.ID);
                    objMusician.Tours = Tours.GetTours((int)objMusician.ID);
                    objMusician.Interviews = Interviews.GetInterviews((int)objMusician.ID);
                }
            }
        }

        return objMusician;
    }
}
person Mike Hofer    schedule 29.02.2016
comment
Я думаю, что это лучшая структура, которую я когда-либо видел, используя операторы для подключения, затем команду, затем читатель, и без явного удаления или закрытия. Что делает CommandBehavior.CloseConnection в ExecuteReader? Почему это необходимо? Если это хорошая практика, я буду использовать ее, но раньше этого не видел. - person Kershaw; 01.03.2016
comment
Это гарантирует, что соединение будет закрыто, как только вы закончите работу со считывателем (то есть, когда оно будет закрыто/удалено). См. здесь: msdn.microsoft. com/ru-ru/library/ - person Mike Hofer; 01.03.2016
comment
Глядя на некоторые из моих недавних кодов в других проектах, я использовал ту же структуру, что и выше, но без CommandBehavior.CloseConnection в ExecuteReader. Я думаю, что я обновлю свою структуру с этим, хотя, как новое уточнение... - person Kershaw; 01.03.2016