Link to home
Start Free TrialLog in
Avatar of dvplayltd
dvplayltdFlag for Bulgaria

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dvplayltd

ASKER

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
I see, one further problem is that you're modifying the clustered index.

User generated image
This may also the cause for you deadlock. Maybe a different clustered index solves this.
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
Seems that PosInTS is part of the clustered index of [VP_ASPDV].[dbo].[LPrgPlanFileLogs].[PK_LPrgPlanFileLogs].
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 ...
10x for time and help! :-)