Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tracing Deadlocks


Tracing Deadlocks

Author
Message
sivakumar-135209
sivakumar-135209
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 11
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/skumar/tracingde
jean b
jean b
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

DONT WORKED!!

I just try it with the scripts bellow:

DBCC TRACEON (3604)
DBCC TRACEON (1204)

GO

begin transaction
select top 1000000 cod_p from p (tablockx)

select top 1000000 p.cod_p,d.cod_d
from p (tablockx)
join d (tablockx) on d.cod_d=p.cod_p

commit

DBCC TRACEON (3604)
DBCC TRACEON (1204)

GO

begin transaction
select top 1000000 cod_d from d (tablockx)

select top 1000000 p.cod_p,d.cod_d
from p (tablockx)
join d (tablockx) on d.cod_d=p.cod_p

commit

I opnend 2 querys at query analyzer. I succeded to create a deadlock but its dont writed to error log.

Jean, i need some help at lock timeout and deadlock.


Doctor Who
Doctor Who
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 1

I've got to ask a follow up question. I am not certain when I should execute the

DBCC TRACEON(3604)
DBCC TRACEON(1294)

commands. For example, I've got a situation in which a deadlock occurs, but maybe only once every three or four weeks. Do I just go into Query Analyzer, execute those commands now, and wait a few weeks until the problem shows up? Or do I only execute those commands while the deadlock is in process?




Doctor Who
rsherron
rsherron
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 82

This did the trick for me. I had an instance where I was getting row level dead lock and needed to know how to get the table name based on the informaiton in the RID print out. This lead me to the table I felt was causing the problem.

THANK YOU!!!!!!!!!!!!!!!!





adrshen
adrshen
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 68

thats is really excellent, but i would like to ask a question that its only track the information SPID (Server Process ID), how one can locate that this SPID was running which one query or stored procedure.because if SPID 65 generate the deadlock than , information track is only SPID and delete and after some time this SPID is assgned to other one

waiting for a respose


adrshen
adrshen
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 68

thats is really excellent, but i would like to ask a question that its only track the information SPID (Server Process ID), how one can locate that this SPID was running which one query or stored procedure.because if SPID 65 generate the deadlock than , information track is only SPID and delete and after some time this SPID is assgned to other one

waiting for a respnose


sthornton
sthornton
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 1
Once you have executed these commands, the error log starts filling up, so you may want to keep an eye on it from time to time and turn it off if its size is worrying you.

You may want to turn it on for a while, and check the logs for "Deadlock encountered.... Printing deadlock information", then turn it off.

To the Author:

I'd like to know once you have identified what tables etc are part of the problem, how do you go about solving the problem in the first place?



Caroline_b
Caroline_b
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 1

I suppose I'm starting to develop a mild if somewhat exasperated affection for SQL Server, but then something like this comes along and annoys me all over again. It's ludicrous to have to go to this amount of trouble to diagnose something as straightforward as a deadlock. On DB2 on the mainframe (what a lot of your readers would regard as 'legacy') the thread which gets chucked out receives an error code (a -911 if my memory serves me correctly) and my all-time favourite error message: "This agent has been selected as the victim of a deadlock", which sounds like an instruction from the CIA But no diagnosis is necessary, nor should it be.

As for how to fix it, it's caused by 2 processes accessing and locking (exclusively) the same data in a different order:

Process 1 has A and is waiting for B. Process 2 has B and is waiting for A. And there they will sit until kingdom come unless the system selects one to kill.

A few things to try:

reduce your lock size - table to page, page to row, and force it to stay there. This has CPU and memory implications, so be careful.

Change your clustering sequence on one of the tables involved (again, look at the bigger picture first).

Ideally, design your apps to access things in the same order. A lot of deadlocks are just the result of bad and inconsistent coding.

If you have a real database hotspot, like a maximum key which gets both read and updated, try adding a very long field to the table, or a very high fill factor, so you only get one row per page. If you are deadlocking at row level, rethink your design.

Dinosaur tricks, but they might work.

good luck!

Caroline


anoel
anoel
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 50

DBCC TRACEON (3604) & DBCC TRACEON (1204) doesn't seem to work on SQL 2005.....I executed both, in that order on the server and watched the logs only to discover users were, in fact, receiving deadlock errors yet nothing was written to the log??

Any suggestions?






VD
VD
Old Hand
Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)

Group: General Forum Members
Points: 301 Visits: 1195

for SQL 2005 run

DBCC TRACEON (1204, -1)
DBCC TRACEON (1222, -1)

-- to check the status
DBCC TRACESTATUS(-1)

for more information please check BOL.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2ed3e3e7-5080-4fa3-b79a-585470602bc2.htm





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