December 5, 2012 at 7:34 am
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,
);
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
December 5, 2012 at 7:51 am
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.
December 5, 2012 at 12:59 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply