SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist
    Join Date
    Oct 2006
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Stored Procedure Deadlock Issue

    Hi,

    Below is my sql stored procedure running on SQL Server 2005. I get a deadloc warning quite often.
    Tried adding 'Begin Transaction', 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE', etc but no effect.

    Any ideas?

    Thanks.

    Code:
    USE [master]
    GO
    /****** Object:  StoredProcedure [dbo].[InsertOrUpdate_News]    Script Date: 03/24/2009 11:31:45 ******/
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[InsertOrUpdate_News]
    	-- Add the parameters for the stored procedure here
    	@FileName nvarchar(max),
    	@Author nvarchar(50)
    AS
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	DECLARE @SavedFileName nvarchar(max);
    
    	DECLARE @FoundData int;
    	SET @FoundData = 0;
    
    	Begin Transaction
    	
    
    	Set @FoundData = (
    		SELECT COUNT (NewsID)
    		From [News]
    		WHERE (NewsID = @NewsID)
    		)
    	if  @FoundData = 0
    	  Begin
    		-- If the data does not exist insert it
    		insert into [News] (NewsID, FileName, Author) 
    		values (@NewsID, @FileName, @Author);
    	  End
    	Else
    	  Begin
    		-- Check FileName
    		SET @SavedFileName = (SELECT FileName
    			FROM [News]
    			WHERE (NewsID = @NewsID)
    			AND (Author = @Author))
    			if @SavedFileName is null begin set @SavedFileName = 'N/A' end
    
    		if(@SavedFileName = @FileName) -- if file name already exists
    			Begin
    			-- If the data does already exist update it
    			update [News]
    			set FileName = @FileName
    			Where (NewsID = @NewsID)
    			AND (Author = @Author)
    			End
    		Else if(@SavedFileName != 'N/A')
    			Begin
    			-- If the data does already exist update it
    			update [News]
    			set
    				FileName = @SavedFileName + ', ' + @FileName
    			Where (NewsID = @NewsID)
    			AND (Author = @Author)
    			End
    		End
    
    	Commit
    END

  2. #2
    SitePoint Member
    Join Date
    Mar 2008
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dont quite know the rest of the datbase, buthere's my guess:

    You have other functions that access the table frequently. When you do an insert, it will aquire exclsuivity and that is why you get locks.

    You must look over other parts of the code itself, not just the insert.

    What i do, is rely on caching to a large extent so that i dont have to do a whole lot of reads to frequently used tables. That dimishes locks. When you do selects on data that isn't mission cirtical, you can set the transaction levels to read uncommited (i.e. read dirty), which is ok for some types of data: not all.

  3. #3
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,629
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Do you have proper indexing supporting the lookups? I suspect this thing runs a bit too long and therefore ends up locking something key to someone else.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •