c# - new - Use SqlTransaction & IsolationLevel para una larga operación de lectura?

transactionscopeoption c#

Question

Estoy ejecutando varias consultas SQL de larga ejecución como parte de un módulo de informes. Estas consultas se construyen dinámicamente en tiempo de ejecución. Dependiendo de la entrada del usuario, pueden ser de declaración única o múltiple, tener uno o más parámetros y operar en una o más tablas de base de datos; en otras palabras, su formulario no puede anticiparse fácilmente.

Actualmente, solo estoy ejecutando estas declaraciones en una SqlConnection ordinaria, es decir,

using (SqlConnection cn = new SqlConnection(ConnectionString)) {
    cn.Open();
    // command 1
    // command 2
    // ...
    // command N
}

Debido a que estas consultas (realmente consultas por lotes) pueden tardar un poco en ejecutarse, me preocupan los bloqueos en las tablas que contienen lecturas / escrituras para otros usuarios. No es un problema si los datos de estos informes cambian durante la ejecución del lote; las consultas de informes nunca deben tener prioridad sobre otras operaciones en esas tablas, ni deben bloquearlas.

Para la mayoría de las operaciones de larga ejecución / declaraciones múltiples que implican modificar datos, usaría transacciones. La diferencia aquí es que estas consultas de informes no modifican ningún dato. ¿Sería correcto incluir estas consultas de informe en una SqlTransaction para controlar su nivel de aislamiento?

es decir:

using (SqlConnection cn = new SqlConnection(ConnectionString)) {
    cn.Open();

    using (SqlTransaction tr = cn.BeginTransaction(IsolationLevel.ReadUncommitted)) {
        // command 1
        // command 2
        // ...
        // command N

        tr.Commit();
    }
}

¿Esto lograría el resultado deseado? ¿Es correcto realizar una transacción, aunque no se hayan modificado los datos? ¿Hay otro enfoque?

Answer 1

Estoy de acuerdo con Marc, pero alternativamente puede usar la sugerencia de consulta NOLOCK en las tablas afectadas. Esto le daría la capacidad de controlarlo en una tabla por nivel de tabla.

El problema con la ejecución de consultas sin tomar bloqueos compartidos es que usted se abre a resultados "no deterministas" y las decisiones comerciales no deben tomarse con estos datos.

Un mejor enfoque puede ser investigar los niveles de aislamiento SNAPSHOT o READ_COMMITED_SNAPSHOT. Estos le brindan protección contra anomolías transaccionales sin tomar bloqueos. La compensación es que aumentan IO contra TempDB. Cualquiera de estos niveles se puede aplicar a la sesión como Marc sugirió o la tabla como sugerí.

Espero que esto ayude

Answer 2

Otro enfoque podría ser emitir, en contra de la conexión:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

que logra el mismo intento, sin jugar con una transacción. O puede usar la WITH(NOLOCK) en las tablas de su consulta, que tiene la ventaja de no cambiar la conexión.

Es importante destacar que (inusualmente): sin embargo se cambia (transacción, alcance de transacción, SET explícito, etc.), el nivel de aislamiento no se restablece entre los usos de la misma conexión subyacente cuando se busca desde el grupo. Esto significa que si su código cambia el nivel de aislamiento (directa o indirectamente), entonces ninguno de su código sabe cuál es el nivel de aislamiento de una nueva conexión:

using(var conn = new SqlConnection(connectionString)) {
    conn.Open();
    // isolation level here could be **ANYTHING**; it could be the default
    // if it is a brand new connection, or could be whatever the last
    // connection was when it finished
}

Lo que hace que el WITH(NOLOCK) bastante tentador.