tipos - SQL Server: filtraciones de nivel de aislamiento a través de conexiones agrupadas

tipos de transacciones en sql

Question

Tal como lo demostraron las preguntas previas de Desbordamiento de pila ( TransactionScope y Pool de conexión y ¿Cómo gestiona SqlConnection IsolationLevel? ), El nivel de aislamiento de transacción se filtra entre las conexiones agrupadas con SQL Server y ADO.NET (también System.Transactions y EF, porque se construyen encima de ADO.NET).

Esto significa que la siguiente secuencia peligrosa de eventos puede ocurrir en cualquier aplicación:

1. Se produce una solicitud que requiere una transacción explícita para garantizar la coherencia de los datos
2. Cualquier otra solicitud viene en la que no se usa una transacción explícita porque solo está haciendo lecturas no críticas. Esta solicitud ahora se ejecutará como serializable, lo que puede causar bloqueos y bloqueos peligrosos.

La pregunta: ¿Cuál es la mejor manera de prevenir este escenario? ¿De verdad se requiere usar transacciones explícitas en todos lados ahora?

Aquí hay una reproducción autónoma. Verá que la tercera consulta heredará el nivel Serializable de la 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();
        }
    }
}

Salida:

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

Answer 1

Acabo de hacer una pregunta sobre este tema y agregué una parte del código C #, que puede ayudar a resolver este problema (es decir, cambiar el nivel de aislamiento solo para una transacción).

Cambiar el nivel de aislamiento solo en transacciones ADO.NET individuales

Básicamente es una clase que se envuelve en un bloque ''using'', que consulta el nivel de aislamiento original antes y lo restaura más tarde.

Sin embargo, requiere dos viajes adicionales al DB para verificar y restaurar el nivel de aislamiento predeterminado, y no estoy absolutamente seguro de que nunca se filtre el nivel de aislamiento alterado, aunque veo muy poco peligro de eso.

Answer 2

El grupo de conexiones llama a sp_resetconnection antes de reciclar una conexión. Restablecer el nivel de aislamiento de transacción no está en la lista de cosas que sp_resetconnection hace. Eso explicaría por qué las fugas "serializables" atraviesan las conexiones agrupadas.

Supongo que puedes comenzar cada consulta asegurándote de que está en el nivel de aislamiento correcto :

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

Otra opción: las conexiones con una cadena de conexión diferente no comparten un grupo de conexiones. Por lo tanto, si usa otra cadena de conexión para las consultas "serializables", no compartirán un grupo con las consultas de "lectura confirmada". Una manera fácil de alterar la cadena de conexión es usar un inicio de sesión diferente. También podría agregar una opción aleatoria como Persist Security Info=False; .

Finalmente, puede asegurarse de que cada consulta "serializable" restablezca el nivel de aislamiento antes de que regrese. Si una consulta "serializable" no se completa, puede borrar el grupo de conexiones para forzar la conexión contaminada fuera del grupo:

SqlConnection.ClearPool(yourSqlConnection);

Esto es potencialmente costoso, pero las consultas ClearPool() son raras, por lo que no debería tener que llamar a ClearPool() menudo.

Answer 3

En SQL Server 2014 esto parece haber sido reparado. Si usa el protocolo TDS 7.3 o superior.

Ejecutando en SQL Server versión 12.0.2000.8 el resultado es:

ReadCommitted
Serializable
ReadCommitted

Lamentablemente, este cambio no se menciona en ninguna documentación, como por ejemplo:

• Cambios de comportamiento en las características del motor de base de datos en SQL Server 2014
• Rompiendo cambios en las características del motor de base de datos en SQL Server 2014

Pero el cambio ha sido documentado en un foro de Microsoft.

Actualización 2017-03-08

Lamentablemente, esto fue más tarde "no fijado" en SQL Server 2014 CU6 y SQL Server 2014 SP1 CU1, ya que presentó un error:

REVISIÓN: el nivel de aislamiento de la transacción se restablece incorrectamente cuando se lanza la conexión de SQL Server en SQL Server 2014

"Suponga que utiliza la clase TransactionScope en el código fuente del lado del cliente de SQL Server y no abre explícitamente la conexión de SQL Server en una transacción. Cuando se lanza la conexión de SQL Server, el nivel de aislamiento de la transacción se restablece incorrectamente".