Nature of Lock is child table while deletion(sql server)

Question

Dear Devs

From couple of days i am thinking of a following scenario

Consider I have 2 tables with parent child relationship of kind one-to-many. On removal of parent row i have to delete the rows in child those are related to parents. simple right?

i have to make a transaction scope to do above operation i can do this as following; (its psuedo code but i am doing this in c# code using odbc connection and database is sql server)

1. begin transaction(read committed)
2. Read all child where child.fk = p1
3. foreach(child) delete child where child.pk = cx
4. delete parent where parent.pk = p1
5. commit trans

OR

1. begin transaction(read committed)
2. delete all child where child.fk = p1
3. delete parent where parent.pk = p1
4. commit trans

Now there are couple of questions in my mind

1. Which one of above is better to use specially considering a scenario of real time system where thousands of other operations(select/update/delete/insert) are being performed within a span of seconds.

2. does it ensure that no new child with child.fk = p1 will be added until transaction completes?

3. If yes for 2nd question then how it ensures? do it take the table level locks or what.

4. Is there any kind of Index locking supported by sql server if yes what it does and how it can be used.

Regards Mubashar

Answer 1

Both your approaches are wrong. You should always:

• insert parent first, then child
• update parent first, then child
• delete parent first, then child
• select parent first, then child

Better still, declare referential integrity with cascade deletes and let that handle the deletion of children.

The gist of the problem is that you have to choose an order and stick with it, either parent-to-child or child-to-parent. The later makes no sense on most cases, so better stick with the first.

Answer 2

I never use (and have never had a legitimate need for) cascading deletes, and also have not used triggers to enforce this. Main reasons for this is that typically:

• Deletes aren't even allowed in the app - things are marked deleted, or they are temporally consistent for all time and have effective dates, termination dates, etc.
• I want to know if a parent is deleted accidentally that all the stuff associated with them doesn't simply vanish - so RI without cascading deletes protects from removable of an entire tree of dependencies
• Forces application and database design to be more thoughtful about the interdependencies of entities and ensure proper refactoring of both structure and processes
• Forcing creation of an appropriate delete procedure for an entity allows you to choose the order of every step and potentially avoid deadlocks - and also ensure your queries are tuned.

The only advantage of cascading deletes I can see is that it's declarative, defined with the tables and would presumably have the smallest possible lock escalation footprint. The benefits above outweigh its use in my view.

As in your second example, I would enclose in a transaction (usually in a stored proc):

DELETE FROM child WHERE child.fk IN (set to delete);
DELETE FROM parent WHERE parent.pk IN (set to delete);

The entire transaction will either succeed leaving your database in a consistent state or fail to commit any changes if the children or parent cannot all be deleted for any reason - i.e. if there was another FK reference to a child or parent not accounted for in your delete.

The database is going to ensure your referential integrity at all times.

USE SandBox
GO

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'Child')
                    AND type IN ( N'U' ) ) 
    DROP TABLE dbo.Child
GO

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'Parent')
                    AND type IN ( N'U' ) ) 
    DROP TABLE dbo.Parent
GO

CREATE TABLE Parent
    (
     PK INT NOT NULL
            IDENTITY
    ,Nm VARCHAR(15)
    ,PRIMARY KEY ( PK )
    )
GO

CREATE TABLE Child
    (
     PK INT NOT NULL
            IDENTITY
    ,FK INT NOT NULL
    ,Nm VARCHAR(15)
    ,PRIMARY KEY ( PK )
    ) 
GO

ALTER TABLE Child
        WITH CHECK
ADD CONSTRAINT FK_Child_Parent FOREIGN KEY ( FK ) REFERENCES Parent ( PK )
GO

DECLARE @LastParent AS INT

INSERT  INTO Parent ( Nm )
VALUES  ( 'Donald Duck' )
SET @LastParent = SCOPE_IDENTITY()

INSERT  INTO Child ( FK, Nm )
VALUES  ( @LastParent, 'Huey' )
INSERT  INTO Child ( FK, Nm )
VALUES  ( @LastParent, 'Dewey' )
INSERT  INTO Child ( FK, Nm )
VALUES  ( @LastParent, 'Louie' )

SELECT  *
FROM    Parent
SELECT  *
FROM    Child
GO

BEGIN TRANSACTION
DELETE  FROM Child
WHERE   FK = ( SELECT   PK
               FROM     Parent
               WHERE    Nm = 'Donald Duck'
             )
 -- Run just to here
 -- In another session do this:
 -- INSERT INTO Child (FK, Nm) VALUES ((SELECT PK FROM Parent WHERE Nm = 'Donald Duck'), 'Cuckoo')
 -- Then return here
DELETE  FROM Parent
WHERE   Nm = 'Donald Duck' -- Should fail
IF @@ERROR <> 0 
    ROLLBACK TRANSACTION
ELSE 
    COMMIT TRANSACTION

SELECT  *
FROM    Parent
SELECT  *
FROM    Child