• Alas no help from the forum, but maybe my solution will ultimately help others. Basically I did 3 things to make this work:

    1. Modified code from above (added "AND XACT_STATE() <> 1" to the WHILE condition.

    SELECT @try_count = 1, @Row_Count = 0

    WHILE @err_cnt = 0 and @try_count > 0 and @try_count <= @try_max and @cons_empty IS NOT NULL and

    XACT_STATE() <> -1

    BEGIN

    BEGIN TRY

    --IF @try_count = 1 and @debug = 3 RAISERROR (N'Simulated Deadlock', 13, 3) WITH SETERROR

    -- Step 8: Update the Invoice Header (totals, status)

    UPDATE i WITH(ROWLOCK)

    SET invoice_status= 'N'

    ,tot_pkg= ISNULL(id.tot_pkg, 0)

    ,tot_wt= ISNULL(id.tot_wt, 0)

    ,tot_dim_wt= ISNULL(id.tot_dim_wt, 0)

    ,chargeable_wt= ISNULL(id.chargeable_wt, 0)

    ,insurance_amt= ISNULL(id.insurance_amt, 0)

    ,other_surcharge= ISNULL(id.other_surcharge, 0)

    ,tot_freight_charge= ISNULL(id.tot_freight_charge, 0)

    ,tot_vas_surcharge= ISNULL(id.tot_vas_surcharge, 0)

    ,esa_surcharge= ISNULL(id.esa_surcharge, 0)

    ,total_exception= ISNULL(id.total_exception, 0)

    ,mbg_amount= ISNULL(id.mbg_amount, 0)

    ,invoice_adj_amount= ISNULL(id.inv_adj_amount, 0)

    ,invoice_amt= ISNULL(id.invoice_amt, 0)

    ,tot_cons= ISNULL(id.tot_cons, 0)

    FROM dbo.Invoice i

    LEFT OUTER JOIN (

    SELECT applicationid, enterpriseid, invoice_no

    ,SUM(tot_pkg)as tot_pkg

    ,SUM(tot_wt)as tot_wt

    ,SUM(tot_dim_wt)as tot_dim_wt

    ,SUM(chargeable_wt)as chargeable_wt

    ,SUM(insurance_amt)as insurance_amt

    ,SUM(other_surcharge)as other_surcharge

    ,SUM(tot_freight_charge)as tot_freight_charge

    ,SUM(tot_vas_surcharge)as tot_vas_surcharge

    ,SUM(esa_surcharge)as esa_surcharge

    ,SUM(total_exception)as total_exception

    ,SUM(mbg_amount)as mbg_amount

    ,SUM(invoice_adj_amount)as inv_adj_amount

    ,SUM(invoice_amt)as invoice_amt

    ,COUNT(consignment_no)as tot_cons

    FROM dbo.Invoice_detail WITH(NOLOCK)

    GROUP BY applicationid, enterpriseid, invoice_no

    ) id

    ON i.applicationid = id.applicationid and i.enterpriseid = id.enterpriseid and

    i.invoice_no = id.invoice_no

    WHERE i.applicationid = @applicationid and i.enterpriseid = @enterpriseid and

    jobid = @jobid

    SELECT @Row_Count = @@ROWCOUNT, @try_count = 0-- @Row_Count = # Consignments updated

    END TRY-- Update of Invoice header

    BEGIN CATCH

    SELECT @ERROR_NUMBER= CAST(ERROR_NUMBER()As VARCHAR(max))

    ,@ERROR_SEVERITY= CAST(ERROR_SEVERITY()As VARCHAR(max))

    ,@ERROR_STATE= CAST(ERROR_STATE()As VARCHAR(max))

    ,@ERROR_PROCEDURE= CAST(ERROR_PROCEDURE()As VARCHAR(max))

    ,@ERROR_LINE= CAST(ERROR_LINE()As VARCHAR(max))

    ,@ERROR_MESSAGE= CAST(ERROR_MESSAGE()As VARCHAR(max))

    IF @ERROR_NUMBER = N'1205' OR @ERROR_NUMBER = N'50000'-- A deadlock (allow retry)

    BEGIN

    SET @errorinfo = @errorinfo + 'Warning: Deadlock on try ' + CAST(@try_count AS NVARCHAR) +

    ' of INVOICE HEADERS UPDATED step.

    '

    SET @try_count = @try_count + 1

    IF @try_count > @try_max

    BEGIN

    SET @errorinfo = @errorinfo + 'Retry count exceeded on this step'+ '

    '

    SET @errorinfo = @errorinfo + 'Error Number: '+ @ERROR_NUMBER+ '

    '

    SET @errorinfo = @errorinfo + 'Error Severity: '+ @ERROR_SEVERITY+ '

    '

    SET @errorinfo = @errorinfo + 'Error State: '+ @ERROR_STATE+ '

    '

    SET @errorinfo = @errorinfo + 'Error Procedure: '+ @ERROR_PROCEDURE+ '

    '

    SET @errorinfo = @errorinfo + 'Error Line: '+ @ERROR_LINE+ '

    '

    SET @errorinfo = @errorinfo + 'Error Message: '+ @ERROR_MESSAGE+ '

    '

    SELECT @err_cnt = @err_cnt + 1

    END

    END

    ELSE BEGIN-- Any other error

    SET @errorinfo = @errorinfo + 'Error Number: '+ @ERROR_NUMBER+ '

    '

    SET @errorinfo = @errorinfo + 'Error Severity: '+ @ERROR_SEVERITY+ '

    '

    SET @errorinfo = @errorinfo + 'Error State: '+ @ERROR_STATE+ '

    '

    SET @errorinfo = @errorinfo + 'Error Procedure: '+ @ERROR_PROCEDURE+ '

    '

    SET @errorinfo = @errorinfo + 'Error Line: '+ @ERROR_LINE+ '

    '

    SET @errorinfo = @errorinfo + 'Error Message: '+ @ERROR_MESSAGE+ '

    '

    SELECT @err_cnt = @err_cnt + 1-- Break out of loop

    END

    END CATCH

    END-- Of WHILE

    2. After all 8 steps where the loops were implemented, I checked for XACT_STATE() = -1 and did a rollback if that was the case.

    The odd thing about this was that only some deadlocks were causing the non-commitable transaction. I can't prove it but this may have been caused by the update trigger on the Shipment table.

    In any event, the reason deadlocks were occurring was that the SP was originally expected to run concurrently (different users or user/sessions) running the same process but for different customers (this is part of an invoicing process). So I think that the final step is what really resolved it:

    3. The SP also creates a log file on each SQL step. So using this log file, it was possible to determine whether another process was currently running. I use that knowledge to throw the SP into a wait state until the competing process finishes.

    This will of course not prevent deadlocks with other processes, however it is hopeful the existing code (deadlock retries) and the XACT_STATE()/ROLLBACK will handle any such cases that arise.

    Hope this is helpful to someone!

    Yeah, yeah, I know - my mantra says "No Loops." In this case not avoidable as much as it pained me to do so.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St