Deadlock issue with a Sproc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Deadlock issue with a Sproc

Author

Topic

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-09-23 : 13:18:59

Hi all,

I've got a nightly job that fails every few days (or nights) because of unlucky Step 13, which is to fire a Sproc. However, the Sproc is being deadlocked 1205 and is chosen as the victim.

Any ideas on how to deal? Here's the code further below...

Cheers,

Jim

CREATE PROCEDURE [dbo].[PrResetLastWAP] AS
-- Commented StatsWapPushHit2 and StatsMMPPagehit2 since this tables have been moved to DBSrvrArchive on 15-03-2010

SELECT UserID, Service, MAX(Datestamp) AS LASTWAP
INTO #LastWAP
from (
--select userid, serviceid as service, max(datestamp) AS Datestamp
--from dbsrvr5.wapplatform.dbo.StatsWapPushHit2 a (nolock)
--group by UserID, Serviceid
--UNION
select userid, serviceid as service, max(datestamp) AS Datestamp
from dbsrvr5.wapplatform.dbo.StatsWapPushHit a (nolock)
group by UserID, Serviceid
--union
--select userid, service, max(datestamp) AS Datestamp
--from dbsrvr5.wapplatform.dbo.StatsMMPPageHit2 a (nolock)
--group by UserID, Service
UNION
select userid, service, max(datestamp) AS Datestamp
from dbsrvr5.wapplatform.dbo.StatsMMPPageHit a (nolock)
group by UserID, Service
) a
group by UserID, Service

CREATE NONCLUSTERED INDEX IX_ServiceUserid ON dbo.#LastWAP
(
USerID,
Service
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

UPDATE a
SET a.LastWAP = b.LastWAP
--select a.lastwap , b.lastwap
from services a
inner join #LastWAP b on a.userid = b.userid and a.service = b.service
where DATEADD(minute,+1,a.lastwap) < b.LastWAP OR a.LASTWAP IS NULL

DROP TABLE #LastWAP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-23 : 13:25:37

You'll need to identify the owner of the deadlock first. We have to see the victim and the owner.

Use trace flag 1222 for this. You can do it for the current process of SQL via DBCC TRACEON(1222,-1). If SQL restarts though, the trace will not be enabled. If you want it enabled no matter if SQL restarts, then do DBCC TRACEON plus add 1222 as a startup parameter.

After you've added the trace flag, you'll need to wait for the deadlock to happen again. And then you'll see the deadlock information in the error log.

Post what you can.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-09-23 : 16:50:47

Cheers Tara, I'll issue the dbcc in the morning. :)

Jim

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-09-23 : 17:21:30

You should refer to these:

Deadlocking
http://msdn.microsoft.com/en-us/library/ms177433(v=SQL.90).aspx

Detecting and Ending Deadlocks
http://msdn.microsoft.com/en-us/library/ms178104(v=SQL.90).aspx

Bart Duncan's SQL Weblog > Deadlock Troubleshooting
http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx

CODO ERGO SUM

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-09-30 : 13:22:58

Is there a way to copy and paste from the log? The entry is quite lengthy.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-09-30 : 17:01:32

quote:
Originally posted by Jim Beam

Is there a way to copy and paste from the log? The entry is quite lengthy.

Yes, the error log is just a text file.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources