Wednesday, May 6, 2015

Design question/ideas for .Net Client Server application with deadlocking issue

Here is the DDL for the table.  (T-SQL executed against in next reply):

--DDL
CREATE TABLE [dbo].[BackgroundTasks](
[Company_Code] [nvarchar](20) NOT NULL CONSTRAINT [DF_BackgroundTasks_Company_Code]  DEFAULT (NULL),
[BackgroundTasks_ID] [int] IDENTITY(1,1) NOT NULL,
[ID_Field_Name] [nvarchar](50) NULL CONSTRAINT [DF_BackgroundTasks_ID_Field_Name]  DEFAULT (NULL),
[ID_Value] [int] NULL CONSTRAINT [DF_BackgroundTasks_ID_Value]  DEFAULT ((0)),
[Status] [nvarchar](50) NULL CONSTRAINT [DF_BackgroundTasks_Status]  DEFAULT (NULL),
[Request_Date] [datetime] NULL CONSTRAINT [DF_BackgroundTasks_Request_Date]  DEFAULT (NULL),
[Processed_Date] [datetime] NULL CONSTRAINT [DF_BackgroundTasks_Processed_Date]  DEFAULT (NULL),
[Number_Of_Tries] [int] NULL CONSTRAINT [DF_BackgroundTasks_Number_Of_Tries]  DEFAULT ((0)),
[Notes] [ntext] NULL CONSTRAINT [DF_BackgroundTasks_Notes]  DEFAULT (NULL),
[Table_Update_Collection_Size] [int] NULL CONSTRAINT [DF_BackgroundTasks_Table_Update_Collection_Size]  DEFAULT ((0)),
[Table_Update_Collection_Content] [image] NULL CONSTRAINT [DF_BackgroundTasks_Table_Update_Collection_Content]  DEFAULT (NULL),
[Action_Size] [int] NULL CONSTRAINT [DF_BackgroundTasks_Action_Size]  DEFAULT ((0)),
[Action_Content] [image] NULL CONSTRAINT [DF_BackgroundTasks_Action_Content]  DEFAULT (NULL),
[Server_Name] [nvarchar](50) NULL CONSTRAINT [DF_BackgroundTasks_Server_Name]  DEFAULT (NULL),
[Thread_ID] [int] NULL CONSTRAINT [DF_BackgroundTasks_Thread_ID]  DEFAULT (NULL),
[Batch_ID] [int] NULL,
 CONSTRAINT [PK_BackgroundTasks] PRIMARY KEY CLUSTERED 
(
[BackgroundTasks_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

/****** Object:  Index [IX_Batch_ID]    Script Date: 5/4/2015 1:24:40 PM ******/
CREATE NONCLUSTERED INDEX [IX_Batch_ID] ON [dbo].[BackgroundTasks]
(
[Batch_ID] ASC,
[BackgroundTasks_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

/****** Object:  Index [IX_Company_Code_Thread_ID]    Script Date: 5/4/2015 1:24:40 PM ******/
CREATE NONCLUSTERED INDEX [IX_Company_Code_Thread_ID] ON [dbo].[BackgroundTasks]
(
[Company_Code] ASC,
[Thread_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
GO

/****** Object:  Index [IX_BackgroundTasks]    Script Date: 5/4/2015 1:24:40 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_BackgroundTasks] ON [dbo].[BackgroundTasks]
(
[Company_Code] ASC,
[ID_Field_Name] ASC,
[ID_Value] 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, FILLFACTOR = 80)
GO

/****** Object:  Index [IX_Status]    Script Date: 5/4/2015 1:24:40 PM ******/
CREATE NONCLUSTERED INDEX [IX_Status] ON [dbo].[BackgroundTasks]
(
[Status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
GO

/****** Object:  Index [IX_Thread_ID]    Script Date: 5/4/2015 1:24:40 PM ******/
CREATE NONCLUSTERED INDEX [IX_Thread_ID] ON [dbo].[BackgroundTasks]
(
[Thread_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
GO



No comments:

Post a Comment