Back to OIM Explorer

dbo.QBM_PExecuteSQLWithRetry_LLP

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 7.355 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FCVDatetimeToString source text reference
  • references source dbo.QBM_FCVFloatToString source text reference
  • references source dbo.QBM_FGISessionErrorCount source text reference
  • references source dbo.QBM_FGISessionErrorForLog source text reference
  • references source dbo.QBM_FGISessionErrorIsDeadlock source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PSessionErrorClean source text reference
  • references source dbo.QBM_PWaitForSeconds source text reference

References

Referenced By

Complete Source

SQL325 lines
1CREATE PROCEDURE QBM_PExecuteSQLWithRetry_LLP(2  @SQLStatement nvarchar(max),3  @LockTimeout_ms int = 150,4  @MaxWaitTimeForLock_s float = 8.0,5  @ProcIDForJournal int = NULL,6  @HandleErrorSilent BIT = 0,7  @DeadlockPriority int = -5,8  @ExecuteWithTransact BIT = 1,9  @ErrorInExecute BIT = 0 OUTPUT10)11AS12BEGIN13  DECLARE @Starttime datetime14  DECLARE @CurrentDeadlock_priority int15  DECLARE @waittime float = 0.016  DECLARE @Success int = 017  DECLARE @OurOwnMessage nvarchar(max)18  DECLARE @TransactionCount int19  DECLARE @erg int = 020  DECLARE @MyName nvarchar(256) = object_name(@@procid)21  DECLARE @VersuchNummer int = 122  DECLARE @DebugSwitch int = 023  DECLARE @DebugLevel varchar(1) = 'W'24  DECLARE @FinalMisserfolg nvarchar(1000) = '#LDS#No success after all retries.|'25  DECLARE @JournalMessage nvarchar(max)26  DECLARE @JournalProcedureExists BIT = 027  DECLARE @SQLIntern nvarchar(max)28  DECLARE @ErrorBuffer QBM_YSessionError29  DECLARE @tran1 int30  DECLARE @tran2 int31  DECLARE @CountEntriesSessionError int32  DECLARE @XactMissmatch BIT = 033  DECLARE @FinalMisserfolgXact nvarchar(1000) = '#LDS#No success after all retries. Please do schema changes without transaction.|'34  DECLARE @DebugRetries int = -135  DECLARE @DebugWaitSum float = 0.036  SET XACT_ABORT off37  BEGIN TRY38    IF EXISTS(39      SELECT TOP 1 140      FROM sys.objects o41      WHERE42        o.name = 'QBM_PJournal' AND type = 'P')43    BEGIN44      SELECT @JournalProcedureExists = 145    END46    IF @DebugSwitch = 2 print ' trancount eingang' + str(@@trancount)47    IF @ExecuteWithTransact = 148    BEGIN49      BEGIN50        TRANSACTION51      END52      IF @DebugSwitch = 2 print ' trancount eingang 2' + str(@@trancount)53      SET nocount54        ON55      SELECT TOP 1 @CurrentDeadlock_priority = s.deadlock_priority56      FROM sys.dm_exec_sessions s57      WHERE58        s.session_id = @@spid59      SELECT @TransactionCount = @@trancount60      SET deadlock_priority @DeadlockPriority61      IF @LockTimeout_ms = 15062      BEGIN63        SET lock_timeout 15064        SELECT @SQLIntern = @SQLStatement65      END66      ELSE67      BEGIN68        SELECT69          @SQLIntern = CONCAT('set lock_timeout ',70          str(@LockTimeout_ms),71          ';SET XACT_ABORT OFF;',72          @SQLStatement)73      END74      SELECT @CountEntriesSessionError = dbo.QBM_FGISessionErrorCount()75      SELECT @starttime = getutcdate()76      WHILE @Success = 0 AND convert(float,77      datediff(ms, @starttime, getutcdate())) < @MaxWaitTimeForLock_s * 1000.0 AND @XactMissmatch = 078      BEGIN79        SELECT @waittime =(RAND() / 2.0) + 0.0580        SELECT @DebugRetries += 181        IF @ExecuteWithTransact = 182        BEGIN83          IF @DebugSwitch = 2 print 'vor save transaction' save TRANSACTION IchVersuche84          IF @DebugSwitch = 2 print 'save transaction geschafft'85        END86        BEGIN TRY87          IF @DebugSwitch = 2 print ' trancount vor execute ' + str(@@trancount)88          SELECT @tran1 = @@TRANCOUNT89          EXEC sp_executesql @SQLIntern90          SELECT @erg = @@ROWCOUNT91          SELECT @Success = 192        END TRY93        BEGIN CATCH94          IF @DebugSwitch = 2 print 'catch 1'95          IF @DebugSwitch = 2 print ' trancount catch 1' + str(@@trancount)96          IF @DebugSwitch = 2 print ERROR_MESSAGE()97          SELECT @tran2 = @@TRANCOUNT98          IF @tran1 = @tran2 -199          BEGIN100            COMMIT TRANSACTION101          END102          DELETE @ErrorBuffer103          INSERT INTO @ErrorBuffer(ErrorMessage,104          ErrorSeverity,105          ErrorState,106          ErrorNumber,107          ProcedureName,108          ProcedureLine,109          MessageDate,110          GenProcID,111          RepeatCounter,112          IsReThrow,113          SourceCode)114          SELECT115            ERROR_MESSAGE(),116            ERROR_SEVERITY(),117            ERROR_STATE(),118            ERROR_NUMBER(),119            @MyName,120            ERROR_LINE(),121            GETUTCDATE(),122            NULL,123            0,124            0,125            @SQLIntern126          EXEC QBM_PSessionErrorAdd @ErrorBuffer127        END CATCH128        IF @Success = 0129        BEGIN130          IF @DebugSwitch = 2 print '@Success ' + str(@Success)131          BEGIN TRY132            IF @DebugSwitch = 2 print 'try 1'133            IF @DebugSwitch = 2 print ' trancount 1' + str(@@trancount)134            IF @DebugSwitch = 2 print 'Current XACT ' + str(XACT_STATE())135            IF @ExecuteWithTransact = 1136            BEGIN137              BEGIN TRY138                ROLLBACK TRANSACTION IchVersuche139                IF @DebugSwitch = 2 print 'Rollbackversuch geklappt'140              END TRY141              BEGIN CATCH142                SELECT @XactMissmatch = 1143                IF @DebugSwitch = 2 print 'Rollbackversuch NICHT geklappt'144              END CATCH145            END146            IF @DebugSwitch = 2 print 'try 2'147            IF @DebugSwitch = 2 print ' trancount 2' + str(@@trancount)148          END TRY149          BEGIN CATCH150            IF @DebugSwitch = 2 print 'catch 2'151          END CATCH152        END153        IF @Success = 1154        BEGIN155          IF @DebugSwitch > 0156          BEGIN157            print dbo.QBM_FCVDatetimeToString(GETUTCDATE()) + ' Versuch geschafft'158          END159        END160        ELSE161        BEGIN162          IF dbo.QBM_FGISessionErrorIsDeadlock(@ErrorBuffer) = 1163          BEGIN164            IF @DebugSwitch = 2 print 'es ist eine lock-Meldung'165            EXEC QBM_PWaitForSeconds @waittime166            SELECT @DebugWaitSum += @waittime167          END168          ELSE169          BEGIN170            SELECT @Success = -1 CONTINUE171          END172        END173        IF @TransactionCount <> @@TRANCOUNT AND @ExecuteWithTransact = 1174        BEGIN175          IF @DebugSwitch = 2 print 'transaction Differenz ' + str(@TransactionCount) + str(@@TRANCOUNT)176          SELECT177            @OurOwnMessage = CONCAT('#LDS#Expected transaction count {0} does not match existing {1}, called from {2}.|',178            str(isnull(@TransactionCount, 0)),179            '|',180            str(@@TRANCOUNT),181            '|',182            @MyName,183            '|')184          DELETE @ErrorBuffer185          INSERT INTO @ErrorBuffer(ErrorMessage,186          ErrorSeverity,187          ErrorState,188          ErrorNumber,189          ProcedureName,190          ProcedureLine,191          MessageDate,192          GenProcID,193          RepeatCounter,194          IsReThrow,195          SourceCode)196          SELECT197            @OurOwnMessage,198            18,199            1,200            50000,201            @MyName,202            0,203            GETUTCDATE(),204            NULL,205            0,206            0,207            @SQLIntern208          EXEC QBM_PSessionErrorAdd @ErrorBuffer209          RAISERROR(@OurOwnMessage,210          18,211          1)212            WITH NOWAIT213        END214        IF @DebugSwitch = 2 print 'auf zum nächsten Versuch'215        SELECT @VersuchNummer += 1216      END217      IF @ExecuteWithTransact = 1 AND @XactMissmatch = 0218      BEGIN219        COMMIT TRANSACTION220      END221      IF @Success IN(0,222      -1)223      BEGIN224        IF @Success = 0 OR(@Success = -1 AND @XactMissmatch = 1)225        BEGIN226          DELETE @ErrorBuffer227          SELECT @OurOwnMessage = CASE @XactMissmatch228          WHEN 0 THEN229          @FinalMisserfolg230          ELSE @FinalMisserfolgXact231          END232          INSERT INTO @ErrorBuffer(ErrorMessage,233          ErrorSeverity,234          ErrorState,235          ErrorNumber,236          ProcedureName,237          ProcedureLine,238          MessageDate,239          GenProcID,240          RepeatCounter,241          IsReThrow,242          SourceCode)243          SELECT244            @OurOwnMessage,245            18,246            1,247            50000,248            @MyName,249            229,250            GETUTCDATE(),251            NULL,252            0,253            0,254            @SQLIntern255          EXEC QBM_PSessionErrorAdd @ErrorBuffer256        END257        ELSE258        BEGIN259          SELECT @OurOwnMessage = '#LDS#Error exececuting direct sql command.|'260        END261        IF @HandleErrorSilent = 0262        BEGIN263          IF @DebugSwitch = 2 print 'raise ' + @OurOwnMessage264          SELECT265            @FinalMisserfolgXact = 'eigentlich müßte hier ein "set XACT_ABORT on" rein, lassen wir aber erst mal, da nur in einem einzigen Trigger verwendet'266          RAISERROR(@OurOwnMessage,267          18,268          1)269            WITH NOWAIT270        END271      END272      ELSE273      BEGIN274        WHILE dbo.QBM_FGISessionErrorCount() > @CountEntriesSessionError275        BEGIN276          EXEC QBM_PSessionErrorClean @LastEntryOnly = 1277        END278      END279    END TRY280    BEGIN CATCH281      IF @DebugSwitch = 2 print 'catch 3'282      IF @DebugSwitch = 2 print ' trancount ausgang' + str(@@trancount)283      EXEC QBM_PSessionErrorAdd DEFAULT,284        @SQLStatement285      RAISERROR('',286      18,287      1)288        WITH NOWAIT289    END CATCH290    endLabel:291    IF(@DebugRetries > 0 OR(@Success IN(0, -1) AND @HandleErrorSilent = 0)) AND @JournalProcedureExists = 1292    BEGIN293      SELECT294        @OurOwnMessage = CONCAT(OBJECT_NAME(@@procid),295        ' ',296        ' retries',297        RIGHT(str(@DebugRetries), 5),298        ' waitsum ',299        dbo.QBM_FCVFloatToString(@DebugWaitSum),300        ' stmt ',301        LEFT(@SQLStatement, 50))302      EXEC QBM_PJournal @OurOwnMessage,303        @@procid,304      'T',305        @DebugLevel306    END307    IF @ProcIDForJournal > ' ' AND @Success IN(0,308    -1) AND @JournalProcedureExists = 1309    BEGIN310      SELECT @OurOwnMessage = dbo.QBM_FGISessionErrorForLog()311      EXEC QBM_PJournal @OurOwnMessage,312        @ProcIDForJournal,313      'E',314        @DebugLevel315    END316    IF @Success IN(0,317    -1)318    BEGIN319      SELECT @ErrorInExecute = 1320    END321    SET deadlock_priority @CurrentDeadlock_priority322    SET lock_timeout -1323    SET nocount off324    RETURN(@erg)325  END
Open raw exported source
SQL · Raw48 lines
1   create   procedure QBM_PExecuteSQLWithRetry_LLP (@SQLStatement nvarchar(max) , @LockTimeout_ms int = 150  , @MaxWaitTimeForLock_s float = 8.02  , @ProcIDForJournal int = null  , @HandleErrorSilent bit = 0   , @DeadlockPriority int = -5  , @ExecuteWithTransact bit = 1  , @ErrorInExecute bit = 30 output ) as begin declare @Starttime datetime declare @CurrentDeadlock_priority int declare @waittime float = 0.0 declare @Success int = 0    declare4 @OurOwnMessage nvarchar(max)  declare @TransactionCount int declare @erg int = 0 declare @MyName nvarchar(256) = object_name(@@procid) declare @VersuchNummer5 int = 1 declare @DebugSwitch int = 0 declare @DebugLevel varchar(1) = 'W' declare @FinalMisserfolg nvarchar(1000) = '#LDS#No success after all retries.|'6 declare @JournalMessage nvarchar(max) declare @JournalProcedureExists bit = 0  declare @SQLIntern nvarchar(max) declare @ErrorBuffer QBM_YSessionError7 declare @tran1 int declare @tran2 int declare @CountEntriesSessionError int declare @XactMissmatch bit = 0 declare @FinalMisserfolgXact nvarchar(1000)8 = '#LDS#No success after all retries. Please do schema changes without transaction.|' declare @DebugRetries int = -1 declare @DebugWaitSum float = 0.09 set XACT_ABORT off BEGIN TRY if exists (select top 1 1 from sys.objects o where o.name = 'QBM_PJournal' and type = 'P' ) begin select @JournalProcedureExists10 = 1 end if @DebugSwitch = 2 print ' trancount eingang' + str(@@trancount) if @ExecuteWithTransact = 1 begin begin transaction  end if @DebugSwitch = 211 print ' trancount eingang 2' + str(@@trancount) set nocount on select top 1 @CurrentDeadlock_priority = s.deadlock_priority FROM sys.dm_exec_sessions 12s where s.session_id = @@spid select @TransactionCount = @@trancount set deadlock_priority @DeadlockPriority  if @LockTimeout_ms = 150  begin set lock_timeout13 150  select @SQLIntern = @SQLStatement end else begin   select @SQLIntern = CONCAT( 'set lock_timeout ' , str(@LockTimeout_ms) , ';SET XACT_ABORT OFF;'14 , @SQLStatement ) end select @CountEntriesSessionError = dbo.QBM_FGISessionErrorCount() select @starttime = getutcdate() while @Success = 0 and convert15(float, datediff(ms, @starttime, getutcdate())) < @MaxWaitTimeForLock_s * 1000.0 and @XactMissmatch = 0 begin select @waittime = (RAND() / 2.0 ) + 0.0516 select @DebugRetries += 1 if @ExecuteWithTransact = 1 begin if @DebugSwitch = 2 print 'vor save transaction' save transaction IchVersuche if @DebugSwitch17 = 2 print 'save transaction geschafft' end BEGIN TRY if @DebugSwitch = 2 print ' trancount vor execute ' + str(@@trancount)  select @tran1 = @@TRANCOUNT18 exec sp_executesql @SQLIntern select @erg = @@ROWCOUNT select @Success = 1 END TRY BEGIN CATCH if @DebugSwitch = 2 print 'catch 1' if @DebugSwitch = 219 print ' trancount catch 1' + str(@@trancount) if @DebugSwitch = 2 print ERROR_MESSAGE() select @tran2 = @@TRANCOUNT if @tran1 = @tran2 -1  begin commit20 transaction end delete @ErrorBuffer    insert into @ErrorBuffer (ErrorMessage, ErrorSeverity, ErrorState, ErrorNumber , ProcedureName, ProcedureLine, 21MessageDate , GenProcID, RepeatCounter, IsReThrow, SourceCode) select ERROR_MESSAGE(),ERROR_SEVERITY(), ERROR_STATE(), ERROR_NUMBER() , @MyName , ERROR_LINE22(), GETUTCDATE() , null, 0, 0, @SQLIntern exec QBM_PSessionErrorAdd @ErrorBuffer END CATCH if @Success = 0 begin if @DebugSwitch = 2 print '@Success ' 23+ str(@Success) BEGIN TRY if @DebugSwitch = 2 print 'try 1' if @DebugSwitch = 2 print ' trancount 1' + str(@@trancount) if @DebugSwitch = 2 print 'Current XACT '24 + str(XACT_STATE()) if @ExecuteWithTransact = 1 begin   begin TRY rollback transaction IchVersuche if @DebugSwitch = 2 print 'Rollbackversuch geklappt'25 END TRY BEGIN CATCH  select @XactMissmatch = 1 if @DebugSwitch = 2 print 'Rollbackversuch NICHT geklappt' END CATCH end if @DebugSwitch = 2 print 'try 2'26 if @DebugSwitch = 2 print ' trancount 2' + str(@@trancount) END TRY BEGIN CATCH if @DebugSwitch = 2 print 'catch 2'  END CATCH end  if @Success = 1 begin27 if @DebugSwitch > 0 begin print dbo.QBM_FCVDatetimeToString(GETUTCDATE()) + ' Versuch geschafft' end end else begin if dbo.QBM_FGISessionErrorIsDeadlock28(@ErrorBuffer) = 1 begin if @DebugSwitch = 2 print 'es ist eine lock-Meldung' exec QBM_PWaitForSeconds @waittime select @DebugWaitSum += @waittime end 29else begin   select @Success = -1 continue end end  if @TransactionCount <> @@TRANCOUNT and @ExecuteWithTransact = 1 begin if @DebugSwitch = 2 print 'transaction Differenz '30 + str(@TransactionCount) + str(@@TRANCOUNT) select @OurOwnMessage = concat('#LDS#Expected transaction count {0} does not match existing {1}, called from {2}.|'31 , str(isnull(@TransactionCount, 0)) , '|' , str( @@TRANCOUNT) , '|' , @MyName , '|' ) delete @ErrorBuffer insert into @ErrorBuffer (ErrorMessage, ErrorSeverity32, ErrorState, ErrorNumber , ProcedureName, ProcedureLine, MessageDate , GenProcID, RepeatCounter, IsReThrow, SourceCode) select @OurOwnMessage,18, 1, 5000033 , @MyName, 0, GETUTCDATE() , null, 0, 0, @SQLIntern exec QBM_PSessionErrorAdd @ErrorBuffer RAISERROR (@OurOwnMessage, 18, 1) WITH NOWAIT end if @DebugSwitch34 = 2 print 'auf zum nächsten Versuch' select @VersuchNummer += 1 end  if @ExecuteWithTransact = 1 and @XactMissmatch = 0 begin commit transaction  end 35 if @Success in ( 0, -1) begin   if @Success = 0  or (@Success = -1 and @XactMissmatch = 1) begin delete @ErrorBuffer select @OurOwnMessage = case @XactMissmatch36 when 0 then @FinalMisserfolg else @FinalMisserfolgXact end insert into @ErrorBuffer (ErrorMessage, ErrorSeverity, ErrorState, ErrorNumber , ProcedureName37, ProcedureLine, MessageDate , GenProcID, RepeatCounter, IsReThrow, SourceCode) select @OurOwnMessage, 18, 1, 50000 , @MyName, 229, GETUTCDATE() , null38, 0, 0, @SQLIntern exec QBM_PSessionErrorAdd @ErrorBuffer end else  begin select @OurOwnMessage = '#LDS#Error exececuting direct sql command.|' end if 39@HandleErrorSilent = 0 begin if @DebugSwitch = 2 print 'raise ' + @OurOwnMessage  select @FinalMisserfolgXact = 'eigentlich müßte hier ein "set XACT_ABORT on" rein, lassen wir aber erst mal, da nur in einem einzigen Trigger verwendet'40 RAISERROR (@OurOwnMessage, 18, 1) WITH NOWAIT end end else begin   while dbo.QBM_FGISessionErrorCount() > @CountEntriesSessionError begin exec QBM_PSessionErrorClean41 @LastEntryOnly = 1 end end END TRY BEGIN CATCH if @DebugSwitch = 2 print 'catch 3' if @DebugSwitch = 2 print ' trancount ausgang' + str(@@trancount) exec42 QBM_PSessionErrorAdd default, @SQLStatement RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: if (@DebugRetries > 0 or ( @Success in (0, -1) and @HandleErrorSilent43 = 0) ) and @JournalProcedureExists = 1 begin select @OurOwnMessage = concat( OBJECT_NAME(@@procid), ' ' , ' retries' , right(str(@DebugRetries), 5) , 44' waitsum ', dbo.QBM_FCVFloatToString(@DebugWaitSum) , ' stmt ' , left(@SQLStatement, 50) ) exec QBM_PJournal @OurOwnMessage, @@procid, 'T', @DebugLevel45 end if @ProcIDForJournal > ' ' and @Success in (0, -1) and @JournalProcedureExists = 1 begin select @OurOwnMessage = dbo.QBM_FGISessionErrorForLog() exec46 QBM_PJournal @OurOwnMessage, @ProcIDForJournal, 'E', @DebugLevel end if @Success in (0, -1) begin select @ErrorInExecute = 1 end set deadlock_priority47 @CurrentDeadlock_priority set lock_timeout -1 set nocount off return (@erg) end 48