Sql server database transaction deadlocks; visual studio insert procedures
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?
some code to view: my insert code
Any ideas really appreciated.
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 for insert so it does a whole bunch of inserts and relies on primary key constraints for dupe checking
complete table create
USE [Feeds]
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] [var开发者_如何学运维char](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_Feed] 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
I tried exporting the deadlock graph, could not get it to work so here are few lines from trace
Lock:Timeout 60468 sa 0X01 247 G01 sa 2011-09- 21 16:00:44.750 1:4557807 3463314605 .Net SqlClient Data Provider 0 0XEF8B45000100000000000000070006 22164 7 postFeeds 0 2011-09-21 16:00:44.750 1 G02 0 - LOCK 8 - IX 0 72057594075152384 1 - TRANSACTION 0 6 - PAGE
Lock:Timeout 60469 sa 0X01 478 G01 sa 2011-09- 21 16:00:44.887 (7bf23fc490ce) 3463299315 .Net SqlClient Data Provider 0 0X3802000000017BF23FC490CE070007 17900 7 postFeeds 0 2011-09-21 16:00:44.887 1 G02 0 - LOCK 5 - X 0 72057594075152384 1 - TRANSACTION 0 7 - KEY
Lock:Timeout 60470 sa 0X01 803 G01 sa 2011-09- 21 16:00:44.887 (379349b72c77) 3463296982 .Net SqlClient Data Provider 0 0X380200000001379349B72C77070007 17900 7 postFeeds 0 2011-09-21 16:00:44.887 1 G02 0 - LOCK 5 - X 0 72057594075152384 1 - TRANSACTION 0 7 - KEY
Lock:Timeout 60471 tdbuser 0X048D73EF643661429B907E6106F78358 93 G01 tdbuser 2011-09-21 16:02:41.333 1:14386936 3463346220 .Net SqlClient Data Provider 0
I've ran into the same situation inserting a boat load of records with parallel threads. To get around my deadlock issue I specified a table lock upon insert...
Insert dbo.MyTable WITH(TABLOCKX) (Column1)
Values ( SomeValue);
I tried using lowel level locks but I still got deadlocks. The TabLockX slowed the throughput down some but it still was a ton faster than serial inserts and no more deadlocks.
精彩评论