Problems with SQL Server 2008 R2 deadlock.

Dear experts,

I use SQL Server 2008 R2 and recently I start get this error when I execute an Stored Procedure at the same time from 3 different computers. I was thinking SQL 2008 use record level records, every start of SP in fact work with different records , so why I get this error???
 
   
      This make error – but why – @LPrgTimeSlotID is unique for different computers start the SP.
         IF @NumPosMoveUP<>0
          UPDATE dbo.LPrgPlanFileLogs SET PosInTS=PosInTS-@NumPosMoveUP WHERE PosInTS>2 and PosInTS<199 AND
                   LPrgPlanID IN (SELECT LPrgPlanID FROM dbo.LPrgPlans WHERE LPrgTimeSlotID=@LPrgTimeSlotID)


This is the error message I got (sometimes, not awayls)
Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I Add work with transaction (BEGIN TRANSACTON), it help partically because at least I not leave records half saved and on next scheduled start the logic is done as expected. But  still I get the error that sometimes deadlock happened.

For more clear look I publish the whole code of my SP – final version with works with transations. Still the problem happened, what to change in code? May be temporaly to swich off transaction because I'm sure every such SP work on its own data???

ALTER PROCEDURE [dbo].[spLDVPLAY_FixRealTSOrder]
@CableTVID  AS TINYINT,
@ChannelID  AS TINYINT
 AS

DECLARE @iCount INT; DECLARE @LPrgTimeSlotID INT;DECLARE @NumPosMoveUP SMALLINT
DECLARE @ID INT;DECLARE @L AS INT; DECLARE @sErr NVARCHAR(1000)= '';DECLARE @sErrAll NVARCHAR(4000)= '';DECLARE @NumError INT = 0;DECLARE @NumErrorTR INT = 0;

SET @ID=0; SET @iCount=0;

 SET NOCOUNT ON

   DECLARE InDetail CURSOR LOCAL FORWARD_ONLY  FOR 
	SELECT TOP (100) PERCENT  dbo.LPrgTimeSlots.LPrgTimeSlotID
	FROM  dbo.LPrgPlanFileLogs INNER JOIN dbo.LPrgPlans ON dbo.LPrgPlanFileLogs.LPrgPlanID = dbo.LPrgPlans.LPrgPlanID INNER JOIN
				   dbo.LPrgTimeSlots ON dbo.LPrgPlans.LPrgTimeSlotID = dbo.LPrgTimeSlots.LPrgTimeSlotID INNER JOIN dbo.LPrgItems ON dbo.LPrgTimeSlots.LPrgItemID = dbo.LPrgItems.LPrgItemID
	WHERE (dbo.LPrgTimeSlots.PosInTS_Fixed = 0) AND (dbo.LPrgTimeSlots.LTypeTSID = 1)  AND (dbo.LPrgTimeSlots.CableTVID = @CableTVID) AND (dbo.LPrgTimeSlots.ChannelID = @ChannelID)
	GROUP BY dbo.LPrgItems.BeginTime, dbo.LPrgTimeSlots.LPrgTimeSlotID
	HAVING (ISNULL(COUNT(DISTINCT dbo.LPrgPlans.LPrgPlanID), 0) = ISNULL(COUNT(DISTINCT dbo.LPrgPlanFileLogs.LPrgPlanFileLogID), 0))
	       OR (BeginTime<DATEADD(d,-5,GETDATE()))
	ORDER BY dbo.LPrgItems.BeginTime	

	OPEN InDetail
	FETCH NEXT FROM InDetail INTO @LPrgTimeSlotID
	WHILE (@@fetch_status =0)
	 BEGIN
	 
	  StartNEW:
	   BEGIN TRANSACTION
	   SET @NumPosMoveUP=0; SET @L=0
	   -- if there is not saved ordered pos:first then - move one ahead all
	   
	   SELECT TOP (100) PERCENT @L=ISNULL(dbo.LPrgPlans.SortOrder,0)
			FROM  dbo.LPrgPlanFileLogs INNER JOIN dbo.LPrgPlans ON dbo.LPrgPlanFileLogs.LPrgPlanID = dbo.LPrgPlans.LPrgPlanID
			WHERE (dbo.LPrgPlans.LPrgTimeSlotID = @LPrgTimeSlotID) AND (dbo.LPrgPlans.SortOrder = 1)
	   
	   IF @L=0 SET @NumPosMoveUP=1 -- there is not set first pos - so move all one up

       SET @L=0
	   SELECT TOP (100) PERCENT @L=ISNULL(dbo.LPrgPlans.SortOrder,0)
			FROM  dbo.LPrgPlanFileLogs INNER JOIN dbo.LPrgPlans ON dbo.LPrgPlanFileLogs.LPrgPlanID = dbo.LPrgPlans.LPrgPlanID
			WHERE (dbo.LPrgPlans.LPrgTimeSlotID = @LPrgTimeSlotID) AND (dbo.LPrgPlans.SortOrder = 2)
	   
	   IF @L=0 SET @NumPosMoveUP=@NumPosMoveUP + 1 -- there is not set second pos - so move all one up
	 
	 TryAgain:  
	   BEGIN TRY 
[b]-- 	This make error – but why – @LPrgTimeSlotID is unique for different computers start the SP. [/b]
	   IF @NumPosMoveUP<>0
          UPDATE dbo.LPrgPlanFileLogs SET PosInTS=PosInTS-@NumPosMoveUP WHERE PosInTS>2 and PosInTS<199 AND
                   LPrgPlanID IN (SELECT LPrgPlanID FROM dbo.LPrgPlans WHERE LPrgTimeSlotID=@LPrgTimeSlotID)
       END TRY 
  	  BEGIN CATCH
  	   SET @NumError=@NumError+1;
  	   SET @sErr = 'UPD 1 LPrgPlanFileLogs ' + CAST( ERROR_NUMBER() AS NVARCHAR) + ' ' + ERROR_MESSAGE()
  	   SET @NumError=@NumError+1; WAITFOR DELAY '00:00:01'
 	   IF @NumError<6 GOTO TryAgain ELSE  BEGIN  SET @sErrAll=@sErrAll + @sErr; ROLLBACK TRANSACTION; GOTO SkipThis;  END
	 END CATCH;

	SET @NumError=0
 TryAgain1:
 
      BEGIN TRY    
         UPDATE dbo.LPrgTimeSlots SET PosInTS_Fixed=1 WHERE LPrgTimeSlotID=@LPrgTimeSlotID
      END TRY 
  	  BEGIN CATCH
  		SET @NumError=@NumError+1;
  		SET @sErr = 'UPD 2 LPrgTimeSlots ' + CAST(ERROR_NUMBER() AS NVARCHAR) + ' ' + ERROR_MESSAGE()
  		SET @NumError=@NumError+1; WAITFOR DELAY '00:00:01'
		IF @NumError<6 GOTO TryAgain1 ELSE  BEGIN  SET @sErrAll=@sErrAll + @sErr; ROLLBACK TRANSACTION; GOTO SkipThis;  END
	  END CATCH;
		 
       
     BEGIN TRY 
        COMMIT TRANSACTION
        SET @iCount= @iCount+1
      END TRY  
  	  BEGIN CATCH
  		SET @NumErrorTR=@NumErrorTR+1;
  		ROLLBACK TRANSACTION; WAITFOR DELAY '00:00:08'
		IF @NumErrorTR<5 GOTO StartNEW ELSE  BEGIN  SET @sErr = 'UPD 3 CT ' + CAST(ERROR_NUMBER() AS NVARCHAR) + ' ' + ERROR_MESSAGE(); ; GOTO SkipThis;  END
	  END CATCH;
	  
      SkipThis: 
	   FETCH NEXT FROM InDetail INTO @LPrgTimeSlotID
	  END

 	CLOSE InDetail
	DEALLOCATE InDetail   

   SELECT @iCount AS iCount,@sErrAll AS sErr
 

Select allOpen in new window