Характер блокировки дочерней таблицы при удалении (сервер sql)

Через пару дней я думаю о следующем сценарии

Представьте, что у меня есть 2 таблицы с родительскими и дочерними отношениями типа один ко многим. При удалении родительской строки я должен удалить строки в дочерних, которые связаны с родителями. просто верно?

я должен сделать транзакцию для выполнения вышеуказанной операции, я могу сделать это следующим образом; (это код psuedo, но я делаю это в коде C#, используя соединение odbc, а база данных является сервером sql)

  1. начать транзакцию (чтение совершено)
  2. Прочитайте все child, где child.fk = p1
  3. foreach (child) удалить child, где child.pk = cx
  4. удалить родителя, где parent.pk = p1
  5. совершить транс

ИЛИ ЖЕ

  1. начать транзакцию (чтение совершено)
  2. удалить всех потомков, где child.fk = p1
  3. удалить родителя, где parent.pk = p1
  4. совершить транс

Теперь у меня есть пара вопросов

  1. Какой из вышеперечисленных лучше использовать, особенно учитывая сценарий системы реального времени, в которой тысячи других операций (выбор / обновление / удаление / вставка) выполняются в течение нескольких секунд.

  2. гарантирует ли это, что новый дочерний элемент с child.fk = p1 не будет добавлен до завершения транзакции?

  3. Если да на второй вопрос, то как это обеспечить? сделать это взять блокировки уровня таблицы или что.

  4. Есть ли какая-либо блокировка индекса, поддерживаемая сервером sql, если да, что он делает и как его можно использовать.

С уважением Мубашар

Источник

2 ответа

Решение

Я никогда не использовал (и никогда не имел законной необходимости) каскадное удаление, а также не использовал триггеры для обеспечения этого. Основными причинами этого являются:

  • Удаление даже не разрешено в приложении - вещи помечены как удаленные, или они постоянно согласованы во времени и имеют даты вступления в силу, даты окончания и т. Д.
  • Я хочу знать, если родитель удален случайно, что все связанное с ним вещество не просто исчезает - поэтому RI без каскадного удаления защищает от удаления целого дерева зависимостей
  • Заставляет проект приложения и базы данных быть более внимательным к взаимозависимости сущностей и обеспечивать надлежащий рефакторинг как структуры, так и процессов.
  • Принудительное создание соответствующей процедуры удаления для сущности позволяет вам выбирать порядок каждого шага и потенциально избегать взаимоблокировок, а также обеспечивать настройку ваших запросов.

Единственное преимущество каскадного удаления, которое я вижу, состоит в том, что оно декларативно, определено с помощью таблиц и, по-видимому, будет иметь наименьшее возможное пространство для эскалации блокировки. Вышеуказанные преимущества перевешивают его использование, на мой взгляд.

Как и во втором примере, я бы включил в транзакцию (обычно в хранимой процедуре):

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

Вся транзакция либо удастся оставить вашу базу данных в согласованном состоянии, либо не сможет зафиксировать какие-либо изменения, если все дочерние или родительские объекты не могут быть удалены по какой-либо причине - т. Е. Если была другая ссылка FK на дочернего или родительского элемента, не учтенного при удалении.,

База данных всегда будет обеспечивать вашу ссылочную целостность.

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
Источник

Оба ваших подхода неверны. Вы должны всегда:

  • сначала вставьте родительский, а затем дочерний
  • сначала обновите родительский, затем дочерний
  • сначала удалите родителя, потом ребенка
  • сначала выберите родителя, затем потомка

Более того, объявите ссылочную целостность с помощью каскадного удаления и разрешите удаление дочерних элементов.

Суть проблемы заключается в том, что вы должны выбрать порядок и придерживаться его, либо родитель-ребенок, либо потомок-родитель. Последнее не имеет смысла в большинстве случаев, поэтому лучше придерживаться первого.

Источник
Другие вопросы по тегам