T-SQL/SSIS - Tables locking after redesign with primary/foreign keys

Question

I've managed to get myself into a bit of a pickle, I did a quick redesign of the tables used in a notifications system & have managed to make the things entirely useless.

Any attempt to use them (select/update/insert/delete/alter) results in a query that runs indefinitely. I believe that I've managed to accidentally misuse the CASCADE option to nearing epic proportions. Below are the three tables and associated keys that I've used

I have managed to SELECT form the tables by using the "WITH (NOLOCK)" hint, however my attempts to delete/alter the CONSTRAINTS on the TBL_NOTIFICATIONS table have met with no luck. Hopefully one of you will be able to see where I've gone wrong & chastise me properly.

https://i.stack.imgur.com/ag2W5.png (Relationship structure, I don't have enough reputation in include images yet)

TBL_NOTIFICATIONS:

CREATE TABLE [dbo].[TBL_NOTIFICATIONS]
(
    [NotificationID]    INT IDENTITY(0,1)   NOT NULL,
    [ApplicationID]     INT                 NULL,
    [SubApplicationID]  INT                 NULL,
    [Title]             NVARCHAR(50)        NULL,
    [ShortDesc]         NVARCHAR(512)       NULL,
    [Link]              NVARCHAR(100)       NULL,
    [RaisedBy]          NVARCHAR(36)        NULL,
    [RaisedFor]         NVARCHAR(36)        NULL,
    [Show]              INT                 NULL,
    [DateCreated]       DATETIME            NULL,
    [DateToArchive]     DATETIME            NULL,
    CONSTRAINT [PK_TBL_NOTIFICATIONS] PRIMARY KEY CLUSTERED 
    (
        [NotificationID] 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

ALTER TABLE [dbo].[TBL_NOTIFICATIONS]
WITH CHECK ADD CONSTRAINT [FK_TBL_NOTIFICATIONS_TBL_NOTIFICATIONS_APPLICATION_DESCRIPTIONS]
FOREIGN KEY( [ApplicationID] )
REFERENCES [dbo].[TBL_NOTIFICATIONS_APPLICATION_DESCRIPTIONS] ( [ApplicationID] )
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[TBL_NOTIFICATIONS] CHECK CONSTRAINT [FK_TBL_NOTIFICATIONS_TBL_NOTIFICATIONS_APPLICATION_DESCRIPTIONS]
GO

ALTER TABLE [dbo].[TBL_NOTIFICATIONS]
WITH CHECK ADD CONSTRAINT [FK_TBL_NOTIFICATIONS_TBL_NOTIFICATIONS_SUBAPPLICATION_DESCRIPTIONS]
FOREIGN KEY( [SubApplicationID], [ApplicationID] )
REFERENCES [dbo].[TBL_NOTIFICATIONS_SUBAPPLICATION_DESCRIPTIONS] ( [SubApplicationID], [ApplicationID] )
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[TBL_NOTIFICATIONS] CHECK CONSTRAINT [FK_TBL_NOTIFICATIONS_TBL_NOTIFICATIONS_SUBAPPLICATION_DESCRIPTIONS]
GO

TBL_NOTIFICATIONS_APPLICATION_DESCRIPTIONS:

CREATE TABLE [dbo].[TBL_NOTIFICATIONS_APPLICATION_DESCRIPTIONS]
(
    [ApplicationID] [int] NOT NULL,
    [Description] [nvarchar](50) NULL,
    CONSTRAINT [PK_TBL_NOTIFICATIONS_APPLICATION_DESCRIPTIONS] PRIMARY KEY CLUSTERED 
    (
        [ApplicationID] 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

And finally...

TBL_NOTIFICATIONS_SUBAPPLICATION_DESCRIPTIONS:

CREATE TABLE [dbo].[TBL_NOTIFICATIONS_SUBAPPLICATION_DESCRIPTIONS]
(
    [SubApplicationID] [int] NOT NULL,
    [ApplicationID] [int] NOT NULL,
    [Description] [nvarchar](50) NULL,
    [ImagePath] [nvarchar](200) NULL,
    CONSTRAINT [PK_TBL_NOTIFICATIONS_SUBAPPLICATION_DESCRIPTIONS] PRIMARY KEY CLUSTERED 
    (
        [SubApplicationID] ASC,
        [ApplicationID] 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

As I've mentioned, SELECT statements can be executed against the tables by using "WITH (NOLOCK)" however any attempt to modify them meets with infinitely loading queries. I've attempted to alter the UPDATE and DELETE CONSTRAINTS (to "NO ACTION" and "SET NULL") with no success. Likewise using ALTER TABLE with DROP CONSTRAINT does nothing but run forever.

I can view the constraints when querying "sys.foreign_keys" however I don't believe I can modify system tables directly, although I'd very much like being told otherwise on this point.

Does anyone have any ideas on either: a) How to remove the constraints/keys on these tables, or b) How to drop the tables without encountering another lock

Any and all ideas welcome, especially welcome would be a hint to how on earth I've managed to make these tables immune to my SQL-fu.

Thanks in advance

score 2 · Accepted Answer · edited May 23 '17 at 12:29

2

I don't have enough reputation to comment, so forgive me for posting this as an answer, maybe a Mod wants to move it?

Have you tried going to the "Activity Monitor" in SSMS and seeing if you have a blocked process (you can kill processes from there)? A stuck process holding a lock would cause the symptoms you are describing.

There is a nice thread on blocking here How to find what is locking my tables

edited May 23 '17 at 12:29

Community

1
1

answered Jul 03 '14 at 16:13

Robert Sheahan

2,100
1
10
12

You were spot-on, for some reason I hadn't considered that. Turns out you suggested the correct answer after all! Thank you for your suggestion – Noob-o-tron 5000 Jul 03 '14 at 16:32
1

I have created a tool that helps you find out what's locking your table: https://stackoverflow.com/a/59838858/612609 Check it out, it's opensource. – Luc Bos Mar 24 '21 at 18:45

T-SQL/SSIS - Tables locking after redesign with primary/foreign keys

1 Answers1