Error handling deadlock issue

Question

Error handling deadlock issue

Kaushik Majumder

Ten Centuries

Points: 1009
More actions
December 1, 2006 at 5:59 am

#193292

Hi,
We do have a multi-stepped job that reconciles two databases (SQL 2000) accross different servers, scheduled to run in every 10 minutes. The job features an SELECT statement that frequently runs into deadlock issue failing the job as a whole.
We tried to set up an error handler by trapping the @@ERROR global variable, but everytime the error occurs the flow does not knock the error routine. The application stops there only ignoring the following steps.
The job is implemented as TSQL batches, not in stored procedures.
The TSQL batch is listed below...
DECLARE @err int
SelectInit:
BEGIN
SELECT *
FROM UKLNNTPA074.AonDmPrd.dbo.Element_Staging
WHERE
  DateUpdated > (Select DTS_Dt From UKLNNTPA076.DMOSDPRD.dbo.ProcessControl_DTS)
AND E_INA07<>''
END
SET @err=@@ERROR
IF @err=1205
BEGIN
  --Print 'error : '+cast(@err as varchar)
  GOTO SelectInit
END

How do we make the flow to go into the error routine?
Any help will be greatly appreciated.

Regards
December 4, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 1

In SQL 2000 (and earlier) a deadlock will terminate the process. The only way to handle errors (that I know of) is to code error handling logic outside the scope of the TSQL. For what it's worth, in SQL2005 you have much more powerful error handling that can handle deadlocks (using TRY...CATCH)

I'm sorry for the bad news, but you will never be able to handle deadlocks in TSQL in SQL2000.

It would be worth investigating why you are getting deadlocks in the first place. Using traceflag 1205, you can get a little more info in the error log.

But try this: see if you have any better luck using OPENQUERY rather than doing the linked server call. It's worth a shot.

SQL guy and Houston Magician

Taj Mr or Mrs. 500 Points: 596 More actions · Answer 2

Try this

Declare @Errcounter INT

Blah blah blah

IF @@error <> 0 GOTO ErrorHandler

ErrorHandler:

If @@error = 1205 AND @ErrCounter = 4

BEGIN

WAITFOR DELAY '00:00:00.30'

SET @ErrCounter = @ErrCounter + 1

GOTO SelectInit

END

Tajammal Butt

Sergiy SSC Guru Points: 110209 More actions · Answer 3

1. Don't use remote queries. At least avoid them if posible.

Use OPENQUERY.

2. Don't use remote query in subquery. Just don't do this. Ever.

Unless you use to teach students in University how to deal with distributed transactions.

DECLARE @err int, @DTS_Dt datetime

Select @DTS_Dt = DTS_Dt From UKLNNTPA076.DMOSDPRD.dbo.ProcessControl_DTS

SELECT *

FROM UKLNNTPA074.AonDmPrd.dbo.Element_Staging

WHERE DateUpdated > @DTS_Dt AND E_INA07<>''

_____________
Code for TallyGenerator

Kaushik Majumder Ten Centuries Points: 1009 More actions · Answer 4

Thanks Taj, Robert.

I wrote a piece of code as Taj suggested, but I believe Robert is correct, in a sense that in SQL 2000 a deadlock does terminate the process and the execution does not go further, even if I try to trap the @@error value.

Sergiy, Thanks for suggestions. Need some light on what would be the negative impacts of using remote query in subquery.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions Follow · Answer 5

Possibly multiple calls to the remote data source, depending on the evecution plan.

No cardinality estimates available for the query optimiser, hence a large possibility of a bad plan.

There are probably others, those are the ones that came to mind.

Regarding trace flage, 1204 puts the deadlock graph in the error log. 1205 puts a large amount of detail as to what lead to the deadlock in the error log. If you get lots of deadlocks, 1205 can make your error log grow very large. Also can slow things down to log that much info.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sergiy SSC Guru Points: 110209 More actions · Answer 6

When you have 2 remote tables in the same query you copy both tables to the temporary location. You copy entire tables.

Than you process the query not having any indexes on the tables. It takes time.

To make sure you are processing actual data SQL Server locks participating records (in this case - entire tables) for all other processes. If you are trying to update one of those tables within the same transaction SQL Server will not let you do it because original table is locked intil temporary copy is dropped, temporary copy will be dropped after update is completed and update will not be completed until original table is unlocked. Dead end.

_____________
Code for TallyGenerator

Kaushik Majumder Ten Centuries Points: 1009 More actions · Answer 7

Gail,

Thanks for the suggestion, yes, whenever I checked the queryplan from the QA, the remote query always showed 100% cost.

However, I have one doubt. This remote query is invoked from a job, running every 5 minutes. Thus I was just wondering about the impact of repeated use of OPENQUERY/OPENROWSET . Does OPENQUERY/OPENROWSET establishes a new connection to the remote server everytime it is invoked? Will that increase the server overhead? Will remote query with linked server be any better in this particular scenario.

With respect to locating the deadlock prone area, the same is already identified, there is a table which is updated/inserted every moment and are accessed by another process at the same time (the second one is a SELECT query). Incase the second process tries to access the record which is currently inserted/updated, sometimes, the SELECT process is rolled back as the DEADLOCK victim.

Thanks again.

Gail Shaw SSC Guru Points: 1004504 More actions Follow · Answer 8

Thanks for the suggestion, yes, whenever I checked the queryplan from the QA, the remote query always showed 100% cost.

That's normal. Remote queries are intensive. what I meant by bad plan is because SQL can't get statisitcs for the remote table, the optimiser doesn't have any idea how many rows to expect. You'll probably see the estimated and actual rows differ widely.

It may be more efficient to insert the remore data into a temp table, index that and use the temp table in your query. If the remote data is fairly static, then make that a real table and update it as necessary.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kaushik Majumder Ten Centuries Points: 1009 More actions · Answer 9

Thanks Gail,Sergiy

I believe that's the better way to explore.

Ronald San Juan SSCrazy Points: 2937 More actions · Answer 10

Do you need the actual data or the instantaneous data in yopur select statement? Or does it matter at all?

If it doesn't, you may want to look at the NOLOCK option. This allows the SELECT statement to retrieve the current state of the data, even when it is exclusively locked by another transaction.

Ronald San Juan | SQL DBA
ID 710124 ~ Code "Northwind"

Error handling deadlock issue

Cookies on SQLServerCentral