Deadlock issue

  • 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

  • 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.

  • 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