Hola, chicos,
¿Cómo estás?

En este post voy a hablar de una configuración a nivel de sesión que mucha gente no conoce y que, en muchos casos, puede ser de gran ayuda para evitar la pérdida accidental de datos, que es el Autocommit.

En algunos otros DBMS (Oracle, Postgree, etc.) esto no está activado por defecto, lo que termina causando cierta extrañeza a los DEV y DBA que vinieron de estas tecnologías. Siempre que hablamos de cambios de datos en Producción, debemos tener presente el uso de transacciones. Esto es especialmente útil y seguro porque, en caso de error en el proceso de actualización de datos, podemos deshacer todos los cambios realizados con un simple comando ROLLBACK. Si no estás utilizando una transacción y has realizado varios INSERT'S, UPDATE's y DELETE's, tendrás mucho trabajo para deshacer estos cambios, sin mencionar el tiempo en el que la base de datos tendrá información incorrecta.

Según el documentación oficial de SQL Server, el modo de manejo de transacciones predeterminado de SQL Server es Autocommit, es decir, cuando ejecuta ACTUALIZAR, INSERTAR o ELIMINAR sin iniciar una transacción explícita, estas operaciones se confirman automáticamente.

Cuando inicia una transacción, SQL Server activa el modo de transacción implícita, lo que hace que todas las operaciones DML se encapsule en esa transacción. Una vez que ejecuta COMMIT o ROLLBACK, el motor SQL deshabilita el modo de transacción implícita y regresa al modo de confirmación automática.

También me gustaría señalar que mi objetivo en esta publicación no es que deshabilites la confirmación automática solo porque lo lees aquí. Debe comprender qué es el compromiso automático y si realmente tiene sentido mantenerlo activado o no. Hay muchos casos en los que el DBA puede preferir continuar con este comportamiento (que es estándar) para SQL Server, mientras que en otros casos, particularmente si viene de Oracle o Postgree, es posible que desee deshabilitar la confirmación automática. Esto depende de tu perfil y tu forma de trabajar.

Transacciones y errores de compilación y ejecución

Muchas veces cuando estamos ejecutando varios comandos por lotes nos encontramos con un mensaje de error en medio de los scripts y no sabemos qué se ejecutó y qué no, porque en algunos casos no se ejecuta nada y en otros se ejecuta todo antes de la línea con el error. Demostraré por qué ocurre esto.

Errores de compilación

Cada vez que ejecutas un comando en SQL Server, el motor SQL realiza una prevalidación de los comandos que ejecutas, realizando algunas validaciones sobre el comando para evitar que genere procesamiento innecesario y al final de los comandos genera un error que se pudo haber evitado. Una de estas validaciones es la sintaxis de los comandos.

Cuando hay un error de sintaxis en uno de los comandos, la ejecución se detiene antes de ejecutar cualquier instrucción SQL. Es decir, cuando hay errores de sintaxis no se ejecuta nada.

USE [Teste];
GO

IF (OBJECT_ID('TestBatch') IS NOT NULL) DROP TABLE TestBatch
CREATE TABLE TestBatch (
    ColA INT PRIMARY KEY, 
    ColB CHAR(3)
);
GO

INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUSE (3, 'ccc'); -- Erro de sintaxe.
GO

SELECT * FROM TestBatch; -- Não retorna linhas.
GO

Mensaje 102, Nivel 15, Estado 1, Línea 13
Sintaxis incorrecta cerca de 'VALUSE'.

Errores de ejecución

A diferencia de los errores de compilación, que se validan previamente antes de ejecutar los comandos en la base de datos, los errores de ejecución no se validan antes de iniciar el procesamiento, ya que requerirían mucho procesamiento para poder validar esta información. Imagine que SQL Server tiene que validar si hay una clave duplicada en un lote de 10.000 INSERT antes de procesar el comando. Sería casi el mismo procesamiento que la propia operación INSERT.

En este caso, cuando hay un error de ejecución, los comandos se ejecutan y confirman hasta la línea que genera el error.

USE [Teste];
GO

IF (OBJECT_ID('TestBatch') IS NOT NULL) DROP TABLE TestBatch
CREATE TABLE TestBatch (
    ColA INT PRIMARY KEY, 
    ColB CHAR(3)
);
GO

INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUES (1, 'ccc'); -- Erro de chave duplicada.
GO

SELECT * FROM TestBatch; -- Retorna as linhas 1 e 2.
GO

Mensaje 2627, Nivel 14, Estado 1, Línea 13
Violación de la restricción PRIMARY KEY 'PK__TestBatc__A259EE44E0B6221D'. No se puede insertar una clave duplicada en el objeto 'dbo.TestBatch'. El valor de la clave duplicada es (1).
La declaración ha sido cancelada.

Otro ejemplo que genera un error de ejecución es cuando el objeto no existe. SQL Server no puede validar un lote de 10.000 INSERTs si cada objeto existe en la base de datos o no, ya que consumiría mucho procesamiento, ya que durante la ejecución esta verificación ya está hecha.

USE [Teste];
GO

IF (OBJECT_ID('TestBatch') IS NOT NULL) DROP TABLE TestBatch
CREATE TABLE TestBatch (
    ColA INT PRIMARY KEY, 
    ColB CHAR(3)
);
GO

INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch2 VALUES (3, 'ccc'); -- Erro de objeto inexistente.
GO

SELECT * FROM TestBatch; -- Retorna as linhas 1 e 2.
GO

Mensaje 208, Nivel 16, Estado 1, Línea 13
Nombre de objeto no válido "TestBatch2".

Deshabilitar la confirmación automática en SQL Server

Ahora que comprende cómo funciona Autocommit y sus reglas de validación, demostraré cómo deshabilitar Autocommit en SQL Server, lo que hace necesario ejecutar COMMIT o ROLLBACK al final de todo el lote de comandos DML en SQL Server para que la información se confirme en la base de datos.

Cómo deshabilitar la confirmación automática a nivel de sesión

Para deshabilitar la confirmación automática a nivel de sesión y hacer que sea necesario ejecutar COMMIT o ROLLBACK al final de sus comandos para que realmente se apliquen a la base de datos, simplemente ejecute el siguiente comando:

SET IMPLICIT_TRANSACTIONS ON
GO

Recuerde que esto sólo aplica para la sesión actual. Si abre una nueva ventana de consulta, esta opción no está habilitada y deberá ejecutar este comando cada vez que abra una nueva ventana.

Cómo deshabilitar la confirmación automática en SQL Server Management Studio (SSMS)

Para deshabilitar el Autocommit en SQL Server Management Studio (SSMS) automáticamente, es decir, cada vez que abres una nueva consulta se activa el modo IMPLICIT_TRANSATION, simplemente sigue los pasos a continuación:

Abra el menú "Herramientas" en SQL Server Management Studio y seleccione la opción "Opciones...".

En la pantalla de opciones, navegue hasta la categoría "Ejecución de consultas" > "SQL Server" > "ANSI" y marque la casilla de verificación "IMPLICIT_TRANSACTIONS".

Después de marcar esta opción y hacer clic en “Aceptar”, cada vez que abra una nueva ventana de consulta (Nueva ventana de consulta), la opción IMPLICIT_TRANSACTIONS se activará de forma predeterminada (puede deshabilitarla a nivel de sesión, usando SET IMPLICIT_TRANSACTIONS OFF).

Vale recordar que este cambio sólo se aplica a las nuevas ventanas que se abrirán. Las ventanas que ya están abiertas no se ven afectadas.

¡Eso es todo, amigos!
Espero que hayas disfrutado de esta publicación y nos vemos la próxima.