DeadLock

Question

Post reply

DeadLock

Viewing post 16 (of 15 total)

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

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

--Some lady helped me work on this which is one of MS threads so credit to her also.

--This script creates a job and a alert for reporting deadlocks

--Change SystemADM to your operator name accordingly (My email operatar is SYSTEMADM)

--First go to SQL Agent and make sure you select Altert System then Click in TOKEN REPLACEMENT

REPLACE TOKENS FOR ALL JOBS

--MAIL SESSION MAKE SURE ENABLE MAIL PROFILE SELECTED

--This will send you an email alert when deadlock occurs

Would suggest create yourself some database of your own for Custom System Code you have

Do not put in master or msdb make your own Database.

------------------PART ONE-------------------

For part one do the following changes

---Find this statement and change SystemADM to your operator

-------SET @SQLOperator = N'SystemADM'

--Get the servername and change this line

--SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\YOURSERVERNAME'

--Easiest way is to right click on Alerts and choose NEW alert and select WMI -

--This path should be same as above

---Change the code below accordingly

USE [somedatabasename ] '-----CHANGE HERE

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DeadlockEvents]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE [dbo].[DeadlockEvents]

GO

CREATE TABLE [dbo].[DeadlockEvents] (

[AlertTime] [datetime] NOT NULL ,

[DeadlockGraph] [xml],

[RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL

) ON [PRIMARY]

GO

CREATE INDEX [DeadlockEvents_IDX01] ON [dbo].[DeadlockEvents]([AlertTime]) WITH FILLFACTOR = 100 ON [PRIMARY]

GO

USE [msdb]

GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture Deadlock Graph')

EXEC msdb.dbo.sp_delete_job @job_name = N'Capture Deadlock Graph', @delete_unused_schedule=1

GO

DECLARE @ServiceAccount varchar(128)

SET @ServiceAccount = N'sa'

DECLARE @SQLOperator varchar(128)

SET @SQLOperator = N'SystemADM' ---CHANGE HERE TO YOUR OPERATOR

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Capture Deadlock Graph',

@enabled=1,

@notify_level_eventlog=2,

@notify_level_email=3,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'Job for responding to DEADLOCK_GRAPH events',

@category_name=N'[Uncategorized (Local)]',

@owner_login_name=@ServiceAccount,

@notify_email_operator_name=@SQLOperator, @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Insert graph into LogEvents] Script Date: 12/18/2007 17:06:34 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert graph into LogEvents',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'

INSERT INTO DeadlockEvents (

AlertTime,

DeadlockGraph

)

VALUES (

GETDATE(),

N''$(ESCAPE_NONE(WMI(TextData)))''

)',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

USE [msdb]

GO

IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to DEADLOCK_GRAPH')

EXEC msdb.dbo.sp_delete_alert @name=N'Respond to DEADLOCK_GRAPH'

GO

DECLARE @server_namespace varchar(255)

IF ISNULL(CHARINDEX('\', @@SERVERNAME), 0) > 0

SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + SUBSTRING

---CHANGE LINE ABOVE TO HAVE servername after ServerEvents\ servername\' etc

(@@SERVERNAME, ISNULL(CHARINDEX('\', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))

ELSE

SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\'

EXEC msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH',

@wmi_namespace=@server_namespace,

@wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',

@job_name='Capture Deadlock Graph' ;

GO

---PART ONE FINISH

--Check configured

--Shoud have one alert called Responsed to Deadlock

--One job Capture Deadlock (open this and check that the insert into graphs is inserting this in to

--your databasename ---insert into deadlocks etc.

------------------PART TWO-------------------

--Once you get a deadlock it puts in the table and then you can use this sp to read it

USE [Systemadmin]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetDeadLockInfo]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[sp_GetDeadLockInfo]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_GetDeadLockInfo]

@AlertTimeStart datetime = NULL,

@AlertTimeEnd datetime = NULL

/*

Description Gets deadlock information for selected period.

Used By System Administrators

Pass @AlertTimeStart - Start date & time of period. If NULL, defaults to the earliest record.

@AlertTimeEnd - End date & time of period. If NULL, defaults to the latest record.

Returns resultset

Created 19.12.2007

Author Diane Sithoo

Comments Initial Creation.

Modified 24.01.2008

Author Diane Sithoo

Comments Fix to allow consideration of stored procedure calls as well as direct SQL.

*/

AS

SET NOCOUNT ON

---------------

-- Declarations

---------------

DECLARE @AlertTime datetime

DECLARE @DeadlockGraph xml

DECLARE @RecordID int

-- Deadlock Events table.

DECLARE @DeadlockEvents table (

AlertTime datetime,

DeadlockGraph xml,

RecordID int

) ;

-- Deadlock Info table.

DECLARE @DeadlockInfo table (

AlertTime datetime,

IsVictim bit,

OwnerID varchar(256),

DatabaseName sysname NULL,

ObjectName sysname NULL,

TransactionName sysname NULL,

LastTranStarted datetime NULL,

OwnerSpid int NULL,

ClientApp varchar(256) NULL,

HostName varchar(256) NULL,

LoginName varchar(256) NULL,

IsolationLevel varchar(256) NULL,

ProcName sysname NULL,

InputBuf varchar(MAX) NULL,

DeadlockGraph xml,

DeadlockEventsRecordID int

) ;

-- Get all deadlock events within selected period.

INSERT INTO @DeadlockEvents (

AlertTime,

DeadlockGraph,

RecordID

)

SELECT

AlertTime,

DeadlockGraph,

RecordID

FROM dbo.DeadlockEvents

WHERE

( @AlertTimeStart IS NULL

OR AlertTime >= @AlertTimeStart

)

AND ( @AlertTimeEnd IS NULL

OR AlertTime <= @AlertTimeEnd

);

----------------------------------------------

-- Loop through Events and process information

----------------------------------------------

WHILE EXISTS

( SELECT RecordID

FROM @DeadlockEvents

)

BEGIN

SELECT TOP 1

@AlertTime = AlertTime,

@DeadlockGraph = DeadlockGraph,

@RecordID = RecordID

FROM @DeadlockEvents

ORDER BY RecordID DESC

-- Insert into temporary table for processing.

INSERT INTO @DeadlockInfo (

AlertTime,

IsVictim,

OwnerID,

DatabaseName,

ObjectName,

TransactionName,

LastTranStarted,

OwnerSpid,

ClientApp,

HostName,

LoginName,

IsolationLevel,

ProcName,

InputBuf,

DeadlockGraph,

DeadlockEventsRecordID

)

SELECT

AlertTime = @AlertTime,

IsVictim =

CASE

WHEN deadlock.victim IS NULL THEN 0

ELSE 1

END,

process.owner_id,

databasename = DB_NAME(process.currentdb),

objectname = typeowner.objectname,

process.transactionname,

process.lasttranstarted,

process.spid,

process.clientapp,

process.hostname,

process.loginname,

process.isolationlevel,

process.procname,

CASE

WHEN LTRIM(RTRIM(ISNULL(process.frame, ''))) = '' THEN process.inputbuf

ELSE process.frame

END,

DeadlockGraph = @DeadlockGraph,

DeadlockEventsRecordID = @RecordID

FROM

( SELECT

ref.value('@id', 'varchar(512)') AS owner_id,

ref.value('@transactionname', 'sysname') AS transactionname,

ref.value('@lasttranstarted', 'datetime') AS lasttranstarted,

ref.value('@spid', 'int') AS spid,

ref.value('@clientapp', 'varchar(256)') AS clientapp,

ref.value('@hostname', 'varchar(256)') AS hostname,

ref.value('@loginname', 'varchar(256)') AS loginname,

ref.value('@currentdb', 'varchar(256)') AS currentdb,

ref.value('@isolationlevel', 'varchar(256)') AS isolationlevel,

ref.value('./executionStack[1]/frame[1]/@procname', 'varchar(256)') AS procname,

ref.value('./executionStack[1]/frame[1]', 'varchar(max)') AS frame,

ref.value('./inputbuf[1]', 'varchar(max)') AS inputbuf

FROM @DeadlockGraph.nodes('//process')

AS node(ref)

) AS process

LEFT JOIN

( ( SELECT

ref.value('../../@dbid', 'int') AS dbid,

ref.value('../../@objectname', 'sysname') AS objectname,

ref.value('@id', 'varchar(512)') AS owner_id

FROM @DeadlockGraph.nodes('//resource-list/child::node()/owner-list/owner')

AS node(ref)

)

) AS typeowner

ON typeowner.owner_id = process.owner_id

LEFT JOIN

( SELECT

ref.value('@victim', 'varchar(512)') AS victim

FROM @DeadlockGraph.nodes('//deadlock')

AS node(ref)

) AS deadlock

ON process.owner_id = deadlock.victim

--Remove the processed item.

DELETE FROM @DeadlockEvents

WHERE RecordID = @RecordID

END

--------------------

-- Get deadlock info

--------------------

SELECT *

FROM @DeadlockInfo

ORDER BY DeadlockEventsRecordID DESC, IsVictim DESC, OwnerID

GO

---------------PART THREE------------

--TEST IT

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

Deadlock Troubleshooting, Part 2

In this post I’ll look at an actual deadlock, then troubleshoot it using the steps I described in Deadlock Troubleshooting, Part 1 so you can see them in action. This is a simplified version of a deadlock scenario that an internal customer here at Microsoft called us for help with. To set up the scenario, run this:

-- Batch #1

CREATE DATABASE deadlocktest

GO

USE deadlocktest

SET NOCOUNT ON

DBCC TRACEON (1222, -1)

GO

IF OBJECT_ID ('t1') IS NOT NULL DROP TABLE t1

IF OBJECT_ID ('p1') IS NOT NULL DROP PROC p1

IF OBJECT_ID ('p2') IS NOT NULL DROP PROC p2

GO

CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 char(5000))

GO

DECLARE @x int

SET @x = 1

WHILE (@x <= 1000) BEGIN

INSERT INTO t1 VALUES (@x*2, @x*2, @x*2, @x*2)

SET @x = @x + 1

END

GO

CREATE CLUSTERED INDEX cidx ON t1 (c1)

CREATE NONCLUSTERED INDEX idx1 ON t1 (c2)

GO

CREATE PROC p1 @p1 int AS

SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1

GO

CREATE PROC p2 @p1 int AS

UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1

UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1

GO

Now, run this from another connection:

-- Batch #2

USE deadlocktest

SET NOCOUNT ON

WHILE (1=1)

EXEC p2 4

GO

Finally, leave that one running while you run this from a third connection:

-- Batch #3

USE deadlocktest

SET NOCOUNT ON

CREATE TABLE #t1 (c2 int, c3 int)

GO

WHILE (1=1) BEGIN

INSERT INTO #t1 EXEC p1 4

TRUNCATE TABLE #t1

END

GO

This will cause a deadlock; you should see one of the batches aborted by a 1205 error. Now that we have a reproducible deadlock, I’ll follow the troubleshooting steps that I posted in Deadlock Troubleshooting, Part 1.

(Give it a minute you should get a email alert)

(Not working -- check on the alert history and history to see if counter going up)

---Looks complicated but it works well

---Just installed on 6 servers

---No flags 1222 or 1204 needed nothing.