sql server 2005 - transaction - ¿Procedimientos almacenados anidados que contienen el patrón TRY CATCH ROLLBACK?

transacciones sql server ejemplos

Question

Me interesan los efectos secundarios y los problemas potenciales del siguiente patrón:

CREATE PROCEDURE [Name]
AS
BEGIN
    BEGIN TRANSACTION
    BEGIN TRY
        [...Perform work, call nested procedures...]
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
    END CATCH
END

Según mi entender, este patrón es sólido cuando se usa con un solo procedimiento: el procedimiento completará todas sus declaraciones sin error, o revertirá todas las acciones y reportará el error.

Sin embargo, cuando un procedimiento almacenado llama a otro procedimiento almacenado para hacer alguna subunidad de trabajo (en el entendimiento de que el procedimiento más pequeño a veces se llama por sí solo) veo surgir un problema relacionado con las reversiones: un mensaje informativo (Nivel 16) se emite indicando que la The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. . Supongo que esto se debe a que la retrotracción en el subprocedimiento siempre retrotrae la transacción más externa, no solo la transacción iniciada en el subprocedimiento.

Quiero que todo se retrotraiga y se anule si ocurre algún error (y el error se informa al cliente como un error de SQL), pero no estoy seguro de todos los efectos secundarios que provienen de las capas externas que intentan deshacer una transacción. eso ya ha sido retrocedido. ¿Quizás un cheque de @@TRANCOUNT antes de realizar una reversión en cada capa de TRY CATCH?

Finalmente está el cliente final (Linq2SQL), que tiene su propia capa de transacción:

try
{
    var context = new MyDataContext();
    using (var transaction = new TransactionScope())
    {       
            // Some Linq stuff
        context.SubmitChanges();
        context.MyStoredProcedure();
        transactionComplete();
    }
}
catch
{
    // An error occured!
}

En caso de que un procedimiento almacenado, "MySubProcedure", llamado dentro de MyStoredProcedure provoque un error, ¿puedo estar seguro de que todo lo que se hizo previamente en MyStoredProcedure se retrotraerá, todas las operaciones de Linq realizadas por SubmitChanges se revertirán, y finalmente que el error será registrado? ¿O qué necesito cambiar en mi patrón para asegurar que toda la operación sea atómica, mientras sigo permitiendo que las partes secundarias se usen individualmente (es decir, los subprocedimientos aún deben tener la misma protección atómica)?

Answer 1

- El método @Amanda anterior no devuelve el número de error correcto

DECLARE  
  @ErrorMessage   nvarchar(4000),  
  @ErrorSeverity   int,  
  @ErrorState int,  
  @ErrorLine  int,  
  @ErrorNumber   int  

BEGIN TRY  
 SELECT 1/0; -- CATCH me  
END TRY  

BEGIN CATCH  

  DECLARE @err int = @@ERROR  

  PRINT @err           -- 8134, divide by zero  
  PRINT ERROR_NUMBER() -- 8134  

  SELECT  
    @ErrorMessage  = ERROR_MESSAGE(),  
    @ErrorSeverity = ERROR_SEVERITY(),  
    @ErrorState    = ERROR_STATE(),  
    @ErrorNumber   = ERROR_NUMBER(),  
    @ErrorLine     = ERROR_LINE()  

  -- error number = 50000 :(  
  RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorLine)  

END CATCH  

-- error number = 8134  
SELECT 1/0

Answer 2

En caso de que no se requiera un manejo especial de errores en CATCH, excepto la cadena de llamadas de reconstrucción de procuración y procesos almacenados, no es demasiado largo, puede ser conveniente utilizar una plantilla tan simple:

create procedure someNestedSP
as
SET XACT_ABORT ON
begin transaction
-- do some work or call some other similar SP
commit transaction

También revertiría la transacción raíz con todos los "anidados" en caso de cualquier error, pero el código es más corto y más directo que la solución de @ gbn. Aún XACT_ABORT se ocupa de la mayoría de los problemas mencionados allí.

Puede haber una sobrecarga adicional para la anidación de transacciones, pero puede que no sea demasiado alta, supongo.

Answer 3

Esta es nuestra plantilla (se eliminó el registro de errores)

Esto está diseñado para manejar

• El artículo de Paul Randal "No hay tal cosa como una transacción anidada en SQL Server"
• Error 266
• Desencadenantes del disparador

Explicaciones

• todos los inicios de TXN y los commit / rollbacks se deben emparejar para que @@TRANCOUNT sea ​​el mismo en la entrada y salida

• los desajustes de @@TRANCOUNT causan el error 266 porque

  • BEGIN TRAN incrementa @@TRANCOUNT

  • COMMIT decrements @@TRANCOUNT

  • ROLLBACK devuelve @@TRANCOUNT a cero

• No puede disminuir @@TRANCOUNT para el alcance actual
  Esto es lo que crees que es la "transacción interna"

• SET XACT_ABORT ON suprime el error 266 causado por un error de coincidencia @@TRANCOUNT
  Y también se ocupa de problemas como este "Tiempo de espera de transacción de SQL Server" en dba.se

• Esto permite TXN del lado del cliente (como LINQ) Un único procedimiento almacenado puede ser parte de una transacción distribuida o XA, o simplemente una iniciada en el código del cliente (por ejemplo, .net TransactionScope)

Uso:

• Cada proceso almacenado debe ajustarse a la misma plantilla

Resumen

• Así que no crees más TXN de lo que necesitas

El código

CREATE PROCEDURE [Name]
AS
SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0
        BEGIN TRANSACTION

       [...Perform work, call nested procedures...]

    IF @starttrancount = 0 
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION;
    THROW;
    --before SQL Server 2012 use 
    --RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
GO

Notas:

• La verificación de reversión es realmente redundante debido a SET XACT_ABORT ON . Sin embargo, me hace sentir mejor, se ve raro sin, y permite situaciones en las que no lo desea

• Remus Rusanu tiene un caparazón similar que usa puntos de guardado. Prefiero una llamada DB atómica y no uso actualizaciones parciales como su artículo

Answer 4

No soy un tipo Linq (y tampoco lo es Erland), pero escribió las biblias absolutas sobre el manejo de errores. Fuera de las complicaciones que Linq podría agregar a su problema, todas sus otras preguntas deberían ser respondidas aquí:

http://www.sommarskog.se/error_handling/Part1.html

(Enlace anterior: http://www.sommarskog.se/error_handling_2005.html )

Answer 5

Para resolver el problema de devolver el número de error y el número de línea mencionados por @AlexKuznetsov, uno puede plantear el error como tal:

DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
DECLARE @ErrorLine INT
DECLARE @ErrorNumber INT

SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorLine = ERROR_LINE()

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorLine)