transacciones - transaction sql server stored procedure

¿Debería comprometer o deshacer una transacción de lectura?

Question

Tengo una consulta de lectura que ejecuto dentro de una transacción para poder especificar el nivel de aislamiento. Una vez que se completa la consulta, ¿qué debo hacer?

• Confirmar la transacción
• Revertir la transacción
• No hacer nada (lo que hará que la transacción se retrotraiga al final del bloque de uso)

¿Cuáles son las implicaciones de hacer cada uno?

using (IDbConnection connection = ConnectionFactory.CreateConnection())
{
    using (IDbTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted))
    {
        using (IDbCommand command = connection.CreateCommand())
        {
            command.Transaction = transaction;
            command.CommandText = "SELECT * FROM SomeTable";
            using (IDataReader reader = command.ExecuteReader())
            {
                // Read the results
            }
        }

        // To commit, or not to commit?
    }
}

EDITAR: La pregunta no es si se debe usar una transacción o si hay otras formas de establecer el nivel de transacción. La pregunta es si hace alguna diferencia que una transacción que no modifica nada se haya comprometido o revertido. ¿Hay una diferencia de rendimiento? ¿Afecta otras conexiones? Cualquier otra diferencia?

Answer 1

¿Necesita bloquear a otros para que no lean los mismos datos? ¿Por qué usar una transacción?

@Joel - Mi pregunta sería mejor redactada como "¿Por qué usar una transacción en una consulta de lectura?"

@Stefan - Si va a utilizar AdHoc SQL y no un proceso almacenado, simplemente agregue el WITH (NOLOCK) después de las tablas en la consulta. De esta forma, no incurrirá en los gastos generales (aunque mínimos) en la aplicación y la base de datos para una transacción.

SELECT * FROM SomeTable WITH (NOLOCK)

EDIT @ Comentario 3: como tenía "sqlserver" en las etiquetas de pregunta, asumí que MSSQLServer era el producto de destino. Ahora que ese punto se ha aclarado, he editado las etiquetas para eliminar la referencia específica del producto.

Todavía no estoy seguro de por qué quieres hacer una transacción en una operación de lectura en primer lugar.

Answer 2

Considera las transacciones anidadas .

La mayoría de RDBMS no son compatibles con las transacciones anidadas, o tratan de emularlas de una manera muy limitada.

Por ejemplo, en MS SQL Server, una reversión en una transacción interna (¡que no es una transacción real, MS SQL Server solo cuenta los niveles de transacción!) Revertirá todo lo que sucedió en la transacción más externa (que es la transacción real).

Algunas envolturas de bases de datos pueden considerar una reversión en una transacción interna como un signo de que se ha producido un error y deshacer todo en la transacción más externa, independientemente de si la transacción más externa se ha comprometido o revertido.

Entonces, COMMIT es una forma segura, cuando no se puede descartar que algún componente del software sea utilizado por algún módulo de software.

Tenga en cuenta que esta es una respuesta general a la pregunta. El ejemplo de código inteligentemente soluciona el problema con una transacción externa abriendo una nueva conexión a la base de datos.

En cuanto al rendimiento: según el nivel de aislamiento, los SELECT pueden requerir un grado variable de LOCKs y datos temporales (instantáneas). Esto se limpia cuando la transacción se cierra. No importa si esto se hace a través de COMMIT o ROLLBACK. Puede haber una diferencia insignificante en el tiempo de CPU gastado: un COMMIT es probablemente más rápido de analizar que un ROLLBACK (dos caracteres menos) y otras diferencias menores. ¡Obviamente, esto solo es cierto para las operaciones de solo lectura!

Totalmente no solicitado: otro programador que pueda leer el código puede asumir que un ROLLBACK implica una condición de error.

Answer 3

Dado que un READ no cambia de estado, no haría nada. Realizar una confirmación no hará nada, excepto perder un ciclo para enviar la solicitud a la base de datos. No ha realizado una operación que ha cambiado de estado. Del mismo modo para la reversión.

Sin embargo, debe asegurarse de limpiar sus objetos y cerrar sus conexiones a la base de datos. No cerrar las conexiones puede generar problemas si se llama repetidamente a este código.

Answer 4

En mi humilde opinión, puede tener sentido envolver las consultas de solo lectura en transacciones como (especialmente en Java) puede decir que la transacción es de "solo lectura", lo que a su vez permite que el controlador JDBC optimice la consulta (pero no tiene que hacerlo). evitará que emita un INSERT sin embargo). Por ejemplo, el controlador de Oracle evitará por completo los bloqueos de tabla en las consultas en una transacción marcada como de solo lectura, que obtiene un gran rendimiento en las aplicaciones muy leídas.

Answer 5

En tu muestra de código, donde tienes

1. // Haz algo útil

   ¿Está ejecutando una declaración SQL que cambia los datos?

De lo contrario, no existe una transacción de "lectura" ... Solo cambios de una inserción, actualización y eliminación de estados de cuenta (declaraciones que pueden cambiar datos) están en una transacción ... De lo que usted habla es de los bloqueos que SQL El servidor pone los datos que está leyendo debido a OTRAS transacciones que afectan esos datos. El nivel de estos bloqueos depende del nivel de aislamiento del servidor SQL.

Pero no puede confirmar, o revertir nada, si su instrucción SQL no ha cambiado nada.

Si está cambiando datos, puede cambiar el nivel de aislamiento sin iniciar explícitamente una transacción ... Cada instrucción SQL individual está implícitamente en una transacción. iniciar explícitamente una transacción solo es necesario para garantizar que 2 o más declaraciones estén dentro de la misma transacción.

Si todo lo que quiere hacer es establecer el nivel de aislamiento de transacción, simplemente establezca CommandText de un comando en "Establecer nivel de aislamiento de transacción Lectura repetible" (o el nivel que desee), establezca CommandType en CommandType.Text y ejecute el comando. (puede usar Command.ExecuteNonQuery ())

NOTA: Si está haciendo MÚLTIPLES declaraciones de lectura, y quiere que todas ellas "vean" el mismo estado de la base de datos que el primero, entonces necesita establecer el nivel de aislamiento superior, lectura repetible o serializable ...

Answer 6

ROLLBACK se usa principalmente en caso de error o circunstancias excepcionales, y COMMIT en caso de finalización exitosa.

Deberíamos cerrar las transacciones con COMMIT (para tener éxito) y ROLLBACK (para fallas), incluso en el caso de las transacciones de solo lectura donde no parece importar. De hecho, sí importa, por coherencia y protección contra el futuro.

Una transacción de solo lectura puede "fallar" lógicamente de muchas maneras, por ejemplo:

• una consulta no devuelve exactamente una fila como se esperaba
• un procedimiento almacenado genera una excepción
• los datos obtenidos son inconsistentes
• el usuario cancela la transacción porque tarda demasiado
• estancamiento o tiempo de espera

Si COMMIT y ROLLBACK se utilizan correctamente para una transacción de solo lectura, continuará funcionando como se espera si el código de escritura DB se agrega en algún momento, por ejemplo, para almacenamiento en caché, auditoría o estadísticas.

ROLLBACK implícito solo debe usarse para situaciones de "error fatal", cuando la aplicación falla o sale con un error irrecuperable, falla de red, falla de energía, etc.

Answer 7

Si coloca el SQL en un procedimiento almacenado y agrega esto encima de la consulta:

set transaction isolation level read uncommitted

entonces no tienes que saltar a través de ningún círculo en el código C #. Establecer el nivel de aislamiento de transacción en un procedimiento almacenado no hace que la configuración se aplique a todos los usos futuros de esa conexión (que es algo de lo que debe preocuparse con otras configuraciones ya que las conexiones se agrupan). Al final del procedimiento almacenado, simplemente regresa a cualquier conexión con la que se inicializó.

Answer 8

Si comienza una transacción, la mejor práctica es siempre comprometerla. Si se lanza una excepción dentro de su bloque de uso (transacción), la transacción se retrotraerá automáticamente.

Answer 9

Si configura AutoCommit como falso, entonces SÍ.

En un experimento con JDBC (controlador de Postgresql), descubrí que si se selecciona una consulta se rompe (debido al tiempo de espera), entonces no puede iniciar una nueva consulta de selección a menos que revertir.

Answer 10

Si no ha cambiado nada, puede usar COMMIT o ROLLBACK. Cualquiera de los dos liberará los bloqueos de lectura que haya adquirido y, dado que no ha realizado ningún otro cambio, serán equivalentes.

Answer 11

Solo una nota al margen, pero también puedes escribir ese código así:

using (IDbConnection connection = ConnectionFactory.CreateConnection())
using (IDbTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted))
using (IDbCommand command = connection.CreateCommand())
{
    command.Transaction = transaction;
    command.CommandText = "SELECT * FROM SomeTable";
    using (IDataReader reader = command.ExecuteReader())
    {
        // Do something useful
    }
    // To commit, or not to commit?
}

Y si usted reestructura las cosas un poco, es posible que también pueda mover el bloque de uso para IDataReader hasta la parte superior.

Answer 12

Usted se compromete Período. No hay otra alternativa sensata. Si comenzó una transacción, debe cerrarla. El compromiso libera cualquier bloqueo que pueda haber tenido, y es igualmente sensato con los niveles de aislamiento ReadUnCommitted o Serializable. Confiar en la reversión implícita, aunque quizás sea técnicamente equivalente, es solo una forma pobre.

Si eso no te ha convencido, solo imagina al siguiente tipo que inserta una declaración de actualización en el medio de tu código, y tiene que rastrear la reversión implícita que ocurre y elimina sus datos.