¿Procedimientos almacenados nesteds que contienen el patrón TRY CATCH ROLLBACK?

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)?

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 scope 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

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 )

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) 

– 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 

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 “nesteds” 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.