Пул подключений к SQL Server (ADO.NET)

Область применения: .NET Framework .NET .NET Standard

Скачать ADO.NET

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

На практике большинство приложений использует только одно или несколько различных конфигураций соединений. Это означает, что во время выполнения приложения многие идентичные соединения будут повторно открываться и закрываться. Чтобы минимизировать расходы на открытие подключений, поставщик данных Microsoft SqlClient для SQL Server использует методику оптимизации, известную как пул подключений.

Использование пула соединений снижает количество случаев, когда необходимо открывать новые соединения. Владелец пула поддерживает управление физическим подключением. Он управляет соединениями с помощью поддержания набора активных соединений для каждой конфигурации данного соединения. Каждый раз, когда пользователь вызывает Open для подключения, пулер ищет доступное соединение в пуле. Если доступно соединение из пула, оно возвращается вызывающей стороне вместо открытия нового соединения. Когда приложение вызывает метод Close для соединения, пул соединений возвращает его в пул активных соединений вместо закрытия. После возвращения соединения в пул оно готово к повторному использованию при следующем вызове метода Open.

В пул могут помещаться только соединения с одинаковой конфигурацией. Поставщик данных Microsoft SqlClient для SQL Server поддерживает одновременно несколько пулов, по одному для каждой конфигурации. Соединения разделяются в пулы строкой соединения, а при использовании встроенной безопасности - удостоверением Windows. Соединения также заносятся в пул в зависимости от того, прикреплены ли они к транзакции. При использовании метода ChangePassword экземпляр SqlCredential влияет на пул соединений. Различные экземпляры SqlCredential используют различные пулы соединений, даже если идентификатор пользователя и пароль совпадают.

Организация пулов соединений может существенно улучшить производительность и масштабируемость приложения. По умолчанию в поставщике данных Microsoft SqlClient для SQL Server включена поддержка пулов подключений. Если вы явно не отключите пул соединений, он будет оптимизировать работу с соединениями по мере их открытия и закрытия в вашем приложении. Также можно указать несколько модификаторов строки соединения для управления поведением пула соединений. Дополнительные сведения см. в разделе Управление пулами подключений с помощью ключевых слов строки подключения далее в этой статье.

Внимание

Если пул подключений включён и возникает ошибка тайм-аута или другая ошибка входа в систему, будет выдано исключение, а последующие попытки подключения будут завершаться сбоем в течение следующих 5 секунд, "blocking period". Если приложение попытается подключиться в течение периода блокировки, снова возникнет первое исключение. Новые сбои после периода блокировки будут вызывать новые периоды блокировки, каждый из которых будет в два раза больше предыдущего, вплоть до максимального периода в 1 минуту.

Примечание.

Механизм blocking periodпо умолчанию не применяется к Azure SQL Server. Это поведение можно изменить, изменив свойство PoolBlockingPeriod в ConnectionString, за исключением .NET Standard.

Создание и назначение пула

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

Примечание.

Подключения объединяются в пулы по процессам, доменам приложения, строкам подключения, а если используется встроенная безопасность, то и по удостоверениям Windows. Строки соединения должны точно совпадать. Ключевые слова, указанные в различном порядке для одного соединения, будут включены в пул по отдельности.

Примечание.

Если аргумент MinPoolSize не указан в строке соединения или указано значение 0, соединения в пуле будут закрыты после периода отсутствия активности. Однако, если указанное значение MinPoolSize больше нуля, пул соединений не уничтожается до тех пор, пока AppDomain не будет выгружен и процесс не завершится. Обслуживание неактивных или пустых пулов требует минимальных системных издержек.

Примечание.

Пул автоматически очищается при возникновении критической ошибки, например, при автоматическом переключении на резервный ресурс.

В следующем примере C# создаются три новых объекта SqlConnection, но для управления ими требуется только два пула соединений. Обратите внимание, что первая и вторая строки соединения отличаются значениями, присвоенными аргументу Initial Catalog.

using (SqlConnection connection = new SqlConnection(
    "Integrated Security=SSPI;Initial Catalog=Northwind"))
        {
            connection.Open();
            // Pool A is created.
        }

    using (SqlConnection connection = new SqlConnection(
    "Integrated Security=SSPI;Initial Catalog=pubs"))
        {
            connection.Open();
            // Pool B is created because the connection strings differ.  
        }

    using (SqlConnection connection = new SqlConnection(
    "Integrated Security=SSPI;Initial Catalog=Northwind"))
        {  
            connection.Open();
            // The connection string matches pool A.  
        }

Добавление подключений

Пул соединений создается для каждой уникальной строки соединения. При создании пула создается множество объектов соединения, которые добавляются в пул для удовлетворения требования к минимальному размеру пула. Подключения добавляются в пул по мере необходимости, пока не будет достигнут его максимальный размер (по умолчанию это 100 подключений). Соединения освобождаются обратно в пул при закрытии или ликвидации.

При запросе объекта SqlConnection он получается из пула при наличии готового к использованию соединения. Чтобы соединение можно было использовать, оно не должно использоваться, иметь совпадающий контекст транзакции либо не иметь связи с каким-либо контекстом транзакций и иметь допустимую ссылку на сервер.

Пул соединений удовлетворяет запросы на подключение, повторно выделяя соединения по мере их возврата в пул. Если достигнут максимальный размер пула, а пригодные соединения недоступны, запрос помещается в очередь. Затем диспетчер пула пытается вернуть любое подключение, пока не истечёт время ожидания (по умолчанию — 15 секунд). Если организатор пулов не может обработать запрос до истечения времени ожидания соединения, возникнет исключение.

Внимание

Мы настоятельно рекомендуем всегда закрывать соединение, когда закончите им пользоваться, чтобы оно вернулось в пул. Это можно сделать с помощью методов Close или Dispose объекта Connection, либо открывая все подключения внутри инструкции using (C#) или Using (Visual Basic). Соединения, не закрытые явным образом, могут не быть добавлены в пул или возвращены в него. Дополнительные сведения см. в разделе оператор using или Практическое руководство. Удаление системного ресурса для Visual Basic.

Примечание.

Не вызывайте Close или Dispose для объекта Connection, DataReader или любого другого управляемого объекта в методе Finalize вашего класса. В финализаторе следует освобождать только те неуправляемые ресурсы, которыми класс владеет напрямую. Если класс не владеет какими-либо неуправляемыми ресурсами, не включайте в его определение метод Finalize. Дополнительные сведения см. в статье Сборка мусора.

Дополнительные сведения о событиях, связанных с открытием и закрытием подключений, вы найдете в разделах документации по SQL Server: Audit Login, класс событий и Audit Logout, класс событий.

Удаление подключений

Если loadBalanceTimeout (или Connection Lifetime) задан, когда соединение возвращается в пул, время его создания сравнивается с текущим временем, и соединение уничтожается, если этот интервал времени (в секундах) превышает указанное значение LoadBalanceTimeout. Эта возможность полезна в кластеризованных конфигурациях для принудительной балансировки нагрузки между выполняющимся сервером и сервером, только что переведенным в оперативный режим.

Если LoadBalanceTimeout (или время существования подключения) не задано (значение по умолчанию = 0), пулер соединений удаляет соединение из пула после примерно 4–8 минут простоя (случайным образом в два прохода) или если пулер обнаруживает, что соединение с сервером было разорвано.

Примечание.

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

Если существующее соединение с сервером исчезло, оно может быть удалено из пула, даже если организатор пулов соединений не определил разорванное соединение и не пометил его как недопустимое. Это связано с тем, что издержки на проверку того, что соединение по-прежнему действительно, свели бы на нет преимущества использования пулера, поскольку потребовался бы дополнительный обмен данными с сервером. В этом случае первая попытка использовать соединение определит его разрыв и вызовет исключение.

Очистка пула

Поставщик данных Microsoft SqlClient для SQL Server представил два новых метода для очистки пулов: ClearAllPools и ClearPool. Метод ClearAllPools очищает пулы соединений данного поставщика, а метод ClearPool - пул, связанный с конкретным соединением.

Примечание.

Если в момент вызова используются какие-либо соединения, они помечаются соответствующим образом. После закрытия они отбрасываются вместо того, чтобы возвращаться в пул.

Поддержка транзакций

Соединения выбираются из пула и назначаются в зависимости от контекста транзакции. Если в строке соединения не указан аргумент Enlist=false, пул соединений гарантирует прикрепление соединения к контексту Current. После закрытия и возврата соединения в пул с прикрепленной транзакцией System.Transactions оно резервируется таким образом, что следующий запрос к пулу соединений с той же транзакцией System.Transactions вернет это соединение, если оно доступно. Если при выдаче такого запроса в пуле нет доступных соединений, соединение берется и прикрепляется из части пула, не использующей транзакции. Если доступных соединений нет во всех частях пула, создается и прикрепляется новое соединение.

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

Управление пулом подключений с помощью ключевых слов строки подключения

Свойство ConnectionString объекта SqlConnection поддерживает пары «ключ-значение» из строки соединения, с помощью которых можно изменять логику организации пулов соединений. Дополнительные сведения см. в разделе ConnectionString.

Фрагментация пулов

Фрагментация пула — распространённая проблема во многих веб-приложениях, где приложение может создавать большое количество пулов, которые не освобождаются до завершения процесса. Это оставляет большое количество соединений открытыми и потребляющими память, что приводит к ухудшению производительности.

Фрагментация пулов из-за встроенной безопасности

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

Фрагментация пулов из-за большого числа баз данных

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

Это также является побочным эффектом конструкции приложений. При соединении с SQL Server существует относительно простой способ устранения этого побочного эффекта без нарушения безопасности. Вместо соединения каждого пользователя или группы с отдельной базой данных устанавливается соединение с одной базой данных на сервере, а затем выполняется инструкция Transact-SQL USE, чтобы переключиться на требуемую базу данных.

Следующий фрагмент кода демонстрирует создание первоначального соединения с базой данных master и последующее переключение на требуемую базу данных, указанную в строковой переменной databaseName.

// Assume that connectionString connects to master.  
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand())
{
    connection.Open();
    command.Connection = connection;
    command.CommandText = "USE DatabaseName";
    command.ExecuteNonQuery();
}

Роли приложений и пулы подключений

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

Альтернативы ролям приложений

Рекомендуется пользоваться преимуществом новых механизмов безопасности, которые пришли на смену ролям приложения.

Связанные материалы

• Организация пулов соединений
• SQL Server и ADO.NET
• Диагностические счетчики в SqlClient
• Microsoft. Data.SqlClient for SQL Server