September 1, 2009 at 3:52 pm
1) Can you let me know the process: how SQL Server internally manage the Deadlock?
2) Is it required to free the deadlock manually?
3) What is the way, SQL Server can handle the Deadlock by itself and can bring back the things into normal form from backend??
Thanks.
September 1, 2009 at 5:30 pm
Sourav,
You will get your answers from the below link
http://msdn.microsoft.com/en-us/library/ms178104.aspx
Same you will get if you search deadlock in book online.
Thanks,
Raj
September 1, 2009 at 5:43 pm
I checked that already. But was looking for some other document containing some real-time scenarios.
Can you please provide one if you are aware of?
Thanks.
September 2, 2009 at 1:17 am
What more are you looking for?
When SQL detects a deadlock it picks one of the processes as the victim, kills that process to release the locks and allows the other processes to carry on running.
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
September 2, 2009 at 1:22 am
Edit: Duplicate post
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
September 2, 2009 at 3:15 am
Sourav (9/1/2009)
1) Can you let me know the process: how SQL Server internally manage the Deadlock?2) Is it required to free the deadlock manually?
3) What is the way, SQL Server can handle the Deadlock by itself and can bring back the things into normal form from backend??
The article mentioned in the other posts in this topic should answer your questions. real life scenarios would not really add anything, unless there is a specific scenario you are looking for.
As mentioned by others, sql server will pick a deadlock victim.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 3, 2009 at 8:03 am
Sourav (9/1/2009)
1) Can you let me know the process: how SQL Server internally manage the Deadlock?2) Is it required to free the deadlock manually?
3) What is the way, SQL Server can handle the Deadlock by itself and can bring back the things into normal form from backend??
I agree with the others that the document has most of the answers you're looking for, but to try to help out a bit:
1) When SQL Server identifies a deadlock situation, it determines which one it thinks will be the least costly to rollback (it's not always right) and rolls back that transaction and generates an error that the process was chosen as a deadlock victim.
2) It all happens automatically behind the scenes. What you have to do manually is figure out the cause of the deadlocks and fix it.
3) It just does. There's no switch you have to flip. Deadlocks are places where serious data integrity loss could occur. SQL Server is built to protect the integrity of data and therefore it stops the deadlock from occurring.
----------------------------------------------------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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply