November 10, 2011 at 5:11 pm
Can anyone point me at some good resources for learning how to track down deadlocks? Must be some standard practices to tracking them down like Profiler sessions.
Thanks.
.
November 10, 2011 at 5:49 pm
DBCC TRACEON (1222,-1)
That will output the deadlock graphic in the sql error logs.
You could do a serverside trace that tracks the deadlocks as well.
Both work, but you can save the trace to a table and do more complexe filtering there so that would be my choice if I were tracking down a problem.
Other than that google is your friend for good content!
November 10, 2011 at 11:58 pm
To trace deadlock events, add the Deadlock graph event class to a trace. This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock. SQL Server Profiler can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio. You can configure SQL Server Profiler to extract Deadlock graph events to a single file that contains all Deadlock graph events, or to separate files.
Analyzing Deadlocks with SQL Server Profiler
November 11, 2011 at 1:11 am
Try using the script to automate when the deadlock happens ....
--== This is for SQL 2005 and higher. ==--
--== We will create a temporary table to hold the error log detail. ==--
--== Before we create the temporary table, we make sure it does not already exist. ==--
IF OBJECT_ID('tempdb.dbo.ErrorLog') IS Not Null
BEGIN
DROP TABLE tempdb.dbo.ErrorLog
END
--== We have checked for the existence of the temporary table and dropped it if it was there. ==--
--== Now, we can create the table called tempdb.dbo.ErrorLog ==--
CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL,
logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX))
--== We create a 3 column table to hold the contents of the SQL Server Error log. ==--
--== Then we insert the actual data from the Error log into our newly created table. ==--
INSERT INTO tempdb.dbo.ErrorLog
EXEC master.dbo.sp_readerrorlog
--== With our table created and populated, we can now use the info inside of it. ==--
BEGIN
--== Set a variable to get our instance name. ==--
--== We do this so the email we receive makes more sense. ==--
declare @servername nvarchar(150)
set @servername = @@servername
--== We set another variable to create a subject line for the email. ==--
declare @mysubject nvarchar(200)
set @mysubject = 'Deadlock event notification on server '+@servername+'.'
--== Now we will prepare and send the email. Change the email address to suite your environment. ==--
EXEC msdb.dbo.sp_send_dbmail @recipients='vishal.nadpurohit@us.pwc.com',
@profile_name='pwc_db_mail',
@subject = @mysubject,
@body = 'Deadlock has occurred. View attachment to see the deadlock info',
@query = 'select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''%Deadlock encountered%'' order by Id DESC)',
@query_result_width = 600,
@attach_query_result_as_file = 1
END
--== Clean up our process by dropping our temporary table. ==--
DROP TABLE tempdb.dbo.ErrorLog
November 11, 2011 at 2:12 am
If you're up to buying something, SQL Server MVP Deep Dives 1 has a chapter on reading deadlock graphs.
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
November 11, 2011 at 2:25 am
I have a situation at the moment with deadlocks which have just started happening with a very minor change to a stored proc.
Inside the proc, the first thing it does is insert a row into a table with getdate()
CREATE TABLE logtimes
(
ID INT IDENTITY,
StartTime DATETIME,
EndTime DATETIME,
Type CHAR(2)
)
1st part of proc
DECLARE @ID INT
INSERT INTO logtimes VALUES (Getdate(),null,'CA')
SET @ID = @@IDENTITY
It then goes and does the main bit of the proc and returns the results, then the last part is to set the EndTime where ID = @ID
UPDATE logtimes SET EndTime = Getdate() WHERE ID = @ID
All was good until yesterday where we added a new parameter to the proc and changed the underlying main table with a new column
Now we are experiencing deadlocks on the UPDATE statement
I tried putting in ROWLOCK and UPDLOCK into the UPDATE statement, which seemed to have solved it for a few minutes atleast until I got another error in the errorlog
UPDATE logtimes WITH(UPDLOCK, ROWLOCK) SET EndTime = Getdate() WHERE ID = @ID
Now I am stumped, the only thing I can think of is that for some reason @@IDENTITY is giving the same ID to the variable if it is run simultainiously.
Any ideas guys
This is the trace dump
deadlock-list
deadlock victim=process72f2bc8
process-list
process id=process72f2bc8 taskpriority=0 logused=0 waitresource=OBJECT: 5:1183759720:0 waittime=3044 ownerId=6286382509 transactionname=UPDATE lasttranstarted=2011-11-11T09:03:28.753 XDES=0x13ca743b0 lockMode=X schedulerid=3 kpid=2552 status=suspended spid=90 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-11-11T09:03:28.620 lastbatchcompleted=2011-11-11T09:03:28.620 clientapp=web1.domain.com hostname=378024-WEB1 hostpid=5392 loginname=domain-website isolationlevel=serializable (4) xactid=6286382509 currentdb=5 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
executionStack
frame procname=domain.search.VehicleSimpleDynamic line=262 stmtstart=27316 sqlhandle=0x030005002affb96632b89000989f00000100000000000000
update ncdba.logsearchtimes WITH (UPDLOCK, ROWLOCK) set endtime = getdate() where id = @id
inputbuf
Proc [Database Id = 5 Object Id = 1723465514]
process id=process72f3b88 taskpriority=0 logused=0 waitresource=OBJECT: 5:1183759720:0 waittime=3044 ownerId=6286382576 transactionname=UPDATE lasttranstarted=2011-11-11T09:03:28.757 XDES=0x1499fa3b0 lockMode=X schedulerid=3 kpid=4680 status=suspended spid=85 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-11-11T09:03:28.183 lastbatchcompleted=2011-11-11T09:03:28.183 clientapp=web1.domain.com hostname=378025-WEB2 hostpid=5340 loginname=domain-website isolationlevel=serializable (4) xactid=6286382576 currentdb=5 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
executionStack
frame procname=domain.search.VehicleSimpleDynamic line=262 stmtstart=27316 sqlhandle=0x030005002affb96632b89000989f00000100000000000000
update logtimes WITH (UPDLOCK, ROWLOCK) set endtime = getdate() where id = @id
inputbuf
Proc [Database Id = 5 Object Id = 1723465514]
resource-list
objectlock lockPartition=0 objid=1183759720 subresource=FULL dbid=5 objectname=domain.ncdba.LogSearchTimes id=lock1df620400 mode=IX associatedObjectId=1183759720
owner-list
owner id=process72f3b88 mode=IX
waiter-list
waiter id=process72f2bc8 mode=X requestType=convert
objectlock lockPartition=0 objid=1183759720 subresource=FULL dbid=5 objectname=domain.ncdba.LogSearchTimes id=lock1df620400 mode=IX associatedObjectId=1183759720
owner-list
owner id=process72f2bc8 mode=IX
waiter-list
waiter id=process72f3b88 mode=X requestType=convert
November 11, 2011 at 2:27 am
anthony.green (11/11/2011)
I have a situation at the moment with deadlocks which have just started happening with a very minor change to a stored proc.
Could you start a new thread for this please, rather than hijacking someone else's thread?
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
November 11, 2011 at 2:32 am
November 11, 2011 at 11:57 am
Thanks all for the great info. Sounds like I need to educate myself on deadlock graphs. I just happen to have the SQL Server MVP Deep Dives book on my shelf.
.
November 11, 2011 at 12:34 pm
BSavoie (11/11/2011)
Thanks all for the great info. Sounds like I need to educate myself on deadlock graphs. I just happen to have the SQL Server MVP Deep Dives book on my shelf.
Chapter 42.
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
November 11, 2011 at 11:45 pm
Wow, thank you Gail. Chapter 42 is the clearest, most straight forward explanation I've ever seen regarding deadlocks. I would highly recommend it to anyone looking to deal with deadlocks. Now I can pick up where our alleged dba's left off! 🙂
.
November 12, 2011 at 1:47 am
BSavoie (11/11/2011)
Wow, thank you Gail. Chapter 42 is the clearest, most straight forward explanation I've ever seen regarding deadlocks. I would highly recommend it to anyone looking to deal with deadlocks.
Thank you. Good to hear.
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
November 12, 2011 at 9:28 pm
I wanted to practice this scenario a time or two so I setup some tsql to cause a deadlock, and sure enough, it will cause a deadlock every time. I'm having trouble figuring out how to find the deadlock in the Profiler trace. Doesn't seem to be there. I'm using two different connections in ssms to cause the deadlock, and I turn on trace flag 1222 in each connection. I sure can't find anything in the profiler trace that looks like a deadlock graph. I do find what looks like a deadlock graph in the sql log, but there is no "process list". I do find a "resource list" though.
Here are the two scripts I'm using to cause a deadlock:
DBCC traceon(1222, -1)
BEGIN TRANSACTION
UPDATE ComponentsMaint SET MaintCompleted = 0
WAITFOR DELAY '0:0:30'
UPDATE Components SET NeedsMaint = 1
COMMIT TRANSACTION
DBCC traceon(1222, -1)
BEGIN TRANSACTION
UPDATE Components SET NeedsMaint = 1
WAITFOR DELAY '0:0:30'
UPDATE ComponentsMaint SET MaintCompleted = 0
COMMIT TRANSACTION
I found another article on deadlocks. It recommended turning on a couple more flags. This seems to have added the log entries I was expecting in the sql log, still not seeing much in the profiler trace though.
Now I'm turning on these:DBCC TRACEON (3605,1204,1222,-1)
Also, I'm running sql 2008 r2 locally on my dev machine if that matters.
Never mind. I found the setting to turn on deadlock graphs in sql profiler. Working perfectly now. If anyone else is having this problem, be sure to click the "show all events" on the trace properties dialog. It's under the locks category.
.
November 13, 2011 at 6:25 am
traceon activate the trace flag.
1222 is the flag number
-1 means global to all connections. So when you do that EVERYBODY on the server is affected.
This is what we need here, but you must keep that in mind if / when you play with other trace flags in the future.
November 13, 2011 at 10:46 am
BSavoie (11/12/2011)
Now I'm turning on these:DBCC TRACEON (3605,1204,1222,-1)
No need for 3605. That just redirects certain output (like DBCC page) to the error log. Nothing to do with deadlocks.
Don't enable 1204 and 1222. Pick one. Having them both on leads to a confusing mess in the error log as you get two deadlock outputs mixed together. 1204 is the SQL 2000 and earlier traceflag. 1222 was added in 2005 and produces a much more readable and detailed deadlock graph
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
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply