Deadlock problem - 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 2000 Forums

Transact-SQL (2000)

Deadlock problem

Author

Topic

MuppetCoder
Starting Member

6 Posts

Posted - 2004-11-24 : 05:54:57

I'm getting deadlocks from two instances of the following procedure running at the same time (on the update statement on the Session table). I can't see why the deadlock would occur - has anybody got any ideas how to solve it?

CREATE PROCEDURE cm_AddPageHit
@SessionToken VARCHAR(125),
@ThreadID INT,
@DomainID INT,
@ReferrerURL VARCHAR(255)=NULL,
@UserAgent VARCHAR(255)=NULL,
@ClientToken VARCHAR(125),
@UserID INT=NULL,
@ScreenSize VARCHAR(50)=NULL,
@AdID INT=NULL
AS

DECLARE @SessionID INT, @UserAgentID INT

-- do the user agent tracking
SELECT @UserAgentID=ID FROM UserAgent WHERE LOWER(Name)=LOWER(@UserAgent)

IF @UserAgentID IS NULL BEGIN
INSERT INTO UserAgent (Name) VALUES (@UserAgent)
SET @UserAgentID=@@IDENTITY
END

SELECT @SessionID=ID FROM Session WHERE AppSessionID=@SessionToken

IF @SessionID IS NULL BEGIN
INSERT INTO Session (AppSessionID,AppClientID,UserAgentID,UserID,AdID)
VALUES (@SessionToken,@ClientToken,@UserAgentID,@UserID,@AdID)
SELECT @SessionID=@@IDENTITY
END ELSE BEGIN
-- check that the session has the UserID if applicable
IF @UserID IS NOT NULL BEGIN
-- deadlock here
IF EXISTS(SELECT 1 FROM Session (READPAST) WHERE ID=@SessionID AND UserID IS NULL) BEGIN
UPDATE Session SET UserID=@UserID WHERE ID=@SessionID
END
END
END

INSERT INTO PageHit (SessionID,ThreadID,DomainID,ReferrerURL)
VALUES (@SessionID,@ThreadID,@DomainID,@ReferrerURL)
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources