SQL Server: vazamentos de nível de isolamento em conexões em pool

Como demonstrado por perguntas anteriores do Stack Overflow ( TransactionScope e pool de conexões e como o SqlConnection gerencia IsolationLevel? ), O nível de isolamento da transação vaza em conexões agrupadas com o SQL Server eo ADO.NET (também System.Transactions e EF, porque eles são criados ADO.NET).

Isso significa que a seguinte seqüência perigosa de events pode acontecer em qualquer aplicativo:

  1. Ocorre uma solicitação que exige uma transação explícita para garantir a consistência dos dados
  2. Qualquer outro pedido vem em que não usa uma transação explícita porque está fazendo apenas leituras não críticas. Esta requisição será executada como serializável, potencialmente causando bloqueio perigoso e deadlocks

A pergunta: qual é a melhor maneira de evitar esse cenário? É realmente necessário usar transactions explícitas em todos os lugares agora?

Aqui está uma reprodução independente. Você verá que a terceira consulta herdará o nível Serializável da segunda consulta.

class Program { static void Main(string[] args) { RunTest(null); RunTest(IsolationLevel.Serializable); RunTest(null); Console.ReadKey(); } static void RunTest(IsolationLevel? isolationLevel) { using (var tran = isolationLevel == null ? null : new TransactionScope(0, new TransactionOptions() { IsolationLevel = isolationLevel.Value })) using (var conn = new SqlConnection("Data Source=(local); Integrated Security=true; Initial Catalog=master;")) { conn.Open(); var cmd = new SqlCommand(@" select case transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncommitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'RepeatableRead' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' end as lvl, @@SPID from sys.dm_exec_sessions where session_id = @@SPID", conn); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { Console.WriteLine("Isolation Level = " + reader.GetValue(0) + ", SPID = " + reader.GetValue(1)); } } if (tran != null) tran.Complete(); } } } 

Saída:

 Isolation Level = ReadCommitted, SPID = 51 Isolation Level = Serializable, SPID = 51 Isolation Level = Serializable, SPID = 51 //leaked! 

No SQL Server 2014, isso parece ter sido corrigido. Se estiver usando o protocolo TDS 7.3 ou superior.

Executando no SQL Server versão 12.0.2000.8 a saída é:

 ReadCommitted Serializable ReadCommitted 

Infelizmente esta mudança não é mencionada em nenhuma documentação como:

  • Alterações de comportamento nos resources do mecanismo de database no SQL Server 2014
  • Quebrando Alterações nos Recursos do Mecanismo de Banco de Dados no SQL Server 2014

Mas a mudança foi documentada em um fórum da Microsoft.

Atualização 2017-03-08

Infelizmente isso foi mais tarde “não corrigido” no CU6 do SQL Server 2014 e no CU1 do SQL Server 2014 SP1 desde que ele introduziu um bug:

CORRECÇÃO: O nível de isolamento de transação é redefinido incorretamente quando a conexão do SQL Server é lançada no SQL Server 2014

“Suponha que você use a class TransactionScope no código-fonte do lado do cliente do SQL Server e não abra explicitamente a conexão do SQL Server em uma transação. Quando a conexão do SQL Server é liberada, o nível de isolamento da transação é redefinido incorretamente.”

O pool de conexão chama sp_resetconnection antes de reciclar uma conexão. A redefinição do nível de isolamento da transação não está na lista de coisas que a sp_resetconnection faz. Isso explicaria por que “serializável” vaza em conexões agrupadas.

Eu acho que você poderia iniciar cada consulta certificando-se de que está no nível de isolamento correto :

 if not exists ( select * from sys.dm_exec_sessions where session_id = @@SPID and transaction_isolation_level = 2 ) set transaction isolation level read committed 

Outra opção: conexões com uma string de conexão diferente não compartilham um pool de conexão. Portanto, se você usar outra string de conexão para as consultas “serializáveis”, elas não compartilharão um pool com as consultas “read committed”. Uma maneira fácil de alterar a seqüência de conexão é usar um login diferente. Você também pode adicionar uma opção aleatória, como Persist Security Info=False; .

Por fim, você pode garantir que todas as consultas “serializáveis” reconfigurem o nível de isolamento antes de retornar. Se uma consulta “serializável” não for concluída, você poderá limpar o conjunto de conexões para forçar a conexão corrompida a sair do pool:

 SqlConnection.ClearPool(yourSqlConnection); 

Isso é potencialmente caro, mas as consultas com falha são raras, portanto, você não deve chamar o ClearPool() freqüência.

Acabei de fazer uma pergunta sobre esse tópico e adicionei um código C #, que pode ajudar a resolver esse problema (ou seja, alterar o nível de isolamento apenas para uma transação).

Alterar o nível de isolamento apenas em transactions individuais do ADO.NET

É basicamente uma class para ser envolvida em um bloco ‘using’, que consulta o nível de isolamento original antes e o restaura mais tarde.

No entanto, ele exige duas idas e voltas adicionais ao BD para verificar e restaurar o nível de isolamento padrão, e não estou absolutamente certo de que ele nunca vazará o nível de isolamento alterado, embora eu veja muito pouco risco disso.