Deadlock between two read-only Stored Procedures

Question

Deadlock between two read-only Stored Procedures

PhilPacha

Hall of Fame

Points: 3645
More actions
March 30, 2010 at 12:40 pm

#222574

I'm looking at a deadlock graph for two stored procedures which contain only SELECT statements which assign values to OUTPUT parameters.
Another deadlock graph that I just received involves two instances of a stored procedures. The procedure performs two SELECTs to assign values to multiple OUTPUT parameters.
How can SELECTs end up in a deadlock situation? I'm completely mystified!

Viewing 11 posts - 1 through 10 (of 10 total)

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

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

Post the stored procedures and the deadlock graph please.

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

PhilPacha Hall of Fame Points: 3645 More actions · Answer 2

<EVENT_INSTANCE>

<EventType>DEADLOCK_GRAPH</EventType>

<deadlock-list>

<process-list>

Select@PwdPhrase = PwdPhrase,

@PwdImageId = PwdImageId,

@userid = UserId

From WebUser

Where UserName = @UserName

AND MbrId IS NOT NULL </frame>

</executionStack>

Proc [Database Id = 5 Object Id = 1516024526] </inputbuf>

</process>

SELECT@ValidMbrStatus =

CASE

WHEN @ValidMbrStatus = 0

THEN 0

WHEN gm.MbrStatusId IN (4, 8, 11, 12)

THEN 0

ELSE 1

END,

@ValidLockStatus =

CASE

WHEN @ValidLockStatus = 0

THEN 0

WHEN gm.LockStatusId IN (2, 6)

THEN 0

ELSE 1

END,

@ValidAddress =

CASE

WHEN @ValidAddress = 0

THEN 0

WHEN (gma.AddrId IS NULL

AND gma_gmac.AddrId IS NULL)

OR (gma.ZipCode ='99998'

OR (gmac.AltContactId IS NOT NULL

AND gma_gmac.ZipCode = '99998'))

THEN 0

ELSE 1

END,

@HasInternalLock =

CASE

WHEN @HasInternalLock = 0

THEN 0

ELSE ISNULL(wu.InternalLock, 0)

END,

@HasSystemLock =

CASE

WHEN @HasSystemLock = 0

THEN 0

ELSE ISNULL(wu.SystemLock, 0)

END

FROMdbo.GenMbr gm

LEFT JOIN dbo.GenMbrAddr gma

ON gma.MbrId = gm.MbrId

LEFT JOIN

(dbo.GenMbrAltContact gmac

INNER JOIN dbo.GenMbrAddr gma_gmac

ON gma_gmac.AltContactId = gmac.AltContactId)

ON gmac.MbrId </frame>

</executionStack>

Proc [Database Id = 5 Object Id = 74574602] </inputbuf>

</process>

SELECT@Exists = Cast(Count(UserName) as bit)

FROMWebUser

WHEREUserName = @UserName

ANDUserId <> @userid </frame>

</executionStack>

Proc [Database Id = 5 Object Id = 1404024127] </inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

<owner-list>

</owner-list>

<waiter-list>

</EVENT_INSTANCE>

CREATE Procedure dbo.s_WebUserSEL_ByUserName

@UserName nvarchar(40),

@PwdPhrase nvarchar(70) OUT,

@PwdImageId smallint OUT,

@userid Int OUT

/*

* DESCRIPTION : This procedure retrieves the web user id from the name.

*

* ORIG AUTHOR : Jeff Turner

* DATE WRITTEN: ?

* CALLEDBY : TRS Web Security

* REVIEWED :

*

* REVISIONS LOG

*

* ID/Date PC# Description

* ------- ---- ----------------------------------------------------------

* CMD 11/16/2009 24107 - Modified to only return user id for member accounts

*/

As

SET NOCOUNT ON

Select@PwdPhrase = PwdPhrase,

@PwdImageId = PwdImageId,

@userid = UserId

From WebUser

Where UserName = @UserName

AND MbrId IS NOT NULL

GO

CREATE PROCEDURE [dbo].[s_WebUserSEL_UserNameExists]

@useridint,

@UserNamenvarchar(20),

@Existsbit OUT

AS

/*

* DESCRIPTION : This procedure checks to see if a user name already exists for a user account

*

* ORIG AUTHOR : Jeff Turner

* DATE WRITTEN: 06/24/2008

* CALLEDBY : TRS Web Security

* REVIEWED :

*

* REVISIONS LOG

*

* ID/Date PC# Description

* ------- ---- ----------------------------------------------------------

*/

SELECT@Exists = Cast(Count(UserName) as bit)

FROMWebUser

WHEREUserName = @UserName

ANDUserId <> @userid

GO

CREATE PROCEDURE [dbo].[s_GenMbrSEL_GetLogonProperties]

@MbrId int,

@ValidLockStatus bit = 1 out,

@ValidMbrStatus bit = 1 out,

@ValidPullBenefitLog bit = 1 out,

@ValidAddress bit = 1 out,

@HasUnverifiedMiscAdj bit = 1 out,

@HasInternalLock bit = 1 out,

@HasSystemLock bit = 1 out

/*DESCRIPTION:Gets properties to determine if a member meets web access requirements.

ORIG AUTHOR: Jeff Turner

DATE WRITTEN: 12/13/2004

CALLED BY: Web: TRSDatabase.Data.GetValidationProperties

REVISIONS LOG:

ID/Date Description

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

*JMT 01/27/2005 I removed the check for foreign address

*JMT 07/22/2008 Added check for unverified misc. adjustment and internal lock

*JMT 08/28/2008 Added check for system lock

* CMD 09/11/2008 Check passed in values of parameters. If false, keep false for the return value.

*JMT 10/29/2008 Issue 21430-Removed check for executive lock

*JMT 12/03/2008 Issue 21745-Added check to determine if a member has an address record

* CMD 11/13/2009 23806 - Phil and I modified query to increase performance.

*/

AS

BEGIN

SET NOCOUNT ON

SELECT@ValidMbrStatus =

CASE

WHEN @ValidMbrStatus = 0

THEN 0

WHEN gm.MbrStatusId IN (4, 8, 11, 12)

THEN 0

ELSE 1

END,

@ValidLockStatus =

CASE

WHEN @ValidLockStatus = 0

THEN 0

WHEN gm.LockStatusId IN (2, 6)

THEN 0

ELSE 1

END,

@ValidAddress =

CASE

WHEN @ValidAddress = 0

THEN 0

WHEN (gma.AddrId IS NULL

AND gma_gmac.AddrId IS NULL)

OR (gma.ZipCode ='99998'

OR (gmac.AltContactId IS NOT NULL

AND gma_gmac.ZipCode = '99998'))

THEN 0

ELSE 1

END,

@HasInternalLock =

CASE

WHEN @HasInternalLock = 0

THEN 0

ELSE ISNULL(wu.InternalLock, 0)

END,

@HasSystemLock =

CASE

WHEN @HasSystemLock = 0

THEN 0

ELSE ISNULL(wu.SystemLock, 0)

END

FROMdbo.GenMbr gm

LEFT JOIN dbo.GenMbrAddr gma

ON gma.MbrId = gm.MbrId

LEFT JOIN

(dbo.GenMbrAltContact gmac

INNER JOIN dbo.GenMbrAddr gma_gmac

ON gma_gmac.AltContactId = gmac.AltContactId)

ON gmac.MbrId = gm.MbrId

AND gmac.SendAllMailings = 1

LEFT JOIN dbo.WebUser wu

ON wu.MbrId = gm.MbrId

WHEREgm.MbrId = @MbrId

SELECT@ValidPullBenefitLog =

CASE

WHEN @ValidPullBenefitLog = 0

THEN 0

WHEN gar.ActRecId IS NOT NULL

THEN 0

ELSE 1

END

FROMdbo.GenActRec gar

WHEREgar.MbrId = @MbrId

ANDgar.ActCompleteDate IS NULL

ANDgar.LogTypeId IN

(SELECTLogTypeId

FROMdbo.GenLogTypeCode

WHERELogCategoryId IN (20, 21)

ANDIsInternetAccessible = 0)

IF @HasUnverifiedMiscAdj <> 0

BEGIN

EXEC dbo.s_CrsMiscAdjStatRecSEL_HasUnverifiedMiscAdj @MbrId, 3, @HasUnverifiedMiscAdj OUT

END

GO

Create PROCEDURE dbo.s_CrsMiscAdjStatRecSEL_HasUnverifiedMiscAdj

@MbrIdInt,

@MiscAdjStatusId_VerifiedTinyInt,

@HasUnverifiedMiscAdjBitOut

/*

* DESCRIPTION : This procedure determines if there is an

* unverified misc adj for the given member.

* ORIG AUTHOR : Cindy Daniels

* DATE WRITTEN: 06/23/08

* CALLEDBY :

* CALLS :

*

* REVISIONS LOG

*

* ID/Date PC# Description

* ------- ---- ----------------------------------------------------------

*

*/

AS

SET NOCOUNT ON

If Exists(

Select msma.MiscAdjId From (

Select masr.MiscAdjId,

Max(masr.EffDate) As EffDate

From CrsMiscAdjStatRec masr

Inner Join CrsMiscAdj msa On masr.MiscAdjId = msa.MiscAdjId

Where msa.MbrId = @MbrId

Group By masr.MiscAdjId) msma

Inner Join CrsMiscAdjStatRec masr

On masr.MiscAdjId = msma.MiscAdjid

And msma.EffDate = masr.EffDate

And masr.MiscAdjStatusId <> @MiscAdjStatusId_Verified)

Set @HasUnverifiedMiscAdj = 1

Else

Set @HasUnverifiedMiscAdj = 0

GO

[/Code]

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

The deadlock graph mentions a user transaction, and neither of the procs that you've posted start or commit a transaction. There's a proc called earlier, somewhere, that starts a transaction and takes some exclusive locks. The two procs that you've posted are just the ones running when then deadlock happened, the exclusive locks (or in this case SIX locks) were taken sometime earlier.

You've going to have to check the call stack, what calls those procs and trace where the transactions are started and committed.

I'll see if I can dig up some info for you on SIX locks.

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

PhilPacha Hall of Fame Points: 3645 More actions · Answer 4

Ah! Thanks! That's the missing piece of the puzzle. I don't have access to the .NET logic, which is where the transaction would be controlled. I'll pass the problem on to the programmers, and breathe a sigh of relief... "not my problem..."

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

Before you declare it SEP¹, check the procs that call these, and any that call those, etc and make sure that there's no explicit BEGIN TRANSACTION in them.

(1) Someone else's problem

p.s. It may be your problem anyway, since it's SQL code. Check indexes, check that the procs are as optimal as possible.

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

PhilPacha Hall of Fame Points: 3645 More actions · Answer 6

PhilPacha

Hall of Fame

Points: 3645

March 30, 2010 at 2:25 pm

#1142689

Will do. Good advice...

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

There's a little on SIX locks here - http://msdn.microsoft.com/en-us/library/ms175519.aspx. Trying to dig up more clear comments.

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