Базы данных Oracle - статьи
Неиндексированный внешний ключ
Этот сценарий взаимного блокирования возникает из-за особенностей работы Oracle с таблицами, связанными друг с другом внешними ключами. Если внешний ключ не проиндексирован, то при попытке изменить значение первичного ключа или удаления строки родительской таблицы, происходит полное блокирование дочерней таблицы. Раньше, ещё до появления версии Oracle 9.2, такое блокирование могло продолжаться до окончания транзакции, что приводило к большой вероятности возникновения взаимного блокирования. Теперь такая блокировка выставляется только на момент выполнения команды, но всё равно при этом между двумя сеансами легко может возникнуть тупиковая ситуация бесконечного ожидания. Чтобы убедиться в этом, мы попробуем смоделировать подобный сценарий взаимного блокирования.
Для начала создадим две таблицы:
ZH@XE> CREATE TABLE t6(c1 NUMBER PRIMARY KEY, c2 VARCHAR2(50)); Таблица создана
ZH@XE> CREATE TABLE t7(c1 NUMBER PRIMARY KEY, c2 VARCHAR2(50), c3 NUMBER REFERENCES t6(c1) ON DELETE CASCADE); Таблица создана
Созданная нами дочерняя таблица t7 имеет неиндексированный внешний ключ с опцией ON DELETE CASCADE на родительскую таблицу t6 . Вставим в эти таблицы строки:
ZH@XE> INSERT INTO t6 (c1, c2) VALUES(1, 'Строка1'); Вставлено: 1 строка
ZH@XE> INSERT INTO t6 (c1, c2) VALUES(2, 'Строка2'); Вставлено: 1 строка
ZH@XE> INSERT INTO t6 (c1, c2) VALUES(3, 'Строка3'); Вставлено: 1 строка
ZH@XE> INSERT INTO t7 (c1, c2, c3) VALUES(1, 'Строка1', 1); Вставлено: 1 строка
ZH@XE> INSERT INTO t7 (c1, c2, c3) VALUES(2, 'Строка2', 2); Вставлено: 1 строка
Далее удалим в первом и во втором сеансе из подчинённой таблицы t7 по одной строке.
Первый сеанс:
ZH@XE(27)> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; Session altered
ZH@XE(27)> DELETE FROM t7 WHERE c1 = 1; Удалено: 1 строка
Второй сеанс:
ZH@XE>(21) DELETE FROM t7 WHERE c1 = 2; Удалено: 1 строка
В результате удаления строк на таблицу t7 оказались выставлены две блокировки таблицы (TM). Данные блокировки всегда выставляются перед установкой блокировок транзакций (TX), чтобы предотвратить изменение структуры таблицы. Посмотрим, как всё это отображается в представлении v$lock:
SQL> SELECT * FROM v$lock WHERE sid IN (27, 21) AND type = 'TM' ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- --- ---- ----- --- ----- ------- ----- ----- 296DAA90 296DAAA8 27 TM 13986 0 2 0 81 0 296DAB54 296DAB6C 27 TM 13988 0 3 0 81 0 296DAC18 296DAC30 21 TM 13986 0 2 0 39 0 296DACDC 296DACF4 21 TM 13988 0 3 0 39 0 Выбрано: 4 строки
Вместо двух TM-блокировок на таблицу t7 в представлении мы наблюдаем все четыре блокировки. Проверим, каким объектам они принадлежат. Для этого выполним следующий запрос к представлению dba_objects, подставив в качестве номера объекта содержимое столбца ID1:
SQL> SELECT object_id, owner, object_name FROM dba_objects WHERE object_id IN (13986, 13988); OBJECT_ID OWNER OBJECT_NAME --------- ----- ----------- 13986 ZH T6 13988 ZH T7 Выбрано: 2 строки
Судя по содержимому представления v$lock, сейчас у нас действительно выставлено две блокировки ТМ на дочернюю таблицу t7 в монопольном строчном режиме (LMODE=3 - SX). К тому же, так как эта таблица связана внешним неиндексированным ключом с родительской таблицей t6, мы наблюдаем дополнительно ещё две блокировки TM в минимальном разделяемом строчном режиме (LMODE=2 - SS) на таблицу t6. Эти две последние блокировки в принципе безобидны. Они просто не дают заблокировать другим сеансам родительскую таблицу t6 в монопольном режиме на время изменения строк в дочерней таблице. Это предохранит изменение структуры данных родительской таблицы. В остальном над таблицей можно выполнять любые действия: выбирать, добавлять, обновлять и удалять строки этой же таблицы. Попробуем, к примеру, в первом сеансе удалить вторую строку таблицы t6:
ZH@XE(27)> DELETE FROM t6 WHERE c1 = 2;
Почему возникло ожидание? Ведь блокировка SS довольно мягкая. Попробуем разобраться в этом. Заглянем для начала в файл трассировки:
WAIT #6: nam='enq: TM - contention' ela= 2999804 name|mode=1414332421 object #=13988 table/partition=0 obj#=-1 tim=9484943543
В первом сеансе у нас постоянно возникает ожидание «Конкуренция TM блокировки». В принципе, данное ожидание можно было бы объяснить тем, что при выполнении оператора удаления первый сеанс попытался выставить TM-блокировку в режиме SX на родительскую таблицу t6. Но как мы выяснили раньше, выставленные ранее на неё блокировки таблицы в разделяемом строчном режиме (SS) не должны приводить к ожиданию. Так какая очередь возникла? Чтобы разобраться в этой ситуации, заглянем в представление v$lock:
SQL> SELECT * FROM v$lock WHERE sid IN (27, 21) AND type = 'TM' ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- --- ---- ----- --- ----- ------- ----- ----- 296DAA90 296DAAA8 27 TM 13986 0 3 0 15 0 296DAB54 296DAB6C 27 TM 13988 0 3 5 162 0 296DAC18 296DAC30 21 TM 13986 0 2 0 120 0 296DACDC 296DACF4 21 TM 13988 0 3 0 120 1 Выбрано: 4 строки
И действительно, мы видим, что первый сеанс преобразовал TM-блокировку для таблицы t6 из разделяемого строчного режима (LMODE=2 - SS) в монопольный строчной режим (LMODE=3 - SX), что говорит о фактическом удалении строки из родительской таблицы t6. В тоже время, мы наблюдаем ожидающий запрос на установку TM-блокировки в разделяемом монопольно - строчном режиме (REQUEST=5 - SSX) на таблицу t7. Не забываем, что внешний ключ у нас создан с опцией ON DELETE CASCADE, поэтому данная блокировка всегда накладывается на дочернюю таблицу при каскадном удалении из неё строк, что фактически не позволяет совершать какие-либо действия над данными этой таблицы, кроме текущей транзакции.
В нашем случае, чтобы выполнить каскадное удаление в дочерней таблице, ожидающий запрос в первом сеансе пытается преобразовать TM-блокировку на дочернюю таблицу t7 из ранее установленного монопольно-строчного режима (SX) в разделяемый монопольно-строчный режим (SSX). Но второй сеанс ранее уже выставил на данную таблицу блокировку в монопольно-строчном режиме (SX), и это препятствует преобразованию блокировок. Данная ситуация чётко прослеживается в представлении v$lock, где мы видим, что столбец BLOCK установившейся TM-блокировки второго сеанса содержит значение 1. Это явно указывает на то, что каскадное удаление ожидает освобожденим именно этой блокировки.
Итак, повторимся. Возникающее у нас ожидание происходит из- за попыток каскадного удаления заблокированных строк в дочерней таблице. Но тогда попробуем удалить из родительской таблицы t6 третью строку, не имеющую ссылающихся на неё записей в дочерней таблице:
ZH@XE(21)> DELETE FROM t6 WHERE c1 = 3; Удалено: 1 строка
Возникло бесконечное ожидание, что привело к ошибке взаимной блокировки в первом сеансе:
ZH@XE(27)> DELETE FROM t6 WHERE c1 = 2; DELETE FROM t6 WHERE c1 = 2 * Ошибка в строке 1: ORA-00060: deadlock detected while waiting for resource
Почему произошло взаимное блокирование? Ведь ссылающих записей в дочерней таблице нет, и, следовательно, второй сеанс не мог осуществлять каскадное удаление строк в дочерней таблице. Единственное, чем можно было бы объяснить такое поведение это то, что когда происходит удаление строки из родительской таблицы, оператор удаления применяется также и к дочерней таблице, причем независимо от того, есть ли там что удалять. В нашем случае второй сеанс, пытаясь удалить третью строку родительской таблицы t6, столкнулся с ситуацией, когда он не смог установить блокировку в разделяемом монопольно-строчном режиме (SSX) на дочернюю таблицу t7, так как на неё ранее уже была выставлена TM-блокировка в SX режиме в первом сеансе. В тоже время, первый сеанс уже ожидал преобразования этой TM-блокировки, и поэтому он никогда не смог бы самостоятельно освободить её. Таким образом, у нас возникла ситуация взаимного блокирования между двумя сеансами. Чтобы визуально её представить, построим схему графа ожидающих транзакций:
В качестве вершин ресурсов в графе выступают блокировки таблицы TM. Красными стрелками отображен цикл ожиданий или взаимная блокировка. Сплошные линии показывают рёбра, образованные в результате выполнения SQL операторов. Пунктирные стрелки отображают рёбра, возникающие в результате выполнения правил ссылочной целостности внешнего ключа. Как видно из графа, в процесс взаимной блокировки вовлечён всего лишь один ресурс – это блокировка TM дочерней таблицы t7. И это не ошибка. В этом мы убедимся, когда будем рассматривать содержимое трассировочного файла взаимной блокировки. Что мы сейчас и сделаем.
В первой секции файла мы видим отменённый оператор удаления:
Current SQL statement for this session: DELETE FROM t6 WHERE c1 = 2
Содержимое графа кардинально изменилось по сравнению с предыдущими сценариями взаимной блокировки:
Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TM-000036a4-00000000 22 27 SX SSX 26 21 SX SSX TM-000036a4-00000000 26 21 SX SSX 22 27 SX SSX
В первом столбце мы наблюдаем ресурсы, участвующие во взаимном блокировании. В нашем случае это только один ресурс, представляющий собой TM-блокировку. Имя ресурса состоит из буквенного идентификатора TM и шестнадцатеричного значения номера объекта, то есть таблицы. Переведя это значение в десятичный вид, мы увидим, что в нашем случае это будет таблица t7. Далее видно, что в столбцах hold и waits присутствуют символьные обозначения режимов блокировок SX и SSX. Эти режимы встречались нам ранее при разборе ситуации ожидания. Наличие их одновременно в столбцах holds и waits не должно нас смущать. В принципе, это только обозначает, что сеанс пытается преобразовать режим ранее установленной блокировки, отображённый в столбце holds, в режим, указанный в столбце waits.
Граф читается следующим образом. Сеанс 27 выставил TM-блокировку в SX-режиме и пытается преобразовать её в SSX-режим, чтобы выполнить каскадное удаление. Его ожидает сеанс 21, который выставил ранее TM-блокировку в SX-режиме и также пытается преобразовать её в SSX-режим. Во второй строке графа ситуация повторяется с точностью наоборот, здесь в качестве ожидающего уже будет сеанс 27. Преобразования блокировок в нашем случае невозможны из-за того, что каждый конкурирующий сеанс уже держит на таблице блокировку TM в SX-режиме.
Так как TM-блокировка – это блокировка таблицы, а не строк, секция Rows waited on в файле трассировки всегда будет пустой, и поэтому на неё не стоит даже обращать внимания:
Rows waited on: Session 21: no row Session 27: no row
Определить вид данного сценария по содержимому трассировочного файла довольно просто. Это, в первую очередь, наличие в графе TM блокировки в SX- и SSX-режимах. Во-вторых, данная взаимная блокировка может возникать только при удалении строк или изменении первичного ключа родительской таблицы. Поэтому мы никогда не встретим в секции файла Current SQL statement for this session операторы вставки.
Рецепт предотвращения взаимного блокирования при неиндексированных внешних ключах кроется уже в названии условия возникновения взаимоблокировки. Надо взять в правило всегда создавать индексы для внешних ключей. Это предотвратит блокирование дочерней таблицы в жестких режимах. В случаях, когда индексирование делать невозможно или нежелательно, следует обратиться к общему правилу последовательной обработки ресурсов. К примеру, обрабатывать вначале родительскую таблицу и только затем переходить к дочерней таблице.
