Организация пулов соединений SQL Server (ADO.NET)

Статья
11/04/2008

Обновлен: November 2007

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

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

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

В пул могут помещаться только соединения с одинаковой конфигурацией. ADO.NET поддерживает несколько пулов одновременно, по одному для каждой конфигурации. Соединения разделяются в пулы строкой соединения, а при использовании встроенной безопасности — удостоверением Windows. Соединения также заносятся в пул в зависимости от того, прикреплены ли они к транзакции.

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечание.
События входа в систему и выхода из системы не вызываются на сервере при выборке подключения из пула подключений и при возврате его в пул подключений. Причина заключается в том, что при возврате в пул подключений подключение фактически не закрывается. Дополнительные сведения см. в разделах Класс события аудита входа в систему и Класс события аудита выхода из системы электронной документации по SQL Server.

События входа в систему и выхода из системы не вызываются на сервере при выборке подключения из пула подключений и при возврате его в пул подключений. Причина заключается в том, что при возврате в пул подключений подключение фактически не закрывается. Дополнительные сведения см. в разделах Класс события аудита входа в систему и Класс события аудита выхода из системы электронной документации по SQL Server.

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

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

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

Очистка пула

В 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 2005 рекомендуется использовать преимущество новых механизмов безопасности, которые можно использовать вместо ролей приложения. Дополнительные сведения см. в разделе Создание ролей приложения в SQL Server (ADO.NET).

См. также

Основные понятия

Счетчики производительности (ADO.NET)

Другие ресурсы

Организация пулов соединений (ADO.NET)

SQL Server и ADO.NET

Поделиться через