ADO.NET 2.0: A importância do Pool de Conexões

Introdução

A maior parte das aplicações desenvolidas normalmente acessam banco de dados. Sempre que precisar acessar os dados no servidor, SQL Server por exemplo, a aplicação o realizará através de um objeto de conexão de um dos Data Providers do ADO.NET, por exemplo a classe System.Data.SqlClient.SqlConnection. Para criar esta conexão é preciso informar a string de conexão, que indica qual servidor e qual banco de dados será estabelecida a conexão etc. Em resumo, a string de conexão identifica o fornecedor do banco de dados, nome da máquina onde a aplicação de banco de dados está instalada, nome do banco de dados da aplicação, informações de segurança (usuário, senha, autenticação integrada etc) e parâmetros de controle do Pool de Conexões, entre outras.

Imagine que sua aplicação tem uma tela que mostra alguns registros resultantes de uma consulta. Já percebeu que existe uma demora até que os dados sejam exibidos? Boa parte desta demora é porque sua aplicação está criando uma conexão com banco de dados. Só que este processo de conexão não é tão simples e tem um custo alto. Por que? Porque uma conexão com banco de dados consiste de algumas tarefas que consomem recursos: uma comunicação física, por exemplo um Socket, precisa ser estabelecida e para isso é necessário um handshake inicial entre as máquinas; a string de conexão precisa ser analisada e validada; a conexão deverá ser autenticada contra o servidor; algumas verificações para registro numa transação corrente etc. Como uma aplicação precisa interagir várias vezes com o banco de dados, dependendo de como esteja sendo utilizada, o desempenho e a escalabilidade da aplicação podem ser comprometidos. Como essas conexões são custosas, percebe-se a necessidade de reaproveitar as conexões que já foram estabelecidas e utilizá-las da melhor forma possível.

A maioria das aplicações usam uma ou poucas configurações de banco de dados diferentes, pois durante a execução da aplicação várias conexões idênticas serão abertas e fechadas, o que poderá impactar a performance da sua aplicação. Como resolver isso? Reutilização das conexões, ok? Até porque você não precisa delas o tempo todo, e sim somente quando for necessário executar algum comando contra o banco de dados.

Para minimizar o custo da abertura das conexões, o ADO .NET dispõe de um recurso poderoso chamado Pool de Conexões (ou Connection Pooling), que já está habilitado por padrão no Data Provider para SQL Server, e ainda melhor no ADO.NET 2.0.

Este artigo tem por objetivo explicar quase tudo que você precisa saber sobre o Pool de Conexões, seu funcionamento para que você o utiize da melhor forma possível de forma eficiente para garantir o desempenho e escalabilidade da sua aplicação.

O que é o Pool de Conexões?

O Pool de Conexões é um repositório que mantém uma lista de conexões abertas e reutilizáveis. Uma aplicação poderá conter um ou vários pools ao mesmo tempo e a qualquer momento, mas estes não são compartilhados entre aplicações. Para cada string de conexão única será criado um pool na primeira vez que a aplicação solicitar uma conexão para uma determinada string de conexão. Se a aplicação fizer uma nova requisição com uma string de conexão diferente, será criado um novo pool. Todo o controle é garantido pelo Connection Pool Manager do ADO.NET, ou gerenciador do pool. Com o Pool de Conexões não haverá mais necessidade de executar todo o procedimento custoso sempre que a aplicação solicitar uma conexão e sua aplicação poderá reaproveitar as conexões que já foram estabelecidas anteriormente. A tabela 1 ilustra quatro strings de conexão, onde serão criados três pools diferentes, um para cada string que não se repete.

Tabela 1: Exemplos de strings de conexão.

String de conexão A:

string strConn =

"Server=tech-renatog;Initial Catalog=framework;User ID=nomeusuario;Password=senhaforte";

using (SqlConnection connection = new SqlConnection(strConn)) {

//A conexão deverá ser aberta somente quando necessário

connection.Open();

//executa comandos etc

}//método Dispose() será invocado automaticamente

String de Conexão B:

string strConn =

"Server=tech-renatog;Initial Catalog=framework; ID=teste;Password=teste";

using (SqlConnection connection = new SqlConnection(strConn)) {

//A conexão deverá ser aberta somente quando necessário

connection.Open();

//executa comandos etc

}//método Dispose() será invocado automaticamente

String de Conexão C:

string strConn =

"Server=tech-renatog;Initial Catalog=crm;Integrated Security=SSPI";

using (SqlConnection connection = new SqlConnection(strConn)) {

//A conexão deverá ser aberta somente quando necessário

connection.Open();

//executa comandos etc

}

String de Conexão D:

string strConn =

"Server=tech-renatog;Initial Catalog=crm;Integrated Security=SSPI";

using (SqlConnection connection = new SqlConnection(strConn)) {

//A conexão deverá ser aberta somente quando necessário

connection.Open();

//executa comandos etc

}

Quando uma conexão é aberta pela primeira vez, neste momento é criado um pool de conexão baseado no algoritmo que analisa a string de conexão e a associa ao pool criado recentemente. Cada pool é associado a uma string de conexão única. Quando uma conexão é aberta, se a string de conexão não combinar com alguma já existente no Pool de Conexões, um novo pool será criado. O pool será mantido até que a aplicação seja encerrada e, mesmo que o pool fique inativo ou vazio, não haverá um custo alto para a aplicação.

No exemplo da tabela 1 temos quatro strings de conexão. Para as strings A e B serão criados dois pools diferentes. Perceba que a principal diferença entre as strings A e B é o contexto de segurança, pois conectam o banco de dados com usuários distintos. Para as strings C e D será criado um pool que será fragmentado devido a autenticação integrada. A conexão com autenticação integrada provoca a fragmentação do pool porque as conexões são colocadas no pool de acordo com a string de conexão mais a identidade do usuário, gerando um pool por usuário. Embora melhore o desempenho da aplicação por usuário, um mesmo usário não poderá se beneficiar das conexões feitas por outro usuário.

As conexões são adicionadas ao pool por processo, por domínio de aplicação (App Domain), por string de conexão, e quando usa segurança integrada, pelo Registro do Windows. É uma tecnologia que atua do lado cliente (client-side), e o banco de dados não faz idéia se existe um ou mais pools associado a uma aplicação. Tecnologia client-side porque o pool é criado na máquina que está iniciando a conexão através de um objeto DbConnection.

O controle do Pool de Conexões é realizado através dos parâmetros que estão listados na tabela 2.

Tabela 2: Parêmatros para controle do Pool de Conexão.

Parâmetro	Valor Padrão	Descrição
Connection Timeout	15 segundos	Quanto tempo deve ser esperado para se obter uma conexão.
Load Balancing Timeout, Connection Lifetime	0	Quando a conexão é devolvida ao pool a hora da criação é comparada com a hora atual e, caso o resultado exceda o valor deste parâmetro, a conexão é destruída. Quando 0, as conexões do pool terão o tempo máximo de timeout. Esta configuração é ideal para cenários com load-balanced para forçar o balanceamento entre o servidor online e o servidor que acabou de ser inicializado. Os parâmetros “Load Balancing Timeout” e “Connection Lifetime” têm o mesmo propósito, o último mantido por questões de compatibilidade.
Connection Reset	True	Indica se a conexão é reiniciada quando é removida do pool. Recomenda-se que para MS SQL Server 7.0, atribuir “false” para evitar solicitações ao servidor quando estiver obtendo uma conexão, mas você deve estar ciente de que o estado da conexão, como o contexto do banco de dados, não está sendo atualizado.
Enlist	True	Indica que o gerenciador registrará a conexão no contexto da transação corrente na criação da thread, caso exista.
Max Pool Size	100	Quantidade máxima de conexões permitidas no pool.
Min Pool Size	0	Quantidade mínima de conexões mantidas no pool. Em alguns casos é uma boa prática configurar esta propriedade para um valor pequeno, 3 a 5 por exemplo.
Pooling	True	Configura se o pool de conexão deve ou não ser criado para uma determinada string de conexão.

Como a conexão é incluida no pool?

No momento da criação do pool, múltiplos objetos de conexão são criados e adicionados ao pool até que o valor do parâmetro “Min Pool Size” seja satisfeito, e adicionados ao pool até o limite do parâmetro “Max Pool Size”, cujo valor padrão é de 100 conexões. Por exemplo, quando um objeto do tipo SqlConnection é solicitado, ele será recuperado do pool se uma conexão reutilizável estiver disponível para mesma string de conexão que foi solicitada conexão. Para ser reutilizável, a conexão não deve estar em uso, deve ser de um mesmo contexto transacional (ou não está associada a algum contexto), e ter um link válido para o servidor. Se o número máximo de conexões do pool for alcançado e nenhuma conexão reutilizável estiver disponível, a solicitação de conexão aguardará numa fila até que uma esteja livre. O gerenciador atende estas solicitações através da reutilização das conexões que forem devolvidas ao pool. Uma conexão é devolvida ao pool quando o método Close() ou Dispose() é executado pelo objeto de conexão. Portanto, nunca esqueça de fechar uma conexão, caso isso aconteça, a conexãonão será devolvida ao pool e sua aplicação sofrerá do que chamamos de conexões órfãs.

Se o parâmetro “Min Pool Size” não for especificado na string de conexão ou for atribuido valor zero, as conexões do pool serão fechadas após um certo período de inatividade. Caso seja especificado um valor diferente de zero, o pool não será destruído até que a aplicação seja encerrada. Manter um pool inativo ou vazio não afeta o desempenho da aplicação.

Como as Conexões são removidas do pool?

O gerenciador do pool temporariamente faz uma pesquisa a procura de conexões que não estão sendo usadas e que não foram fechadas usando o método Close() ou Dispose(), recupera-as caso exista. Caso uma aplicação não libere as conexões através de um destes métodos, pode levar um certo tempo até que o gerenciador as recupere.

As conexões serão removidas do pool após um certo período de inatividade, ou se a conexão com o servidor de banco de dados for interrompida. A interrupção da conexão só serão identificadas após uma tentativa de se comunicar com o servidor e, quando detectado que não há comunicação, a mesma será marcada como inválida. Conexões inválidas são removidas do pool quando são fechadas ou quando são recuperadas pelo gerenciador.

Pode acontecer de uma conexão permanecer ativa para um servidor de banco de dados que esteja fora do ar, e o gerenciador ainda não a marcou como inválida. Esta verificação só será realizada na próxima vez que uma conexão for solicitada, pois o custo é muito alto para fazer esta verificação com frequência, o que eliminaria todo o benefício do pool. Na próxima tentativa de usar a conexão o gerenciador identificará que a mesma não é mais válida e ocorrerá uma exceção.

A Conexão: Abra-a o mais tardar, feche-a o quanto antes

Existe uma regrinha básica que você deve seguir quando estiver utilizando um objeto de conexão no seu código: abra a conexão o mais tardar possível, e feche-a o quanto antes. Uma conexão deve ser aberta somente no momento que for necessário, por exemplo, uma linha antes da linha que executa o comando SQL. Caso a abra antes, com certeza, você estará bloqueando uma conexão que deveria estar livre no pool. Seguindo esta regra utilizará o pool da melhor forma possível e evitará possíveis problemas de desempenho.

Como já foi comentado, caso o número de conexões do pool exceda o limite (parâmetro “Max Pool Size”), a solicitação de conexão aguardará até que uma conexão seja liberada. E, dependendo do tempo, sua aplicação poderá experimentar um erro devido ao timeout. Atenção especial também deve ser dada ao fechamento da conexão pois deverá acontecer o quanto antes. Nunca esquecer de chamar o método Close() ou Dispose() assim que utilizar a conexão. A listagem 1 ilustra um trecho de código que será executado ao clique de um botão, por exemplo. Simplesmente conecta com banco de dados e configura o Pool de Conexões para ter, no máximo, três conexões. Perceba que a conexão não é fechada porque o código está comentado de propósito.

Listagem 1: Exemplo de conexão onde o método Close() não é chamado após o uso.

private void button1_Click(object sender, EventArgs e){

SqlConnection connection = null;

string strConn =

"Data Source=TECH-RENATOG\\SQL2000;Initial Catalog=framework;"

+ "Integrated Security=SSPI;Max Pool Size=3";

try{

connection = new SqlConnection(strConn);

connection.Open();

//Executa os comandos SQL

}finally{

//connection.Close();

}

Quando o código da listagem 1 for executado pela primeira vez, o pool de conexão será criado para esta string de conexão. Como a segurança integrada foi configurada, o pool será fragmentado por usuário, como só o meu usuário acessa a máquina onde estou executando os testes, não terei problemas com a fragmentação do pool. Perceba que a conexão não é fechada após o uso, o que aparenta ainda em uso para o gerenciador do pool, e será criada uma nova conexão quando o código for executado pela segunda vez. Não haverá reaproveitamento da conexão criada na primeira vez em que o código foi executado. Da mesma forma para terceira vez que o código for executado. Como o limite do pool será atingido, a aplicação obterá o erro abaixo. Esta mesma exceção também pode ocorrer quando o servidor ou o banco de dados estão fora do ar.

System.InvalidOperationException= "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."

Considerando que o código da listagem 1 foi executado três vezes, a figura 1 ilustra as conexões com o banco de dados para este pool. Veja que os três últimos registros representam as conexões do pool da listagem 1. Os registros da figura 1 foram obtidos através da execução da Stored Procedure sp_who2 (ou sp_who). Para facilitar a localização da conexão da sua aplicação, configure o parâmetro “Application Name” na string de conexão. Não esquecendo, assim que a aplicação for concluída, o pool e as conexões serão limpados.

Figura 1: Conexões do pool da listagem 1.

Para evitar problemas com o fechamento de uma conexão por esquecimento, uma boa prática é a utilização da palavra-chave “using”. Assim que o escopo do “using” for concluído, o método Dispose() será executado automaticamente, fazendo com que a conexão seja fechada, ver listagem 2. Desta forma, como a conexão sempre será fechada, veja que o pool não alcançará o limite de conexões, mesmo clicando o código da listagem 1 por várias vezes. Ver figura 2 para resultado do pool de conexões, onde só existe uma conexão para o banco de dados “framework”. O foco da listagem 2 é mostrar o uso da palavra “using”, por isso não atenta aos detalhes de tratamento de exceção, segurança de código etc.

Listagem 2: Exemplo de abertura de conexão com a palavra-chave “using”.

private void button1_Click(object sender, EventArgs e){

string strConn =

"Data Source=TECH-RENATOG\\SQL2000;Initial Catalog=framework;"

+ "Integrated Security=SSPI;Max Pool Size=3";

//A conexão será fechada ao fim do escopo do "using"

using (SqlConnection connection = new SqlConnection(strConn)) {

//A conexeão deverá ser aberta somente quando necessário

connection.Open();

using (SqlCommand cmd = new SqlCommand()) {

cmd.CommandText = "Select GETDATE()";

cmd.ExecuteScalar();

}

Figura 2: Resultado do pool de conexão após execução do código da listagem 1 por várias vezes.

ADO.NET 2.0: Novidades para Limpeza do Pool de Conexões

Diferentemente das versões 1.0 e 1.1, as classes SqlConnection e OracleConnection do ADO.NET 2.0 introduziram novos métodos para suportar a limpeza do pool: ClearAllPools() e ClearPool(SqlConnection connection). O método ClearAllPools, como próprio nome já diz, limpa todos os pools de conexão, já o ClearPool limpa o pool de conexão associado a um objeto de conexão. Para os dois métodos, se alguma conexão estiver em uso no momento da execução do método, a conexão será marcada e não retornará ao pool quando o método Close() ou Dispose() for executado.

No exemplo da listagem 2, no momento da execução do método Open() será criado o pool de conexão com três conexões, devido ao parâmetro “Min Pool Size”. Assim que o método ClearAllPools() é executado todos os pools são limpados bem como as conexões com banco de dados são removidas.

Pool de Conexão e as Transações

Como já foi comentado, as conexões também são colocadas no pool com base num contexto transacional. Se o parâmetro “Enlist=true” for especificado na string de conexão, o pool de conexão assegurará que a conexão será registrada no contexto transacional corrente, que é representado pela propriedade System.Transactions.Transaction.Current. Quando uma conexão registrada em uma transação é fechada e devolvida ao pool, esta mesma conexão será retornada para próxima solicitação de conexão na mesma transação. Se não existir uma conexão disponível para a transação, a conexão será automaticamente colocada no pool. Assim que a conexão for fechada, ela é devolvida ao pool e dentro de uma divisão apropriada baseada no contexto transacional.

Conclusão

Se sua aplicação faz acesso a banco de dados, é importante que as conexões seja utilizadas da melhor forma possível, com isso garantido um bom desempenho e escalabilidade para sua palicação. Para isso, o ADO .NET dispõe do Pool de Conexões, que é um repositório que mantém uma lista de objetos de conexão que são reutlizáveis. Quando o Data Provider recebe a solicitação de uma conexão para uma determinada string de conexão, é realizada uma pesquisa no pool de conexões para verificar se já existe algum pool para aquela conexão e, caso exista, a conexão do pool é retornada. Caso contrário, um pool é criado, a conexão é aberta e, em seguida, adicionada ao pool.

Sempre siga a regra de abrir as conexões o mais tardar e fechar o quanto antes. Desta forma evitará que sua aplicação experimente erro de conexão devido as conexões órfãs. Evite usar o pool de conexão com segurança integrada visto que provoca a fragmentação do pool e as conexões não serão reaproveitas entre os usuários.

O pool de conexões é uma funcionalidade muito poderosa que poderá melhorar a performance da sua palicação. Então, faz-se necessário seu monitoramento para certificar que os parâmetros configurados estão de acordo com as necessidades da sua aplicação, do contrário, esta funcionalidade se tornará um “gargalo” ao invés de um benefício.

O monitoramento do pool de conexões do pode ser realizado através das procedures sp_who ou sp_who2, o SQL Server Profiler ou com Monitor de Performance e os contadores de performance (Performance Counter). A categoria “.NET CLR Data” dispõe de seis contadores para monitoramento do poo de conexão.

Este artigo explicou o que é um pool de conexões, seu funcionamento e benefícios para as aplicações que acessam banco de dados. Também, comentou os detalhes para garantir que sua aplicação ganhe em performance e escalabilidade.

Desenvolvimento - ASP. NET

Leia também