Dead Locks for Update and Delete

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Dead Locks for Update and Delete

Author

Topic

erikkl2000

15 Posts

Posted - 2012-09-07 : 13:47:46

I have a situation to where I've got a constraint table that has a FK in my primary table.

The primary table is called Sidelite and the constraint table is called Size. When is happening is that when I delete a sidelite record I then have to delete the related size; however, when I try to delete the sidelite row the size for that sidelite is being updated and is locked, in turn, sql chooses the delete statement to the sidelite table as the one that has to be cancelled.

How can I stop all operations to the size row that is related to the sidelite row so that I can delete the sidelite then delete the size, because at this point the size is no longer valid anyway.?.?

I've been on this for a few days now and cannot get this down. Cascading is NOT an option for this situation either.

I've added a image below to illistrate my schema.

My question is as simple as this. How can I kill all operations / locks that are held for a RECORD / ROW in the size table so that I can perform a delete in the sidelite table?

[url]http://www.vinylwindows.org/help/deakLock.jpg[/url]

Thank you very much!!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-09-07 : 17:25:25

Can you show the T-SQL definition of your foreign key?

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

erikkl2000

15 Posts

Posted - 2012-09-12 : 12:38:10

The deal is I am getting a deadlock when a row in the Sidelite table is being deleted while a row in the Size table is being updated. I need a way to let sql know that if a row in the Sidelite table is being deleted that is related to a row in the size table that is being updated then "sql please remove the lock and let me delete the row in the sidelite table"

********************************HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP*****************************************************************

/****** Object: Table [Storefront].[Sidelite] Script Date: 9/12/2012 11:34:17 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [Storefront].[Sidelite](
[ID] [int] IDENTITY(231,1) NOT NULL,
[Position] [tinyint] NOT NULL,
[DoorID] [int] NOT NULL,
[SizeID] [int] NOT NULL,
[Note] [varchar](150) NULL,
CONSTRAINT [PK_Sidelite_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].[Sidelite] SET (LOCK_ESCALATION = DISABLE)
GO

ALTER TABLE [Storefront].[Sidelite] ADD CONSTRAINT [DF_Sidelite_Note] DEFAULT (' ') FOR [Note]
GO

ALTER TABLE [Storefront].[Sidelite] WITH CHECK ADD CONSTRAINT [FK_Sidelite_Door_DoorID] FOREIGN KEY([DoorID])
REFERENCES [Storefront].[Door] ([ID])
ON DELETE CASCADE
GO

ALTER TABLE [Storefront].[Sidelite] CHECK CONSTRAINT [FK_Sidelite_Door_DoorID]
GO

ALTER TABLE [Storefront].[Sidelite] WITH CHECK ADD CONSTRAINT [FK_Sidelite_Size_SizeID] FOREIGN KEY([SizeID])
REFERENCES [Storefront].[Size] ([ID])
GO

ALTER TABLE [Storefront].[Sidelite] CHECK CONSTRAINT [FK_Sidelite_Size_SizeID]
GO

--------------------------------------------------------------------------------------------------------------------

/****** Object: Table [Storefront].[Size] Script Date: 9/12/2012 11:33:52 AM ******/
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

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-12 : 13:36:36

You have a Cascading Delete for Door, why not for Size?

-Chad

erikkl2000

15 Posts

Posted - 2012-09-12 : 15:25:18

Because size has a key in door as well. This gives me an error of "Cannot Cascade Because this could cause a circular reference"

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-12 : 16:06:44

Can you delete the row in size first, then delete the sidelite row?

-Chad

erikkl2000

15 Posts

Posted - 2012-09-12 : 17:40:31

I do not think that I can do that because of the fk constraint.

erikkl2000

15 Posts

Posted - 2012-09-14 : 11:15:40

quote:
Originally posted by chadmat

Can you delete the row in size first, then delete the sidelite row?

-Chad

Any suggestions?

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-14 : 12:39:24

Get rid of all cascading, do your updates/deletes in a transaction, and access the tables in the same order. It is hard to diagnose this without seeing more info, like the deadlock graph, and the different code/transactions that are accessing the tables.

-Chad

erikkl2000

15 Posts

Posted - 2012-09-14 : 19:50:48

quote:
Originally posted by chadmat

like the deadlock graph, and the different code/transactions that are accessing the tables.

-Chad

How do I see the deadlock graph , or where is it and how do i use it?

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-15 : 04:02:38

You have to capture it in a trace.

-Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources