Como gerenciar conexões SQL no .NET Core

Gerenciamento de conexões SQL é um tema que sempre quis abordar, mas acreditava ser desnecessário, pois não havia me deparado com muitos problemas desse tipo.

Porém, recentemente, deparei com um caso bem desafiador, onde uma aplicação extremamente crítica estava caindo, e adivinhe só? A causa raiz era o gerenciamento de conexões SQL.

O objetivo desse artigo é explicar e demonstrar através de provas de conceito o que fazer para evitar esse tipo de problema.

 

SQL Connection Pool no ADO.NET

Um objeto de SqlConnection representa uma conexão física com um banco de dados, onde o método Open é utilizado para abrir a conexão e o método Close é utilizado para fechar a conexão.

Abrir e fechar conexões é uma operação cara, pois envolve algumas etapas, como:

• Estabelecer um canal físico, como um socket ou um pipe nomeado.
• Realizar o handshake inicial com o servidor.
• Analisar as informações da cadeia de conexão (connection string).
• Autenticar a conexão no servidor.
• Realizar verificações para a inclusão na transação atual.
• Executar outras verificações e procedimentos necessários durante a conexão.

Em resumo, é um processo que envolve muitas etapas que podem e devem ser evitadas. A biblioteca ADO.NET, implementa o Connection Polling, onde as conexões são criadas sob demanda, e reutilizadas durante o ciclo de vida da aplicação.

O pool reduz a necessidade de criação de novas conexões, quando a aplicação chamar o método Open, ele irá verificar se já existe uma conexão aberta disponível antes de abrir uma nova. Quando o método Close é chamado, a conexão é devolvida ao pool.

 

Problemas comuns

O problema mais comum que ocorre com o gerenciamento de conexões SQL é o vazamento de conexões. Isso ocorre quando a aplicação não fecha a conexão corretamente. Os impactos no desempenho e escalabilidade da aplicação são significativos, pois o pool de conexões é limitado, e quando uma conexão não é fechada corretamente, ela fica indisponível pois, uma vez que o pool atinga o número máximo de conexões, a aplicação irá esperar até que uma conexão seja liberada.

 

Exemplo de vazamento de conexão

O código a seguir é um exemplo de vazamento de conexão:

  public int ExecuteNonQuery(string command)
  {
    SqlConnection connection = new SqlConnection("connectionString");
    DbCommand dbCommand = Connection.CreateCommand();
    dbCommand.CommandText = command;
    dbCommand.Connection = connection;
    return dbCommand.ExecuteNonQuery();
  }

Vamos executar os seguintes passos para simular o problema e entender qual é o problema dessa implementação:

• Implementar o código acima em um projeto de prova de conceito
• Simular o problema através de um teste de carga
• Coletar e analisar um dump de memória

O código de referência está disponível em: https://github.com/claudiogodoy99/Sql-Demo

Para reproduzir o problema vou utilizar o k6 como ferramenta de deste de carga, e vou utilizar o seguinte script:

import http from "k6/http";

export default function () {

const response = http.get("<http://localhost:5096/exemplo>");
}

 

O comando que utilizei para rodar o teste foi: k6 run -u 100 -d 120s .\loadTest.js. Ele simula 100 usuários acessando a url http://localhost:5096/exemplo durante 120 segundos.

O resultado do teste foi o seguinte:

execution: local

script: loadTest.js

output: -

scenarios: (100.00%) 1 scenario, 100 max VUs, 2m30s max duration (incl. graceful stop):

http_req_duration..........: avg=33.44s min=1.53s med=33.21s max=1m0s p(90)=51.56s p(95)=57.29s

http_req_failed............: 100.00% ✓ 390 ✗ 0

running (2m30.0s), 000/100 VUs, 390 complete and 19 interrupted iterations

 

Em linhas gerais foi um resultado muito ruim, o tempo médio de resposta foi de 33 segundos.

Utilizei o dotnet-dump para gerar e analisar o dump de memória, através dos comandos:

dotnet-dump collect -p PID
dotnet-dump analyze .\NOME-DO-ARQUIVO-GERADO.dmp

 

Com o dump aberto no terminal, vou rodar o comando clrthreads que vai listar todas as pilhas de execuções gerenciadas, enumerando suas respectivas threads:

...
System.Threading.WaitHandle.WaitMultiple
Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection
Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection
Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection
Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal
Microsoft.Data.SqlClient.SqlConnection.TryOpen
Microsoft.Data.SqlClient.SqlConnection.Open
UnityOfWork.OpenConnection
UnityOfWork.BeginTransaction
ExemploRepository.AlgumaOperacao
pocSql.Controllers.ExemploController.Get
....
==\> 48 threads with 7 roots

 

Repare que todas as threads gerenciadas que estavam processando alguma requisição estavam esperando uma resposta do método: Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection, UInt32, Boolean, Boolean, DbConnectionOptions, DbConnectionInternal ByRef).

Isto significa que todas as threadsaguardavam uma conexão ao

banco de dados ser liberada para que pudessem continuar o processamento da requisição.

 

Solução

Neste exemplo a utilização da palavra reservada using já resolveria o problema:

  public int ExecuteNonQuery(string command)
  {
    using SqlConnection connection = new SqlConnection("connectionString");
    DbCommand dbCommand = Connection.CreateCommand();
    dbCommand.CommandText = command;
    dbCommand.Connection = connection;
    return dbCommand.ExecuteNonQuery();
  }

 

A palavra reservada using garante uso correto de objetos que implementam a interface IDisposable, em outras palavras, quando o programa finalizar o escopo do método acima, o método Dispose da conexão será chamado, garantindo que a conexão seja fechada corretamente, mesmo que ocorra uma exceção.

Segue o resultado do teste após a implementação da correção:

script: .\pocSql\loadTest.js

output:

scenarios: (100.00%) 1 scenario, 100 max VUs, 2m30s max duration (incl. graceful stop):

http_req_connecting........: avg=77.15µs min=0s med=0s max=9.22ms p(90)=0s p(95)=0s

http_req_duration..........: avg=1.38s min=286.15ms med=1.14s max=17.94s p(90)=1.99s p(95)=2.6s

http_req_failed............: 100.00% ✓ 8689 ✗ 0

running (2m01.3s), 000/100 VUs, 8689 complete and 0 interrupted iterations

 

A diferença é gritante, o tempo médio de resposta caiu de 33 segundos para 1,38 segundos.

 

Padrão Dispose

Infelizmente nem toda implementação do ADO.NET é tão simples como a que demonstrei nesse artigo. Em diversos casos, deparei-me com classes que implementam o objeto SqlConnection como propriedade para reutilizar a conexão em diversos métodos, controlar transações, entre outras coisas.

Para esses casos, a utilização do using é inviável, e a implementação do padrão Dispose pode ser necessária. Para nossa sorte, as versões recentes do container de injeção de dependência no .NET Core o Microsoft.Extensions.DependencyInjection, já resolve boa parte do problema.

Imagine que temos a seguinte classe:

public class Connection
{
  private readonly SqlConnection _connection;
  public Connection(SqlConnection connection)
  {
    _connection = connection;
  }
}

 

Se a classe acima foi registrada corretamente, o container de injeção de dependência irá chamar o método Dispose da conexão quando a aplicação finalizar o escopo do método que a utilizou.

Para registrar a classe corretamente:

services.AddScoped<IDbConnection>((sp) => new SqlConnection(dbConnectionString));
services.AddScoped<Connection>();

 

Como a conexão foi injetada como uma dependência, a classe Connection não precisa implementar a interface Dispose.

 

Agora um exemplo onde o método construtor é responsável por instânciar o objeto _connection:

public class ExemploRepository
{
  private readonly IDbConnection _connection;
  public ExemploRepository()
  {
    _connection = new SqlConnection("connectionString");
  }
}

 

A classe ExemploRepository precisa implementar a interface IDisposable, e chamar o método Dispose da conexão, caso contrário o container de injeção de dependência não conseguiria identificar que a propriedade _connectio implementa a interface IDisposable.

public class ExemploRepository : IDisposable
{
  private readonly IDbConnection _connection;
  public ExemploRepository()
  {
    _connection = new SqlConnection("connectionString");
  }

  public void Dispose()
  {
    _connection.Dispose();
  }
}

 

Conclusão

Os objetos do tipo SqlConnection são objetos que representam uma conexão física com um banco de dados, e devem ser gerenciados corretamente para evitar problemas de desempenho e escalabilidade. A utilização da palavra reservada using é a forma mais simples de garantir que a conexão seja fechada corretamente, mesmo que ocorra uma exceção. Em casos mais complexos, a implementação do padrão Dispose pode ser necessária.

Embora sutil, o gerenciamento de conexões SQL é um tema que merece atenção, pois pode impactar significativamente o desempenho e escalabilidade de uma aplicação.