Procedure memorizzate nidificate contenenti il ​​modello TRY CATCH ROLLBACK?

Sono interessato agli effetti collaterali e ai potenziali problemi del seguente schema:

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 

Per quanto mi risulta, questo schema è valido se utilizzato con una singola procedura: la procedura completa tutte le sue istruzioni senza errori, o ripristina tutte le azioni e riporta l’errore.

Tuttavia, quando una stored procedure chiama un’altra stored procedure per fare alcune sottounità di lavoro (con la consapevolezza che a volte la procedura più piccola viene chiamata da sola), vedo un problema in relazione ai rollback: un messaggio informativo (livello 16) viene emessa affermando che The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. . Ciò presuppone che il rollback nella sottoprocedura stia sempre ripristinando la transazione più esterna, non solo la transazione avviata nella sottoprocedura.

Voglio che l’intera operazione venga annullata e interrotta se si verifica un errore (e l’errore segnalato al client come errore SQL), non sono sicuro di tutti gli effetti collaterali che provengono dai livelli esterni che tentano di eseguire il rollback di una transazione che è già stato ripristinato. Forse un controllo di @@TRANCOUNT prima di eseguire un rollback su ogni livello di TRY CATCH?

Infine c’è il client end (Linq2SQL), che ha il proprio livello di transazione:

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

Nel caso in cui una procedura memorizzata, “MySubProcedure”, chiamata in MyStoredProcedure sollevi un errore, posso essere sicuro che tutto ciò che è stato fatto precedentemente in MyStoredProcedure verrà eseguito il rollback, tutte le operazioni di Linq eseguite da SubmitChanges verranno ripristinate e infine che il errore verrà registrato? O cosa devo modificare nel mio schema per garantire che l’intera operazione sia atomica, pur consentendo l’utilizzo individuale delle parti secondarie (vale a dire che le sottoprocedure dovrebbero avere ancora la stessa protezione atomica)

Questo è il nostro modello (la registrazione degli errori è stata rimossa)

Questo è progettato per gestire

  • Articolo di Paul Randal “Non esiste una transazione nidificata in SQL Server”
  • Errore 266
  • Trigger Rollbacks

spiegazioni:

  • tutti i TXN iniziano e commit / rollbacks devono essere abbinati in modo che @@TRANCOUNT sia lo stesso in entrata e in uscita

  • mancata corrispondenza di @@TRANCOUNT causa l’errore 266 perché

    • @@TRANCOUNT BEGIN TRAN incrementi @@TRANCOUNT

    • @@TRANCOUNT COMMIT @@TRANCOUNT

    • ROLLBACK restituisce @@TRANCOUNT a zero

  • Non è ansible decrementare @@TRANCOUNT per l’ambito corrente
    Questo è ciò che pensi sia la “transazione interna”

  • SET XACT_ABORT ON sopprime l’errore 266 causato dal non corrispondente @@TRANCOUNT
    E si occupa anche di problemi come questo “Timeout transazioni SQL Server” su dba.se

  • Ciò consente TXN lato client (come LINQ) Una singola stored procedure può far parte della transazione XA o distribuita, o semplicemente una iniziata nel codice client (ad esempio .Net TransactionScope)

Uso:

  • Ogni processo memorizzato deve essere conforms allo stesso modello

Sommario

  • Quindi non creare più TXN del necessario

Il codice

 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 

Gli appunti:

  • Il controllo rollback è effettivamente ridondante a causa di SET XACT_ABORT ON . Tuttavia, mi fa sentire meglio, sembra strano senza e consente situazioni in cui non lo desideri

  • Remus Rusanu ha una shell simile che usa i punti di salvataggio. Preferisco una chiamata DB atomica e non uso aggiornamenti parziali come il loro articolo

Non sono un ragazzo di Linq (e nemmeno Erland), ma ha scritto la bibbia assoluta sulla gestione degli errori. Al di fuori delle complicazioni che Linq potrebbe aggiungere al tuo problema, tutte le altre domande dovrebbero essere risolte qui:

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

(Vecchio link: http://www.sumrskog.se/error_handling_2005.html )

Per risolvere il problema della restituzione del numero di errore e del numero di linea menzionati da @AlexKuznetsov, è ansible sollevare l’errore in quanto tale:

 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) 

– Il metodo @ Amanda sopra non restituisce il corretto numero di errore

 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 

Nel caso in cui non sia necessaria alcuna gestione degli errori speciale in CATCH, ad eccezione del rethrow e della catena di chiamate del proc memorizzato non è troppo lungo, potrebbe essere opportuno utilizzare tale modello semplice:

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

Effettua inoltre il rollback della transazione radice con tutti quelli “annidati” in caso di errore, ma il codice è più breve e più diretto della soluzione di @ gbn. Ancora XACT_ABORT prende cura della maggior parte dei problemi menzionati qui.

Potrebbe esserci un sovraccarico aggiuntivo per il nesting delle transazioni, ma potrebbe non essere troppo alto, immagino.