How to resolve and avoid deadlock

Question

How to resolve and avoid deadlock

satish-348625

SSC Enthusiast

Points: 194
More actions
November 22, 2007 at 11:18 pm

#179405

Hi ,
Recently I was asked in an interview how to resolve and avoid deadlock.
I know regarding sysprocesses,syslocks,sp_lock and other stuffs.
Basically most of the sites say that in deadlock condition one of the process becomes the deadlock victim and is killed.
But in case both are equally impt then we need to have transactions short as possible.
But suppose mistake has been made by developers and normally it is recommended not to kill the process then how can we avoid such deadlock situation?
Hope this topic will benefit me and others too
Satish

Viewing 3 posts - 1 through 3 (of 3 total)

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

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

There's no need to kill processes manually in a deadlock. When SQL picks up that a deadlock has occured, one of the processes is selected as the deadlock victim and that process is terminated by SQL server and its work rolled back. There is no way to prevent that from occuring.

As for your interview question, if I was asking that question in an interview, I'd be looking for ways to avoid deadlocks occuring (since once they occur, SQL resolves them automatically)

To got details of the deadlock, switch traceflag 1204 on. Any time a deadlock occurs, the deadlock graph, including all the details on the contended resource and what the two processes were doing is written to the SQL error log. This really helps in finding the cases of the deadlock.

Keep transcations as small as possible. Do no unneccessary work in a transaction. Never begin a transaction then wait for user input. Ensure that queries within the transaction are as efficient as possible.

Access objects in the same order. If one proc updates tbl1 first, then tbl2 after, all other procs that use those two should use them in the same order. Done properly, this can prevent deadlocks completely.

Hope that helps

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

satish-348625 SSC Enthusiast Points: 194 More actions · Answer 2

Thank you Mr Gail for your efforts and suggestions.

But I am aware of DBCC TRACEON(1204,1807) Flags for monitoring and resolving deadlock.

Is there any thing apart from this that has to be covered.

I would appreciate if u all can contribute ur experiences. I am less expereinced than u guyz so plz help me

Satish