Объединение подключений в пул в SQL Server (ADO.NET)

  • Статья

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

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

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

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

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

Примечание.

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

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

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

В следующем примере 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.  
    }

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

Примечание.

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

Добавление соединений

Пул соединений создается для каждой уникальной строки соединения. При создании пула создается множество объектов соединения, которые добавляются в пул для удовлетворения требования к минимальному размеру пула. Подключения добавляются в пул по мере необходимости, пока не будет достигнут его максимальный размер (по умолчанию это 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, класс событий.

Удаление соединений

Владелец пулов подключений удаляет из пула подключения, которые остаются неактивными в течение 4–8 минут или теряют соединение с сервером. Обратите внимание, что разорванное соединение можно определить только после попытки связи с сервером. При обнаружении соединения, которое больше не имеет связи с сервером, оно помечается как недействительное. Недействительные соединения удаляются из пула соединений только после их закрытия или возврата.

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

Очистка пула

ADO.NET 2.0 ввели два новых метода очистки пула: ClearAllPools и ClearPool. Метод ClearAllPools очищает пулы соединений данного поставщика, а метод ClearPool - пул, связанный с конкретным соединением. Если во время вызова методов соединения использовались, они соответствующим образом помечаются. При их закрытии вместо возвращения в пул они удаляются.

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

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

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

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

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

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

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

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

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

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

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

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

' Assumes that command is a valid SqlCommand object and that  
' connectionString connects to master.  
    command.Text = "USE DatabaseName"  
Using connection As New SqlConnection(connectionString)  
    connection.Open()  
    command.ExecuteNonQuery()  
End Using  

// Assumes that command is a SqlCommand object and that  
// connectionString connects to master.  
command.Text = "USE DatabaseName";  
using (SqlConnection connection = new SqlConnection(  
  connectionString))  
  {  
    connection.Open();  
    command.ExecuteNonQuery();  
  }

Роли приложений и пул соединений

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

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

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

См. также