January 19, 2012 at 12:23 pm
Hi all...
I know to capture the deadlock we use trace flags like 1204 1222 and also by using profiler with Dead lock chain,graph.
But i didnt get ans of How SQl server internally identify it...please give me more details on it.
Regards,
Shivrudra W
January 19, 2012 at 1:52 pm
Shivrudra (1/19/2012)
Hi all...I know to capture the deadlock we use trace flags like 1204 1222 and also by using profiler with Dead lock chain,graph.
But i didnt get ans of How SQl server internally identify it...please give me more details on it.
Microsoft documentation is pretty clear about it, let me quote "A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock."
SQL Server engine knows it all, it has set the locks, it is attempting to set a lock that can't be set, knows the SPIDs involved... not that difficult to realized two (or more processes) are waiting for each other.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 20, 2012 at 5:52 am
There's an internal blocking monitor that observes all blocks and looks for places where that cross-referenced lock exists. When it finds that, it identifies the least costly query by it's estimated cost and chooses that as the victim and initiates a rollback.
By the way, don't use both 1222 and 1204. Just use 1222. Although, if anything, I'd suggest using the extended events xml_deadlock_report event. It's excellent. All the information from traceflag 1222, but in XML format and not in your error log. Great stuff.
"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
January 20, 2012 at 5:59 am
Isn't it better to use 1222 rather than 1204??
January 20, 2012 at 6:04 am
Ninja's_RGR'us (1/20/2012)
Isn't it better to use 1222 rather than 1204??
Oh god. I can't believe I typed that. Yes, 1222 is better. I edited the post. Yikes.
"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
January 20, 2012 at 6:07 am
Grant Fritchey (1/20/2012)
Ninja's_RGR'us (1/20/2012)
Isn't it better to use 1222 rather than 1204??Oh god. I can't believe I typed that. Yes, 1222 is better. I edited the post. Yikes.
I was about to call in Gail and ask her to edit the 20 posts she had given the reverse info. Then I thought better of it :hehe:.
January 20, 2012 at 6:12 am
Ninja's_RGR'us (1/20/2012)
Grant Fritchey (1/20/2012)
Ninja's_RGR'us (1/20/2012)
Isn't it better to use 1222 rather than 1204??Oh god. I can't believe I typed that. Yes, 1222 is better. I edited the post. Yikes.
I was about to call in Gail and ask her to edit the 20 posts she had given the reverse info. Then I thought better of it :hehe:.
Good thought. Those bruises would have taken a while to heal. :w00t:
"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
January 20, 2012 at 6:16 am
Grant Fritchey (1/20/2012)
Ninja's_RGR'us (1/20/2012)
Grant Fritchey (1/20/2012)
Ninja's_RGR'us (1/20/2012)
Isn't it better to use 1222 rather than 1204??Oh god. I can't believe I typed that. Yes, 1222 is better. I edited the post. Yikes.
I was about to call in Gail and ask her to edit the 20 posts she had given the reverse info. Then I thought better of it :hehe:.
Good thought. Those bruises would have taken a while to heal. :w00t:
I live far enough. Not worth to waste 4 days on this, but you on the other hand live much closer to her ;-).
January 20, 2012 at 6:21 am
Grant Fritchey (1/20/2012)
When it finds that, it identifies the least costly query by it's estimated cost and chooses that as the victim and initiates a rollback.
Just one minor clarification, the deadlock monitor identifies the query that will be cheapest to roll back. Nothing to do with the actual cost of the query (optimisation cost), has to do with how much work the query is to undo. In short, it's the query that's generated the least amount of transaction log entries.
A high-cost select will be picked as the victim over a single-row update, because the update requires work to roll back and the select doesn't.
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
January 20, 2012 at 6:25 am
Ninja's_RGR'us (1/20/2012)
I live far enough. Not worth to waste 4 days on this, but you on the other hand live much closer to her ;-).
Much closer? Maybe for certain definitions of much. Quebec and New England are how may thousands of km apart?
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
January 20, 2012 at 6:29 am
GilaMonster (1/20/2012)
Ninja's_RGR'us (1/20/2012)
I live far enough. Not worth to waste 4 days on this, but you on the other hand live much closer to her ;-).Much closer? Maybe for certain definitions of much. Quebec and New England are how may thousands of km apart?
My bad, I thought he was living somewhere in Europe.
I get 2 hits on google maps. One is 250 KM and the other one 2 500 KM.
So to sum this up, we're both safe :hehe:.
January 20, 2012 at 7:27 am
GilaMonster (1/20/2012)
Grant Fritchey (1/20/2012)
When it finds that, it identifies the least costly query by it's estimated cost and chooses that as the victim and initiates a rollback.Just one minor clarification, the deadlock monitor identifies the query that will be cheapest to roll back. Nothing to do with the actual cost of the query (optimisation cost), has to do with how much work the query is to undo. In short, it's the query that's generated the least amount of transaction log entries.
A high-cost select will be picked as the victim over a single-row update, because the update requires work to roll back and the select doesn't.
Really? I thought the query cost was involved... Oops.
"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
January 20, 2012 at 7:28 am
Ninja's_RGR'us (1/20/2012)
GilaMonster (1/20/2012)
Ninja's_RGR'us (1/20/2012)
I live far enough. Not worth to waste 4 days on this, but you on the other hand live much closer to her ;-).Much closer? Maybe for certain definitions of much. Quebec and New England are how may thousands of km apart?
My bad, I thought he was living somewhere in Europe.
I get 2 hits on google maps. One is 250 KM and the other one 2 500 KM.
So to sum this up, we're both safe :hehe:.
We're both relatively safe...
"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
January 20, 2012 at 7:36 am
Grant Fritchey (1/20/2012)
GilaMonster (1/20/2012)
Grant Fritchey (1/20/2012)
When it finds that, it identifies the least costly query by it's estimated cost and chooses that as the victim and initiates a rollback.Just one minor clarification, the deadlock monitor identifies the query that will be cheapest to roll back. Nothing to do with the actual cost of the query (optimisation cost), has to do with how much work the query is to undo. In short, it's the query that's generated the least amount of transaction log entries.
A high-cost select will be picked as the victim over a single-row update, because the update requires work to roll back and the select doesn't.
Really? I thought the query cost was involved... Oops.
No, it's solely the work required to undo the victim's transaction. Any CPU or IO from the query's execution cost has already been spent, the concern is solely how quickly can the victim's work be undone, because until that rollback is complete the locks can't be released and the other query still has to sit and wait
Edit: Well, that and the deadlock priority and maybe a couple other things.
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
January 20, 2012 at 7:42 am
GilaMonster (1/20/2012)
Grant Fritchey (1/20/2012)
GilaMonster (1/20/2012)
Grant Fritchey (1/20/2012)
When it finds that, it identifies the least costly query by it's estimated cost and chooses that as the victim and initiates a rollback.Just one minor clarification, the deadlock monitor identifies the query that will be cheapest to roll back. Nothing to do with the actual cost of the query (optimisation cost), has to do with how much work the query is to undo. In short, it's the query that's generated the least amount of transaction log entries.
A high-cost select will be picked as the victim over a single-row update, because the update requires work to roll back and the select doesn't.
Really? I thought the query cost was involved... Oops.
No, it's solely the work required to undo the victim's transaction. Any CPU or IO from the query's execution cost has already been spent, the concern is solely how quickly can the victim's work be undone, because until that rollback is complete the locks can't be released and the other query still has to sit and wait
Edit: Well, that and the deadlock priority and maybe a couple other things.
Crud. That means Joe is going to be savaging another chapter... oh well.
"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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply