February 10, 2009 at 9:52 pm
Hi,
we are facing deadlock problem in the SQL server 200 job . while getting the deadlock we want to restart roolback traction.
Can any one please suggest that how we can achive this.
Thanks,
Nivedita.
February 10, 2009 at 10:02 pm
The application needs to trap the error and resubmit the transaction. The deadlock manager kills the connection, so you can't do this in T-SQL.
February 10, 2009 at 10:15 pm
thanks,
But how deadlock manager kills the connection(transaction) . In my job deadlock is occuring within a loop in the Stored procedure,once deadlock occured ,transaction gets roolback, and we wants to restarts the transaction again.could you please suggest how we can achive this.
February 10, 2009 at 10:17 pm
The deadlock manager just picks a connection and kills it. There's no "how", it does it.
You need the client to detect that there was no successful transaction and resubmit it. Reconnect and run the stored procedure again.
February 11, 2009 at 5:43 am
Just a question, it says "SQL Server 200" is the last digit there a "0" or a "5"? If it's 2005, you can use TRY/CATCH to catch the deadlock error yourself within your TSQL code and resubmit the statement that caused the deadlock.
Regardless of version though, you should track down and identify the source of the deadlock. Sometimes it's between two different sessions, but sometimes a single session deadlocks itself. If you're in the latter category, then simply trapping the deadlock isn't going to help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 11, 2009 at 6:37 am
Hi Grant,
Thanks....
IT's SQL Server 2000,
I will look into the deadlock issue ,that whether it is related to single session or not.
February 11, 2009 at 6:46 am
Ah, SQL Server 2000, you need to follow Steve's advice then.
BTW, this is the SQL Server 2005 forum that you posted in. You will get different answers to questions depending on the version of SQL Server you're using. Just so you know.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 11, 2009 at 6:56 am
What's the exact error that you're getting?
Turn traceflag 1204 on and the deadlock detector will write a deadlock graph into the error logwhenever it finds the deadlock. That graph will show all the sessions involved in the deadlock, what they were running and what objects were involved.
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
February 11, 2009 at 7:08 am
Moved to 2000 forum. Was too tired to do it last night 🙁
February 11, 2009 at 9:03 am
why not trying to trap the code in the profiler by defining the deadlock event type
by this way you will get the specific sp that cause the deadlock error
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply