SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deadlocks


Deadlocks

Author
Message
MannySingh
MannySingh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2941 Visits: 787
Adding to it:
sometimes, the Deadlocks might resolve by themselves in time, but if you kill a long waiting deadlock, you might end up with a Phantom Process/lock and it stays there, unless you have the option to restart the SQL Service.

Maninder
www.dbanation.com
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210109 Visits: 41973
I believe you're thinking of blocking.... deadlocks do not resolve themselves in a nice manner... there is always a "victim" that get's rolled back... always.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218875 Visits: 46279
Mani Singh (7/9/2008)
Adding to it:
sometimes, the Deadlocks might resolve by themselves in time, but if you kill a long waiting deadlock, you might end up with a Phantom Process/lock and it stays there, unless you have the option to restart the SQL Service.


For the third or fourth time, No! As I said earlier in this thread

You don't ever have to worry about killing processes involved in a deadlock. SQL has a deadlock detector built in, if it detects an unresolvable locking condition (a deadlock) it will pick one of the processes involved and automatically kill it.


The definition of a deadlock is an unresolvable locking condition. Hence left alone they will never resolve themselves. That said, you will almost never be able to kill a process involved in a deadlock. The SQL deadlock detector is a lot faster than you are.

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


MannySingh
MannySingh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2941 Visits: 787
Yes I ment was Blocked not DEADLOCK.
Yes again i agree with GILA.

Maninder
www.dbanation.com
tedo
tedo
SSC Eights!
SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)

Group: General Forum Members
Points: 926 Visits: 523
Yes it is possible to have no deadlocks, however, in this day and age most code is written badly, however, if you do get deadlocks there is no need to worry as the system will detect this and kill one of the processes.

Terry
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210109 Visits: 41973
terry.jago (7/9/2008)
Yes it is possible to have no deadlocks, however, in this day and age most code is written badly, however, if you do get deadlocks there is no need to worry as the system will detect this and kill one of the processes.

Terry


...which also causes either performance loss due to the inherent rollback or dataloss because something didn't happen right, or both. That's why I'm always busting chops about doing it right the first time or take 6 times longer to find it and fix it later. Wink

Worry about deadlocks w00t Shoot for zero deadlocks and high performance scalable code or find a new profession (NOT directed at you, Terry!).

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
vikkin
vikkin
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Hi Where can I find this in SQL Sever, I ma having endless issues of Deadlocks
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218875 Visits: 46279
vikkin (7/9/2008)
Hi Where can I find this in SQL Sever, I ma having endless issues of Deadlocks


Where can you find what?

To trace the source of deadlocks. switch traceflag 1204 or 1222 (SQL 2005 only) on. With one of those traceflags on, SQL writes out the deadlock graph into the error log. There's enough info in the deadlock graph to trace the source of the deadlock on both sides. That should give you a good idea where to start fixing.

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


er.kalidass
er.kalidass
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 133
Hi guys
Try with snapshot isolation levels. it will reduce the db blocking Massively ., bu it will require large tempDB space since it is taking the snapshot of physical data to tempdb.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218875 Visits: 46279
terry.jago (7/9/2008)
Yes it is possible to have no deadlocks, however, in this day and age most code is written badly, however, if you do get deadlocks there is no need to worry as the system will detect this and kill one of the processes.


That, IMHO, is the height of laziness. "It's broken, but don't worry about fixing it."

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search