We help IT Professionals succeed at work.

Problems with SQL Server 2008 R2 deadlock.

500 Views
Last Modified: 2014-08-08
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

Comment
Watch Question

Senior Developer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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.

Author

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
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Seems that PosInTS is part of the clustered index of [VP_ASPDV].[dbo].[LPrgPlanFileLogs].[PK_LPrgPlanFileLogs].

Author

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

Author

Commented:
10x for time and help! :-)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.