Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deadlock issue Expand / Collapse
Author
Message
Posted Wednesday, December 05, 2012 7:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 6:50 AM
Points: 295, Visits: 391
I have 2 tables:

CREATE TABLE [Prescription].[Weight]
(
[SessionID] INT NOT NULL,
[TargetWeight] REAL NULL,
[Intake] REAL NULL,
[WeightLoss] REAL NULL,
....................................
);
ALTER TABLE [Prescription].[Weight]
ADD CONSTRAINT [PK_Weight] PRIMARY KEY CLUSTERED ([SessionID] ASC);

CREATE TABLE [SessionData].[Weight]
(
[SessionID] INT NOT NULL,
[Scale] REAL NULL,
[WeightLoss] REAL NULL,
......................................
);
ALTER TABLE [SessionData].[Weight]
ADD CONSTRAINT [PK_Weight] PRIMARY KEY CLUSTERED ([SessionID] ASC);

and 2 stored procedures one that save data in that 2 tables and one that get data from these tables:

CREATE PROCEDURE [SessionData].[spGetWeight]
@SessionID INT
AS
BEGIN
SELECT WO.SessionID,
WO.Scale,
WO.WeightLoss,
WP.TargetWeight
FROM [SessionData].[Weight] AS WO LEFT OUTER JOIN [Prescription].[Weight] WP ON(WO.SessionID = WP.SessionID)
WHERE WO.SessionID = @SessionID;
END


CREATE PROCEDURE [Prescription].[spSaveWeight]
@SessionID AS INT,
@TargetWeight AS REAL = NULL,
@Intake AS REAL = NULL,
@WeightLoss AS REAL = NULL
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;

IF NOT EXISTS (SELECT 1 FROM [Prescription].[Weight] WHERE SessionID = @SessionID)
BEGIN
INSERT INTO [Prescription].[Weight]
(SessionID,
TargetWeight,
Intake,
WeightLoss
)
VALUES (@SessionID,
@TargetWeight,
@Intake,
@WeightLoss
);
END
ELSE
BEGIN
UPDATE [Prescription].[Weight]
SET TargetWeight = @TargetWeight,
Intake = @Intake,
PreTara = @WeightLoss
WHERE SessionID = @SessionID;
END

IF NOT EXISTS (SELECT 1 FROM [SessionData].[Weight] WHERE SessionID = @SessionID)
BEGIN
INSERT INTO [SessionData].[Weight] (SessionID, WeightLoss )
VALUES (@SessionID, @WeightLoss );
END
ELSE
BEGIN
UPDATE [SessionData].[Weight]
SET WeightLoss= @WeightLoss
WHERE SessionID = @SessionID;
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- log error
END CATCH
END

I get an deadlock error between the select from first procedure and the update of the [SessionData].[Weight] from the second procedure.
See the attachement.

How can I solve this problem?

Thanks,
ioani


  Post Attachments 
Weight.PNG (6 views, 35.62 KB)
Post #1393005
Posted Wednesday, December 05, 2012 7:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 22, 2013 10:13 AM
Points: 79, Visits: 200
Your code seems to be incomplete. I can see a begin try ... but no end to the try block, and no catch block either. A simple fix would be to look for error 1204(and 1222 if you want) and retry again from the calling proc/program. Another fix would be to do your selects with read uncommitted, if that is a possibility.
Post #1393023
Posted Wednesday, December 05, 2012 12:59 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, Visits: 460
The problem I see, is there is no ROLLBACK should an error exist. Therefore you have uncommitted changes pending, which of course, will cause the GET procedure to deadlock. Thats a guess, as I have no way to test my theory where I am at currently.
Post #1393178
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse