Hi,
I have only one table in my database, i created 4 single indexes on it.
I have an application in C# .NET using LINQ with 2 threads:
THREAD 1: Insert new records in this table
THREAD 2: Read record and update them
I do not understand why i got this exception:
System.Data.SqlClient.SqlE
xception: Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
à System.Data.SqlClient.SqlC
onnection.
OnError(Sq
lException
exception, Boolean breakConnection)
à System.Data.SqlClient.SqlI
nternalCon
nection.On
Error(SqlE
xception exception, Boolean breakConnection)
à System.Data.SqlClient.TdsP
arser.Thro
wException
AndWarning
(TdsParser
StateObjec
t stateObj)
à System.Data.SqlClient.TdsP
arser.Run(
RunBehavio
r runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
à System.Data.SqlClient.SqlD
ataReader.
HasMoreRow
s()
à System.Data.SqlClient.SqlD
ataReader.
ReadIntern
al(Boolean
setTimeout)
à System.Data.SqlClient.SqlD
ataReader.
Read()
à System.Data.Linq.SqlClient
.ObjectRea
derCompile
r.ObjectRe
aderBase`1
.Read()
à System.Data.Linq.SqlClient
.ObjectRea
derCompile
r.ObjectRe
ader`2.Mov
eNext()
à System.Collections.Generic
.List`1..c
tor(IEnume
rable`1 collection)
à System.Linq.Enumerable.ToL
ist[TSourc
e](IEnumer
able`1 source)
How to solve this exception ?
Here is my database script
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Recipient](
[RecipientId] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_Recipients_IdRecipient
] DEFAULT (newid()),
[NewsletterId] [uniqueidentifier] NULL,
[Email] [nvarchar](100) NOT NULL,
[Domain] [nvarchar](50) NULL,
[Message] [ntext] NOT NULL,
[Processed] [bit] NOT NULL CONSTRAINT [DF_Recipient_Processed] DEFAULT ((0)),
[Status] [int] NOT NULL CONSTRAINT [DF_Recipient_Status] DEFAULT ((0)),
[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_Recipients_CreatedOn] DEFAULT (getdate()),
[SentOn] [datetime] NULL,
[QueuedOn] [datetime] NOT NULL CONSTRAINT [DF_Recipient_QueuedOn] DEFAULT (''),
[Journal] [ntext] NULL,
[Retry] [int] NOT NULL CONSTRAINT [DF_Recipients_Retry] DEFAULT ((0)),
[Priority] [int] NOT NULL CONSTRAINT [DF_Recipients_Priority] DEFAULT ((0)),
[Version] [timestamp] NOT NULL,
[Duration] [int] NULL CONSTRAINT [DF_Recipient_Duration] DEFAULT ((0)),
[ReceivedOn] [datetime] NULL,
CONSTRAINT [PK_Recipients] PRIMARY KEY CLUSTERED
(
[RecipientId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Email] ON [dbo].[Recipient]
(
[Email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Newsletter] ON [dbo].[Recipient]
(
[NewsletterId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_StatusProcessed] ON [dbo].[Recipient]
(
[Status] ASC,
[Processed] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Thank you