none
sql server insert deadlocks from visual studio application RRS feed

  • Question

  • I have a sql server database setup that accepts insert transactions from a visual studio application that is running using threads. Now the transactions are getting deadlocked and I have a workaround in that when the visual studio 2010 code detects a timeout, it will just reattempt to insert the data. Looking at my text logs that I have setup, this is happening way too often and causing performance issues. Some of the online resources indicate finding the offending transaction and killing it but if my application is dependent on the results obtained in the database that may not be an option. Are there suggestions out there as to how to deal with this. I am using the Parallel Taskfactory in visual studion 2010, so there are at least 1000 threads running at any given time?

     

    m_param = CommandObj.Parameters.Add("searchterm", SqlDbType.NVarChar, 400)         
    m_param.Direction = ParameterDirection.Input         
    m_param.Value = searchTerm          
    m_param = CommandObj.Parameters.Add("dateadded", SqlDbType.DateTime)         
    m_param.Direction = ParameterDirection.Input         
    m_param.Value = gp.fileDate.ToString           
    Dim recordsAffected As Integer = CommandObj.ExecuteNonQuery         Return recordsAffected     
    Catch ex As SqlException         
    'only report errors if it's not related to duplicate/unique key errors         If Not ex.Message.ToLower().Contains("unique key") And Not ex.Message.ToLower().Contains("duplicate key") Then             ExecutionError = ex.Message           
    If ex.message.contains("time") then               thread.sleep(5000)               Return 0 '// return of 0 recalls this function          
    EndIf           
    Return 0        
     Else              
    Return 1 'consider duplicate record we tried to add as a record we added.         
    End If     
    Catch ex As Exception         
    ExecutionError = ex.Message         
    Return 0     
    End Try 
    End Function 
    

    sql table schema

     

          Table PostFeed
       
         id          int    
         entryid     varchar(100)
         feed        varchar(MAX)
         pubdate     varchar(50)
         authorName  nvarchar(100)
         authorId    nvarchar(100)
         age         nvarchar(50)
         locale      nvarchar(50)
         pic         nvarchar(50)
         searchterm  nvarchar(100)
         dateadded   datetime
    
         PK - entryid + searchterm     
    
    

     

    stored procedure

     

    USE [GnipFeeds]
    GO
    
    /****** Object:  Table [dbo].[PostFeed]    Script Date: 09/21/2011 11:21:38 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[PostFeed](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[entryId] [varchar](100) NOT NULL,
    	[feed] [varchar](max) NULL,
    	[entryContent] [nvarchar](max) NULL,
    	[pubDate] [varchar](50) NOT NULL,
    	[authorName] [nvarchar](100) NOT NULL,
    	[authorId] [nvarchar](100) NULL,
    	[age] [nvarchar](50) NULL,
    	[sex] [nvarchar](50) NULL,
    	[locale] [nvarchar](50) NULL,
    	[pic] [nvarchar](100) NULL,
    	[fanPage] [nvarchar](400) NULL,
    	[faceTitle] [nvarchar](100) NULL,
    	[feedtype] [varchar](50) NULL,
    	[searchterm] [nvarchar](400) NOT NULL,
    	[clientId] [nvarchar](100) NULL,
    	[dateadded] [datetime] NULL,
    	[matchfound] [nvarchar](50) NULL,
    	[hashField]  AS ([dbo].[getMd5Hash]([entryId])),
     CONSTRAINT [PK_FacebookFeed] PRIMARY KEY CLUSTERED 
    (
    	[entryId] ASC,
    	[searchterm] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    
    


     



     

     

    so id does a whole bunch of inserts and relies on primary key constraints for dupe checking



    • Edited by faafa Wednesday, September 21, 2011 3:35 PM
    • Moved by Stephanie Lv Friday, September 23, 2011 8:23 AM (From:Getting started with SQL Server)
    • Moved by Bob Beauchemin Friday, September 23, 2011 4:45 PM Moving to a more appropriate forum (From:.NET Framework inside SQL Server)
    Wednesday, September 21, 2011 3:05 PM

All replies

  • Hi faafa,

    You may have a try to use trace flag to troubleshoot the deadlock. With the information return by trace, you can find the reason a deadlock occurred. Also, you can identify the statements which generate the deadlock and analyze them in tuning advisor to increase the performance. The following articles may be helpful on this topic:
    http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx


    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    Friday, September 23, 2011 11:37 AM
  • Thanks for the response. Tried to trace deadlock and did not find any and found that I was interpreting the timeout issue as a deadlock issue when in fact it is probably not. The error message included:

    timeout expired. the timeout period elapsed prior to completion of the operation or the server is not responding.

     

    When I look at my transactions for the sql server: exec sp_who2

     

    and those transactions that reference my DB are mainly sleeping AWAITING COMMAND. Not sure how to interprate this other than they are waiting for access which I thought meant deadlocks. 

    Wednesday, October 12, 2011 4:39 PM