February 29, 2012 at 7:03 pm
In a recent flurry of posts, I've attempted to help users of SSC with some of their query questions and now it is I that must seek the advice of those more experienced than myself.
I have a rather complex SP (1300+ lines) that includes a single transaction consisting of 8 SQL steps (INSERTs, UPDATEs, etc.). I know you should keep transactions short but in this case I have no alternative.
The database I'm working with is prone to frequent deadlocks that are partly due to high volume but also due to inefficient SQL, which is an issue I'm trying to address in this rewrite, so I've gone to some pains to ensure that each of the 8 SQL statements is performing the best it possibly can.
I've wrapped a retry-LOOP structure around each of the 8 queries, so that if a deadlock is detected it will retry 3 times and then give up. We performed a concurrency test and we were able to get a real deadlock condition and the re-try worked, but it was on two different steps than where this error is occurring now. When this ran in UAT (the users were trying hard to break the logic by executing multiple instances of the SP concurrently) it failed with this 3930 error.
Let me show you a sample of the code:
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
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
Explanation:
1. @try_max = 3 and there were no errors reported (e.g., deadlock retries) in any of the 2 prior transaction steps.
2. The SP produces an email (this information is provided below) that indicates line 632 is where the error occurred and that is the BEGIN TRY statement above. The error occurs on the second try of the UPDATE (apparently).
3. Upon entry to the above code segment, @err_cnt must be = 0 because it would be <> 0 if errors had occurred in either of the 2 prior steps. You can see how it is being set within the CATCH block (identical to prior steps).
4. With regard to the commented line of code right after BEGIN TRY, I want to assure you I not only tested it with a simulated deadlock but also was able to generate a real case of deadlock (although I believe it occurred in one of the prior steps).
5. I believe the actual error is being generated by the UPDATE, however a search suggests this error occurs within a CATCH on an attempt to execute any SQL statement considered to be part of the transaction. This is what is perplexing me as the UPDATE is clearly not in the CATCH and I reviewed the code prior and the CATCH/WHILE END statements seem all to be properly placed. I also don't see how any of the CATCH code could be considered as part of the on-going transaction.
6. All the code in the CATCH is setup to handle the deadlock error code (1205) specially (i.e., set it up to retry up to @try_max), otherwise capture the error information for reporting in the email.
7. In this case, the SP is being run by a .Net client. The client starts a thread to execute the SP and then returns a message back to the web form indicating the process has been started in the background. I've never seen this error message in attempts to run the SP from SSMS and I'm wondering if somehow the XACT_STATE() may be different when running in these modes.
The error message reported in the email is thus:
Warning: Deadlock on try 1 of INVOICE HEADERS UPDATED step.
Error Number: 3930
Error Severity: 16
Error State: 1
Error Procedure: GenerateInvoices
Error Line: 632
Error Message: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
I'm not necessarily asking you to debug my code as I'm probably capable of doing that myself. I've already checked that the WHILE loop is well formed, exactly as the prior ones are. Perhaps my eyes are missing something though.
What I am asking for is if anyone knows a clear, concise URL where this error is explained, that might help me.
Thanks in advance for your support and I know this is going to be a tricky one.
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
March 7, 2012 at 6:54 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply