Cascading diamond shaped deletes in SQL

Question

If I have a simple User table in my database and a simple Item table with a User.id as a foreign key thus:

(id UNIQUEIDENTIFIER DEFAULT (NEWID()) NOT NULL,
name NVARCHAR (MAX) NULL,
email NVARCHAR (128) NULL,
authenticationId NVARCHAR (128) NULL,
createdAt DATETIME DEFAULT GETDATE() NOT NULL,
PRIMARY KEY (id))

CREATE TABLE Items
(id UNIQUEIDENTIFIER DEFAULT (NEWID()) NOT NULL,
userId UNIQUEIDENTIFIER NOT NULL,
name NVARCHAR (MAX) NULL,
description NVARCHAR (MAX) NULL,
isPublic BIT DEFAULT 0 NOT NULL,
createdAt DATETIME DEFAULT GETDATE() NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (userId) REFERENCES Users (id))

If a user is removed from the table I need all of the related items to be removed first to avoid breaking referential integrity constraints. This is easily done with CASCADE DELETE

CREATE TABLE Items
(id UNIQUEIDENTIFIER DEFAULT (NEWID()) NOT NULL,
userId UNIQUEIDENTIFIER NOT NULL,
name NVARCHAR (MAX) NULL,
description NVARCHAR (MAX) NULL,
isPublic BIT DEFAULT 0 NOT NULL,
createdAt DATETIME DEFAULT GETDATE() NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (userId) REFERENCES Users (id) ON DELETE CASCADE)

But if I also have collections which reference users, and a table collecting items into collections I am in trouble, i.e. the following additional code does not work.

CREATE TABLE Collections
(id UNIQUEIDENTIFIER DEFAULT (NEWID()) NOT NULL,
userId UNIQUEIDENTIFIER NOT NULL,
name NVARCHAR (MAX) NULL,
description NVARCHAR (MAX) NULL,
isPublic BIT DEFAULT 0 NOT NULL,
layoutSettings NVARCHAR (MAX) NULL,
createdAt DATETIME DEFAULT GETDATE() NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (userId) REFERENCES Users (id) ON DELETE CASCADE)

CREATE TABLE CollectedItems
(itemId UNIQUEIDENTIFIER NOT NULL,
collectionId  UNIQUEIDENTIFIER NOT NULL,
createdAt DATETIME DEFAULT GETDATE() NOT NULL,
PRIMARY KEY CLUSTERED (itemId, collectionId),
FOREIGN KEY (itemId) REFERENCES Items (id) ON DELETE CASCADE,
FOREIGN KEY (collectionId) REFERENCES Collections (id) ON DELETE CASCADE)

The error indicates that this "may cause cycles or multiple cascade paths". The way around this I see recommended is to

1. Redesign the tables, but I cannot see how; or, and often stated as "a last resort"
2. Use triggers.

So I remove the ON DELETE CASCADE and instead use triggers (documentation) like this:

CREATE TRIGGER DELETE_User
   ON Users
   INSTEAD OF DELETE
AS 
BEGIN
 SET NOCOUNT ON
 DELETE FROM Items WHERE userId IN (SELECT id FROM DELETED)
 DELETE FROM Collections WHERE userId IN (SELECT id FROM DELETED)
 DELETE FROM Users WHERE id IN (SELECT id FROM DELETED)
END

CREATE TRIGGER DELETE_Item
   ON Items
   INSTEAD OF DELETE
AS 
BEGIN
 SET NOCOUNT ON
 DELETE FROM CollectedItems WHERE itemId IN (SELECT id FROM DELETED)
 DELETE FROM Items WHERE id IN (SELECT id FROM DELETED)
END

CREATE TRIGGER DELETE_Collection
   ON Collections
   INSTEAD OF DELETE
AS 
BEGIN
 SET NOCOUNT ON
 DELETE FROM CollectedItems WHERE collectionId IN (SELECT id FROM DELETED)
 DELETE FROM Collections WHERE id IN (SELECT id FROM DELETED)
END

However this fails, although subtly. I have a bunch of unit tests (written in xUnit). Individually the tests always pass. But run en masse some randomly fail with a SQL deadlock. In another answer I was pointed to the SQL Profiler which shows a deadlock between two delete calls.

What is the correct way to solve these diamond shaped delete cascades?

Answer 1

Several ways of working come to mind:

1. Don't delete the user, simply deactivate it. Add a BIT field active and set it to 0 for deactivated users. Simple, easy, fast, and maintains a log what users there were in your system and what their associated state is. Usually you are not supposed to delete such information about a user, you want to keep it for future reference.

2. Don't rely on cascades and triggers, handle it yourself in code. Cascades and triggers can be hard to maintain and their behaviour hard to predict (cf the deadlock you experience).

3. If you can't/don't want to do any of the above, consider deleting everything from the User delete trigger. First disable the delete triggers on referring tables, do all your deletes, then enable the delete triggers on referring tables.

Answer 2

Another thing to try is setting isolation level to SERIALIZABLE in your trigger when you delete a user/item/collection. Since you are possibly deleting many items/collections/collected items when deleting a user, having another transaction INSERT something during this run can cause problems. SERIALIZABLE solves that to some extent.

SQL-Server uses that isolation level on cascading deletes for exactly this reason: https://learn.microsoft.com/en-us/archive/blogs/conor_cunningham_msft/conor-vs-isolation-level-upgrade-on-updatedelete-cascading-ri

Answer 3

I prefer to not have automatic cascade operations, being it DELETE or UPDATE. Just for the sake of peace of mind. Imagine you've configured your cascade deletes and then your program due to some bug tries to delete the wrong user, even though the database has some data related to it. All related data in related tables will be gone without any warning.

Normally I make sure that all related data is deleted first using explicit separate procedures, one for each related table, and then I delete the row in the master table. The delete will succeed because there are no child rows in referenced tables.

For your example I'd have a dedicated stored procedure DeleteUser with one parameter UserID, which knows what tables are related to the user and in what order the details should be deleted. This procedure is tested and is the only way to remove the user. If the rest of the program by mistake would try to directly delete a row from the Users table, this attempt would fail, if there is some data in the related tables. If the mistakenly deleted user didn't have any details, the attempt would go through, but at least you will not lose a lot of data.

For your schema the procedure may look like this:

CREATE PROCEDURE dbo.DeleteUser
    @ParamUserID int
AS
BEGIN
    SET NOCOUNT ON; SET XACT_ABORT ON;

    BEGIN TRANSACTION;
    BEGIN TRY
        -- Delete from CollectedItems going through Items
        DELETE FROM CollectedItems
        WHERE CollectedItems.itemId IN
        (
            SELECT Items.id
            FROM Items
            WHERE Items.userId = @ParamUserID
        );

        -- Delete from CollectedItems going through Collections
        DELETE FROM CollectedItems
        WHERE CollectedItems.collectionId IN
        (
            SELECT Collections.id
            FROM Collections
            WHERE Collections.userId = @ParamUserID
        );

        -- Delete Items
        DELETE FROM Items WHERE Items.userId = @ParamUserID;

        -- Delete Collections
        DELETE FROM Collections WHERE Collections.userId = @ParamUserID;

        -- Finally delete the main user
        DELETE FROM Users WHERE ID = @ParamUserID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        ...
        -- process the error
    END CATCH;
END

---

If you really want to set up cascade deletes, then I'd define one trigger, only for Users table. Again, there will be no foreign keys with a cascade delete, but the trigger on Users table would have the logic very similar to the procedure above.