Неиндексированный внешний ключ
Этот сценарий взаимного блокирования возникает из-за особенностей работы 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 файла операторы вставки.
Рецепт предотвращения взаимного блокирования при неиндексированных внешних ключах уже лежит в названии условия возникновения взаимоблокировки. Надо взять в правило всегда создавать индексы для внешних ключей. Это предотвратит блокирование дочерней таблицы в жестких режимах. В случаях, когда индексирование делать невозможно или нежелательно следует обратиться к общему правилу последовательной обработки ресурсов. К примеру, обрабатывать вначале родительскую таблицу и только затем переходить к дочерней таблице.
Вставка методом прямой загрузки
Эта возможность образования взаимного блокирования встречается в Oracle довольно редко, в виду специфики режима применяемых при её возникновении команд. Если два и более сеанса пытаются осуществить прямые вставки в таблицы, то они могут создать такую ситуацию, при которой они будут бесконечно ожидать друг друга. Отличие этого сценария взаимного блокирования от сценариев с транзакционной блокировкой заключается в особом режиме применения команды вставки INSERT. При указании подсказки /*+ APPEND */ и использовании подзапроса, команда начинает вставлять строки непосредственно в файлы данных, минуя кеш. При этом выполняется монопольная блокировка таблицы, которая иногда и приводит к взаимной блокировке сеансов.
Попробуем смоделировать этот сценарий взаимного блокирования. Для начала создадим две таблицы t8 и t9:
ZH@XE> CREATE TABLE t8 (c1 NUMBER PRIMARY KEY, c2 VARCHAR2(50)); Таблица создана ZH@XE> CREATE TABLE t9 (c1 NUMBER PRIMARY KEY, c2 VARCHAR2(50)); Таблица создана
Образуем два сеанса и выполним в них методом прямой загрузки вставку строк в эти таблицы, используя имитацию подзапроса.
Первый сеанс:
ZH@XE(24)> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; Session altered ZH@XE(24)> INSERT /*+ APPEND */ INTO t8 SELECT 1, 'Строка1' FROM dual; Вставлено: 1 строка
Второй сеанс:
ZH@XE(23)> INSERT /*+ APPEND */ INTO t9 SELECT 1, 'Строка1' FROM dual; Вставлено: 1 строка
Как было сказано выше, выполнение команд прямой вставки должно приводить к монопольному блокированию таблицы. Следовательно, сейчас у нас должно быть в каждом из сеансов выставлено по одной TM блокировке в монопольном режиме. Проверим, так ли это, на самом деле, обратившись к представлению V$lock:
SYSTEM@XE> SELECT * FROM v$lock WHERE sid IN (24, 23) AND type = 'TM' ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- --- ---- ----- --- ----- ------- ----- ----- 296DAA90 296DAAA8 24 TM 14016 0 6 0 81 0 296DAB54 296DAB6C 23 TM 14018 0 6 0 42 0 Выбрано: 2 строки
Действительно, в представлении мы видим две блокировки TM в монопольном режиме (LMODE = 6 - X). Таблицы t8 и t9 оказались полностью заблокированы для изменений структуры и данных другими сеансами. Теперь выполнение любой команды изменяющей данные в этих таблицах должно привести к ожиданию. Попробуем снова осуществить прямую загрузку в первом сеансе только уже для таблицы t9:
ZH@XE(24)> INSERT /*+ APPEND */ INTO t9 SELECT 2, 'Строка2' FROM dual;
Ожидание…
Так и есть, возникло ожидание. Заглянем в трассировочный файл:
WAIT #2: nam='enq: TM - contention' ela= 2999757 name|mode=1414332422 object #=14018 table/partition=0 obj#=14017 tim=11169951762
В первом сеансе постоянно возникает ожидание «Конкуренция TM блокировки». Сеанс ждёт освобождения блокировки. Для того чтобы убедиться в этом, заглянем в представление v$lock:
SYSTEM@XE(35)> SELECT * FROM v$lock WHERE sid IN (24, 23) AND type = 'TM' ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- --- ---- ----- --- ----- ------- ----- ----- 296DAA90 296DAAA8 24 TM 14016 0 6 0 132 0 296DAB54 296DAB6C 23 TM 14018 0 6 0 93 1 296DAC18 296DAC30 24 TM 14018 0 0 6 15 0 Выбрано: 3 строки
Из содержимого представления видно, что первый сеанс пытается установить TM блокировку в монопольном режиме (REQUEST = 6) на таблицу t9. Но так как данная таблица была уже заблокирована ранее вторым сеансом, это привело к ожиданию. Если теперь второй сеанс попытается осуществить прямую загрузку в таблицу t8, то возникнет бесконечное ожидание, так как таблица t8 уже заблокирована ожидающим первым сеансом:
ZH@XE(23)> INSERT /*+ APPEND */ INTO t8 SELECT 2, 'Строка2' FROM dual;
Ожидание…
Так и есть, возникло взаимное блокирование и в первом сеансе происходит исключение:
ZH@XE(24)> INSERT /*+ APPEND */ INTO t9 SELECT 2, 'Строка2' FROM dual;
INSERT /*+ APPEND */ INTO t9 SELECT 2, 'Строка2' FROM dual;
*
Ошибка в строке 1:
ORA-00060: deadlock detected while waiting for resource
Посмотрим, что нам покажет трассировочный файл взаимной блокировки. В первой секции отменённый оператор вставки первого сеанса:
Current SQL statement for this session: INSERT /*+ APPEND */ INTO t9 SELECT 2, 'Строка2' FROM dual
Граф взаимной блокировки:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TM-000036c0-00000000 26 24 X 27 23 X
TM-000036c2-00000000 27 23 X 26 24 X
В качестве ресурсов графа выступают TM блокировки, выставленные на таблицы t8 и t9 . Столбцы holds и waits содержат символьное значение X. Этот символ соответствует монопольному режиму установленной или ожидающей TM блокировки. В остальном граф читается стандартным образом, поэтому не будем заострять на этом внимание и сразу обратимся к секции ожидающих строк. В отличие от предыдущего сценария с TM блокировками, эта секция не пуста:
Rows waited on: Session 23: obj - rowid = 000036C2 - AAADbMAAEAAAAJBAAA (dictionary objn - 14018, file - 4, block - 577, slot - 0) Session 24: obj - rowid = 000036C1 - AAADbKAAEAAAAI8AAA (dictionary objn - 14017, file - 4, block - 572, slot - 0)
Впрочем, практического значения содержимое этой секции в большинстве случаев не имеет. Не стоит забывать, что данный вид взаимного блокирования представляет собой блокирование на уровне объектов, но не как не строк.
Как не допустить возникновение взаимных блокировок при прямой загрузке? Первое, это конечно в виду того, что данный режим вставки накладывает на таблицу самую жесткую из известных блокировок, надо по возможности ограничить его использование. Второе, транзакция, в которой присутствует прямая загрузка должна быть по возможности короткой, это уменьшит время в течении которого может возникнуть взаимное блокирование. И наконец, последнее, можно попытаться организовать одинаковую последовательность обработки ресурсов в сеансах, что просто превратит взаимоблокировку в обыкновенную очередь.