<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
Deadlocks
48 posts, Page 2 of 5
««
1
2
3
4
5
»
»»
Deadlocks
Rate Topic
Display Mode
Topic Options
Author
Message
MannySingh
MannySingh
Posted Wednesday, July 09, 2008 8:04 AM
SSChasing Mays
Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:10 PM
Points: 646,
Visits: 729
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
Post #530839
Jeff Moden
Jeff Moden
Posted Wednesday, July 09, 2008 8:39 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 32,910,
Visits: 26,802
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #530876
GilaMonster
GilaMonster
Posted Wednesday, July 09, 2008 10:21 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 37,739,
Visits: 30,014
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 2008, MVP
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
Post #531031
MannySingh
MannySingh
Posted Wednesday, July 09, 2008 11:00 AM
SSChasing Mays
Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:10 PM
Points: 646,
Visits: 729
Yes I ment was Blocked not DEADLOCK.
Yes again i agree with GILA.
Maninder
www.dbanation.com
Post #531080
tedo
tedo
Posted Wednesday, July 09, 2008 7:48 PM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Friday, March 22, 2013 1:27 AM
Points: 568,
Visits: 494
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
Post #531323
Jeff Moden
Jeff Moden
Posted Wednesday, July 09, 2008 9:03 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 32,910,
Visits: 26,802
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. ;)
Worry about deadlocks
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #531335
vikkin
vikkin
Posted Wednesday, July 09, 2008 11:29 PM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, March 03, 2009 6:57 AM
Points: 1,
Visits: 1
Hi Where can I find this in SQL Sever, I ma having endless issues of Deadlocks
Post #531358
GilaMonster
GilaMonster
Posted Wednesday, July 09, 2008 11:39 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 37,739,
Visits: 30,014
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 2008, MVP
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
Post #531362
er.kalidass
er.kalidass
Posted Wednesday, July 09, 2008 11:39 PM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, June 20, 2012 12:37 AM
Points: 201,
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.
Post #531363
GilaMonster
GilaMonster
Posted Wednesday, July 09, 2008 11:47 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 37,739,
Visits: 30,014
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 2008, MVP
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
Post #531369
« Prev Topic
|
Next Topic »
48 posts, Page 2 of 5
««
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
