DeadLock

Question

DeadLock

kumar99ms

SSCommitted

Points: 1584
More actions
March 16, 2008 at 1:26 am

#204307

Hi Friends,
In a query Dead lock,Bloking accours
How can we find out This query occurs Dead lock, Blocking
can you any body plz explain me
T&r
Ms

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

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

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

Are you trying to find deadlocks or blocking? They're fairly different things.

To find blocking, you can use the sys.dm_exec_requests or sys.dm_os_waiting_tasks DMVs. Both have a column that will show if a task is blocked by another.

Blocking is generally transitory and will go away in time. Deadlocks do not resolve by themselves, and hence SQL will kill one of the processes involved in the deadlock. This will cause an error message "Your process was involved in a deadlock and selected as the deadlock victim"

One of the easier ways to find deadlocks is to enable traceflag 1204 (DBCC TRACEON (1204,-1)) which will write the deadlock graphs into the SQL error log.

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

kumar99ms SSCommitted Points: 1584 More actions · Answer 2

Thanku for response

could plz tel me is there another way to find out dead lock

How can i rectify this deadlock

Which situvastion it will occure

Blocking and dead lock which situvastion it will occur plz tel me

or give some good links in this example with good explanation

Best regards

ms

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 3

This is a good link on deadlocks:

http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

Poor indexing, unnecessarily long transactions are the 2 major culprits of persistent deadlocks.

Generally, deadlocks are *OK* if they do not occur too frequently. You will want to focus in on those that happen on a regular basis, as those signal a problem with your app.

You can also insert some defensive TRY/CATCH logic into your code to repeat an operation several times if it's victimized in a deadlock.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

You can also use profiler to detect deadlocks, but it requires running a constant trsace, which I prefer not doing.

Fixing deadlocks is a pretty wide topic. Generally bad code is the main cause of deadlocks, with poor indexing a close second. Generally, you need to find the code causing the deadlocks, then work through why the deadlocks occur. There isn't really a magic bullet fix.

Blocking occurs when one process has a lock on a resource that another process want to use. The second process has to wait for the first to finish. Not a bad thing, unless you have lots of long-term blocks occurring.

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

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 5

kumar99ms (3/16/2008)
Thanku for response
could plz tel me is there another way to find out dead lock
How can i rectify this deadlock
Which situvastion it will occure
Blocking and dead lock which situvastion it will occur plz tel me
or give some good links in this example with good explanation
Best regards
ms

As you've seen, there are plenty of ways... but the absolute best way is to do as first suggested... turn on trace flag 1204 when the server starts... there is no better way. To wit, trace flag 1204 should be turned on all the time, anyway. There is no easy fix... but trace flat 1204 is the first step to making it easier.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rstone SSCertifiable Points: 6209 More actions · Answer 6

Hi. Since you are using SQL Server 2005, you can use Notification Events and Service Broker. You don't have to do all the wiring yourself if you define a WMI alert for a deadlock. See http://technet.microsoft.com/en-us/library/ms186385.aspx

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 7

Trace flag 1222 is new to SQL Server 2005 and gives XML-like output, which is much easier to manipulate programmatically, should one be interested in producing a report.

I don't think I'm alone in feeling that the old T1204 input was monstrously ugly and a pain to work with!... 🙂

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 8

Marios Philippopoulos (3/17/2008)
Trace flag 1222 is new to SQL Server 2005 and gives XML-like output, which is much easier to manipulate programmatically, should one be interested in producing a report.
I don't think I'm alone in feeling that the old T1204 input was monstrously ugly and a pain to work with!... 🙂

Perfect... I only installed 2k5 a bit ago and I'm still learning some of these improvements... thanks.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 9

I haven't used the new flag much either.

A while ago I wrote an app that went and parsed the ERRORLOG text file extracting deadlock info into a database for reporting.

Ugly!... And to think I did that for fun! 🙂

It's in my TO-DO list to do the same on the XML version of the deadlock info in ERRORLOG, using the new XML toys SQL 2005 ships with.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 10

Marios Philippopoulos (3/17/2008)
And to think I did that for fun! 🙂

I miss the old brute force days... they were much more "fun" 🙂

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

I'm so used to the output of 1204 that it's not a challenge to read anymore. Could be that this is not a good thing.

Is the output of 1222 the same as profiler captures for a deadlock graph? Is it written into the error log or a separate file?

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

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 12

GilaMonster (3/17/2008)
I'm so used to the output of 1204 that it's not a challenge to read anymore. Could be that this is not a good thing.
Is the output of 1222 the same as profiler captures for a deadlock graph? Is it written into the error log or a separate file?

Yes, the T1222 output is also written into the error log, and I think it's the same as that captured in the deadlock graph.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 13

Here is a little tool http://www.sqlsolutions.com i downloaded tried it out its very easy and 2 minutes set up the deadlock and you do not have to worry about anything.

I have another script that sends an email to me but personally the one above is nice and easy to use and graphically.

DBA-640728 SSChampion Points: 12898 More actions · Answer 14

can you please post your script that sends emails?