Care este avantajul utilizării „SET XACT_ABORT ON” într-o procedură stocată? (Programare, Sql, Server Sql)

odiseh a intrebat.

Care este avantajul utilizării SET XACT_ABORT ON într-o procedură stocată?

Comentarii

5 răspunsuri
Ben Griswold

SET XACT_ABORT ON instruiește SQL Server să returneze întreaga tranzacție și să anuleze lotul atunci când apare o eroare de execuție. Aceasta vă acoperă în cazuri precum o comandă timeout care apare pe aplicația client și nu în SQL Server însuși (care nu este acoperită de opțiunea implicită XACT_ABORT OFF setare).

Deoarece un timeout al unei interogări va lăsa tranzacția deschisă, SET XACT_ABORT ON este recomandată în toate procedurile stocate cu tranzacții explicite (cu excepția cazului în care aveți un motiv specific pentru a proceda altfel), deoarece consecințele unei aplicații care efectuează lucrări pe o conexiune cu o tranzacție deschisă sunt dezastruoase.

Există o prezentare generală foarte bună pe Blogul lui Dan Guzman,

Comentarii

    46

  • deci de ce nu este activată în mod implicit? –  > Por Mike W.
  • Este XACT_ABORT în continuare necesar dacă aveți BEGIN TRYBEGIN CATCH și ROLLBACK cu BEGIN CATCH bloc în Sql? –  > Por user20358.
  • @user20358 BEGIN TRYBEGIN CATCH nu va prinde lucruri cum ar fi un timeout care apare în aplicația client, iar unele erori SQL nu pot fi prinse, de asemenea, lăsându-vă cu o tranzacție deschisă acolo unde nu v-ați aștepta la una. –  > Por Tom Lint.
Remus Rusanu

După părerea mea, SET XACT_ABORT ON a devenit caduc prin adăugarea BEGIN TRY/BEGIN CATCH în SQL 2k5. Înainte de blocurile de excepție în Transact-SQL era foarte dificil de gestionat erorile și procedurile dezechilibrate erau mult prea frecvente (proceduri care aveau un @@TRANCOUNT diferit la ieșire față de intrare).

Odată cu adăugarea gestionării excepțiilor Transact-SQL, este mult mai ușor să se scrie proceduri corecte, care sunt garantate să echilibreze corect tranzacțiile. De exemplu, eu utilizez următoarea procedură șablon pentru gestionarea excepțiilor și a tranzacțiilor imbricate:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end
go

Acesta îmi permite să scriu proceduri atomice care își returnează doar propria activitate în cazul unor erori recuperabile.

Una dintre principalele probleme cu care se confruntă procedurile Transact-SQL este puritatea datelor: uneori, parametrii primiți sau datele din tabele sunt pur și simplu greșite, ceea ce duce la erori de cheie duplicată, erori de constrângere referențială, erori de constrângere de verificare și așa mai departe. La urma urmei, acesta este exact rolul acestor constrângeri, dacă aceste erori de puritate a datelor ar fi imposibile și toate ar fi surprinse de logica de afaceri, constrângerile ar fi toate depășite (exagerare dramatică adăugată pentru efect). Dacă XACT_ABORT este activat, atunci toate aceste erori duc la pierderea întregii tranzacții, spre deosebire de posibilitatea de a codifica blocuri de excepție care să gestioneze excepțiile în mod elegant. Un exemplu tipic este încercarea de a face un INSERT și revenirea la un UPDATE la încălcarea PK.

Comentarii

  • Cu excepția timeout-urilor clienților… și părerea mea este că SET XACT_ABORT este mai eficient în SQL 2005, deoarece comportamentul este mai previzibil: mult mai puține erori de anulare a lotului. –  > Por gbn.
  • Sunt de acord într-o oarecare măsură, dar îmi planific gestionarea erorilor în funcție de toate eventualitățile, deoarece știu că voi fi învinuit în calitate de DBA dezvoltator dacă apare o comandă de timeout. –  > Por gbn.
  • @RemusRusanu Cum altfel ai putea gestiona o operațiune de lungă durată, sincronă, în baza de date? –  > Por Ian Boyd.
  • Documentația MSDN afirmă: „XACT_ABORT trebuie să fie setat ON pentru declarațiile de modificare a datelor într-o tranzacție implicită sau explicită împotriva majorității furnizorilor OLE DB, inclusiv SQL Server. Singurul caz în care această opțiune nu este necesară este în cazul în care furnizorul suportă tranzacții imbricate.” msdn.microsoft.com/en-us/library/ms188792(v=sql.120).aspx –  > Por Nathan.
  • „După părerea mea, SET XACT_ABORT ON a devenit caduc prin adăugarea BEGIN TRY/BEGIN CATCH” – Vă înțeleg, dar vă rog să vedeți sommarskog.se/error_handling/Part1.html –  > Por Inginer inversat.
VladV

Citez MSDN:

Când SET XACT_ABORT este activat, dacă o instrucțiune Transact-SQL generează o eroare de execuție, întreaga tranzacție este încheiată și reluată. Când SET XACT_ABORT este dezactivat, în unele cazuri, doar instrucțiunea Transact-SQL care a generat eroarea este anulată și tranzacția continuă să fie procesată.

În practică, acest lucru înseamnă că unele dintre declarații pot eșua, lăsând tranzacția „parțial finalizată”, iar pentru cel care face apelul poate să nu existe niciun semn de eșec.

Un exemplu simplu:

INSERT INTO t1 VALUES (1/0)    
INSERT INTO t2 VALUES (1/1)    
SELECT 'Everything is fine'

Acest cod se va executa „cu succes” cu XACT_ABORT OFF și se va încheia cu o eroare cu XACT_ABORT ON („INSERT INTO t2” nu va fi executat, iar o aplicație client va ridica o excepție).

Ca o abordare mai flexibilă, ați putea verifica @@ERROR după fiecare instrucțiune (vechea școală) sau să utilizați blocuri TRY…CATCH (MSSQL2005+). Personal, prefer să setez XACT_ABORT ON ori de câte ori nu există un motiv pentru o gestionare avansată a erorilor.

ionutm

În ceea ce privește timeout-urile clienților și utilizarea lui XACT_ABORT pentru a le gestiona, în opinia mea, există cel puțin un motiv foarte bun pentru a avea timeout-uri în API-uri client precum SqlClient, și anume pentru a proteja codul aplicației client de blocajele care apar în codul serverului SQL. În acest caz, codul clientului nu are nicio vină, dar trebuie să se protejeze de blocajul permanent în așteptarea finalizării comenzii pe server. Deci, invers, dacă trebuie să existe timeout-uri pentru client pentru a proteja codul clientului, la fel și XACT_ABORT ON trebuie să protejeze codul serverului de întreruperile clientului, în cazul în care execuția codului server durează mai mult decât este dispus să aștepte clientul.

Dan Diplo

Este utilizat în gestionarea tranzacțiilor pentru a se asigura că orice eroare are ca rezultat anularea tranzacției.