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
 

Open in new window

dvplayltdAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
When it's really caused by the UPDATE:

UPDATE	dbo.LPrgPlanFileLogs 
SET		PosInTS = PosInTS - @NumPosMoveUP 
WHERE	PosInTS > 2 
	AND PosInTS < 199 
	AND LPrgPlanID IN (
			SELECT	LPrgPlanID 
			FROM	dbo.LPrgPlans 
			WHERE	LPrgTimeSlotID=@LPrgTimeSlotID
		);

Open in new window


Then I would guess the >2 and < 199 lead to a range or even table lock. Please post the actual execution plan before indexing. But on the other hand, have you indexed your tables? Try these and post the new actual execution plan.

CREATE INDEX dbo.IX_LPrgPlanFileLogs_UPDATE_TEST_1
ON dbo.LPrgPlanFileLogs ( PosInTS , LPrgPlanID );

CREATE INDEX dbo.IX_LPrgPlanFileLogs_UPDATE_TEST_2
ON dbo.LPrgPlanFileLogs ( PosInTS ) INCLUDE ( LPrgPlanID );

CREATE INDEX dbo.IX_LPrgPlanFileLogs_UPDATE_TEST_3
ON dbo.LPrgPlanFileLogs ( LPrgPlanID, PosInTS );

CREATE INDEX dbo.IX_LPrgPlanFileLogs_UPDATE_TEST_4
ON dbo.LPrgPlanFileLogs ( LPrgPlanID ) INCLUDE ( PosInTS );

CREATE INDEX dbo.LPrgPlans_UPDATE_TEST_1
ON dbo.LPrgPlans ( LPrgTimeSlotID, LPrgPlanID );

CREATE INDEX dbo.LPrgPlans_UPDATE_TEST_2
ON dbo.LPrgPlans ( LPrgTimeSlotID ) INCLUDE ( LPrgPlanID );

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dvplayltdAuthor Commented:
Hi

Thanks for your offer, here you the execution plan.
Well, It look that I have miss of some indexes over LPrgPlanFileLogs table - will add it. But I was thinking that the lock is happend only with Update, now you say me that it could be because of WHERE ???

 Bad is that it will take few day to see if your guess is right - i mean - i;ll applied it but should gone few days without any such message.

 I'm sure this row is the first one failed - i located it with error message I get back in application with error code you see in SP
E--Temp-spLDVPLay-FixTSOrder.sqlplan
0
ste5anSenior DeveloperCommented:
I see, one further problem is that you're modifying the clustered index.

Clustered Index Update
This may also the cause for you deadlock. Maybe a different clustered index solves this.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dvplayltdAuthor Commented:
Hyyym. Which part of code make this Clustered Index Update ? Here I do not add or delete Primary Key (INT) ...
I post the whole code, if you could guess what to change I'll test it
0
ste5anSenior DeveloperCommented:
Seems that PosInTS is part of the clustered index of [VP_ASPDV].[dbo].[LPrgPlanFileLogs].[PK_LPrgPlanFileLogs].
0
dvplayltdAuthor Commented:
Hi Ste5an,

I was starting to give you the whole T-SQL of the table, its indexes when I notice someting. I get an tigger - I forget about it - i almost not use tiggers in my 10+ years in SQL programming ...

This explain the lock - every single computer works on its own data and lock should not happend, but this tigger works with ALL data /because it actually calculate the amount of all reported executions of all 3 servers) and when this is happen on one time - I get deadlock problem ...

So - it look the simplest solution is to change the time that different servers return the log so they never do it on the same time - i can do that easy but was wanted to know where the problem is ...
0
dvplayltdAuthor Commented:
10x for time and help! :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.