Nível de isolamento e sp_reset_connection

Ajudando um cliente encontrei um comportamento do ADO.NET que eu desconhecia e, sinceramente, acredito que todos que trabalham com ADO.NET deveriam saber.

Cenário: uma mudança no EF 6.1 fez com que este cliente mudasse a aplicação e chamasse explicitamente a instrução SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ao instanciar o DBContext.
O problema surgiu quando a aplicação começa a abrir/utilizar/fechar as conexões, e o nível de isolamento READ UNCOMITTED é mantido.

Minha reação inicial: o ADO.NET não está chamando o sp_reset_connection ao reutilizar a conexão do pool, então pode ser um problema na configuração do pool de conexões.

Ao pesquisar sobre a configuração do pool de conexões me deparei com o comportamento que não conhecia: a sp_reset_connection não redefine o nível de isolamento.

Referências:

• http://blogs.msdn.com/b/jimmymay/archive/2009/02/02/sp-reset-connection-does-not-reset-transaction-isolation-level-unexpected-behavior-by-design.aspx 
• https://connect.microsoft.com/SQLServer/feedback/details/243527/sp-reset-connection-doesnt-reset-isolation-level 

O item do Connect foi fechado como by design.

Então suponha o seguinte, você uma aplicação que funciona perfeitamente bem. Um novo módulo é desenvolvido e para uma interface específica você define o nível de isolamento READ UNCOMMITTED ou SERIALIZABLE, quando essa conexão é devolvida para o pool, uma outra classe da sua aplicação pode pegar essa conexão e trabalhar com um nível de isolamento inadequado, causando efeitos colaterais inesperados na aplicação. Nada bom.

Se você é desenvolvedor .NET e já trabalha com o ADO.NET provavelmente já sabe disso, mas achei importante registrar aqui para referência futura.

The devil is in the details…

Quer fazer o teste você mesmo?
Utilize os scripts abaixo para:

1. Criar o banco de dados e deixar uma transação aberta
2. Executar uma aplicação console em C# com pool de conexão habilitado
a. A segunda execução não vai ficar bloqueada (comportamento READ UNCOMMITTED)
3. Executar uma aplicação console em C# com pool de conexão desabilitado
a. A segunda execução vai ficar bloqueada (comportamento READ COMMITTED)

Você pode brincar com o profiler e DMVs para monitorar o comportamento das sessões.

1 - Scripts para testar comportamento (SSMS e VS)

USE master
go

CREATE DATABASE IsolationLevel
go

USE IsolationLevel
go

IF (OBJECT_ID('dbo.TabelaBase') IS NOT NULL)
DROP TABLE dbo.TabelaBase
go

CREATE TABLE dbo.TabelaBase
(Codigo INT IDENTITY NOT NULL PRIMARY KEY,
 Nome VARCHAR(100) NOT NULL,
 DataHora DATETIME2 NOT NULL DEFAULT SYSDATETIME())
GO

INSERT INTO DBO.TabelaBase (Nome) VALUES ('Sr. Nimbus')
INSERT INTO DBO.TabelaBase (Nome) VALUES ('SQL Server')
INSERT INTO DBO.TabelaBase (Nome) VALUES ('ADO.NET')
GO

-- Monitorar as sessões
-- 1 = read uncommitted
-- 2 = read committed
SELECT session_id, transaction_isolation_level, * 
FROM sys.dm_exec_sessions 
WHERE program_name = '.Net SqlClient Data Provider'


BEGIN TRANSACTION
UPDATE dbo.TabelaBase SET Nome = 'Luti @ Sr. Nimbus' WHERE Codigo = 1
SELECT @@TRANCOUNT





static void Main(string[] args)
{

    SqlConnection con = new SqlConnection("Server=LUTIXPS;Database=IsolationLevel;UID=sa;PWD=.sql2014.;");
    // SqlConnection con = new SqlConnection("Server=LUTIXPS;Database=IsolationLevel;UID=sa;PWD=.sql2014.;Pooling=false;");            
    SqlCommand com = new SqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED", con);
    con.Open();
    com.ExecuteNonQuery();

    com.CommandText = "SELECT * FROM dbo.TabelaBase";
    SqlDataReader dr = com.ExecuteReader();
    while(dr.Read())
    {
        Console.WriteLine(dr["Nome"].ToString());
    }                        
    con.Close();

    // novamente...
    con.Open();
    dr = com.ExecuteReader();

    while (dr.Read())
    {
        Console.WriteLine(dr["Nome"].ToString());
    }

    con.Close();
}

Abraços

Luciano Caixeta Moreira - {Luti}
luciano.moreira@srnimbus.com.br
www.twitter.com/luticm
www.srnimbus.com.br