Lógica de SQL Server dentro de la declaración EXECUTE ignorando transacciones [cerrado]
Encontré esto en SQL Server 2019 (v15).
Esta es una versión simplificada de un procedimiento almacenado mucho más grande. Pero esto muestra el comportamiento que me desconcierta: dentro del ejecutivo hay una transacción, esta transacción luego se confirma y luego se genera un error.
Esperaría que este cambio persista, pero no es así, parece que todo el contenido de la declaración EXEC se revierte.
¿Alguien puede explicar el comportamiento en este escenario?
Aquí está el ejemplo de código para reproducir esto:
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table (test int);
INSERT INTO my_table VALUES (1);
BEGIN TRY
SELECT 'start'
EXEC(' EXEC( ''
BEGIN TRANSACTION;
SET IMPLICIT_TRANSACTIONS OFF;
INSERT INTO my_table VALUES (2);
COMMIT;
THROW 1;
BEGIN TRANSACTION;
INSERT INTO my_table VALUES (3);
COMMIT;
'' )' )
SELECT 'after EXEC'
THROW
END TRY
BEGIN CATCH
EXEC ('ROLLBACK TRANSACTION;');
SELECT 'In CATCH Block'
END CATCH
SELECT 'After END CATCH'
SELECT * FROM my_table;
El problema no es la transacción, es su T-SQL, contiene un error de sintaxis. Podemos ver esto si exponemos el error dentro de CATCHcon algo como PRINT ERROR_MESSAGE(), que nos muestra el error dentro de TRYes:
Sintaxis incorrecta cerca ';'.
Un error de sintaxis significa que no se ejecuta todo el lote y, si el lote no se ejecuta, no hay ninguna transacción, ya que tampoco se ejecutó nunca .
El error de sintaxis es tuyo THROW 1;; no se puede dar THROWun número de error arbitrario. La sintaxis THROWes cualquiera THROW;o THROW <Error Number>, <Error Message>, <Error State>;; debe pasar todos los parámetros o ninguno. Si no utiliza ningún parámetro, esto se conoce como "relanzar" y solo puede aparecer dentro de un archivo CATCH.
Para que su SQL funcione como desea, debe corregir la sintaxis y pasar 3 parámetros a THROW. Por ejemplo:
DROP TABLE IF EXISTS my_table;
GO
CREATE TABLE my_table (test int);
GO
INSERT INTO my_table
VALUES (1);
GO
BEGIN TRY
SELECT 'start';
EXEC (' EXEC( ''
BEGIN TRANSACTION;
SET IMPLICIT_TRANSACTIONS OFF;
INSERT into my_table values (2);
COMMIT;
THROW 78912, N''''A non-descript error'''',16;
BEGIN TRANSACTION;
INSERT into my_table values (3);
COMMIT; '' )');
SELECT 'after EXEC' AS [throw];
END TRY
BEGIN CATCH
EXEC ('ROLLBACK TRANSACTION;');
SELECT 'In CATCH Block';
END CATCH;
SELECT 'After END CATCH';
GO
SELECT *
FROM my_table;
GO
DROP TABLE dbo.my_table
Esto devuelve los resultados que esperaba:
| prueba |
|---|
| 1 |
| 2 |
También produce el siguiente error:
La solicitud ROLLBACK TRANSACTION no tiene BEGIN TRANSACTION correspondiente.
Esto es lo esperado, ya que THROWestá después COMMITy antes del next BEGIN TRANSACTION, por lo que no hubo ninguna transacción que deshacer. Sin embargo, mover el THROWdespués de BEGIN TRANSACTIONdará como resultado un error diferente.
El recuento de transacciones después de EXECUTE indica que el número de declaraciones BEGIN y COMMIT no coinciden. Recuento anterior = 1, recuento actual = 0.
Cuando se trata de transacciones en lotes diferidos, como este, es necesario manejar las transacciones en el mismo alcance. Saltar al alcance externo y luego crear un nuevo alcance interno para revertir una transacción desde un alcance completamente diferente no funcionará.
