[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Problems with SQL Server 2008 R2 deadlock.

Posted on 2014-08-07
7
Medium Priority
?
440 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

0
Comment
Question by:dvplayltd
  • 4
  • 3
7 Comments
 
LVL 36

Accepted Solution

by:
ste5an earned 2000 total points
ID: 40248064
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
 

Author Comment

by:dvplayltd
ID: 40248182
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
 
LVL 36

Expert Comment

by:ste5an
ID: 40248202
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dvplayltd
ID: 40248454
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
 
LVL 36

Expert Comment

by:ste5an
ID: 40248513
Seems that PosInTS is part of the clustered index of [VP_ASPDV].[dbo].[LPrgPlanFileLogs].[PK_LPrgPlanFileLogs].
0
 

Author Comment

by:dvplayltd
ID: 40248840
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
 

Author Closing Comment

by:dvplayltd
ID: 40248845
10x for time and help! :-)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question