can we have any alternative to remove deadlock apart from killing the process???

Question

Post reply

can we have any alternative to remove deadlock apart from killing the process???

abhi4u86

SSC Veteran

Points: 251
More actions
May 11, 2009 at 6:41 am

#209380

Hi all,

can anyone tell me if we have any alternative to remove deadlock apart from killing the blocking process???

Regards,

Abhishek

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Are you talking about deadlocks or are you talking about blocking? With deadlocks, SQL will automatically find the involved processes, pick one and kill it. You don't have to do anything.

Are you seeing the error 1205: Process X was deadlocked on a resource with another process and was selected as the deadlock victim. Rerun the transaction

Preventing both blocking and deadlocks often boils down to much the same thing. Optimised queries, useful indexes.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abhi4u86 SSC Veteran Points: 251 More actions · Answer 2

abhi4u86

SSC Veteran

Points: 251

May 11, 2009 at 7:17 am

#992086

Hi Gail,

i think it is blocking,

In the activity monitor if any process is blocking other process then is it the only way to kill that blocking process to remove blocking??? or if u know then plz tell me anything apart from this we can do.

regards,

abhishek

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

That's blocking, not deadlocks. Different problems.

You can kill the connection causing the block, but before you do, find out what it is. If it's a critical process that must not fail, killing it's not a good idea. Other than killing the connection, you could wait for it to finish what it's doing.

Long term fix, find what queries frequently cause blocking and optimise them so that they're taking fewer locks and running a shorter time.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abhi4u86 SSC Veteran Points: 251 More actions · Answer 4

abhi4u86

SSC Veteran

Points: 251

May 11, 2009 at 8:45 am

#992172

Hi gail,

i have no idea how to optomize query, will u plz help me out.

regards,

abhishek

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

That's a huge topic and there's been a lot written about it. Start with google and see what you find.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

I wrote an article on using the Blocked Process Report on here last year that you might take a look at if you are on SQL 2005/2008.

http://qa.sqlservercentral.com/articles/Blocking/64474/

It will capture blocking processes to let you better analyze them for correction. I also just wrote a tool that is on codeplex for free download to assist with collecting these reports as well.

http://sqlblockedprocessmon.codeplex.com/

maechismo_8514 SSCoach Points: 17510 More actions · Answer 7

abhi4u86 (5/11/2009)

Hi gail,

i have no idea how to optomize query, will u plz help me out.

regards,

abhishek

Start from indexes, check whether the your tables have got proper indexes and also the queries involved in blocking each other and check whether those queries are utilizing the indexes that you have created on the tables.