Внешние ключи, блокировка и конфликты обновления
Пересказ статьи Paul White. Foreign Keys, Blocking, and Update Conflicts
Большинство баз данных должны использовать внешние ключи для поддержания ссылочной целостности (RI), где это возможно. Однако есть еще кое-что, влияющее на это решение, чем просто решить использовать ограничения FK и создать их. Чтобы ваша база данных работала как можно более гладко, необходимо учесть ряд факторов.
В этой статье рассматривается один такой фактор, которые не получил широкого обсуждения: чтобы минимизировать блокирование вы должны обратить пристальное внимание на индексы, используемые для поддержания уникальности на родительской стороне связей по внешнему ключу.
Это применимо, используете ли вы блокировки read committed (чтение зафиксированных транзакций) или версионную изоляцию снимков read committed snapshot isolation (RCSI). Обе могут приводить к блокировкам, когда связи внешних ключей проверяются ядром SQL Server.
Имеется дополнительное предостережение в случае изоляции снимка (SI). Фактически та же самая проблема может привести к неожиданным (и, возможно, нелогичным) сбоям транзакций из-за явных конфликтов обновления.
Статья состоит из двух частей. В первой части рассматривается блокировка внешних ключей при уровнях изоляции read committed и read committed snapshot isolation. Вторая часть посвящена связанным с обновлением конфликтам при изоляции снимка.
Давайте сначала посмотрим, как структура индекса может повлиять на блокировку из-за проверок внешнего ключа.
Следующий пример должен запускаться при изоляции read committed. По умолчанию для SQL Server используется блокировка read committed; а для Azure SQL Database - RCSI. Выбирайте то, что вам нравится, или выполняйте скрипты по разу для каждой установки, чтобы убедиться, что поведение то же самое.
Создайте две таблицы, связанных по внешнему ключу:
Добавьте строку в родительскую таблицу:
На втором подключении обновите неключевой атрибут родительской таблицы ParentValue внутри транзакции, но пока не делайте commit:
Если хотите, можете написать в update предикат, используя естественный ключ, это никак не скажется на нашей цели.
Вернитесь на первое подключение и попытайтесь добавить дочернюю запись:
Этот оператор Insert будет блокироваться вне зависимости от того, используете вы блокирующую или версионную изоляцию транзакций read committed в этом тесте.
План выполнения для этой вставки дочерней записи имеет вид:
После вставки новой строки в дочернюю таблицу план выполнения проверяет ограничение внешнего ключа. Проверка пропускается, если вставляемый родительский id есть null (достигается посредством предиката ‘pass through’ в левом полусоединении). В представленном случае добавляемый родительский id не является null, поэтому проверка внешнего ключа выполняется.
SQL Server проверяет ограничение внешнего ключа поиском соответствующей строки в родительской таблице. Чтобы сделать это, движок не может использовать версионность строки - требуется убедиться, что проверяемые данные являются последними зафиксированными данными, а не некоторой старой версией. Движок гарантирует это, добавляя внутренний табличный хинт READCOMMITTEDLOCK к проверке внешнего ключа на родительской таблице.
Это приводит к тому, что SQL Server пытается запросить разделяемую блокировку на соответствующую строку в родительской таблице, что блокируется, поскольку другая сессия удерживает несовместную эксклюзивную блокировку, т.к. обновление пока еще не зафиксировано.
Поясним, что хинт внутренней блокировки применим только к проверке внешнего ключа. Остальная часть плана все еще использует RCSI, если вы выбрали эту реализацию уровня изоляции read committed.
Зафиксируйте или откатите открытую транзакцию во втором подключении, затем восстановите тестовую среду:
Снова создайте тестовые таблицы, но теперь вместо принятия значений по умолчанию мы сделаем первичный ключ некластеризованным, а ограничение уникальности кластеризованным:
Как и раньше добавим строку в родительскую таблицу:
Во второй сессии опять выполните обновление без фиксации. Я использую теперь естественный ключ просто для разнообразия - это не оказывает влияния на результат. Используйте суррогатный ключ, если вам нравится.
Теперь снова выполните вставку в дочернюю таблицу в первой сессии:
Теперь вставка дочерней записи не блокируется. Это справедливо при запуске и блокирующей, и версионной изоляции транзакций read committed. Это не опечатка или ошибка: RCSI ничем не отличается.
План выполнения для вставки дочерней записи теперь немного отличается:
Все так же, как и раньше (включая невидимый хинт READCOMMITTEDLOCK), за исключением того, что проверка внешнего ключа теперь использует некластеризованный уникальный индекс, принуждаемый первичным ключом родительской таблицы. В первом тесте этот индекс был кластеризованным.
Но почему мы не получаем теперь блокировки?
Еще не зафиксированное обновление родительской таблицы во второй сессии накладывает эксклюзивную блокировку на строку в кластеризованном индексе, поскольку обновляется базовая таблица. Изменения в столбце ParentValue не влияют на некластеризованный первичный ключ на ParentID, поэтому эта строка некластеризованного индекса не блокируется.
Проверка внешнего ключа может, следовательно, запросить разделемую блокировку на индекс некластеризованного первичного ключа безпрепятственно, и вставка в дочернюю таблицу происходит сразу.
Когда первичный ключ был кластеризованным, проверка внешнего ключа требовала разделяемую блокировку на тот же ресурс (строка кластеризованного индекса), который был эксклюзивно блокирован оператором обновления.
Это поведение может показаться удивительным, но это не баг. Предоставление проверке внешнего ключа своего собственного оптимизированного метода доступа позволяет избежать логически не являющейся необходимой конкуренции за блокировку. Нет необходимости блокировать поиск внешнего ключа, поскольку на атрибут ParentID не влияет на конкурентное обновление.
Если выполнить предыдущие тесты при уровне изоляции снимка (SI), вывод будет тот же самый. Вставка дочерней строки блокируется, когда внешний ключ определяется кластеризованным индексом, и не блокируется, когда поддержка ключа использует некластеризованный уникальный индекс.
Хотя имеется одно важное потенциальное отличие при использовании SI. При изоляции read committed (блокирующей или RCSI) вставка дочерней строки в конечном итоге происходит после фиксации или отката обновления во второй сессии. При использовании SI существует риск прерывания транзакции из-за явного конфликта обновления.
Тут немного более хитрая демонстрация, поскольку транзакция снимка не начинается с оператора BEGIN TRANSACTION - она начинается с первого доступа пользователя к данным после этой точки.
Первый скрипт устанавливает демонстрацию SI с еще одной фиктивной таблицей, используемой только для того, чтобы убедиться, что транзакция снимка действительно началась. Он использует изменение теста, при котором ссылочный первичный ключ определен уникальным кластеризованным индексом (по умолчанию):
Вставка родительской строки:
Все еще в первой сессии начинаем транзакцию снимка:
Во второй сессии (выполняется при любом уровне изоляции):
Попытка вставить дочернюю строку в первой сессии блокируется, как и ожидалось:
Различие имеет место, когда мы завершаем транзакцию во второй сессии. Если мы откатим её, вставка дочерней строки в первой сессии завершается успешно.
Если же мы зафиксируем открытую транзакцию:
Первая сессия сообщит о конфликте обновления и откатит транзакцию:
Конфликт обновления происходит, несмотря на тот факт, что проверяемый внешний ключ не менялся при обновлении во второй сессии.
Причина в сущности та же самая, что и в первом наборе тестов. Когда в качестве ссылочного ключа используется кластеризованный индекс, транзакция снимка встречает строку, которая изменилась с момента её запуска. Это не допускается при изоляции снимка.
Когда ключ поддерживается некластеризованным индексом, транзакция снимка видит только немодифицированную строку индекса, поэтому здесь нет блокирования и не обнаруживается никакого конфликта обновления.
Существуют многие другие обстоятельства, при которых транзакция снимка может сообщить о неожиданных конфликтах обновления или о других ошибках. Примеры можно найти в моей предыдущей статье.
Имеется много соображений относительно выбора кластеризованного индекса для таблицы с построчным хранением. Описанная здесь проблема - просто еще один фактор, который следует принимать в расчет.
Это особенно справедливо, если вы будете использовать изоляцию снимка. Никому не понравится прерывание транзакции, особенно когда это не кажется логичным. Если вы будете использовать RCSI, блокирование при чтении, когда проверяется ограничение внешнего ключа, может оказаться неожиданным и привести к тупику.
По умолчанию для ограничения PRIMARY KEY выполняется создание поддерживающего его кластеризованного индекса, если явно не определен другой индекс или ограничение вместо кластеризованного. При проектировании является хорошей привычкой явно указывать ваши намерения, поэтому я бы настоятельно советовал писать всякий раз CLUSTERED или NONCLUSTERED.
Иногда по веским причинам вы серьезно рассматриваете вариант, когда кластеризованный индекс и некластеризованный индекс имеют одни и те же ключи.
Намерением может быть обеспечить оптимальный доступ на чтение для пользовательских запросов посредством кластеризованного индекса (избегая поиска ключа), обеспечивая при этом также минимально блокирующую (и конфликтующую на обновлениях) проверку внешних ключей посредством компактного некластеризованного индекса, как показано здесь.
Это достижимо, однако имеется пара подводных камней, на которые следует обратить внимание:
Следующий фрагмент кода демонстрирует последнее различие:
Люди привыкли в значительной степени игнорировать конфликты чтения-записи в RCSI и SI. Надеюсь, что эта статья дала вам дополнительные мысли о применении физического дизайна к таблицам, связанных внешним ключом.
Это применимо, используете ли вы блокировки read committed (чтение зафиксированных транзакций) или версионную изоляцию снимков read committed snapshot isolation (RCSI). Обе могут приводить к блокировкам, когда связи внешних ключей проверяются ядром SQL Server.
Имеется дополнительное предостережение в случае изоляции снимка (SI). Фактически та же самая проблема может привести к неожиданным (и, возможно, нелогичным) сбоям транзакций из-за явных конфликтов обновления.
Статья состоит из двух частей. В первой части рассматривается блокировка внешних ключей при уровнях изоляции read committed и read committed snapshot isolation. Вторая часть посвящена связанным с обновлением конфликтам при изоляции снимка.
1. Проверки блокировки внешнего ключа
Давайте сначала посмотрим, как структура индекса может повлиять на блокировку из-за проверок внешнего ключа.
Следующий пример должен запускаться при изоляции read committed. По умолчанию для SQL Server используется блокировка read committed; а для Azure SQL Database - RCSI. Выбирайте то, что вам нравится, или выполняйте скрипты по разу для каждой установки, чтобы убедиться, что поведение то же самое.
-- Использование блокировки read committed
ALTER DATABASE CURRENT
SET READ_COMMITTED_SNAPSHOT OFF;
-- Или используйте row-versioning read committed
ALTER DATABASE CURRENT
SET READ_COMMITTED_SNAPSHOT ON;Создайте две таблицы, связанных по внешнему ключу:
CREATE TABLE dbo.Parent
(
ParentID integer NOT NULL,
ParentNaturalKey varchar(10) NOT NULL,
ParentValue integer NOT NULL,
CONSTRAINT [PK dbo.Parent ParentID]
PRIMARY KEY (ParentID),
CONSTRAINT [AK dbo.Parent ParentNaturalKey]
UNIQUE (ParentNaturalKey)
);
CREATE TABLE dbo.Child
(
ChildID integer NOT NULL,
ChildNaturalKey varchar(10) NOT NULL,
ChildValue integer NOT NULL,
ParentID integer NULL,
CONSTRAINT [PK dbo.Child ChildID]
PRIMARY KEY (ChildID),
CONSTRAINT [AK dbo.Child ChildNaturalKey]
UNIQUE (ChildNaturalKey),
CONSTRAINT [FK dbo.Child to dbo.Parent]
FOREIGN KEY (ParentID)
REFERENCES dbo.Parent (ParentID)
);Добавьте строку в родительскую таблицу:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
DECLARE
@ParentID integer = 1,
@ParentNaturalKey varchar(10) = 'PNK1',
@ParentValue integer = 100;
INSERT dbo.Parent
(
ParentID,
ParentNaturalKey,
ParentValue
)
VALUES
(
@ParentID,
@ParentNaturalKey,
@ParentValue
);На втором подключении обновите неключевой атрибут родительской таблицы ParentValue внутри транзакции, но пока не делайте commit:
DECLARE
@ParentID integer = 1,
@ParentNaturalKey varchar(10) = 'PNK1',
@ParentValue integer = 200;
BEGIN TRANSACTION;
UPDATE dbo.Parent
SET ParentValue = @ParentValue
WHERE ParentID = @ParentID;Если хотите, можете написать в update предикат, используя естественный ключ, это никак не скажется на нашей цели.
Вернитесь на первое подключение и попытайтесь добавить дочернюю запись:
DECLARE
@ChildID integer = 101,
@ChildNaturalKey varchar(10) = 'CNK1',
@ChildValue integer = 999,
@ParentID integer = 1;
INSERT dbo.Child
(
ChildID,
ChildNaturalKey,
ChildValue,
ParentID
)
VALUES
(
@ChildID,
@ChildNaturalKey,
@ChildValue,
@ParentID
);Этот оператор Insert будет блокироваться вне зависимости от того, используете вы блокирующую или версионную изоляцию транзакций read committed в этом тесте.
Объяснение
План выполнения для этой вставки дочерней записи имеет вид:
После вставки новой строки в дочернюю таблицу план выполнения проверяет ограничение внешнего ключа. Проверка пропускается, если вставляемый родительский id есть null (достигается посредством предиката ‘pass through’ в левом полусоединении). В представленном случае добавляемый родительский id не является null, поэтому проверка внешнего ключа выполняется.
SQL Server проверяет ограничение внешнего ключа поиском соответствующей строки в родительской таблице. Чтобы сделать это, движок не может использовать версионность строки - требуется убедиться, что проверяемые данные являются последними зафиксированными данными, а не некоторой старой версией. Движок гарантирует это, добавляя внутренний табличный хинт READCOMMITTEDLOCK к проверке внешнего ключа на родительской таблице.
Это приводит к тому, что SQL Server пытается запросить разделяемую блокировку на соответствующую строку в родительской таблице, что блокируется, поскольку другая сессия удерживает несовместную эксклюзивную блокировку, т.к. обновление пока еще не зафиксировано.
Поясним, что хинт внутренней блокировки применим только к проверке внешнего ключа. Остальная часть плана все еще использует RCSI, если вы выбрали эту реализацию уровня изоляции read committed.
Избежать блокировки
Зафиксируйте или откатите открытую транзакцию во втором подключении, затем восстановите тестовую среду:
DROP TABLE IF EXISTS
dbo.Child, dbo.Parent;Снова создайте тестовые таблицы, но теперь вместо принятия значений по умолчанию мы сделаем первичный ключ некластеризованным, а ограничение уникальности кластеризованным:
CREATE TABLE dbo.Parent
(
ParentID integer NOT NULL,
ParentNaturalKey varchar(10) NOT NULL,
ParentValue integer NOT NULL,
CONSTRAINT [PK dbo.Parent ParentID]
PRIMARY KEY NONCLUSTERED (ParentID),
CONSTRAINT [AK dbo.Parent ParentNaturalKey]
UNIQUE CLUSTERED (ParentNaturalKey)
);
CREATE TABLE dbo.Child
(
ChildID integer NOT NULL,
ChildNaturalKey varchar(10) NOT NULL,
ChildValue integer NOT NULL,
ParentID integer NULL,
CONSTRAINT [PK dbo.Child ChildID]
PRIMARY KEY NONCLUSTERED (ChildID),
CONSTRAINT [AK dbo.Child ChildNaturalKey]
UNIQUE CLUSTERED (ChildNaturalKey),
CONSTRAINT [FK dbo.Child to dbo.Parent]
FOREIGN KEY (ParentID)
REFERENCES dbo.Parent (ParentID)
);Как и раньше добавим строку в родительскую таблицу:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
DECLARE
@ParentID integer = 1,
@ParentNaturalKey varchar(10) = 'PNK1',
@ParentValue integer = 100;
INSERT dbo.Parent
(
ParentID,
ParentNaturalKey,
ParentValue
)
VALUES
(
@ParentID,
@ParentNaturalKey,
@ParentValue
);Во второй сессии опять выполните обновление без фиксации. Я использую теперь естественный ключ просто для разнообразия - это не оказывает влияния на результат. Используйте суррогатный ключ, если вам нравится.
DECLARE
@ParentID integer = 1,
@ParentNaturalKey varchar(10) = 'PNK1',
@ParentValue integer = 200;
BEGIN TRANSACTION
UPDATE dbo.Parent
SET ParentValue = @ParentValue
WHERE ParentNaturalKey = @ParentNaturalKey;Теперь снова выполните вставку в дочернюю таблицу в первой сессии:
DECLARE
@ChildID integer = 101,
@ChildNaturalKey varchar(10) = 'CNK1',
@ChildValue integer = 999,
@ParentID integer = 1;
INSERT dbo.Child
(
ChildID,
ChildNaturalKey,
ChildValue,
ParentID
)
VALUES
(
@ChildID,
@ChildNaturalKey,
@ChildValue,
@ParentID
);Теперь вставка дочерней записи не блокируется. Это справедливо при запуске и блокирующей, и версионной изоляции транзакций read committed. Это не опечатка или ошибка: RCSI ничем не отличается.
Объяснение
План выполнения для вставки дочерней записи теперь немного отличается:
Все так же, как и раньше (включая невидимый хинт READCOMMITTEDLOCK), за исключением того, что проверка внешнего ключа теперь использует некластеризованный уникальный индекс, принуждаемый первичным ключом родительской таблицы. В первом тесте этот индекс был кластеризованным.
Но почему мы не получаем теперь блокировки?
Еще не зафиксированное обновление родительской таблицы во второй сессии накладывает эксклюзивную блокировку на строку в кластеризованном индексе, поскольку обновляется базовая таблица. Изменения в столбце ParentValue не влияют на некластеризованный первичный ключ на ParentID, поэтому эта строка некластеризованного индекса не блокируется.
Проверка внешнего ключа может, следовательно, запросить разделемую блокировку на индекс некластеризованного первичного ключа безпрепятственно, и вставка в дочернюю таблицу происходит сразу.
Когда первичный ключ был кластеризованным, проверка внешнего ключа требовала разделяемую блокировку на тот же ресурс (строка кластеризованного индекса), который был эксклюзивно блокирован оператором обновления.
Это поведение может показаться удивительным, но это не баг. Предоставление проверке внешнего ключа своего собственного оптимизированного метода доступа позволяет избежать логически не являющейся необходимой конкуренции за блокировку. Нет необходимости блокировать поиск внешнего ключа, поскольку на атрибут ParentID не влияет на конкурентное обновление.
2. Избежать конфликтов обновления
Если выполнить предыдущие тесты при уровне изоляции снимка (SI), вывод будет тот же самый. Вставка дочерней строки блокируется, когда внешний ключ определяется кластеризованным индексом, и не блокируется, когда поддержка ключа использует некластеризованный уникальный индекс.
Хотя имеется одно важное потенциальное отличие при использовании SI. При изоляции read committed (блокирующей или RCSI) вставка дочерней строки в конечном итоге происходит после фиксации или отката обновления во второй сессии. При использовании SI существует риск прерывания транзакции из-за явного конфликта обновления.
Тут немного более хитрая демонстрация, поскольку транзакция снимка не начинается с оператора BEGIN TRANSACTION - она начинается с первого доступа пользователя к данным после этой точки.
Первый скрипт устанавливает демонстрацию SI с еще одной фиктивной таблицей, используемой только для того, чтобы убедиться, что транзакция снимка действительно началась. Он использует изменение теста, при котором ссылочный первичный ключ определен уникальным кластеризованным индексом (по умолчанию):
ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
DROP TABLE IF EXISTS
dbo.Dummy, dbo.Child, dbo.Parent;
GO
CREATE TABLE dbo.Dummy
(
x integer NULL
);
CREATE TABLE dbo.Parent
(
ParentID integer NOT NULL,
ParentNaturalKey varchar(10) NOT NULL,
ParentValue integer NOT NULL,
CONSTRAINT [PK dbo.Parent ParentID]
PRIMARY KEY (ParentID),
CONSTRAINT [AK dbo.Parent ParentNaturalKey]
UNIQUE (ParentNaturalKey)
);
CREATE TABLE dbo.Child
(
ChildID integer NOT NULL,
ChildNaturalKey varchar(10) NOT NULL,
ChildValue integer NOT NULL,
ParentID integer NULL,
CONSTRAINT [PK dbo.Child ChildID]
PRIMARY KEY (ChildID),
CONSTRAINT [AK dbo.Child ChildNaturalKey]
UNIQUE (ChildNaturalKey),
CONSTRAINT [FK dbo.Child to dbo.Parent]
FOREIGN KEY (ParentID)
REFERENCES dbo.Parent (ParentID)
);Вставка родительской строки:
DECLARE
@ParentID integer = 1,
@ParentNaturalKey varchar(10) = 'PNK1',
@ParentValue integer = 100;
INSERT dbo.Parent
(
ParentID,
ParentNaturalKey,
ParentValue
)
VALUES
(
@ParentID,
@ParentNaturalKey,
@ParentValue
);Все еще в первой сессии начинаем транзакцию снимка:
-- Сессия 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
-- Проверка, что транзакция снимка началась
SELECT COUNT_BIG(*) FROM dbo.Dummy AS D;Во второй сессии (выполняется при любом уровне изоляции):
-- Сессия 2
DECLARE
@ParentID integer = 1,
@ParentNaturalKey varchar(10) = 'PNK1',
@ParentValue integer = 200;
BEGIN TRANSACTION;
UPDATE dbo.Parent
SET ParentValue = @ParentValue
WHERE ParentID = @ParentID;Попытка вставить дочернюю строку в первой сессии блокируется, как и ожидалось:
-- Сессия 1
DECLARE
@ChildID integer = 101,
@ChildNaturalKey varchar(10) = 'CNK1',
@ChildValue integer = 999,
@ParentID integer = 1;
INSERT dbo.Child
(
ChildID,
ChildNaturalKey,
ChildValue,
ParentID
)
VALUES
(
@ChildID,
@ChildNaturalKey,
@ChildValue,
@ParentID
);Различие имеет место, когда мы завершаем транзакцию во второй сессии. Если мы откатим её, вставка дочерней строки в первой сессии завершается успешно.
Если же мы зафиксируем открытую транзакцию:
-- Сессия 2
COMMIT TRANSACTION;Первая сессия сообщит о конфликте обновления и откатит транзакцию:
Объяснение
Конфликт обновления происходит, несмотря на тот факт, что проверяемый внешний ключ не менялся при обновлении во второй сессии.
Причина в сущности та же самая, что и в первом наборе тестов. Когда в качестве ссылочного ключа используется кластеризованный индекс, транзакция снимка встречает строку, которая изменилась с момента её запуска. Это не допускается при изоляции снимка.
Когда ключ поддерживается некластеризованным индексом, транзакция снимка видит только немодифицированную строку индекса, поэтому здесь нет блокирования и не обнаруживается никакого конфликта обновления.
Существуют многие другие обстоятельства, при которых транзакция снимка может сообщить о неожиданных конфликтах обновления или о других ошибках. Примеры можно найти в моей предыдущей статье.
Выводы
Имеется много соображений относительно выбора кластеризованного индекса для таблицы с построчным хранением. Описанная здесь проблема - просто еще один фактор, который следует принимать в расчет.
Это особенно справедливо, если вы будете использовать изоляцию снимка. Никому не понравится прерывание транзакции, особенно когда это не кажется логичным. Если вы будете использовать RCSI, блокирование при чтении, когда проверяется ограничение внешнего ключа, может оказаться неожиданным и привести к тупику.
По умолчанию для ограничения PRIMARY KEY выполняется создание поддерживающего его кластеризованного индекса, если явно не определен другой индекс или ограничение вместо кластеризованного. При проектировании является хорошей привычкой явно указывать ваши намерения, поэтому я бы настоятельно советовал писать всякий раз CLUSTERED или NONCLUSTERED.
Дублированные индексы?
Иногда по веским причинам вы серьезно рассматриваете вариант, когда кластеризованный индекс и некластеризованный индекс имеют одни и те же ключи.
Намерением может быть обеспечить оптимальный доступ на чтение для пользовательских запросов посредством кластеризованного индекса (избегая поиска ключа), обеспечивая при этом также минимально блокирующую (и конфликтующую на обновлениях) проверку внешних ключей посредством компактного некластеризованного индекса, как показано здесь.
Это достижимо, однако имеется пара подводных камней, на которые следует обратить внимание:
- При наличии более одного подходящего индекса SQL Server не дает способа, гарантирующего использование того или иного индекса для проверки ограничения внешнего ключа.
Dan Guzman задокументировал свои наблюдения в Secrets of Foreign Key Index Binding, но они могут быть несовершенны и в любом случае недокументированы, а значит могут измениться.
Вы можете обойти это, убедившись, что во время создания внешнего ключа существует только один целевой индекс, но это вносит усложнение и может вызвать проблемы в будущем, если ограничение внешнего ключа когда-нибудь будет удалено и создано заново. - Если вы используете сокращенный синтаксис для внешнего ключа SQL Server будет только привязывать ограничение к первичному ключу, вне зависимости от того, является он некластеризованным или кластеризованным.
Следующий фрагмент кода демонстрирует последнее различие:
CREATE TABLE dbo.Parent
(
ParentID integer NOT NULL UNIQUE CLUSTERED
);
-- Сокращенный (неявный) синтаксис
-- падает с ошибкой 1773
CREATE TABLE dbo.Child
(
ChildID integer NOT NULL PRIMARY KEY NONCLUSTERED,
ParentID integer NOT NULL
REFERENCES dbo.Parent
);
-- Явный синтаксис выполняется успешно
CREATE TABLE dbo.Child
(
ChildID integer NOT NULL PRIMARY KEY NONCLUSTERED,
ParentID integer NOT NULL
REFERENCES dbo.Parent (ParentID)
);Люди привыкли в значительной степени игнорировать конфликты чтения-записи в RCSI и SI. Надеюсь, что эта статья дала вам дополнительные мысли о применении физического дизайна к таблицам, связанных внешним ключом.
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой