March 4, 2009 at 2:32 am
Hello All,
Please direct me in the right way:
Me really confused when my colleague raised me this doubt, its been haunting me for sometime now:D
Lets say I have 2 processes P1 and P2.
Now, P1 completes 75% of its process but uses less resources from the server and P2 completes 50%of its process but uses more resources from the server and then dead lock occurs.
Which one will be the victim for the dead lock agent?
Is it
P1 because it uses less resources will be easier to release them for SQL server OR not P1 because it completed 75% of its process
and
P2 because it completed 50% of its process or not P2 because its using more resources and it will be harder for server to release resources.
As what I understood It should be P2 because it uses less resources and will be easier for SQL server to release them but according to my colleague its P1.
Am I missing something and you say I should dig more into this concept?
March 4, 2009 at 6:40 am
Krishna (3/4/2009)
Which one will be the victim for the dead lock agent?
Who knows?
The victim is typically the process that is cheaper to roll back. It has nothing to do with how far through its processing it is, or how many resources it's currently using, the key is how long and how many resources will it take to undo what it's currently done.
A process that's done a couple hundred massive select statements and one single row update will take less to roll back than one that's done a single 10 row update.
If one of them has a deadlock priority set to low, then that will be the one rolled back
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
March 4, 2009 at 7:17 am
GilaMonster (3/4/2009)
Krishna (3/4/2009)
Which one will be the victim for the dead lock agent?Who knows?
The victim is typically the process that is cheaper to roll back. It has nothing to do with how far through its processing it is, or how many resources it's currently using,the key is how long and how many resources will it take to undo what it's currently done.
If one of them has a deadlock priority set to low, then that will be the one rolled back
thanks for the reply Gail, i got the key so server will calculate the minimum resources it will take to roll back a process rather than looking at how much a process is consuming resources, isnt it? I should dig more into the concept. 🙂
March 4, 2009 at 7:31 am
There's not much written about it anywhere that I've found, (other than in BoL) and I've been told that it can change between versions.
If you want to control which is the victim, set the deadlock priority to low.
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
March 4, 2009 at 7:37 am
Yepp, that's true even i was not able to find much information anywhere except discussing more as a general deadlock concept.
I was asked this question and we had a bit of argument, as you know my answer was the process which uses fewer resources from the server than the one which uses more resources.
I take it I would have to give a test and will post. Thanks for your time:)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply