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 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