Deadlocks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Deadlocks

Author

Topic

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-04-02 : 07:51:26

Gurus
This problem is killing me.I am getting these deadlocks every now and then ad they are killing my systemI am actually loosing data because of these deadlocks.Does anyone have any idea how do i begin solving it.
Please
Nitin

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-02 : 08:02:16

On Deadlocks there are various articles just look in Book online..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-04-02 : 08:17:29

Thanks for telling.i know this.any other suggestion please
Nitin

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-02 : 08:24:52

well... first figure out where they happen.
then why they happen and finnaly what you need to change so that they don't happen...

give us more info like what you're doing and how do you get deadlocks...

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-02 : 08:52:18

Are you trying to run lots of transaction simulataneous ..

what is the locktype you are using??

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-04-02 : 09:42:19

We have a site running.in certain pages i am receiving this deadlock.in the page the lock type is optimistic.we have certain SPs running.now what do i change?please help
Regards
Nitin

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-02 : 10:43:16

what do the sprocs do?

have you tried lock hints?

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-04-02 : 10:51:45

hi
How can u use these lock hints
Regards
Nitin

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-02 : 10:55:03

BOL -> lock hints

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_1hf7.asp

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-04-02 : 10:58:57

Can i set query wait with sp_configure.
And what should it be.
Nitin

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-02 : 11:06:04

i don't think that's your solution.
look what query wait does:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_5rfy.asp

you need to identify the statements that are the cause of deadlocks and then fix that.
optimize the queries.

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-04-02 : 11:11:51

Now how do i do that?if i identify these statements with trace on ,there are certain select statements.How do i optimize them.I meant how do i start the process.
You have to guide me
Nitin

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-02 : 11:17:31

this is a great artcle on how to find and minimize deadlocks.
http://www.sql-server-performance.com/deadlocks.asp

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-04-02 : 11:35:59

HI
Can i have your mail id?i wnt disturb you much
Regards

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-02 : 17:05:03

quote:
Originally posted by nitin1353

HI
Can i have your mail id?i wnt disturb you much
Regards

Is that;
Can i have your mail id?i want disturb you much
or
Can i have your mail id?i wont disturb you much

You realy should try to find the root cause of the deadlocks.
Optimize the queries, and keep transactions short.
Maybe you are missing some indexes on the tables,
find out which sprocs are causing the trouble, and optimize those in query analyzer. Also check if your client app is starting the transactions and let them hang for a long time before committing.

rockmoose

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-04-04 : 20:06:16

Wherever possible you should update tables in the same order in each transaction. Deadlocks are A waiting for B waiting for C waiting for A. You can remove virtually all deadlocks by doing that. Short transactions etc are fine and to be encouraged but they just narrow the window of opportunity they don't actually solve the problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources