How to overcome a Deadlock specifically a Key Lock
-
Legend:
Storefront = the glass & entry door portion of the front of a store like 7-11
Bay = the different section of glass that are divided by aluminum verticals that span from the ground to the ceiling
Horizontal = A piece of aluminum that spans from the left to right of the inside of a bay.
I've got an application that deletes a record from time to time just like what a database does and everything is fine.
But I've got a case now where we've added a feature to where 1 - n many of rows could be deleted. What my app is doing is calling a static method for each newly deleted item because that is the way the client is sending the request to my server.
It's a loop that does a delete request for each removed / deleted item from the client, and when these request arrive at the server, they arrive one at a time in order, so my static delete method is called for each newly delete request; therefore, it creates a new process (SPID) for each time my stored procedure is called to delete that row/ record, and this leads to a key deadlock.
Keep in mind my fellow professionals I'm fairly good at SQL but I'm no guru; however, if someone can point me in the right direction I would be grateful!
Let me give you some background on what I'm developing. I working on a CAD program that the CAD UI is written JS, so my brain is already mushy!
For this specific situation here is what is happening. I'm allowing the user to add a door to a bay that has horizontals lower than what the door is in height; therefore, the new door covers up the existing horizontals. So, the solution is to remove all horizontals from a bay that are lower to the ground than what a door is in height.
Here is a portion of what the CAD UI looks like. I've got the steps in order.
1.) A bay with horizontals that are lower than what the doors height is
2.) The door is added to the bay and everything looks as if the horizontals were removed.
3.) I removed the door from that bay and the horizontals are still in place, they were never moved because the deadlocks would not allow it.
A snap shot reflecting adding a door to the bay with horizontals and then removing the door to see no changes ever took place because of deadlocks
Diagram of the important portion of this deadlock
A few of the deallock graphs that I was able to capture.
The script relevant to this question
I've got to get this resolved extremely quickly so any and all suggestions would be great.
I'm needing to know how to keep sql from deadlocking when a batch of deletes need to take place on one table.
----------------------------------------------------------------------------------------------------
--Here is the script in text format rather that that link.
USE [StorefrontSystem]
GO
/****** Object: StoredProcedure [Storefront].[proc_DeleteHorizontal] Script Date: 7/10/2013 3:16:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [Storefront].[proc_DeleteHorizontal]
@HorizontalID INT
AS
SET NOCOUNT ON;
BEGIN
BEGIN TRAN DeleteHorizontal
DECLARE @SizeID INT, @FinishID INT, @SitelineID INT;
SELECT @SizeID= h.SizeID, @FinishID = h.FinishID, @SitelineID = h.SitelineID FROM Storefront.Horizontal h
with(nolock) WHERE h.ID = @HorizontalID
DELETE FROM [Storefront].[Horizontal] WHERE [Storefront].[Horizontal].ID = @HorizontalID;
DELETE FROM Storefront.Size WHERE ID= @SizeID;
DELETE FROM Storefront.Finish WHERE ID= @FinishID;
DELETE FROM Storefront.Siteline WHERE ID= @SitelineID;
COMMIT TRAN DeleteHorizontal
END;
SET NOCOUNT OFF;
GO
/****** Object: Table [Storefront].[Finish] Script Date: 7/10/2013 3:16:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [Storefront].[Finish](
[ID] [int] IDENTITY(23,1) NOT NULL,
[Left] [varchar](30) NULL,
[Right] [varchar](30) NULL,
[Top] [varchar](30) NULL,
[Bottom] [varchar](30) NULL,
[Type] [varchar](8) NOT NULL,
[Note] [varchar](150) NULL,
CONSTRAINT [PK_Finish_ID] PRIMARY KEY CLUSTERED
(
[ID] 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
/****** Object: Table [Storefront].[Horizontal] Script Date: 7/10/2013 3:16:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [Storefront].[Horizontal](
[ID] [int] IDENTITY(11,1) NOT NULL,
[Position] [tinyint] NOT NULL,
[Name] [varchar](15) NULL,
[Floor] [decimal](8, 4) NOT NULL,
[SizeID] [int] NOT NULL,
[IsFiller] [bit] NOT NULL,
[FinishID] [int] NOT NULL,
[SitelineID] [int] NOT NULL,
[Note] [varchar](150) NULL,
CONSTRAINT [PK_Horizontal_ID] PRIMARY KEY CLUSTERED
(
[ID] 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
/****** Object: Table [Storefront].[Siteline] Script Date: 7/10/2013 3:16:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Storefront].[Siteline](
[ID] [int] IDENTITY(1,1) NOT NULL,
[LeftCompID] [int] NULL,
[RightCompID] [int] NULL,
[TopCompID] [int] NULL,
[BottomCompID] [int] NULL,
[Top] [decimal](6, 4) NULL,
[Bottom] [decimal](6, 4) NULL,
[Left] [decimal](6, 4) NULL,
[Right] [decimal](6, 4) NULL,
CONSTRAINT [PK_Siteline_ID] PRIMARY KEY CLUSTERED
(
[ID] 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
/****** Object: Table [Storefront].[Size] Script Date: 7/10/2013 3:16:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [Storefront].[Size](
[ID] [int] IDENTITY(231,1) NOT NULL,
[HeightFeet] AS ([HeightInches]/(12)) PERSISTED,
[HeightInches] [decimal](8, 4) NOT NULL,
[HeightPercent] [decimal](7, 4) NOT NULL,
[HeightPixels] AS (([HeightInches]/(12))*(25)),
[WidthFeet] AS ([WidthInches]/(12)) PERSISTED,
[WidthInches] [decimal](8, 4) NOT NULL,
[WidthPercent] [decimal](7, 4) NOT NULL,
[WidthPixels] AS (([WidthInches]/(12))*(25)),
CONSTRAINT [PK_Size_ID] PRIMARY KEY CLUSTERED
(
[ID] 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
ALTER TABLE [Storefront].[Size] SET (LOCK_ESCALATION = DISABLE)
GO
ALTER TABLE [Storefront].[Finish] ADD CONSTRAINT [DF_Finish_Left] DEFAULT (' ') FOR [Left]
GO
ALTER TABLE [Storefront].[Finish] ADD CONSTRAINT [DF_Finish_Right] DEFAULT (' ') FOR [Right]
GO
ALTER TABLE [Storefront].[Finish] ADD CONSTRAINT [DF_Finish_Top] DEFAULT (' ') FOR [Top]
GO
ALTER TABLE [Storefront].[Finish] ADD CONSTRAINT [DF_Finish_Bottom] DEFAULT (' ') FOR [Bottom]
GO
ALTER TABLE [Storefront].[Finish] ADD CONSTRAINT [DF_Finish_Type] DEFAULT ('Anodized') FOR [Type]
GO
ALTER TABLE [Storefront].[Finish] ADD CONSTRAINT [DF_Finish_Note] DEFAULT (' ') FOR [Note]
GO
ALTER TABLE [Storefront].[Horizontal] ADD CONSTRAINT [DF_Horizontal_Position] DEFAULT ((0)) FOR [Position]
GO
ALTER TABLE [Storefront].[Horizontal] ADD CONSTRAINT [DF_Horizontal_Floor] DEFAULT ((0)) FOR [Floor]
GO
ALTER TABLE [Storefront].[Horizontal] ADD CONSTRAINT [DF_Horizontal_IsFiller] DEFAULT ((0)) FOR [IsFiller]
GO
ALTER TABLE [Storefront].[Horizontal] ADD CONSTRAINT [DF_Horizontal_Note] DEFAULT (' ') FOR [Note]
GO
ALTER TABLE [Storefront].[Siteline] ADD CONSTRAINT [DF_Siteline_Left] DEFAULT ((0)) FOR [LeftCompID]
GO
ALTER TABLE [Storefront].[Siteline] ADD CONSTRAINT [DF_Siteline_Right] DEFAULT ((0)) FOR [RightCompID]
GO
ALTER TABLE [Storefront].[Siteline] ADD CONSTRAINT [DF_Siteline_Top] DEFAULT ((0)) FOR [TopCompID]
GO
ALTER TABLE [Storefront].[Siteline] ADD CONSTRAINT [DF_Siteline_Bottom] DEFAULT ((0)) FOR [BottomCompID]
GO
ALTER TABLE [Storefront].[Horizontal] WITH CHECK ADD CONSTRAINT [FK_Horizontal_Finish_FinishID] FOREIGN KEY([FinishID])
REFERENCES [Storefront].[Finish] ([ID])
GO
ALTER TABLE [Storefront].[Horizontal] CHECK CONSTRAINT [FK_Horizontal_Finish_FinishID]
GO
ALTER TABLE [Storefront].[Horizontal] WITH CHECK ADD CONSTRAINT [FK_Horizontal_Siteline_SitelineID] FOREIGN KEY([SitelineID])
REFERENCES [Storefront].[Siteline] ([ID])
GO
ALTER TABLE [Storefront].[Horizontal] CHECK CONSTRAINT [FK_Horizontal_Siteline_SitelineID]
GO
ALTER TABLE [Storefront].[Horizontal] WITH CHECK ADD CONSTRAINT [FK_Horizontal_Size_SizeID] FOREIGN KEY([SizeID])
REFERENCES [Storefront].[Size] ([ID])
GO
ALTER TABLE [Storefront].[Horizontal] CHECK CONSTRAINT [FK_Horizontal_Size_SizeID]
GO
Dam again!
-
I've managed to get beyond the Key lock by using this little snippet. I'm not sure if I'm moving in the right or wrong direction?
create nonclustered index ix_Horizontal_ID_included
on [Storefront].[Horizontal] (ID)
include (SizeID, FinishID, SitelineID)
-------------------------------------------------------------------------------
Now I'm receiving a Object Lock dead lock.
Dam again!
-
After reading this artical http://www.mssqltips.com/sqlservertip/1485/using-sql-server-indexes-to-bypass-locks/ I added a INDEX to my table in question, added a few transactions and added predicate parameters to each column that comprised the table in question.
After working with these deadlocks I tip my hat to all you data base gurus
Here is the INDEX that save me: CREATE INDEX ix_Horizontal ON [Storefront].[Horizontal](ID,SizeID, FinishID, SitelineID)
USE [StorefrontSystem]GO
/****** Object: StoredProcedure [Storefront].[proc_DeleteHorizontal] Script Date: 7/10/2013 11:48:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--CREATE INDEX ix_Horizontal ON [Storefront].[Horizontal](ID,SizeID, FinishID, SitelineID)
ALTER proc [Storefront].[proc_DeleteHorizontal]
@HorizontalID INT
AS
SET NOCOUNT ON;
BEGIN
DECLARE @SizeID INT, @FinishID INT, @SitelineID INT;
BEGIN TRAN SelectHorizontal
SELECT @SizeID= h.SizeID, @FinishID = h.FinishID, @SitelineID = h.SitelineID FROM Storefront.Horizontal h
with(nolock) WHERE h.ID = @HorizontalID
COMMIT TRAN SelectHorizontal
BEGIN TRAN DeleteHorizontal
DELETE FROM [Storefront].[Horizontal]
WHERE [Storefront].[Horizontal].ID = @HorizontalID AND SizeID = @SizeID AND FinishID = @FinishID AND SitelineID = @SitelineID;
COMMIT TRAN DeleteHorizontal
DELETE FROM Storefront.Size WHERE ID= @SizeID;
DELETE FROM Storefront.Finish WHERE ID= @FinishID;
DELETE FROM Storefront.Siteline WHERE ID= @SitelineID;
END;
SET NOCOUNT OFF;
Dam again!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply