Welcome to Sign in | Help
in Search

modificare sql proc - deadlock sql server 2008

Last post 03-30-2011, 11:20 AM by B_gd_n[ ]Sahlean. 7 replies.
Sort Posts: Previous Next
  •  03-29-2011, 12:12 PM 8667

    modificare sql proc - deadlock sql server 2008

    salut, am procedura stocata de mai jos pe un sql server 2008

    CREATE proc VireazaSal
    @ImportRef int,
    @IBANAngajat varchar(50) ,
    @ContD varchar(50),
    @Suma money,
    @ContC varchar(50),
    @DataTr datetime,
    @DataExec datetime,
    @Comment varchar(200)
    as
    set nocount on
    declare @text varchar(200)

    if exists(select top 1 1 from V_SAL where ImportRef = @ImportRef and Virat = 0 and IBAN = @IBANAngajat)
    begin
    exec ExecTrans @ContD, @Suma, @ContC, @DataTr, @DataExec, @Comment

    update V_SAL
    set Virat = 1
    where ImportRef = @ImportRef and IBAN = @IBANAngajat and Virat = 0
    end
    else
    begin
    set @text = 'Suma a fost virata deja pt IBAN: ' + @IBANAngajat
    raiserror(@text, 1, 1)
    end
    go

    cateodata se intampla ca procedura ExecTrans sa dea deadlock si atunci nu vreau sa-mi mai faca update-ul cu Virat = 1, ce conditie trebuie sa pun a.i. update-ul cu Virat = 1 sa se faca doar daca ExecTran a fost executata cu succes?

    Multumesc.
  •  03-29-2011, 1:01 PM 8668 in reply to 8667

    Re: modificare sql proc - deadlock sql server 2008

    1. Încearcă să elimini deadlock-ul. Pentru a obține informații despre deadlock poți creare un server-side trace care să intercepteze evenimentul ”Deadlock graph” sau poți activa flag-ul 1222. La fel de bine poți crea un trace folosind SQL Profiler dar acest implică (uzual) un overhead mai mare decât un trace server-side.
    2. Folosește tranzacții și TRY ... CATCH. TRY ... CATCH poate intercepta un deadlock. În Books Online ai câteva exemple privitoare la TRY ... CATCH și tranzacții.
  •  03-29-2011, 1:41 PM 8669 in reply to 8668

    Re: modificare sql proc - deadlock sql server 2008

    Multumesc pt raspuns.
    Pt varianta 2 este ok asa?

    CREATE proc VirareSal
    @ImportRef int,
    @IBANAngajat varchar(50) ,
    @ContD varchar(50),
    @Suma money,
    @ContC varchar(50),
    @DataTr datetime,
    @DataExec datetime,
    @Comment varchar(200)
    as
    set nocount on
    declare @text varchar(200)
    BEGIN TRY
    BEGIN TRAN
    if exists(select top 1 1 from V_SAL where ImportRef = @ImportRef and Virat = 0 and IBAN = @IBANAngajat)
    begin
    exec ExecTran @ContD, @Suma, @ContC, @DataTr, @DataExec, @Comment

    update V_SAL
    set Virat = 1
    where ImportRef = @ImportRef and IBAN = @IBANAngajat and Virat = 0
    end
    else
    begin
    set @text = 'Suma a fost virata deja pt IBAN: ' + @IBANAngajat
    raiserror(@text, 1, 1)
    end
    COMMIT TRAN
    END TRY
    BEGIN CATCH
    if @@trancount > 0 ROLLBACK TRAN
    declare @ERROR_MESSAGE nvarchar(4000),
    @ERROR_SEVERITY int
    select @ERROR_MESSAGE = ERROR_MESSAGE() + ' in SP: ' + isnull(ERROR_PROCEDURE() + ' line ' + convert(nvarchar, ERROR_LINE()), ''),
    @ERROR_SEVERITY = ERROR_SEVERITY()

    raiserror(@ERROR_MESSAGE, @ERROR_SEVERITY, 1)
    return -1
    END CATCH
    go
  •  03-29-2011, 2:54 PM 8670 in reply to 8669

    Re: modificare sql proc - deadlock sql server 2008

    Cele două puncte nu sunt exclusive. Dacă ai timp încearcă să găsești cauza/ele apariției deadlock-ului.

    Încearcă să folosești tranzacții plus TRY ... CATCH și la nivelul procedurii ExecTran.

    Dpmdv RETURN -1 și RAISERROR sunt redundante în sensul că ambele semnalează apelantului apariția unei erori la nivelul procedurii VirareSal. Ai motive să folosești ambele soluții ?

    Dacă dorești să returnezi mesajul ”Suma a fost virată ...” poți să folosești un parametru OUTPUT în loc de RAISERROR(...,1,1).

    RAISERROR poate fi utilizată la fel ca funcția printf din C:
    RAISERROR('Eroare %s in procedura %s linia %d', @Sev, 1, @ErrMsg, @ErrProc, @ErrLine)

  •  03-29-2011, 3:50 PM 8671 in reply to 8670

    Re: modificare sql proc - deadlock sql server 2008

    o sa incerc sa aflu cauza deadlock-ului dar pana atunci imi trebuie o solutie provizorie.

    am scos linia return -1, intra-adevar am inteles si nu-si are rostul, nu o foloseam cum un scop anume ci doar a fost un copy paste neverificat :)

    Intrebarea mea este, asa cum arata procedura acum, daca am vreo eroare la nivelul procedurii ExecTran (deadlock sau orice altceva, care duce la neexecutarea cu succes a procedurii ExecTran) update-ul cu Virat =1 se executa?

    Multumesc.
  •  03-29-2011, 4:15 PM 8672 in reply to 8671

    Re: modificare sql proc - deadlock sql server 2008

    Depinde :-) este răspunsul corect : atât timp cât eroarea (inclusiv DL) din ExecTran poate fi detectată de blocul TRY CATCH din VireazaSal  și are severitatea între 11 și 19 da.

    Referitor la DL BOL spune: ”TRY…CATCH can be used to handle deadlocks. The 1205 deadlock victim error can be caught by the CATCH block and the transaction can be rolled back until the threads become unlocked. ” (Using TRY...CATCH in Transact-SQL ).
  •  03-29-2011, 4:41 PM 8673 in reply to 8672

    Re: modificare sql proc - deadlock sql server 2008

    am modificat si ExecTran pe acelasi principiu

    begin try
    begin tran
    .................cod mult................
    commit tran
    end try
    begin catch
    if @@trancount > 0 rollback tran
    declare @ERROR_MESSAGE nvarchar(4000),
    @ERROR_SEVERITY int
    select @ERROR_MESSAGE = ERROR_MESSAGE() + ' in SP: ' + isnull(ERROR_PROCEDURE() + ' line ' + convert(nvarchar, ERROR_LINE()), ''),
    @ERROR_SEVERITY = ERROR_SEVERITY()

    raiserror(@ERROR_MESSAGE, @ERROR_SEVERITY, 1)
    end catch

    ca sa fiu sigur ca are severitate intre 11 si 19 nu e mai bine sa folosesc
    raiserror(@ERROR_MESSAGE, 16, 1)
    in loc de
    raiserror(@ERROR_MESSAGE, @ERROR_SEVERITY, 1) ?
  •  03-30-2011, 11:20 AM 8674 in reply to 8673

    Re: modificare sql proc - deadlock sql server 2008

    CATCH este activat dacă severitatea se încadrează în limitele 11 - 19. Un deadlock (eroarea 1205) are severitatea 13 (”Msg 1205, Level 13, State 51, Line 1”).  Nu are sens să modifici severitatea unei erori interceptate de CATCH la 16 decât dacă este 19 (doar un admin poate folosi RAISERROR cu severitatea 19; pp. că utilizatorul care apelează procedurile stocate amintite nu este admin).

    LE:
    Erland Sommarskog are un articol ft. bun pe tema gestiunii erorilor Error Handling in SQL 2005 and Later.
View as RSS news feed in XML
Powered by Community Server (Commercial Edition), by Telligent Systems