Тупик Использование внешнего ключа, ссылающегося на себя

Использование SQL Server 2005: у меня есть таблица с внешним ключом, ссылающимся на себя.

Взаимная блокировка возникает, когда обновление и вставка в эту таблицу происходят одновременно, но только при следующих условиях:

  • обновление происходит 1-го
  • обновление относится к записи, на которую будет ссылаться внешний ключ оператора вставки

Во время обновления блокировка X создается для первичного ключа, который является кластеризованным индексом таблицы. Я пробовал несколько вещей, чтобы попытаться предотвратить это, например:

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

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

Спасибо.


sql foreign-keys indexing deadlock locking
person Stew52    schedule 05.05.2011    source источник
comment
У вас взаимоблокировка (одна из транзакций завершается с ошибкой 1205) или ожидание блокировки?   -  person Quassnoi    schedule 05.05.2011

Ответы (4)


arrow_upward
0
arrow_downward

Вы пытались разделить свои транзакции UPDATE и INSERT с помощью BEGIN TRANSACTION и COMMIT. Так вы избежите тупиковых ситуаций.

Что-то такое;

BEGIN TRANSACTION insert
  <INSERT SQL>
  COMMIT TRANSACTION insert
  BEGIN TRANSACTION update
     <UPDATE SQL>
  COMMIT TRANSACTION update
END
person mevdiven    schedule 05.05.2011
comment
К сожалению, эти запросы выполняются в разных хранимых процедурах, которые выполняются в отдельных потоках, поэтому я не могу синхронно фиксировать один за другим. - person Stew52; 05.05.2011
comment
Существует общая концепция СУБД, называемая двухфазной фиксацией (2PC). Вы можете использовать это для достижения желаемого. Но вам все равно нужно использовать операторы BEGIN/COMMIT. Вот некоторые ссылки; SQL Server: msdn.microsoft.com/en- us/library/aa754091(v=bts.10).aspx Oracle: download.oracle.com/docs/cd/E14072_01/server.112/e10595/ MySQL: dev.mysql.com/doc/refman/5.0/en/xa.html - person mevdiven; 05.05.2011
comment
какое отношение двухфазная фиксация имеет к этой проблеме? - person Quassnoi; 05.05.2011
comment
Quassnoi, ты прав. Вопрос не имеет отношения к 2PC. Я просто отклонился от темы. Спасибо... - person mevdiven; 05.05.2011

arrow_upward
0
arrow_downward

Пожалуйста, предоставьте DDL и ваши операторы UPDATE / INSERT. Я не могу это воспроизвести.

CREATE TABLE T
(
id int identity(1,1) primary key,
refid int references T(id),
filler char(10)
)

INSERT INTO T (refid)
select number 
FROM master..spt_values where number between 1 and 2248

Соединение 1

BEGIN TRAN
UPDATE T SET filler = 'A' WHERE id=500

Соединение 2

BEGIN TRAN
INSERT INTO T (refid) VALUES (500) /*Blocked - No deadlock*/
person Martin Smith    schedule 05.05.2011
comment
99% пользователей имеют в виду lock, говоря о deadlock. Они должны были назвать это lock resolution error или как-то так. - person Quassnoi; 05.05.2011
comment
Вы правы, мы видим блокировку ожидания при запуске процедур из окон запросов на сервере sql. Однако у нас есть взаимоблокировка в этой области кода и журнал трассировки, в котором эти процедуры выделены как причина. В настоящее время мы считаем, что это возможная причина тупика. - person Stew52; 05.05.2011
comment
@ Stew52 - У вас есть график взаимоблокировок? Если да, не могли бы вы отредактировать XML в своем вопросе? - person Martin Smith; 05.05.2011

arrow_upward
0
arrow_downward

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

Это предотвращает размещение блокировки S на затронутом ресурсе, который требуется для получения его значения.

Поскольку для вставки значения в дочернюю таблицу требуется сверить его с родительской таблицей, оператору INSERT придется ждать до тех пор, пока транзакция UPDATE не зафиксируется или не откатится.

Если вы сделаете PRIMARY KEY некластеризованным, UPDATE не повлияет на него (если только вы не обновите сам PRIMARY KEY, что вы не должны делать в обычных обстоятельствах), поэтому INSERT будет успешным.

Команды ниже работают для меня:

Транзакция 1:

CREATE TABLE parent (id INT NOT NULL PRIMARY KEY NONCLUSTERED, value INT NOT NULL, parentId INT REFERENCES parent)

INSERT
INTO    parent
VALUES  (1, 1, NULL)

BEGIN TRANSACTION
UPDATE  parent
SET     value = 2
WHERE   id = 1

Транзакция 2:

INSERT
INTO    parent
VALUES  (2, 1, 1)
person Quassnoi    schedule 05.05.2011
comment
Наша проблема в том, что есть одна таблица, и она ссылается на себя. Ребенок не может находиться за отдельным столом. - person Stew52; 05.05.2011
comment
@Stew52: сначала не заметил, моя вина :) Впрочем, это не имеет значения. Пожалуйста, смотрите обновление поста. - person Quassnoi; 05.05.2011

arrow_upward
0
arrow_downward

Недавно я столкнулся с точно такой же проблемой. Как упоминалось в этом сообщении, решение для меня состояло в том, чтобы просто добавить индекс в столбце, ссылающемся на себя (не в pk). После этого зависания полностью исчезли.

person Kirk Woll    schedule 22.06.2011