How to solve a deadlocked transaction in SQL Server 2005

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.SqlException: 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.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   à System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   à System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   à System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   à System.Data.SqlClient.SqlDataReader.HasMoreRows()
   à System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
   à System.Data.SqlClient.SqlDataReader.Read()
   à System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReaderBase`1.Read()
   à System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
   à System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   à System.Linq.Enumerable.ToList[TSource](IEnumerable`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



marsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DimitrisSenior Solution ArchitectCommented:
the issue is that the 2 threads run at the same time
Do u use transactions when you insert records???
If yes then the first thread locks the table for a period of time and when the 2nd thread tries to read and update records the table is locked due to the 1st thread.

Work around's

1. Don't use Transactions
2. Try to minimize the lock using Row Lock on the insert- update statements
2. Prepare the data to be inserted or updated in other tables and do a single insert or update on your main table (this will minimize the lock period

I hope that i helped you
0
marsAuthor Commented:
I'm using LINQ so i cannot customize the self generated SQL.

In Thread 1, the code is :

using( Smtpd smtpd = new Smtpd( "ConnectionString" ) )
{
   Recipient recpt = new Recipient();
   recpt.MEMBER INITIALISATION HERE
   smtpd.InsertOnSubmit( recpt );
   smtpd.SubmitChanges();
}

In Thread 2, the LINQ code is :

using( Smtpd smtpd = new Smtpd( "ConnectionString" ) )
{
    var list = from e in smtpd.Recipient
                 where e.Processed == false .....
                select e;

   // In some cases I update the record
   foreach( Recipient recpt in list )
   {
         recpt.MEMBER CHANGES
   }
   smtpd.SubmitChanges();
}

Any hints to solve this exception in LINQ ?
0
mironCommented:
Hi, did you try to chage function newid() to NEWSEQUENTIALID for the constraint generating default value

ALTER TABLE  [dbo].[Recipient] DROP CONSTRAINT [DF_Recipients_IdRecipient]
ALTER TABLE  [dbo].[Recipient] ADD CONSTRAINT [DF_Recipients_IdRecipient]  DEFAULT (NEWSEQUENTIALID()) FOR RecipientId

personally I would prefer to use integer value for a primary key column with identity applied to generate monotonously incremented values. Hope it helps.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

marsAuthor Commented:
I cannot use the integer as a key because i need this key to be a guid in order to be shared accross computer.

I explicitly implement a TransactionScope and set a timeout of 10 minutes but it failed anyway, i got a deadlocked transaction :

                        using( TransactionScope ts = new TransactionScope( TransactionScopeOption.Required,
                            new TimeSpan( 0, 10, 0 ) ) )
                        {
                            smtpd.SubmitChanges( ConflictMode.ContinueOnConflict );
                            ts.Complete();
                        }

It's really annoying to not select and insert SQL statement at the same time on one table ?
The worst scenario i do not want to is to surround my SubmitChanges() by a MUTEX.

0
mironCommented:
no problem, did you try to switch to NEWSEQUENTIALID() for the default.
0
mironCommented:
by the same token, I would explore possibility to add rowid [big]int identity primary key and change RecipientId to unique non - clustered index...
0
marsAuthor Commented:
I did this :

1/ Change NEWID to NEWSEQUENTIALID
2/ Remove the NEWID and the NEWSEQUENTIALID, GUID is now set in my application
3/ Remove RowGuid for the RecipientId column
4/ Change the place where SubmitChanges() is called in both Thread
    - After each insertion or changes, a SubmitChanges() is called
    - After many records insertion or changes, a SubmitChanges() is called
5/ The RecipientId index is Unique Non clustered index
6/ With and without the use of TransactionScope as described above

The most of the time, i always get the exception deadlocked transaction.
But sometimes, it works without exception and i do not know why.

The use of bigint is not greatful for me because it implies to add more code because my apllication will serialize and deserialize records and pass the xml file to another server. So i will have trouble with the uniqueness of bigint.



0
mironCommented:
Hi could you please apply NEWSEQUENTIALID() as the function to generate the next guid.
In this setting please do make sure that RecipientIId column is applied with primary key ( which is by default is clustered index and we need it )

Once this change is applied please run code that causes deadlock to arise.
We will need to trace caused lock conflict to arise one at a time find deadlock need to be resolved. To trace apply SQL Server startup trace flag T1222 and run DBCC TRACEON(1222,-1) so that the trace is started without having to re - start SQL Server service.

Please post trace once available.
0
marsAuthor Commented:
I changed to SQL Server 2008 express edition, it does not change, i always get the deadlock.
Thank you for helping me, here is the trace.

2008-11-17 09:55:55.64 spid52      DBCC TRACEON 1222, server process ID (SPID) 52. This is an informational message only; no user action is required.
2008-11-17 09:59:15.31 spid24s     deadlock-list
2008-11-17 09:59:15.31 spid24s      deadlock victim=processcdf000
2008-11-17 09:59:15.31 spid24s       process-list
2008-11-17 09:59:15.31 spid24s        process id=processcdf000 taskpriority=0 logused=0 waitresource=RID: 5:1:112009:24 waittime=188 ownerId=227923 transactionname=SELECT lasttranstarted=2008-11-17T09:59:15.123 XDES=0x170fed90 lockMode=S schedulerid=2 kpid=1644 status=suspended spid=62 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2008-11-17T09:59:15.123 lastbatchcompleted=2008-11-17T09:59:15.123 clientapp=.Net SqlClient Data Provider hostname=TOMATE hostpid=1620 loginname=sa isolationlevel=read committed (2) xactid=227923 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2008-11-17 09:59:15.31 spid24s         executionStack
2008-11-17 09:59:15.31 spid24s          frame procname=adhoc line=1 stmtstart=98 sqlhandle=0x0200000032d38913ea5ad3dfc817e89976eb48a154d70d90
2008-11-17 09:59:15.31 spid24s     SELECT TOP (4) [t0].[RecipientId], [t0].[NewsletterId], [t0].[Email], [t0].[Domain], [t0].[Message], [t0].[Processed], [t0].[Status], [t0].[CreatedOn], [t0].[SentOn], [t0].[QueuedOn], [t0].[Journal], [t0].[Retry], [t0].[Priority], [t0].[Version], [t0].[Duration], [t0].[ReceivedOn]
2008-11-17 09:59:15.31 spid24s     FROM [dbo].[Recipient] AS [t0]
2008-11-17 09:59:15.31 spid24s     WHERE (NOT ([t0].[Processed] = 1)) AND ([t0].[Retry] < @p0) AND (DATEADD(ms, (CONVERT(BigInt,@p1 * 60000)) % 86400000, DATEADD(day, (CONVERT(BigInt,@p1 * 60000)) / 86400000, [t0].[QueuedOn])) <= @p2) AND ([t0].[Domain] = @p3)
2008-11-17 09:59:15.31 spid24s     ORDER BY [t0].[Priority] DESC    
2008-11-17 09:59:15.31 spid24s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2008-11-17 09:59:15.31 spid24s     unknown    
2008-11-17 09:59:15.31 spid24s         inputbuf
2008-11-17 09:59:15.31 spid24s     (@p0 int,@p1 float,@p2 datetime,@p3 nvarchar(11))SELECT TOP (4) [t0].[RecipientId], [t0].[NewsletterId], [t0].[Email], [t0].[Domain], [t0].[Message], [t0].[Processed], [t0].[Status], [t0].[CreatedOn], [t0].[SentOn], [t0].[QueuedOn], [t0].[Journal], [t0].[Retry], [t0].[Priority], [t0].[Version], [t0].[Duration], [t0].[ReceivedOn]
2008-11-17 09:59:15.31 spid24s     FROM [dbo].[Recipient] AS [t0]
2008-11-17 09:59:15.31 spid24s     WHERE (NOT ([t0].[Processed] = 1)) AND ([t0].[Retry] < @p0) AND (DATEADD(ms, (CONVERT(BigInt,@p1 * 60000)) % 86400000, DATEADD(day, (CONVERT(BigInt,@p1 * 60000)) / 86400000, [t0].[QueuedOn])) <= @p2) AND ([t0].[Domain] = @p3)
2008-11-17 09:59:15.31 spid24s     ORDER BY [t0].[Priority] DESC    
2008-11-17 09:59:15.31 spid24s        process id=processdb31c8 taskpriority=0 logused=0 waitresource=RID: 5:1:112009:24 waittime=191 ownerId=227900 transactionname=SELECT lasttranstarted=2008-11-17T09:59:15.090 XDES=0x165d0400 lockMode=S schedulerid=4 kpid=4016 status=suspended spid=69 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2008-11-17T09:59:15.090 lastbatchcompleted=2008-11-17T09:59:15.090 clientapp=.Net SqlClient Data Provider hostname=TOMATE hostpid=1620 loginname=sa isolationlevel=read committed (2) xactid=227900 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2008-11-17 09:59:15.31 spid24s         executionStack
2008-11-17 09:59:15.31 spid24s          frame procname=adhoc line=1 stmtstart=78 sqlhandle=0x02000000c900b61a27f37feb1c248902921c5407f3cfc079
2008-11-17 09:59:15.31 spid24s     SELECT COUNT(*) AS [value]
2008-11-17 09:59:15.31 spid24s     FROM [dbo].[Recipient] AS [t0]
2008-11-17 09:59:15.31 spid24s     WHERE ([t0].[NewsletterId] = @p0) AND ([t0].[Email] = @p1)    
2008-11-17 09:59:15.31 spid24s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2008-11-17 09:59:15.31 spid24s     unknown    
2008-11-17 09:59:15.31 spid24s         inputbuf
2008-11-17 09:59:15.31 spid24s     (@p0 uniqueidentifier,@p1 nvarchar(23))SELECT COUNT(*) AS [value]
2008-11-17 09:59:15.31 spid24s     FROM [dbo].[Recipient] AS [t0]
2008-11-17 09:59:15.31 spid24s     WHERE ([t0].[NewsletterId] = @p0) AND ([t0].[Email] = @p1)    
2008-11-17 09:59:15.31 spid24s        process id=processcdf1c8 taskpriority=0 logused=154368 waitresource=KEY: 5:72057594038910976 (8a00126b493f) waittime=166 ownerId=227915 transactionname=user_transaction lasttranstarted=2008-11-17T09:59:15.123 XDES=0x16742280 lockMode=X schedulerid=2 kpid=2468 status=suspended spid=67 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2008-11-17T09:59:15.123 lastbatchcompleted=2008-11-17T09:59:15.123 clientapp=.Net SqlClient Data Provider hostname=TOMATE hostpid=1620 loginname=sa isolationlevel=read committed (2) xactid=227915 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2008-11-17 09:59:15.31 spid24s         executionStack
2008-11-17 09:59:15.31 spid24s          frame procname=adhoc line=1 stmtstart=460 stmtend=1128 sqlhandle=0x020000007774a1285b6b371d9e588fd6f41d147cee951f2f
2008-11-17 09:59:15.31 spid24s     UPDATE [dbo].[Recipient]
2008-11-17 09:59:15.31 spid24s     SET [NewsletterId] = @p2, [Email] = @p3, [Domain] = @p4, [Message] = @p5, [Processed] = @p6, [Status] = @p7, [CreatedOn] = @p8, [SentOn] = @p9, [QueuedOn] = @p10, [Journal] = @p11, [Retry] = @p12, [Priority] = @p13, [Duration] = @p14, [ReceivedOn] = @p15
2008-11-17 09:59:15.31 spid24s     WHERE ([RecipientId] = @p0) AND ([Version] = @p1)    
2008-11-17 09:59:15.31 spid24s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2008-11-17 09:59:15.31 spid24s     unknown    
2008-11-17 09:59:15.31 spid24s         inputbuf
2008-11-17 09:59:15.31 spid24s     (@p0 uniqueidentifier,@p1 timestamp,@p2 uniqueidentifier,@p3 nvarchar(18),@p4 nvarchar(9),@p5 ntext,@p6 bit,@p7 int,@p8 datetime,@p9 datetime,@p10 datetime,@p11 ntext,@p12 int,@p13 int,@p14 int,@p15 datetime,@p16 uniqueidentifier)UPDATE [dbo].[Recipient]
2008-11-17 09:59:15.31 spid24s     SET [NewsletterId] = @p2, [Email] = @p3, [Domain] = @p4, [Message] = @p5, [Processed] = @p6, [Status] = @p7, [CreatedOn] = @p8, [SentOn] = @p9, [QueuedOn] = @p10, [Journal] = @p11, [Retry] = @p12, [Priority] = @p13, [Duration] = @p14, [ReceivedOn] = @p15
2008-11-17 09:59:15.31 spid24s     WHERE ([RecipientId] = @p0) AND ([Version] = @p1)
2008-11-17 09:59:15.31 spid24s     SELECT [t1].[Version]
2008-11-17 09:59:15.31 spid24s     FROM [dbo].[Recipient] AS [t1]
2008-11-17 09:59:15.31 spid24s     WHERE ((@@ROWCOUNT) > 0) AND ([t1].[RecipientId] = @p16)    
2008-11-17 09:59:15.31 spid24s       resource-list
2008-11-17 09:59:15.31 spid24s        ridlock fileid=1 pageid=112009 dbid=5 objectname=SMTPD.dbo.Recipient id=lock1a4ca880 mode=X associatedObjectId=72057594038714368
2008-11-17 09:59:15.31 spid24s         owner-list
2008-11-17 09:59:15.31 spid24s         waiter-list
2008-11-17 09:59:15.31 spid24s          waiter id=processcdf000 mode=S requestType=wait
2008-11-17 09:59:15.31 spid24s        ridlock fileid=1 pageid=112009 dbid=5 objectname=SMTPD.dbo.Recipient id=lock1a4ca880 mode=X associatedObjectId=72057594038714368
2008-11-17 09:59:15.31 spid24s         owner-list
2008-11-17 09:59:15.31 spid24s          owner id=processcdf1c8 mode=X
2008-11-17 09:59:15.31 spid24s         waiter-list
2008-11-17 09:59:15.31 spid24s          waiter id=processdb31c8 mode=S requestType=wait
2008-11-17 09:59:15.31 spid24s        keylock hobtid=72057594038910976 dbid=5 objectname=SMTPD.dbo.Recipient indexname=IX_StatusProcessed id=lock4bbae40 mode=S associatedObjectId=72057594038910976
2008-11-17 09:59:15.31 spid24s         owner-list
2008-11-17 09:59:15.31 spid24s          owner id=processcdf000 mode=S
2008-11-17 09:59:15.31 spid24s         waiter-list
2008-11-17 09:59:15.31 spid24s          waiter id=processcdf1c8 mode=X requestType=wait
2008-11-17 09:59:22.81 spid27s     deadlock-list
2008-11-17 09:59:22.81 spid27s      deadlock victim=processcdf000
2008-11-17 09:59:22.81 spid27s       process-list
2008-11-17 09:59:22.81 spid27s        process id=processcdf000 taskpriority=0 logused=0 waitresource=RID: 5:1:131921:19 waittime=2375 ownerId=232213 transactionname=SELECT lasttranstarted=2008-11-17T09:59:20.437 XDES=0x5997de8 lockMode=S schedulerid=2 kpid=1644 status=suspended spid=62 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2008-11-17T09:59:20.437 lastbatchcompleted=2008-11-17T09:59:20.437 clientapp=.Net SqlClient Data Provider hostname=TOMATE hostpid=1620 loginname=sa isolationlevel=read committed (2) xactid=232213 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2008-11-17 09:59:22.81 spid27s         executionStack
2008-11-17 09:59:22.81 spid27s          frame procname=adhoc line=1 stmtstart=64 sqlhandle=0x0200000092de9402cf5582d43a572f9782b22fddcfe93d04
2008-11-17 09:59:22.81 spid27s     SELECT TOP (82) [t1].[Domain] AS [Name], [t1].[value] AS [Priority2]
2008-11-17 09:59:22.81 spid27s     FROM (
2008-11-17 09:59:22.81 spid27s         SELECT MAX([t0].[Priority]) AS [value], [t0].[Domain]
2008-11-17 09:59:22.81 spid27s         FROM [dbo].[Recipient] AS [t0]
2008-11-17 09:59:22.81 spid27s         WHERE (NOT ([t0].[Processed] = 1)) AND ([t0].[Retry] < @p0) AND (DATEADD(ms, (CONVERT(BigInt,@p1 * 60000)) % 86400000, DATEADD(day, (CONVERT(BigInt,@p1 * 60000)) / 86400000, [t0].[QueuedOn])) <= @p2)
2008-11-17 09:59:22.81 spid27s         GROUP BY [t0].[Domain]
2008-11-17 09:59:22.81 spid27s         ) AS [t1]
2008-11-17 09:59:22.81 spid27s     ORDER BY [t1].[value] DESC    
2008-11-17 09:59:22.81 spid27s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2008-11-17 09:59:22.81 spid27s     unknown    
2008-11-17 09:59:22.81 spid27s         inputbuf
2008-11-17 09:59:22.81 spid27s     (@p0 int,@p1 float,@p2 datetime)SELECT TOP (82) [t1].[Domain] AS [Name], [t1].[value] AS [Priority2]
2008-11-17 09:59:22.81 spid27s     FROM (
2008-11-17 09:59:22.81 spid27s         SELECT MAX([t0].[Priority]) AS [value], [t0].[Domain]
2008-11-17 09:59:22.81 spid27s         FROM [dbo].[Recipient] AS [t0]
2008-11-17 09:59:22.81 spid27s         WHERE (NOT ([t0].[Processed] = 1)) AND ([t0].[Retry] < @p0) AND (DATEADD(ms, (CONVERT(BigInt,@p1 * 60000)) % 86400000, DATEADD(day, (CONVERT(BigInt,@p1 * 60000)) / 86400000, [t0].[QueuedOn])) <= @p2)
2008-11-17 09:59:22.81 spid27s         GROUP BY [t0].[Domain]
2008-11-17 09:59:22.81 spid27s         ) AS [t1]
2008-11-17 09:59:22.81 spid27s     ORDER BY [t1].[value] DESC    
2008-11-17 09:59:22.81 spid27s        process id=processceb558 taskpriority=0 logused=154408 waitresource=KEY: 5:72057594038910976 (520044d774cc) waittime=2363 ownerId=232191 transactionname=user_transaction lasttranstarted=2008-11-17T09:59:20.420 XDES=0x1701e280 lockMode=X schedulerid=1 kpid=3212 status=suspended spid=70 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2008-11-17T09:59:20.437 lastbatchcompleted=2008-11-17T09:59:20.420 clientapp=.Net SqlClient Data Provider hostname=TOMATE hostpid=1620 loginname=sa isolationlevel=read committed (2) xactid=232191 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2008-11-17 09:59:22.81 spid27s         executionStack
2008-11-17 09:59:22.81 spid27s          frame procname=adhoc line=1 stmtstart=462 stmtend=1130 sqlhandle=0x02000000f2ed090edb568a90107d4c01455821c6f4d2272b
2008-11-17 09:59:22.81 spid27s     UPDATE [dbo].[Recipient]
2008-11-17 09:59:22.81 spid27s     SET [NewsletterId] = @p2, [Email] = @p3, [Domain] = @p4, [Message] = @p5, [Processed] = @p6, [Status] = @p7, [CreatedOn] = @p8, [SentOn] = @p9, [QueuedOn] = @p10, [Journal] = @p11, [Retry] = @p12, [Priority] = @p13, [Duration] = @p14, [ReceivedOn] = @p15
2008-11-17 09:59:22.81 spid27s     WHERE ([RecipientId] = @p0) AND ([Version] = @p1)    
2008-11-17 09:59:22.81 spid27s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2008-11-17 09:59:22.81 spid27s     unknown    
2008-11-17 09:59:22.81 spid27s         inputbuf
2008-11-17 09:59:22.81 spid27s     (@p0 uniqueidentifier,@p1 timestamp,@p2 uniqueidentifier,@p3 nvarchar(28),@p4 nvarchar(14),@p5 ntext,@p6 bit,@p7 int,@p8 datetime,@p9 datetime,@p10 datetime,@p11 ntext,@p12 int,@p13 int,@p14 int,@p15 datetime,@p16 uniqueidentifier)UPDATE [dbo].[Recipient]
2008-11-17 09:59:22.81 spid27s     SET [NewsletterId] = @p2, [Email] = @p3, [Domain] = @p4, [Message] = @p5, [Processed] = @p6, [Status] = @p7, [CreatedOn] = @p8, [SentOn] = @p9, [QueuedOn] = @p10, [Journal] = @p11, [Retry] = @p12, [Priority] = @p13, [Duration] = @p14, [ReceivedOn] = @p15
2008-11-17 09:59:22.81 spid27s     WHERE ([RecipientId] = @p0) AND ([Version] = @p1)
2008-11-17 09:59:22.81 spid27s     SELECT [t1].[Version]
2008-11-17 09:59:22.81 spid27s     FROM [dbo].[Recipient] AS [t1]
2008-11-17 09:59:22.81 spid27s     WHERE ((@@ROWCOUNT) > 0) AND ([t1].[RecipientId] = @p16)    
2008-11-17 09:59:22.81 spid27s       resource-list
2008-11-17 09:59:22.81 spid27s        ridlock fileid=1 pageid=131921 dbid=5 objectname=SMTPD.dbo.Recipient id=lock4bb90c0 mode=X associatedObjectId=72057594038714368
2008-11-17 09:59:22.81 spid27s         owner-list
2008-11-17 09:59:22.81 spid27s          owner id=processceb558 mode=X
2008-11-17 09:59:22.81 spid27s         waiter-list
2008-11-17 09:59:22.81 spid27s          waiter id=processcdf000 mode=S requestType=wait
2008-11-17 09:59:22.81 spid27s        keylock hobtid=72057594038910976 dbid=5 objectname=SMTPD.dbo.Recipient indexname=IX_StatusProcessed id=lock1a491600 mode=S associatedObjectId=72057594038910976
2008-11-17 09:59:22.81 spid27s         owner-list
2008-11-17 09:59:22.81 spid27s          owner id=processcdf000 mode=S
2008-11-17 09:59:22.81 spid27s         waiter-list
2008-11-17 09:59:22.81 spid27s          waiter id=processceb558 mode=X requestType=wait
2008-11-17 10:04:20.31 spid25s     deadlock-list
2008-11-17 10:04:20.31 spid25s      deadlock victim=processd98e38
2008-11-17 10:04:20.31 spid25s       process-list
2008-11-17 10:04:20.31 spid25s        process id=processd98e38 taskpriority=0 logused=0 waitresource=RID: 5:1:62991:20 waittime=2852 ownerId=234115 transactionname=SELECT lasttranstarted=2008-11-17T10:04:17.340 XDES=0x5093de8 lockMode=S schedulerid=3 kpid=3784 status=suspended spid=59 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2008-11-17T10:04:17.340 lastbatchcompleted=2008-11-17T10:04:17.340 clientapp=.Net SqlClient Data Provider hostname=TOMATE hostpid=1620 loginname=sa isolationlevel=read committed (2) xactid=234115 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2008-11-17 10:04:20.31 spid25s         executionStack
2008-11-17 10:04:20.31 spid25s          frame procname=adhoc line=1 stmtstart=64 sqlhandle=0x02000000acee4328384fa7e38da836a96f8d84d0594b4177
2008-11-17 10:04:20.31 spid25s     SELECT TOP (131) [t1].[Domain] AS [Name], [t1].[value] AS [Priority2]
2008-11-17 10:04:20.31 spid25s     FROM (
2008-11-17 10:04:20.31 spid25s         SELECT MAX([t0].[Priority]) AS [value], [t0].[Domain]
2008-11-17 10:04:20.31 spid25s         FROM [dbo].[Recipient] AS [t0]
2008-11-17 10:04:20.31 spid25s         WHERE (NOT ([t0].[Processed] = 1)) AND ([t0].[Retry] < @p0) AND (DATEADD(ms, (CONVERT(BigInt,@p1 * 60000)) % 86400000, DATEADD(day, (CONVERT(BigInt,@p1 * 60000)) / 86400000, [t0].[QueuedOn])) <= @p2)
2008-11-17 10:04:20.31 spid25s         GROUP BY [t0].[Domain]
2008-11-17 10:04:20.31 spid25s         ) AS [t1]
2008-11-17 10:04:20.31 spid25s     ORDER BY [t1].[value] DESC    
2008-11-17 10:04:20.31 spid25s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2008-11-17 10:04:20.31 spid25s     unknown    
2008-11-17 10:04:20.31 spid25s         inputbuf
2008-11-17 10:04:20.31 spid25s     (@p0 int,@p1 float,@p2 datetime)SELECT TOP (131) [t1].[Domain] AS [Name], [t1].[value] AS [Priority2]
2008-11-17 10:04:20.31 spid25s     FROM (
2008-11-17 10:04:20.31 spid25s         SELECT MAX([t0].[Priority]) AS [value], [t0].[Domain]
2008-11-17 10:04:20.31 spid25s         FROM [dbo].[Recipient] AS [t0]
2008-11-17 10:04:20.31 spid25s         WHERE (NOT ([t0].[Processed] = 1)) AND ([t0].[Retry] < @p0) AND (DATEADD(ms, (CONVERT(BigInt,@p1 * 60000)) % 86400000, DATEADD(day, (CONVERT(BigInt,@p1 * 60000)) / 86400000, [t0].[QueuedOn])) <= @p2)
2008-11-17 10:04:20.31 spid25s         GROUP BY [t0].[Domain]
2008-11-17 10:04:20.31 spid25s         ) AS [t1]
2008-11-17 10:04:20.31 spid25s     ORDER BY [t1].[value] DESC    
2008-11-17 10:04:20.31 spid25s        process id=processd98c70 taskpriority=0 logused=154384 waitresource=KEY: 5:72057594038910976 (1000021417b8) waittime=2640 ownerId=234168 transactionname=user_transaction lasttranstarted=2008-11-17T10:04:17.437 XDES=0x1aa8f5a0 lockMode=X schedulerid=3 kpid=2892 status=suspended spid=67 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2008-11-17T10:04:17.437 lastbatchcompleted=2008-11-17T10:04:17.437 clientapp=.Net SqlClient Data Provider hostname=TOMATE hostpid=1620 loginname=sa isolationlevel=read committed (2) xactid=234168 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2008-11-17 10:04:20.31 spid25s         executionStack
2008-11-17 10:04:20.31 spid25s          frame procname=adhoc line=1 stmtstart=462 stmtend=1130 sqlhandle=0x02000000f2f23a051e073c0461bee362cb2f96ae8094fac2
2008-11-17 10:04:20.31 spid25s     UPDATE [dbo].[Recipient]
2008-11-17 10:04:20.31 spid25s     SET [NewsletterId] = @p2, [Email] = @p3, [Domain] = @p4, [Message] = @p5, [Processed] = @p6, [Status] = @p7, [CreatedOn] = @p8, [SentOn] = @p9, [QueuedOn] = @p10, [Journal] = @p11, [Retry] = @p12, [Priority] = @p13, [Duration] = @p14, [ReceivedOn] = @p15
2008-11-17 10:04:20.31 spid25s     WHERE ([RecipientId] = @p0) AND ([Version] = @p1)    
2008-11-17 10:04:20.31 spid25s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2008-11-17 10:04:20.31 spid25s     unknown    
2008-11-17 10:04:20.31 spid25s         inputbuf
2008-11-17 10:04:20.31 spid25s     (@p0 uniqueidentifier,@p1 timestamp,@p2 uniqueidentifier,@p3 nvarchar(21),@p4 nvarchar(10),@p5 ntext,@p6 bit,@p7 int,@p8 datetime,@p9 datetime,@p10 datetime,@p11 ntext,@p12 int,@p13 int,@p14 int,@p15 datetime,@p16 uniqueidentifier)UPDATE [dbo].[Recipient]
2008-11-17 10:04:20.31 spid25s     SET [NewsletterId] = @p2, [Email] = @p3, [Domain] = @p4, [Message] = @p5, [Processed] = @p6, [Status] = @p7, [CreatedOn] = @p8, [SentOn] = @p9, [QueuedOn] = @p10, [Journal] = @p11, [Retry] = @p12, [Priority] = @p13, [Duration] = @p14, [ReceivedOn] = @p15
2008-11-17 10:04:20.31 spid25s     WHERE ([RecipientId] = @p0) AND ([Version] = @p1)
2008-11-17 10:04:20.31 spid25s     SELECT [t1].[Version]
2008-11-17 10:04:20.31 spid25s     FROM [dbo].[Recipient] AS [t1]
2008-11-17 10:04:20.31 spid25s     WHERE ((@@ROWCOUNT) > 0) AND ([t1].[RecipientId] = @p16)    
2008-11-17 10:04:20.31 spid25s       resource-list
2008-11-17 10:04:20.31 spid25s        ridlock fileid=1 pageid=62991 dbid=5 objectname=SMTPD.dbo.Recipient id=lock4bbf180 mode=X associatedObjectId=72057594038714368
2008-11-17 10:04:20.31 spid25s         owner-list
2008-11-17 10:04:20.31 spid25s          owner id=processd98c70 mode=X
2008-11-17 10:04:20.31 spid25s         waiter-list
2008-11-17 10:04:20.31 spid25s          waiter id=processd98e38 mode=S requestType=wait
2008-11-17 10:04:20.31 spid25s        keylock hobtid=72057594038910976 dbid=5 objectname=SMTPD.dbo.Recipient indexname=IX_StatusProcessed id=lock4bb4980 mode=S associatedObjectId=72057594038910976
2008-11-17 10:04:20.31 spid25s         owner-list
2008-11-17 10:04:20.31 spid25s          owner id=processd98e38 mode=S
2008-11-17 10:04:20.31 spid25s         waiter-list
2008-11-17 10:04:20.31 spid25s          waiter id=processd98c70 mode=X requestType=wait
2008-11-17 10:09:22.81 spid24s     deadlock-list
2008-11-17 10:09:22.81 spid24s      deadlock victim=processcdf720
2008-11-17 10:09:22.81 spid24s       process-list
2008-11-17 10:09:22.81 spid24s        process id=processcdf720 taskpriority=0 logused=0 waitresource=RID: 5:1:54908:21 waittime=761 ownerId=234991 transactionname=SELECT lasttranstarted=2008-11-17T10:09:22.043 XDES=0x4d68ac8 lockMode=S schedulerid=2 kpid=3896 status=suspended spid=57 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2008-11-17T10:09:22.043 lastbatchcompleted=2008-11-17T10:09:22.043 clientapp=.Net SqlClient Data Provider hostname=TOMATE hostpid=1620 loginname=sa isolationlevel=read committed (2) xactid=234991 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2008-11-17 10:09:22.81 spid24s         executionStack
2008-11-17 10:09:22.81 spid24s          frame procname=adhoc line=1 stmtstart=98 sqlhandle=0x02000000f81f9e177dbcd52b6cefd53b1ca5991712261c99
2008-11-17 10:09:22.81 spid24s     SELECT TOP (8) [t0].[RecipientId], [t0].[NewsletterId], [t0].[Email], [t0].[Domain], [t0].[Message], [t0].[Processed], [t0].[Status], [t0].[CreatedOn], [t0].[SentOn], [t0].[QueuedOn], [t0].[Journal], [t0].[Retry], [t0].[Priority], [t0].[Version], [t0].[Duration], [t0].[ReceivedOn]
2008-11-17 10:09:22.81 spid24s     FROM [dbo].[Recipient] AS [t0]
2008-11-17 10:09:22.81 spid24s     WHERE (NOT ([t0].[Processed] = 1)) AND ([t0].[Retry] < @p0) AND (DATEADD(ms, (CONVERT(BigInt,@p1 * 60000)) % 86400000, DATEADD(day, (CONVERT(BigInt,@p1 * 60000)) / 86400000, [t0].[QueuedOn])) <= @p2) AND ([t0].[Domain] = @p3)
2008-11-17 10:09:22.81 spid24s     ORDER BY [t0].[Priority] DESC    
2008-11-17 10:09:22.81 spid24s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2008-11-17 10:09:22.81 spid24s     unknown    
2008-11-17 10:09:22.81 spid24s         inputbuf
2008-11-17 10:09:22.81 spid24s     (@p0 int,@p1 float,@p2 datetime,@p3 nvarchar(10))SELECT TOP (8) [t0].[RecipientId], [t0].[NewsletterId], [t0].[Email], [t0].[Domain], [t0].[Message], [t0].[Processed], [t0].[Status], [t0].[CreatedOn], [t0].[SentOn], [t0].[QueuedOn], [t0].[Journal], [t0].[Retry], [t0].[Priority], [t0].[Version], [t0].[Duration], [t0].[ReceivedOn]
2008-11-17 10:09:22.81 spid24s     FROM [dbo].[Recipient] AS [t0]
2008-11-17 10:09:22.81 spid24s     WHERE (NOT ([t0].[Processed] = 1)) AND ([t0].[Retry] < @p0) AND (DATEADD(ms, (CONVERT(BigInt,@p1 * 60000)) % 86400000, DATEADD(day, (CONVERT(BigInt,@p1 * 60000)) / 86400000, [t0].[QueuedOn])) <= @p2) AND ([t0].[Domain] = @p3)
2008-11-17 10:09:22.81 spid24s     ORDER BY [t0].[Priority] DESC    
2008-11-17 10:09:22.81 spid24s        process id=processdb3558 taskpriority=0 logused=154392 waitresource=KEY: 5:72057594038910976 (7d00ac8a6e4c) waittime=755 ownerId=234986 transactionname=user_transaction lasttranstarted=2008-11-17T10:09:22.043 XDES=0x178fcc10 lockMode=X schedulerid=4 kpid=860 status=suspended spid=56 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2008-11-17T10:09:22.043 lastbatchcompleted=2008-11-17T10:09:22.043 clientapp=.Net SqlClient Data Provider hostname=TOMATE hostpid=1620 loginname=sa isolationlevel=read committed (2) xactid=234986 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2008-11-17 10:09:22.81 spid24s         executionStack
2008-11-17 10:09:22.81 spid24s          frame procname=adhoc line=1 stmtstart=462 stmtend=1130 sqlhandle=0x02000000143bdd1c280823d2baaa214606519050662141dc
2008-11-17 10:09:22.81 spid24s     UPDATE [dbo].[Recipient]
2008-11-17 10:09:22.81 spid24s     SET [NewsletterId] = @p2, [Email] = @p3, [Domain] = @p4, [Message] = @p5, [Processed] = @p6, [Status] = @p7, [CreatedOn] = @p8, [SentOn] = @p9, [QueuedOn] = @p10, [Journal] = @p11, [Retry] = @p12, [Priority] = @p13, [Duration] = @p14, [ReceivedOn] = @p15
2008-11-17 10:09:22.81 spid24s     WHERE ([RecipientId] = @p0) AND ([Version] = @p1)    
2008-11-17 10:09:22.81 spid24s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2008-11-17 10:09:22.81 spid24s     unknown    
2008-11-17 10:09:22.81 spid24s         inputbuf
2008-11-17 10:09:22.81 spid24s     (@p0 uniqueidentifier,@p1 timestamp,@p2 uniqueidentifier,@p3 nvarchar(23),@p4 nvarchar(11),@p5 ntext,@p6 bit,@p7 int,@p8 datetime,@p9 datetime,@p10 datetime,@p11 ntext,@p12 int,@p13 int,@p14 int,@p15 datetime,@p16 uniqueidentifier)UPDATE [dbo].[Recipient]
2008-11-17 10:09:22.81 spid24s     SET [NewsletterId] = @p2, [Email] = @p3, [Domain] = @p4, [Message] = @p5, [Processed] = @p6, [Status] = @p7, [CreatedOn] = @p8, [SentOn] = @p9, [QueuedOn] = @p10, [Journal] = @p11, [Retry] = @p12, [Priority] = @p13, [Duration] = @p14, [ReceivedOn] = @p15
2008-11-17 10:09:22.81 spid24s     WHERE ([RecipientId] = @p0) AND ([Version] = @p1)
2008-11-17 10:09:22.81 spid24s     SELECT [t1].[Version]
2008-11-17 10:09:22.81 spid24s     FROM [dbo].[Recipient] AS [t1]
2008-11-17 10:09:22.81 spid24s     WHERE ((@@ROWCOUNT) > 0) AND ([t1].[RecipientId] = @p16)    
2008-11-17 10:09:22.81 spid24s       resource-list
2008-11-17 10:09:22.81 spid24s        ridlock fileid=1 pageid=54908 dbid=5 objectname=SMTPD.dbo.Recipient id=lock52acf40 mode=X associatedObjectId=72057594038714368
2008-11-17 10:09:22.81 spid24s         owner-list
2008-11-17 10:09:22.81 spid24s          owner id=processdb3558 mode=X
2008-11-17 10:09:22.81 spid24s         waiter-list
2008-11-17 10:09:22.81 spid24s          waiter id=processcdf720 mode=S requestType=wait
2008-11-17 10:09:22.81 spid24s        keylock hobtid=72057594038910976 dbid=5 objectname=SMTPD.dbo.Recipient indexname=IX_StatusProcessed id=lock52d46c0 mode=S associatedObjectId=72057594038910976
2008-11-17 10:09:22.81 spid24s         owner-list
2008-11-17 10:09:22.81 spid24s          owner id=processcdf720 mode=S
2008-11-17 10:09:22.81 spid24s         waiter-list
2008-11-17 10:09:22.81 spid24s          waiter id=processdb3558 mode=X requestType=wait
0
marsAuthor Commented:
I changed my code to minimize the SQL processing time :
- Update on very few fields AND not on all fields
- Select count(*) is replaced by Select top 1

Here is the trace of the deadlock :

2008-11-17 11:15:11.07 spid52      DBCC TRACEON 1222, server process ID (SPID) 52. This is an informational message only; no user action is required.
2008-11-17 11:16:45.31 spid28s     deadlock-list
2008-11-17 11:16:45.31 spid28s      deadlock victim=processd998e8
2008-11-17 11:16:45.31 spid28s       process-list
2008-11-17 11:16:45.31 spid28s        process id=processd998e8 taskpriority=0 logused=0 waitresource=RID: 5:1:11105:21 waittime=2103 ownerId=298780 transactionname=SELECT lasttranstarted=2008-11-17T11:16:43.217 XDES=0x5093de8 lockMode=S schedulerid=3 kpid=696 status=suspended spid=54 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2008-11-17T11:16:43.217 lastbatchcompleted=2008-11-17T11:16:43.217 clientapp=.Net SqlClient Data Provider hostname=TOMATE hostpid=5176 loginname=sa isolationlevel=read committed (2) xactid=298780 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2008-11-17 11:16:45.31 spid28s         executionStack
2008-11-17 11:16:45.31 spid28s          frame procname=adhoc line=1 stmtstart=98 sqlhandle=0x02000000c08ac70610ff95357c1348452b373311887e623c
2008-11-17 11:16:45.31 spid28s     SELECT TOP (4) [t0].[RecipientId], [t0].[NewsletterId], [t0].[Email], [t0].[Domain], [t0].[Message], [t0].[Processed], [t0].[Status], [t0].[CreatedOn], [t0].[SentOn], [t0].[QueuedOn], [t0].[Journal], [t0].[Retry], [t0].[Priority], [t0].[Version], [t0].[Duration], [t0].[ReceivedOn]
2008-11-17 11:16:45.31 spid28s     FROM [dbo].[Recipient] AS [t0]
2008-11-17 11:16:45.31 spid28s     WHERE (NOT ([t0].[Processed] = 1)) AND ([t0].[Retry] < @p0) AND (DATEADD(ms, (CONVERT(BigInt,@p1 * 60000)) % 86400000, DATEADD(day, (CONVERT(BigInt,@p1 * 60000)) / 86400000, [t0].[QueuedOn])) <= @p2) AND ([t0].[Domain] = @p3)
2008-11-17 11:16:45.31 spid28s     ORDER BY [t0].[Priority] DESC    
2008-11-17 11:16:45.31 spid28s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2008-11-17 11:16:45.31 spid28s     unknown    
2008-11-17 11:16:45.31 spid28s         inputbuf
2008-11-17 11:16:45.31 spid28s     (@p0 int,@p1 float,@p2 datetime,@p3 nvarchar(14))SELECT TOP (4) [t0].[RecipientId], [t0].[NewsletterId], [t0].[Email], [t0].[Domain], [t0].[Message], [t0].[Processed], [t0].[Status], [t0].[CreatedOn], [t0].[SentOn], [t0].[QueuedOn], [t0].[Journal], [t0].[Retry], [t0].[Priority], [t0].[Version], [t0].[Duration], [t0].[ReceivedOn]
2008-11-17 11:16:45.31 spid28s     FROM [dbo].[Recipient] AS [t0]
2008-11-17 11:16:45.31 spid28s     WHERE (NOT ([t0].[Processed] = 1)) AND ([t0].[Retry] < @p0) AND (DATEADD(ms, (CONVERT(BigInt,@p1 * 60000)) % 86400000, DATEADD(day, (CONVERT(BigInt,@p1 * 60000)) / 86400000, [t0].[QueuedOn])) <= @p2) AND ([t0].[Domain] = @p3)
2008-11-17 11:16:45.31 spid28s     ORDER BY [t0].[Priority] DESC    
2008-11-17 11:16:45.31 spid28s        process id=processceb1c8 taskpriority=0 logused=0 waitresource=RID: 5:1:11105:21 waittime=2128 ownerId=298769 transactionname=SELECT lasttranstarted=2008-11-17T11:16:43.187 XDES=0x164ebde8 lockMode=S schedulerid=1 kpid=1396 status=suspended spid=53 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2008-11-17T11:16:43.187 lastbatchcompleted=2008-11-17T11:16:43.187 clientapp=.Net SqlClient Data Provider hostname=TOMATE hostpid=5176 loginname=sa isolationlevel=read committed (2) xactid=298769 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2008-11-17 11:16:45.31 spid28s         executionStack
2008-11-17 11:16:45.31 spid28s          frame procname=adhoc line=1 stmtstart=78 sqlhandle=0x02000000370e8d05b25faaa8332fb65b3a61d11165ae9f79
2008-11-17 11:16:45.31 spid28s     SELECT TOP (1) [t0].[RecipientId]
2008-11-17 11:16:45.31 spid28s     FROM [dbo].[Recipient] AS [t0]
2008-11-17 11:16:45.31 spid28s     WHERE ([t0].[NewsletterId] = @p0) AND ([t0].[Email] = @p1)    
2008-11-17 11:16:45.31 spid28s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2008-11-17 11:16:45.31 spid28s     unknown    
2008-11-17 11:16:45.31 spid28s         inputbuf
2008-11-17 11:16:45.31 spid28s     (@p0 uniqueidentifier,@p1 nvarchar(20))SELECT TOP (1) [t0].[RecipientId]
2008-11-17 11:16:45.31 spid28s     FROM [dbo].[Recipient] AS [t0]
2008-11-17 11:16:45.31 spid28s     WHERE ([t0].[NewsletterId] = @p0) AND ([t0].[Email] = @p1)    
2008-11-17 11:16:45.31 spid28s        process id=processcdf000 taskpriority=0 logused=940 waitresource=KEY: 5:72057594038910976 (62004ff579ae) waittime=2063 ownerId=298762 transactionname=user_transaction lasttranstarted=2008-11-17T11:16:43.170 XDES=0x4d69868 lockMode=X schedulerid=2 kpid=1644 status=suspended spid=57 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2008-11-17T11:16:43.187 lastbatchcompleted=2008-11-17T11:16:43.170 clientapp=.Net SqlClient Data Provider hostname=TOMATE hostpid=5176 loginname=sa isolationlevel=read committed (2) xactid=298762 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2008-11-17 11:16:45.31 spid28s         executionStack
2008-11-17 11:16:45.31 spid28s          frame procname=adhoc line=1 stmtstart=224 stmtend=590 sqlhandle=0x020000004d595b073b3dc8294670a9775f4a764c76be40b9
2008-11-17 11:16:45.31 spid28s     UPDATE [dbo].[Recipient]
2008-11-17 11:16:45.31 spid28s     SET [Processed] = @p2, [Status] = @p3, [SentOn] = @p4, [Journal] = @p5, [Retry] = @p6, [Duration] = @p7
2008-11-17 11:16:45.31 spid28s     WHERE ([RecipientId] = @p0) AND ([Version] = @p1)    
2008-11-17 11:16:45.31 spid28s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2008-11-17 11:16:45.31 spid28s     unknown    
2008-11-17 11:16:45.31 spid28s         inputbuf
2008-11-17 11:16:45.31 spid28s     (@p0 uniqueidentifier,@p1 timestamp,@p2 bit,@p3 int,@p4 datetime,@p5 ntext,@p6 int,@p7 int,@p8 uniqueidentifier)UPDATE [dbo].[Recipient]
2008-11-17 11:16:45.31 spid28s     SET [Processed] = @p2, [Status] = @p3, [SentOn] = @p4, [Journal] = @p5, [Retry] = @p6, [Duration] = @p7
2008-11-17 11:16:45.31 spid28s     WHERE ([RecipientId] = @p0) AND ([Version] = @p1)
2008-11-17 11:16:45.31 spid28s     SELECT [t1].[Version]
2008-11-17 11:16:45.31 spid28s     FROM [dbo].[Recipient] AS [t1]
2008-11-17 11:16:45.31 spid28s     WHERE ((@@ROWCOUNT) > 0) AND ([t1].[RecipientId] = @p8)    
2008-11-17 11:16:45.31 spid28s       resource-list
2008-11-17 11:16:45.31 spid28s        ridlock fileid=1 pageid=11105 dbid=5 objectname=SMTPD.dbo.Recipient id=lock1a4842c0 mode=X associatedObjectId=72057594038714368
2008-11-17 11:16:45.31 spid28s         owner-list
2008-11-17 11:16:45.31 spid28s         waiter-list
2008-11-17 11:16:45.31 spid28s          waiter id=processd998e8 mode=S requestType=wait
2008-11-17 11:16:45.31 spid28s        ridlock fileid=1 pageid=11105 dbid=5 objectname=SMTPD.dbo.Recipient id=lock1a4842c0 mode=X associatedObjectId=72057594038714368
2008-11-17 11:16:45.31 spid28s         owner-list
2008-11-17 11:16:45.31 spid28s          owner id=processcdf000 mode=X
2008-11-17 11:16:45.31 spid28s         waiter-list
2008-11-17 11:16:45.31 spid28s          waiter id=processceb1c8 mode=S requestType=wait
2008-11-17 11:16:45.31 spid28s        keylock hobtid=72057594038910976 dbid=5 objectname=SMTPD.dbo.Recipient indexname=IX_StatusProcessed id=lock1a487080 mode=S associatedObjectId=72057594038910976
2008-11-17 11:16:45.31 spid28s         owner-list
2008-11-17 11:16:45.31 spid28s          owner id=processd998e8 mode=S
2008-11-17 11:16:45.31 spid28s         waiter-list
2008-11-17 11:16:45.31 spid28s          waiter id=processcdf000 mode=X requestType=wait
2008-11-17 11:16:52.81 spid27s     deadlock-list
2008-11-17 11:16:52.81 spid27s      deadlock victim=processd98c70
2008-11-17 11:16:52.81 spid27s       process-list
2008-11-17 11:16:52.81 spid27s        process id=processd98c70 taskpriority=0 logused=0 waitresource=RID: 5:1:72065:24 waittime=1487 ownerId=304432 transactionname=SELECT lasttranstarted=2008-11-17T11:16:51.327 XDES=0x1b3e4138 lockMode=S schedulerid=3 kpid=2892 status=suspended spid=55 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2008-11-17T11:16:51.327 lastbatchcompleted=2008-11-17T11:16:51.327 clientapp=.Net SqlClient Data Provider hostname=TOMATE hostpid=5176 loginname=sa isolationlevel=read committed (2) xactid=304432 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2008-11-17 11:16:52.81 spid27s         executionStack
2008-11-17 11:16:52.81 spid27s          frame procname=adhoc line=1 stmtstart=64 sqlhandle=0x02000000d11d1a01669b4f961e50f1fdfbec25f1dffac6fa
2008-11-17 11:16:52.81 spid27s     SELECT TOP (97) [t1].[Domain] AS [Name], [t1].[value] AS [Priority2]
2008-11-17 11:16:52.81 spid27s     FROM (
2008-11-17 11:16:52.81 spid27s         SELECT MAX([t0].[Priority]) AS [value], [t0].[Domain]
2008-11-17 11:16:52.81 spid27s         FROM [dbo].[Recipient] AS [t0]
2008-11-17 11:16:52.81 spid27s         WHERE (NOT ([t0].[Processed] = 1)) AND ([t0].[Retry] < @p0) AND (DATEADD(ms, (CONVERT(BigInt,@p1 * 60000)) % 86400000, DATEADD(day, (CONVERT(BigInt,@p1 * 60000)) / 86400000, [t0].[QueuedOn])) <= @p2)
2008-11-17 11:16:52.81 spid27s         GROUP BY [t0].[Domain]
2008-11-17 11:16:52.81 spid27s         ) AS [t1]
2008-11-17 11:16:52.81 spid27s     ORDER BY [t1].[value] DESC    
2008-11-17 11:16:52.81 spid27s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2008-11-17 11:16:52.81 spid27s     unknown    
2008-11-17 11:16:52.81 spid27s         inputbuf
2008-11-17 11:16:52.81 spid27s     (@p0 int,@p1 float,@p2 datetime)SELECT TOP (97) [t1].[Domain] AS [Name], [t1].[value] AS [Priority2]
2008-11-17 11:16:52.81 spid27s     FROM (
2008-11-17 11:16:52.81 spid27s         SELECT MAX([t0].[Priority]) AS [value], [t0].[Domain]
2008-11-17 11:16:52.81 spid27s         FROM [dbo].[Recipient] AS [t0]
2008-11-17 11:16:52.81 spid27s         WHERE (NOT ([t0].[Processed] = 1)) AND ([t0].[Retry] < @p0) AND (DATEADD(ms, (CONVERT(BigInt,@p1 * 60000)) % 86400000, DATEADD(day, (CONVERT(BigInt,@p1 * 60000)) / 86400000, [t0].[QueuedOn])) <= @p2)
2008-11-17 11:16:52.81 spid27s         GROUP BY [t0].[Domain]
2008-11-17 11:16:52.81 spid27s         ) AS [t1]
2008-11-17 11:16:52.81 spid27s     ORDER BY [t1].[value] DESC    
2008-11-17 11:16:52.81 spid27s        process id=processcdf000 taskpriority=0 logused=940 waitresource=KEY: 5:72057594038910976 (8200bdc9a76c) waittime=1487 ownerId=304424 transactionname=user_transaction lasttranstarted=2008-11-17T11:16:51.327 XDES=0x4d69868 lockMode=X schedulerid=2 kpid=1644 status=suspended spid=56 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2008-11-17T11:16:51.327 lastbatchcompleted=2008-11-17T11:16:51.327 clientapp=.Net SqlClient Data Provider hostname=TOMATE hostpid=5176 loginname=sa isolationlevel=read committed (2) xactid=304424 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2008-11-17 11:16:52.81 spid27s         executionStack
2008-11-17 11:16:52.81 spid27s          frame procname=adhoc line=1 stmtstart=208 stmtend=538 sqlhandle=0x02000000a9380e08b3f72672249985dfb888ac1c14208a0d
2008-11-17 11:16:52.81 spid27s     UPDATE [dbo].[Recipient]
2008-11-17 11:16:52.81 spid27s     SET [Processed] = @p2, [Status] = @p3, [SentOn] = @p4, [Journal] = @p5, [Retry] = @p6
2008-11-17 11:16:52.81 spid27s     WHERE ([RecipientId] = @p0) AND ([Version] = @p1)    
2008-11-17 11:16:52.81 spid27s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2008-11-17 11:16:52.81 spid27s     unknown    
2008-11-17 11:16:52.81 spid27s         inputbuf
2008-11-17 11:16:52.81 spid27s     (@p0 uniqueidentifier,@p1 timestamp,@p2 bit,@p3 int,@p4 datetime,@p5 ntext,@p6 int,@p7 uniqueidentifier)UPDATE [dbo].[Recipient]
2008-11-17 11:16:52.81 spid27s     SET [Processed] = @p2, [Status] = @p3, [SentOn] = @p4, [Journal] = @p5, [Retry] = @p6
2008-11-17 11:16:52.81 spid27s     WHERE ([RecipientId] = @p0) AND ([Version] = @p1)
2008-11-17 11:16:52.81 spid27s     SELECT [t1].[Version]
2008-11-17 11:16:52.81 spid27s     FROM [dbo].[Recipient] AS [t1]
2008-11-17 11:16:52.81 spid27s     WHERE ((@@ROWCOUNT) > 0) AND ([t1].[RecipientId] = @p7)    
2008-11-17 11:16:52.81 spid27s       resource-list
2008-11-17 11:16:52.81 spid27s        ridlock fileid=1 pageid=72065 dbid=5 objectname=SMTPD.dbo.Recipient id=lock1a484300 mode=X associatedObjectId=72057594038714368
2008-11-17 11:16:52.81 spid27s         owner-list
2008-11-17 11:16:52.81 spid27s          owner id=processcdf000 mode=X
2008-11-17 11:16:52.81 spid27s         waiter-list
2008-11-17 11:16:52.81 spid27s          waiter id=processd98c70 mode=S requestType=wait
2008-11-17 11:16:52.81 spid27s        keylock hobtid=72057594038910976 dbid=5 objectname=SMTPD.dbo.Recipient indexname=IX_StatusProcessed id=lock4bb8f40 mode=S associatedObjectId=72057594038910976
2008-11-17 11:16:52.81 spid27s         owner-list
2008-11-17 11:16:52.81 spid27s          owner id=processd98c70 mode=S
2008-11-17 11:16:52.81 spid27s         waiter-list
2008-11-17 11:16:52.81 spid27s          waiter id=processcdf000 mode=X requestType=wait
0
mironCommented:
Hi,
before we begin, did you apply NEWSEQUENTIALID() as a default constraint for the primary key column to generate unidirectionally increasing value for the clustered key ( and applied PK on this column using default property of Clusterered index )

-- cheers
0
marsAuthor Commented:
I applied NEWSEQUENTIALID, and created a PK Key with "Create as clustered" enabled on RecipientId column.

Here is the result after running the application 5 times :
- 4 without the deadlock, all is running perfectly
- 1 with deadlock

It's a big step because before applying NEWSEQUENTIALID and "Create as clustered", i got a deadlock all the times.

I think we are close to the solution, thank you
0
PockyMasterCommented:
apart from what miron has already told you, there might be something interesting for you in:
http://msdn.microsoft.com/en-us/library/system.transactions.isolationlevel.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marsAuthor Commented:
I use The TransactionScope and set the Isolation Level to RepeatableRead and it works without any exception. The drawback is to add more code to my application like this :

using( ... new DataContext() )
  using( TransactionScope scope = new TransactionScope( Required, new TransactionOptions() { } )
  {
      Customer cust = from e in dc....

      .... insert, update, select

      scope.Complete();    
  }

It's very annoying to add this code to each access to my database without forgetting the scope.Complete().

Do you knonw a better way to setup once the isolation level for all my application ?

Thank you very much for helping me
0
PockyMasterCommented:
I personally don't know a way to set this globally, other than introducing factory methods.
I don't know how many times you need to create a transactionscope in your application?

If you need this pattern a lot, you might create a code snippet.
Btw, code that gets called by code that's already inside a transactionscope will be using that transaction as well..
0
marsAuthor Commented:
I really thank you for the help you gave me through time
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.