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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 🙁
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
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