InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 10:06:01 GMT20http://www.sqlservercentral.com/Forums/Topic1393005-391-1.aspxThe 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.Wed, 05 Dec 2012 12:59:39 GMTDiverKashttp://www.sqlservercentral.com/Forums/Topic1393005-391-1.aspxYour 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.Wed, 05 Dec 2012 07:51:54 GMTsqlGDBAhttp://www.sqlservercentral.com/Forums/Topic1393005-391-1.aspxI have 2 tables:[code="sql"]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);[/code]and 2 stored procedures one that save data in that 2 tables and one that get data from these tables:[code="sql"]CREATE PROCEDURE [SessionData].[spGetWeight] @SessionID INTASBEGIN 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;ENDCREATE PROCEDURE [Prescription].[spSaveWeight] @SessionID AS INT, @TargetWeight AS REAL = NULL, @Intake AS REAL = NULL, @WeightLoss AS REAL = NULLASBEGIN 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 CATCHEND[/code]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,ioaniWed, 05 Dec 2012 07:34:57 GMTioani