Введение
Взаимная блокировка (deadlock) —это ситуация в СУБД, при которой двое или более сеансов находятся в состоянии бесконечного ожидания ресурсов, захваченных самими этими же сеансами. При обычном состоянии, когда один из сеансов захватывает, какой либо ресурс, другие сеансы будут ожидать его освобождения, выстраиваясь в очередь, друг за другом. Но если удерживающий сеанс не может освободить ресурс, вследствие того что он ожидает освобождения ресурса, захваченного одним из ожидающих сеансов, возникает парадоксальная ситуация, при которой ни один из захваченных сеансами ресурсов не может быть освобождён. В этом случае СУБД сама должна вмешаться в процесс бесконечного ожидания и принудительно освободить запрос в сеансе к одному из ресурсов. При этом никак не должна пострадать уже начатая транзакция сеанса. СУБД должна только отменить последнее действие в сеансе, которое привело к сложившейся ситуации взаимного блокирования, и предоставить сеансу решить, зафиксировать ли изменения в данных или отменить их.
Надо сказать, что возникновение взаимного блокирования это исключительный случай для СУБД. У Oracle даже для этого случая есть специальная ошибка ORA-00060. Если взаимные блокировки возникают очень редко, то можно просто игнорировать эту ошибку, обрабатывая в приложении данное исключение. Но когда подобная ошибка начинает возникать очень часто, требуется детальный анализ возникающей ситуации. Большую помощь в этом нам может оказать файл трассировки, который создаёт Oracle в каждом случае возникновения взаимных блокировок. Образуется этот файл в каталоге, который определяется параметром инициализации user_dump_dest. Кроме создания файла, Oracle делает так же запись о возникшей ошибке в системный журнал alert.log. В этом журнале наряду с самим фактом происшедшей ошибки записывается ссылка к образованному файлу трассировки. Так как данный журнал и файл доступны только администратору базы данных, одним из пунктов его обязанностей, должно являться обнаружение подобных записей, и предоставление разработчику приложения всей доступной информации для исправления повторяющихся взаимных блокировок.
Граф ожидания транзакций
Прежде чем начинать подробно изучать устройство взаимоблокировки, попробуем разобраться в том, как Oracle обнаруживает ситуации взаимного блокирования. В будущем это позволит нам легче понять этот механизм при некоторых сложных сценариях развития взаимной блокировки, а так же позволит лучше ориентироваться в содержимом трассировочного файла взаимного блокирования.
В Oracle, впрочем, как и в других современных СУБД, поиск ситуаций взаимного блокирования происходит постоянно. Вначале строится так называемый граф ожидания транзакций. Граф состоит из вершин и соединяющих их рёбер. Существуют два типа вершин – это вершины соответствующие транзакциям или сеансам, и вершины, представляющие из себя ресурсы или объекты. Ребра в данном случае представляют собой блокировки. Если блокировка захвачена, то ребро направлено от вершины соответствующей сеансу к вершине определяющей ресурс. Если же блокировка ожидает установки, то, наоборот, ребро направлено от вершины ресурса к вершине соответствующей сеансу. Если в этом сплетении рёбер и вершин обнаруживается цикл, то это означает, что возникла ситуация взаимного блокирования. При этом Oracle должен выбрать и отменить одно из ожидающих рёбер, что приведёт к разрыву цикла и нормализации ситуации.
В дальнейшем для некоторых сложных сценариев взаимной блокировки мы будем приводить схематическое изображение графа ожидания транзакций, что позволит нам увидеть взаимную блокировку в графическом виде.
Сценарии возникновения
Сценариев возникновения взаимных блокировок в Oracle на самом деле не так уж много. Их отличие друг от друга заключается в видах удерживающих и ожидающих блокировок, а так же в режимах в которых эти блокировки находятся. Ниже, мы попробуем смоделировать большинство ситуаций, при которых возникают взаимные блокировки, а также попытаемся детально разобраться в механизме самого взаимного блокирования. Научимся разбирать, анализировать содержимое трассировочного файла, и определять, к какому сценарию взаимного блокирования относиться данная взаимная блокировка.
Блокировки транзакций. Захват и ожидание в исключительном режиме.
Рассмотрим первый сценарий взаимного блокирования. Необходимым условием для его возникновения является наличие в двух разных сеансах установленной и ожидающей блокировок транзакций (TX) в исключительном режиме. Моделировать ситуацию будем с использованием редакции Oracle Express Edition и инструмента администратора ZhiSQL for Oracle.
Механизм взаимоблокировки
Вначале создадим тестового пользователя zh и выдадим ему все необходимые привилегии:
Подключение к:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production
SYSTEM@XE> CREATE USER zh IDENTIFIED BY test DEFAULT TABLESPACE users;
Пользователь создан
SYSTEM@XE> GRANT connect, resource, alter session TO zh;
Grant succeeded
Далее, создадим простую таблицу и вставим в неё две строки:
ZH@XE> CREATE TABLE t1 (c1 NUMBER PRIMARY KEY, c2 VARCHAR2(50)); Таблица создана ZH@XE> INSERT INTO t1 (c1) VALUES(1); Вставлено: 1 строка ZH@XE> INSERT INTO t1 (c1) VALUES(2); Вставлено: 1 строка ZH@XE> COMMIT; Commit complete
Образуем два сеанса и для наглядности разбора ситуации включим в первом из них трассировку на уровне событий ожидания. Далее, в этом же сеансе будем последовательно изменять первую и вторую строки, а во втором проделаем те же действия, но в обратном порядке.
Первый сеанс:
ZH@XE(28)> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; Session altered ZH@XE(28)> UPDATE t1 SET c2 = 'Строка1' WHERE c1 = 1; Изменено: 1 строка
Второй сеанс:
ZH@XE(24)> UPDATE t1 SET c2 = 'Строка2' WHERE c1 = 2; Изменено: 1 строка
В результате выполненных нами действий в существующих сеансах были открыты две транзакции. В первом сеансе была выставлена блокировка транзакции (TX) на первую строку в исключительном режиме. Такая же блокировка выставлена и во втором сеансе, но на вторую строку. Убедиться в этом, мы можем, сделав небольшой запрос к системному представлению v$lock:
SYSTEM@XE> SELECT * FROM v$lock WHERE sid IN (24, 28) AND type = 'TX'; ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- --- ---- ------ --- ----- ------- ----- ----- 296EE5D8 296EE6F4 28 TX 262166 169 6 0 1110 0 296FA680 296FA79C 24 TX 327718 163 6 0 879 0
Рассмотрим более подробно содержимое этого запроса. Столбец SID здесь содержит идентификаторы первого и второго сеансов (28 и 24). Содержимое столбца TYPE указывает на тип блокировки, в нашем случае блокировки транзакции (TX). Столбец LMODE хранит значение 6, что соответствует установившемуся исключительному режиму блокировки. В результате мы видим, что в каждом из сеансов имеется по одной TX блокировке в установившемся исключительном режиме. Определить к какой транзакции относиться каждая из этих TX блокировок можно с помощью содержимого дополнительных столбцов ID1 и ID2. В них находятся составные части идентификатора транзакций, декодировать которые можно с помощью следующего запроса:
SYSTEM@XE> SELECT sid, TRUNC(id1/POWER(2,16)) rbs, BITAND(id1, POWER(2,16)-1)+ 0 slot, id2 seq FROM v$lock WHERE sid IN (24, 28) AND type = 'TX'; SID RBS SLOT SEQ --- --- ---- --- 28 4 22 169 24 5 38 163 Выбрано: 2 строки
Расшифровав значения столбцов ID1 и ID2, мы получили номер сегмента отката, слот и номер изменения транзакции. Эти значения полностью совпадают со значениями из представления v$transaction и все вместе, в шестнадцатеричном виде, представляют собой идентификатор транзакции:
SYSTEM@XE> SELECT s.sid, t.xidusn, xidslot, xidsqn FROM v$transaction t, v$session s WHERE t.addr = s.taddr; SID XIDUSN XIDSLOT XIDSQN --- ------ ------- ------ 24 5 38 163 28 4 22 169 Выбрано: 2 строки
Данный идентификатор нам встретиться ещё в дальнейшем, когда мы будем разбирать содержимое трассировочного файла взаимной блокировки, а пока продолжим последовательность начатых нами действий и изменим в первом сеансе содержимое второй строки:
ZH@XE(28)> UPDATE t1 SET c2 = 'Строка2' WHERE c1 = 2;
Ожидание …
Сеанс находиться в ожидании. Оно возникло от того, что первый сеанс пытается установить TX блокировку в исключительном режиме на вторую строку, которая уже захвачена TX блокировкой второго сеанса. Если в это время заглянуть в файл трассировки первого сеанса, то мы увидим там следующие строки:
WAIT #1: nam='enq: TX - row lock contention' ela= 3000022 name|mode=1415053318 usn<<16 slot="327718" sequence="163" obj="" 13766="" tim="14923101611"
В сеансе постоянно возникает ожидание “конкуренция блокировки строки”. В параметрах этого ожидания мы видим уже знакомые нам значения идентификатора транзакции второго сеанса (slot, sequence). Именно эта транзакция, установила ранее TX блокировку в исключительном режиме на вторую строку и привела к ожиданию. Более детально это можно просмотреть в содержимом представления v$lock:
SYSTEM@XE> SELECT * FROM v$lock WHERE sid IN (24, 28) AND type = 'TX'; ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- --- ---- ------ --- ----- ------- ----- ----- 2A201720 2A201734 28 TX 327718 163 0 6 1074 0 296EE5D8 296EE6F4 28 TX 262166 169 6 0 7048 0 296FA680 296FA79C 24 TX 327718 163 6 0 6817 1 Выбрано: 3 строки
Как мы видим, в представлении появилась новая строка о TX блокировке со значением 6 в поле REQUEST. Данное значение означает, что сеанс 28 запросил установку TX блокировки на строку в исключительном режиме. При этом значения столбцов ID1 и ID2 этого запроса содержат идентификатор транзакции сеанса 24. Это свидетельствует о том, что первый сеанс ожидает освобождения строки захваченной транзакцией именно второго сеанса.
И так, на данный момент, мы имеем классическую картину ожидания. Но что произойдет, если мы изменим во втором сеансе первую строку? Ведь она уже захвачена первым сеансом:
ZH@XE(24)> UPDATE t1 SET c2 = 'Строка1' WHERE c1 = 1;
Ожидание…
Возникает ожидание, и мы становимся свидетелями типичного случая взаимного блокирования. Второй сеанс ожидает освобождения первой строки, заблокированной в результате начатой транзакции первого сеанса. В то же время первый сеанс ожидает освобождение второй строки, заблокированной транзакцией второго сеанса. Такое ожидание могло бы продолжаться вечно, но как мы рассматривали ранее, Oracle сам определяет подобные возникающие тупиковые ситуации в течение примерно трёх секунд. В нашем случае он просто отменит выполнение последнего оператора UPDATE в первом сеансе и сгенерирует следующую ошибку:
ZH@XE(28)> UPDATE t1 SET c2 = 'Строка2' WHERE c1 = 2;
UPDATE t SET c2 = 'Строка2' WHERE c1 = 2
*
Ошибка в строке 1:
ORA-00060: deadlock detected while waiting for resource
Убедимся в том, что транзакции отменены не были. Для этого выполним следующий запрос:
SYSTEM@XE> SELECT s.sid, t.status, t.start_time, t.xid FROM v$transaction t, v$session s WHERE t.addr = s.taddr; SID STATUS START_TIME XID --- ------ ----------------- ---------------- 24 ACTIVE 01/21/10 23:14:40 05002600A3000000 28 ACTIVE 01/21/10 23:10:49 04001600A9000000 Выбрано: 2 строки
Как видно из результатов запроса, транзакции по-прежнему активны. Отменять их полностью у Oracle нет необходимости, достаточно лишь просто вернуться в одном из сеансов к неявной точке сохранения, которая делается перед каждым DML оператором.
Теперь мы должны решить, что нам делать с этой незавершённой транзакцией в первом сеансе. Повторять отменённый оператор не имеет смысла. Ситуация взаимного блокирования повториться, и ошибка возникнет уже в другом сеансе. Поэтому нам остаётся либо произвести отмену, либо зафиксировать транзакцию. После этого второй сеанс продолжит выполнение оператора UPDATE, так как необходимая строка для этого будет освобождена.
Что же касается самой Oracle, то в результате всех перечисленных выше действий приведших к возникновению взаимной блокировки, в файл журнала alert_xe.log будет занесена запись следующего вида:
Fri Jan 22 01:09:58 2010 ORA-00060: Deadlock detected. More info in file c:\oraclexe\app\oracle\admin\xe\udump\xe_ora_2480.trc.
По этой записи мы всегда можем определить, когда произошел случай взаимного блокирования, а также получить ссылку к образовавшемуся трассировочному файлу. Чуть ниже мы попытаемся детально разобрать содержимое этого файла, так как в большинстве случаев он является единственным источником дополнительной информации о произошедшей взаимной блокировке.
Трассировочный файл взаимной блокировки
Сам файл трассировки содержит множество данных, в том числе здесь находится и полный дамп состояния процессов Oracle на момент возникновения взаимоблокировки. Но нам важны только несколько секций файла. Первая из них – это текущий SQL оператор сеанса, который столкнулся с ошибкой взаимной блокировки и был отменён. Для этого находим в файле строку DEADLOCK DETECTED. Чуть ниже её, после ключевых слов «Current SQL statement for this session» будет находиться необходимая нам секция:
Current SQL statement for this session: UPDATE t1 SET c2 = 'Строка2' WHERE c1 = 2
Вторая секция, которая нас заинтересует – это граф взаимной блокировки. Он находиться после ключевой строки Deadlock graph, и отображает цепочку захватов и ожиданий блокировок между сеансами:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00040016-000000a9 20 28 X 21 24 X
TX-00050026-000000a3 21 24 X 20 28 X
Вспомним, как Oracle обнаруживает блокировки. Для этого он постоянно строит граф ожидания транзакций. Если обнаружен цикл в этом графе, то это означает, что возникла взаимная блокировка. Так вот именно этот цикл и отображается в секции Deadlock graph, правда в очень специфическом виде.
Визуально граф представлен в виде таблицы, которая разделена на три логических блока. Первый блок обозначает ресурсы, участвующие во взаимной блокировке. В нашем случае таким ресурсом является блокировка транзакции. Ресурс обозначается символьным кодом TX, после которого идет идентификатор транзакции в шестнадцатеричном виде. Именно этот идентификатор, но только в десятичном виде мы получили, когда расшифровывали значения столбцов ID1 и ID2 представления v$lock. Второй блок состоит из столбцов, содержащих информацию о сеансах удерживающих данную блокировку, а также режим, в котором эта блокировка установлена. И наконец, третий блок аналогичен второму, но противоположен по содержанию. Он хранит информацию о сеансах, которые сделали запрос на установление блокировки, но вынуждены ожидать освобождения блокировки из второго блока.
Расшифровка графа не представляет сложности. Для этого нам надо проанализировать содержимое таблицы построчно, слева направо. К примеру, для нашего случая это будет выглядеть следующим образом. Транзакционная блокировка TX-00040016-000000a9 на строку удерживается сеансом 28 (поле session) в исключительном режиме (символ X в поле holds). Сеанс 24 одновременно ждёт освобождение этого ресурса, чтобы установить свою TX блокировку в исключительном режиме (символ X в поле waits).
Пока это нормальное ожидание необходимого ресурса. Поэтому далее мы обратимся ко второй строке графа. Здесь транзакционная блокировка TX-00050026-000000a3 на строку удерживается сеансом 24 в исключительном режиме, а сеанс 28 ждёт освобождение строки, чтобы установить свою TX блокировку в исключительном режиме. В то же время в первой строке графа сеанс 24 уже ожидает освобождения ресурса, в результате чего получается, что сеансы находятся в состоянии бесконечного ожидания. Единственным логичным действием в этом случае, явилось бы отмена ожидания установки блокировки в сеансе 28, что собственно и было сделано Oracle. В графе такое отменённое ожидание всегда отображается последним в блоке Waiter(s).
Итак, граф расшифрован. Он дал нам описание цепочки захватов и ожиданий TX блокировок в сеансах. Но по этой цепочке мы можем судить только об общей картине возникновения взаимной блокировки. Если же нам потребуется найти конкретные ресурсы, из-за которых возникают ожидания, сделать нам это будет затруднительно. К счастью Oracle сам позаботился об этом, записав в файл трассировки информацию о строках, освобождения которых от TX блокировок ожидают сеансы. Рассмотрим более подробно эту секцию. Найти её можно сразу после графа, по ключевой строке Rows waited on:
Rows waited on: Session 24: obj - rowid = 000035C6 - AAADXGAAEAAAAFkAAA (dictionary objn - 13766, file - 4, block - 356, slot - 0) Session 28: obj - rowid = 000035C6 - AAADXGAAEAAAAFkAAB (dictionary objn - 13766, file - 4, block - 356, slot - 1)
В этой секции для каждого ожидающего сеанса, который перечислен в графе, указана строка, на которую этот сеанс пытается получить TX блокировку. Строка идентифицируется номером объекта, которому она принадлежит, и идентификатором ROWID. Чуть ниже дана их полная расшифровка в десятичном виде. Это позволяет, с лёгкостью, обратившись, например, к системному представлению dba_objects, идентифицировать объект, к которому принадлежит данная строка:
SYS@XE> SELECT owner, object_name FROM dba_objects WHERE object_id = 13766; OWNER OBJECT_NAME ----- ----------- ZH T1
Следующая секция трассировочного файла, которую мы рассмотрим, хотя и не столь важна, но позволяет дополнить картину взаимной блокировки. Она располагается сразу за секцией Rows waited on и находится по следующим ключевым словам:
Information on the OTHER waiting sessions:
Session 24:
pid=21 serial=48 audsid=141 user: 39/ZH
O/S info: user: ALFA\Сергей, term: ALFA, ospid: 1984:2524, machine:
program: DBASQL.exe
client info: DBASQL
application name: DBASQL.exe, hash value=0
Current SQL Statement:
UPDATE t1 SET c2 = 'Строка1' WHERE c1 = 1
End of information on OTHER waiting sessions.
Из этой секции можно получить информацию о пользователе, приложении и текущем SQL курсоре ожидающих сеансов, вовлеченных в процесс взаимной блокировки. Иногда это может быть полезно при поиске причин образования ситуации взаимного блокирования.
Как определить по содержимому трассировочного файла, что произошел первый сценарий взаимного блокирования? Для ответа на этот вопрос обратимся в первую очередь к графу взаимной блокировки. Для начала мы должны определить, с какого идентификатора начинаются имена ресурсов графа в столбце «Resource Name». В нашем случае это всегда будет идентификатор TX, то есть блокировка транзакции. Далее нам следует проверить значения режимов блокировок, отображаемые в столбцах holds и waits. Они должны иметь одинаковое значение равное символу X. Не следует так же забывать, что данный сценарий взаимного блокирования возникает на уровне строк, и, следовательно, в секции «Rows waited» всегда будут присутствовать данные об ожидающих строках. Отсюда следует непреложное правило о том, что в первой секции «Current SQL statement for this session» при данном сценарии вы никогда не встретите оператора INSERT, так как строки, вставленные в одном из сеансов, никогда не будут доступны для другого сеанса до фиксации транзакции.
Выводы
Пришло время обобщить полученную информацию, которую мы узнали, изучая взаимную блокировку, образующуюся по первому сценарию. Обычно, такая взаимная блокировка возникает из-за неодинаковой последовательности обработки строк, в пределах транзакций разных сеансов. Если бы мы, к примеру, в нашем случае проводили бы обновление строк таблицы в каждом из сеансов последовательно в соответствии со значением ключевого столбца «c1», то мы могли бы избежать взаимных блокировок. Образовалась бы простая очередь ожидания ресурса. Правда, бывают ситуации, когда одинаковую последовательность обработки строк в разных сеансах сделать трудно или даже невозможно. Как тогда выходить из ситуации? Самое простое, что приходит на ум, и о чём я уже упоминал выше, это можно обрабатывать ошибку в приложении, и в случае её возникновения в одном из сеансов откатывать транзакцию, повторяя все необходимые действия позже. Велика вероятность, что транзакция в другом сеансе за это время завершиться и не будет мешать производить изменения в данных. Вообще по возможности надо всегда уменьшать время длительности транзакций в приложениях. Если транзакция будет короткой, то это уменьшает время в течение, которого сеансы могут попасть в неприятную ситуацию с взаимным блокированием. Это справедливо и для длинной цепочки каскадного срабатывания триггеров, которым так грешат некоторые сложные системы. По возможности эту цепочку надо укорачивать.
Блокировки транзакций. Захват в исключительном и ожидание в разделяемом режимах.
Наконец-то пришло время рассмотреть второй сценарий возникновения ситуации взаимных блокировок. Его отличие от предыдущего заключается в том, что одна из TX блокировок здесь находиться в исключительном режиме, а вторая, ожидающая, в разделяемом. Условий для возникновения такой взаимной блокировки на самом деле не так уж много, вернее их всего три. Это нехватка места в таблице транзакций, перекрытие фрагментов индекса на основе битовых карт и наложение значений первичного или уникального ключа при вставке. В какой-то мере, к последнему условию, можно отнести и DML операции с таблицами организованными по индексу.
Ниже мы попробуем на примерах рассмотреть ситуации взаимного блокирования со всеми перечисленными выше условиями. Но сделать это будет не просто, из-за особенностей установки разделяемого режима транзакционной блокировки. В связи с этим моделирование начнем, с самого простого случая, с наложения значений первичного или уникального ключа при вставке.
Наложение значений первичного или уникального ключа при вставке
Для начала образуем два сеанса. Очистим таблицу t1 и для наглядности исследования процесса взаимного блокирования включим трассировку в первом сеансе. Далее, в этом же сеансе будем последовательно вставлять в таблицу t1 две строки с ключами 1 и 2, а во втором проделаем те же действия, но только в обратном направлении.
Первый сеанс:
ZH@XE(28)> TRUNCATE TABLE t1; Таблица очищена ZH@XE(28)> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; Session altered ZH@XE(28)> INSERT INTO t1 (c1, c2) VALUES(1, ‘Строка1’); Вставлено: 1 строка
Второй сеанс:
ZH@XE(38)> INSERT INTO t1 (c1, c2) VALUES(2, ‘Строка2’); Вставлено: 1 строка
В результате выполненных выше действий, в каждом из сеансов нами были установлены по одной транзакционной блокировке в исключительном режиме (LMODE=6):
SYSTEM@XE> SELECT * FROM v$lock WHERE sid IN (28, 38) AND type = 'TX'; ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- --- ---- ------ --- ----- ------- ----- ----- 296EF030 296EF14C 28 TX 524323 168 6 0 84 0 296F6648 296F6764 38 TX 65564 175 6 0 72 0 Выбрано: 2 строки
Далее, в первом сеансе, попытаемся вставить строку с ключом 2. В связи с тем, что таблица t1 имеет ограничения первичного ключа, и строка с таким же значением данного ключа уже вставлена в незавершённой транзакции второго сеанса, выполнение этого оператора должно привести к ожиданию:
ZH@XE(28)> INSERT INTO t1 (c1, c2) VALUES(2, 'Строка2');
Ожидание …
Если в это время заглянуть в трассировочный файл первого сеанса, можно увидеть, что в сеансе постоянно возникает ожидание “конкуренция блокировки строки”:
WAIT #2: nam='enq: TX - row lock contention' ela= 3001240 name|mode=1415053316 usn<<16 slot="65564" sequence="175" obj="" 13766="" tim="3276462301" pre="">
Но какой строки? Вставленные в таблицу строки не видны сеансам, так как транзакции не зафиксированы. Следовательно, они не могут являться причиной ожидания. Для прояснения ситуации заглянем в системное представление v$lock:
SYSTEM@XE> SELECT * FROM v$lock WHERE sid IN (28, 38) AND type = 'TX'; ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- --- ---- ------ --- ----- ------- ----- ----- 2A201720 2A201734 28 TX 65564 175 0 4 1146 0 296EF030 296EF14C 28 TX 524323 168 6 0 1893 0 296F6648 296F6764 38 TX 65564 175 6 0 1881 1 Выбрано: 3 строки
В представлении мы обнаруживаем три записи о TX блокировках. Две из них установлены в исключительном режиме на вновь вставленные строки. Третья блокировка, ожидающая, имеет в поле REQUEST значение 4, что соответствует разделяемому режиму. Значения полей ID1 и ID2 этой блокировки явно указывают на транзакцию во втором сеансе. Из этого ясно, что первый сеанс ожидает освобождения какой-то строки заблокированной вторым сеансом. Этой строкой в нашем случае является строка индекса первичного ключа соответствующая значению ключа равного двум. Данное значение ранее было добавлено сеансом 38 в индекс в момент вставки второй строки в таблицу. После чего, для защиты уникальности первичного ключа на эту строку индекса была наложена TX блокировка в исключительном режиме. Если теперь второй сеанс (38) отменит транзакцию, то в первом сеансе для вновь вставленной строки таблицы будет установлена TX блокировка в исключительном режиме. Если же второй сеанс зафиксирует транзакцию, то в первом сеансе будет наблюдаться ошибка нарушения ограничения первичного ключа:
INSERT INTO t1 (c1, c2) VALUES(2, 'Строка2')
*
Ошибка в строке 1:
ORA-00001: unique constraint (ZH.SYS_C004053) violated
А что произойдет, если второй сеанс попытается вставить строку со значением первичного ключа равным единице:
ZH@XE(38)> INSERT INTO t1 (c1, c2) VALUES(1, 'Строка1');
Ожидание…
Так как первый сеанс уже ранее вставил в таблицу строку с данным первичным ключом и выставил на строку индекса TX блокировку в исключительном режиме, второй сеанс должен ждать освобождения этой блокировки. В тоже время первый сеанс уже ожидает окончания транзакции во втором сеансе и, следовательно, не может освободить эту блокировку. В результате у нас снова образуется бесконечное ожидание, которое приводит в первом сеансе к исключению:
ZH@XE(28)> INSERT INTO t1 (c1, c2) VALUES(2, 'Строка2');
INSERT INTO t (c1, c2) VALUES(2, 'Строка2')
*
Ошибка в строке 1:
ORA-00060: deadlock detected while waiting for resource
И так, взаимоблокировка произошла. Настало самое время заглянуть в сгенерированный в результате ошибки трассировочный файл первого сеанса. В первой секции файла мы видим, оператор вставки INSERT который был отменён:
Current SQL statement for this session: INSERT INTO t1 (c1, c2) VALUES(2, 'Строка2')
Граф взаимоблокировки имеет небольшие отличия от предыдущего сценария:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00080023-000000a8 20 28 X 18 38 S
TX-0001001c-000000af 18 38 X 20 28 S
В столбце wait появилось значение S. Это означает, что ожидаемая блокировка находиться в разделяемом режиме. В остальном разбор графа не изменился и соответствует первому сценарию. Поэтому сразу перейдём к секции ожидания строк:
Rows waited on: Session 38: no row Session 28: obj - rowid = 000035C6 - AAADXSAAEAAAAFiAAA (dictionary objn - 13766, file - 4, block - 354, slot - 0)
В секции присутствует информация только по одной ожидающей строке, да и то это ожидание блокировки, которое было отменено. Если вам попались эти данные, то считайте, что вам повезло. Обычно информации здесь нет. Это лишний раз доказывает, что рассматриваемый случай взаимной блокировки не является ситуацией взаимного блокирования на уровне строк таблицы. Поэтому, для того чтобы распознать в трассировочном файле блокирование при наложении значений первичного или уникального ключей, необходимо в первую очередь обратиться к секции «Current SQL statement for this session». Если это оператор INSERT и таблица, в которую он вставляет значения, имеет первичные или уникальные ключи, то с большой долей вероятности можно судить о том, что произошел второй сценарий взаимного блокирования.
Обобщая всё выше перечисленное, можно сказать, что ситуация с взаимным блокированием при вставке первичного и уникального ключей обычно складывается из-за неправильно разработанного приложения, когда значения этих ключей имеют одинаковые значения в разных сеансах. Частично этого можно было бы избежать, если, к примеру, использовать генерацию неповторяющихся значений ключей с помощью последовательностей. В других случаях, когда такой вариант не подходит, необходимо просто обеспечить одинаковую последовательность действий при вставке в таблицу или использовать пакет DBMS_LOCK, как указано у Тома Кайта.
Выполнение DML операторов над таблицами организованными по индексу
Этот сценарий взаимной блокировки можно в какой-то мере отнести к предыдущему случаю. Здесь также, ожидающие сеансы запрашивают блокировки транзакций в разделяемом режиме, и также TX блокировки в исключительном режиме выставляются на строки индекса. Но в отличие от наложения первичных ключей при вставке эта взаимная блокировка может проявляться при выполнении любого DML оператора. Продемонстрируем это на примере. Организуем два сеанса. В первом сеансе создадим индекс организованную таблицу, вставим в неё две строки и включим трассировку:
ZH@XE(31)> CREATE TABLE t2 (c1 NUMBER PRIMARY KEY, c2 VARCHAR2(50)) ORGANIZATION INDEX; Таблица создана ZH@XE(31)> INSERT INTO t2 (c1) VALUES(1); Вставлено: 1 строка ZH@XE(31)> INSERT INTO t2 (c1) VALUES(2); Вставлено: 1 строка ZH@XE(31)> COMMIT; Commit complete ZH@XE(31)> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; Session altered
Теперь в первом сеансе изменим первую и вторую строки. Во втором сеансе проделаем эти же действия но в обратной последовательности:
Первый сеанс:
ZH@XE(31)> UPDATE t2 SET c2 = 'Строка1' WHERE c1 = 1; Изменено: 1 строка
Второй сеанс:
ZH@XE(38)> UPDATE t2 SET c2 = 'Строка2' WHERE c1 = 2; Изменено: 1 строка
Далее, в первом сеансе изменим вторую строку таблицы. Так как на эту строку во втором сеансе выставлена TX блокировка в исключительном режиме, данные действия приведут к ожиданию:
ZH@XE(31)> UPDATE t2 SET c2 = 'Строка2' WHERE c1 = 2;
Ожидание…
В трассировочном файле первого сеанса наблюдаем в это время ожидание “конкуренция блокировки строки”:
WAIT #1: nam='enq: TX - row lock contention' ela= 3000041 name|mode=1415053316 usn<<16 slot="262149" sequence="180" obj="" -1="" tim="6315257799"
Если после этого мы заглянем в представление v$lock, то обнаружим там точно такую же картину, как и при наложении значений первичных или уникальных ключей:
SYSTEM@XE> SELECT * FROM v$lock WHERE sid IN (31, 38) AND type = 'TX'; ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- --- ---- ------ --- ----- ------- ----- ----- 2A201668 2A20167C 31 TX 262149 180 0 4 114 0 296F5604 296F5720 31 TX 196615 179 6 0 447 0 296F6648 296F6764 38 TX 262149 180 6 0 339 1
Продолжим изменения и во втором сеансе поправим первую строку таблицы:
ZH@XE(38)> UPDATE t2 SET c2 = 'Строка1' WHERE c1 = 1;
Ожидание…
И снова, как и раньше, у нас возникло взаимное блокирование:
ZH@XE(31)> UPDATE t2 SET c2 = 'Строка2' WHERE c1 = 2;
UPDATE t2 SET c2 = 'Строка2' WHERE c1 = 2
*
Ошибка в строке 1:
ORA-00060: deadlock detected while waiting for resource
Рассмотрим содержимое трассировочного файла взаимной блокировки.
Текущий отменённый оператор:
Current SQL statement for this session: UPDATE t2 SET c2 = 'Строка2' WHERE c1 = 2
Граф взаимоблокировки:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00030007-000000b3 21 31 X 18 38 S
TX-00040005-000000b4 18 38 X 21 31 S
Сеансы, ожидающие строки:
Rows waited on: Session 38: no row Session 31: no row
Как видим, в секции «Rows waited on» нет значений. Это связано с тем, что таблица, организованная по индексу представляет по организационной структуре в некотором смысле слова индекс. И хотя в данной таблице представлен идентификатор строки, но он, по сути, является логическим идентификатором, построенным на основе значений первичного ключа, а не на основе физического размещения. Поэтому если в предыдущем случае взаимной блокировки в данной секции трассировочного файла иногда и могла появиться информация о сеансе, ожидающем строку, здесь он будет отсутствовать в любых случаях.
В остальном все секции трассировочного файла очень похожи на предыдущий случай. Поэтому для того чтобы определить, что произошло именно взаимное блокирование при выполнении DML операторов над таблицами организованными по индексу, необходимо обратиться в первую очередь в секции Current SQL statement for this session. Если среди объектов, включённых в отменённый SQL оператор, присутствует таблица этого типа, то вполне возможно произошёл именно данный сценарий взаимного блокирования.
Как избежать возникновения взаимной блокировки на таблицах организованных по индексу? Рекомендации здесь такие же, как и в первых двух случаях: последовательность обработки, сокращение времени транзакции и генерация уникальных значений первичных ключей при вставке.
Наложение фрагментов индекса на основе битовых карт
Случай взаимной блокировки с разделяемым режимом может возникать и в тех случаях, когда несколько сеансов пытаются обновить или удалить строки в таблицах с битовыми индексами. Правда, для этого необходимо наличие одного условия. Изменения в индексируемом столбце должны приводить к наложению строк в этом битовом индексе. Попробуем продемонстрировать это на примере. Для этого нам придётся взять экземпляр редакции Oracle Enterprise Edition, так как в используемой нами до этого редакции Express Edition отсутствует опция битовые индексы. Для начала, в первом сеансе, создадим таблицу t3 и заполним её данными:
Подключение к: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options ZH@XE(146)> CREATE TABLE t3 (c1 NUMBER PRIMARY KEY, c2 VARCHAR2(1)); Таблица создана ZH@XE(146)> INSERT INTO t3 (c1, c2) VALUES(1, 'А'); Вставлено: 1 строка ZH@XE(146)> INSERT INTO t3 (c1, c2) VALUES(2, 'Б'); Вставлено: 1 строка ZH@XE(146)> INSERT INTO t3 (c1, c2) VALUES(3, 'В'); Вставлено: 1 строка ZH@XE(146)> INSERT INTO t3 (c1, c2) VALUES(4, 'Г'); Вставлено: 1 строка ZH@XE(146)> INSERT INTO t3 (c1, c2) VALUES(5, 'Д'); Вставлено: 1 строка ZH@XE(146)> COMMIT; Commit complete
Создадим битовый индекс по столбцу c2:
ZH@ALFA(146)> CREATE BITMAP INDEX t3_bmp_idx ON t3(c2); Индекс создан
Включим трассировку для первого сеанса на уровне ожидания событий:
ZH@XE(146)> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; Session altered
Далее, попробуем изменить значение столбца «с2» второй строки на значение «В»:
ZH@ALFA(146)> UPDATE t3 SET c2 = 'В' where c1 = 2; Изменено: 1 строка
Во втором сеансе в это время изменяем значение столбца «с2» четвёртой строки на значение «Д»:
ZH@ALFA(144)> UPDATE t3 SET c2 = 'Д' where c1 = 4; Изменено: 1 строка
Если теперь в первом сеансе изменить значение столбца «с2» пятой строки на значение «Е», то возникнет ожидание:
ZH@ALFA(146)> UPDATE t3 SET c2 = 'Е' where c1 = 5; Ожидание…
Отчего возникло ожидание? Ведь мы изменяли разные строки таблицы, никак не связанные друг с другом. Может быть, дело здесь вовсе не в таблице, а в битовом индексе? На самом деле, данное ожидание происходит из-за того, что при изменении значения индексируемого столбца таблицы, блокируется не только строка битового индекса соответствующая текущему значению столбца, но и строка индекса соответствующая его новому значению. Проще говоря, в нашем случае, второй сеанс при обновлении четвёртой строки таблицы заблокировал строки битового индекса, значения которых соответствовали значению «Г» и «Д». Поэтому первый сеанс, пытающийся обновить столбец c2 пятой строки таблицы, с находящимся в нём значением «Д», будет ожидать освобождения необходимой ему строки битового индекса. Проверим это. Для начала заглянем в трассировочный файл первого сеанса:
WAIT #4: nam='enq: TX - row lock contention' ela= 2999504 name|mode=1415053316 usn<<16 slot="262154" sequence="2992" obj="" 59268="" tim="954083404"
В файле наблюдаем привычное ожидание “конкуренция блокировки строки”. Похожая ситуация отображена и в системном представлении v$lock:
SYSTEM@ALFA> SELECT * FROM v$lock WHERE type = 'TX'; ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- --- ---- ------ ---- ----- ------- ----- ----- 338343A8 338343BC 146 TX 589842 3019 0 4 9 0 32E01ABC 32E01AE0 146 TX 393235 2995 6 0 36 0 32E37268 32E3728C 144 TX 589842 3019 6 0 24 1 Выбрано: 3 строки
Первый сеанс (146) сделал запрос на установку TX блокировки в разделяемом режиме (REQUEST = 4) и ожидает освобождения строки индекса от блокировки транзакций исключительного режима во втором сеансе (144).
Продолжим изменение и попробуем во втором сеансе изменить значение столбца «с2» третьей строки на значение «Г»:
ZH@ALFA(144)> UPDATE t3 SET c2 = 'Г' where c1 = 3;
Ожидание…
По идее, мы сейчас пытаемся обновить столбец c2 в строке, где предыдущее его значение было равно «В». Следовательно, в битовом индексе на строку соответствующую этому значению должна быть выставлена блокировка. Но строка уже была заблокирована первым сеансом при обновлении значения столбца c2 второй строки на значение «В». Поэтому второй сеанс будет ждать её освобождения. В тоже время первый сеанс так же ждёт освобождения строки индекса соответствующей значению «Д», которая была захвачена вторым сеансом. Происходит бесконечное ожидание, и у нас возникает ситуация взаимной блокировки, о которой Oracle сигнализирует в первом сеансе:
ZH@ALFA(146)> UPDATE t3 SET c2 = 'Е' where c1 = 5;
UPDATE t3 SET c2 = 'Е' where c1 = 5
*
Ошибка в строке 1:
ORA-00060: deadlock detected while waiting for resource
Изучим содержимое образовавшегося трассировочного файла взаимной блокировки. Первые две секции содержат обычную для таких случаев информацию:
Current SQL statement for this session:
UPDATE t3 SET c2 = 'Е' where c1 = 5
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00060013-00000bb3 19 146 X 18 144 S
TX-00090012-00000bcb 18 144 X 19 146 S
А вот в секции сеансов ожидающих строки появились нужные нам данные:
Rows waited on: Session 144: obj - rowid = 0000E784 - AAAOeEAAAAAAAAAAAA (dictionary objn - 59268, file - 0, block - 0, slot - 0) Session 146: obj - rowid = 0000E784 - AAAOeEAAAAAAAAAAAA (dictionary objn - 59268, file - 0, block - 0, slot - 0)
По номеру objn мы легко находим объект, строки которого блокируются. В нашем случае это битовый индекс нашей таблицы:
SYSTEM@ALFA> SELECT owner, object_name FROM dba_objects WHERE object_id = 59268; OWNER OBJECT_NAME ----- ----------- ZH T3_BMP_IDX Выбрано: 1 строка
Наложение фрагментов индекса на основе битовых карт это, пожалуй, единственный случай из сценариев взаимной блокировки транзакций с разделяемым режимом, который содержит полную секцию Rows waited on, поэтому определить его по содержимому файлу трассировки не составляет труда.
Теперь поговорим немного о том, как избежать возникновения взаимных блокировок в битовых индексах. Первое, это конечно надо по возможности ограничить обновления значений столбца, по которому построен битовый индекс. Это убережёт от лишних блокировок строк индекса. Во- вторых, можно подобрать обновления строк так, чтобы они, к примеру, происходили последовательно в соответствии с упорядоченными значениями столбца, по которому построен битовый индекс. В данном случае будут просто возникать очереди ожиданий.
Нехватка слотов в таблице транзакций блока
Последний случай возникновения взаимного блокирования с ожидающей блокировкой в разделяемом режиме можно с уверенность назвать вырождающимся. Он связан с ожиданием, которое возникает при нехватке свободного слота в таблице транзакций блока (ITL). Начиная с десятой версии Oracle данный случай взаимной блокировки очень трудно воспроизвести. Это связано с тем, что максимальное количество слотов, которое может быть в таблице транзакций блока стало фиксированным и составляет на данный момент времени 255.
В ранних версиях Oracle начальное и максимальное количество слотов таблицы транзакций можно было задавать с помощью параметров таблицы INITRANS и MAXTRANS. Теперь изменение этих параметров не имеет никакого значения. В связи с этим, для того чтобы смоделировать данный сценарий взаимного блокирования, нам пришлось бы организовать одновременно более 255 транзакций. Сделать это было бы затруднительно, поэтому для моделирования ситуации мы будем использовать экземпляр Oracle версии 9i, где изменение одного из перечисленных параметров всё ещё было возможно.
Для начала, создадим две одинаковые таблицы t4 и t5, с искусственно ограниченным максимальным размером таблицы транзакций блока равным двум слотам:
Подключение к: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 – Production ZH@ALFA9> CREATE TABLE t4 (c1 NUMBER PRIMARY KEY, c2 VARCHAR2(50)) PCTFREE 0 INITRANS 2 MAXTRANS 2; Таблица создана ZH@ALFA9> CREATE TABLE t5 (c1 NUMBER PRIMARY KEY, c2 VARCHAR2(50)) PCTFREE 0 INITRANS 2 MAXTRANS 2; Таблица создана
Занесём в каждую из этих таблиц по три значения. Причём все добавленные строки должны уместиться в двух блоках. По одному на каждую таблицу:
ZH@ALFA9> INSERT INTO t4 (c1) VALUES(1); Вставлено: 1 строка ZH@ALFA9> INSERT INTO t4 (c1) VALUES(2); Вставлено: 1 строка ZH@ALFA9> INSERT INTO t4 (c1) VALUES(3); Вставлено: 1 строка ZH@ALFA9> INSERT INTO t5 (c1) VALUES(1); Вставлено: 1 строка ZH@ALFA9> INSERT INTO t5 (c1) VALUES(2); Вставлено: 1 строка ZH@ALFA9> INSERT INTO t5 (c1) VALUES(3); Вставлено: 1 строка ZH@ALFA9> COMMIT; Commit complete
Далее, образуем три сеанса и в каждом из них изменим следующие строки.
Первый сеанс:
ZH@ALFA9I(12)> UPDATE t4 SET c2 = 'Строка1' WHERE c1 = 1; Изменено: 1 строка ZH@ALFA9I(12)> UPDATE t5 SET c2 = 'Строка1' WHERE c1 = 1; Изменено: 1 строка
Второй сеанс:
ZH@XE(13)> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; Session altered ZH@ALFA9I(13)> UPDATE t4 SET c2 = 'Строка2' WHERE c1 = 2; Изменено: 1 строка
Третий сеанс:
ZH@ALFA9I(14)> UPDATE t5 SET c2 = 'Строка3' WHERE c1 = 3; Изменено: 1 строка
Теперь снова вернёмся ко второму сеансу:
ZH@ALFA9I(13)> UPDATE t5 SET c2 = 'Строка2' WHERE c1 = 2;
Ожидание…
Почему возникло ожидание, ведь изменяются совершенно разные и не зависящие друг от друга строки? На самом деле в этом нет ничего неожиданного. Ещё при создании таблиц мы указали, что количество слотов в таблице транзакций блока не может быть больше двух. Так как данные в каждой из созданных нами таблиц занимают по одному блоку, то выполнив команды, перечисленные выше, мы исчерпали лимит слотов в блоке таблицы t5. В результате этого, при попытке обновить данные в блоке таблицы второй сеанс вынужден ждать появления свободного слота. Чтобы убедиться в этом, как говориться наглядно, заглянем в трассировочный файл второго сеанса, а так же в системное представление v$lock.
В файле трассировки у нас довольно скудная информация. Здесь мы видим только то, что в сеансе постоянно возникает непонятное ожидание «очередь»:
WAIT #1: nam='enqueue' ela= 3077805 p1=1415053316 p2=65545 p3=455
А вот представление v$lock даёт нам гораздо больше информации:
SYSTEM@ALFA9I> SELECT * FROM v$lock WHERE type = 'TX'; ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- --- ---- ------ --- ----- ------- ----- ----- 67B7F044 67B7F150 12 TX 589870 447 6 0 211 1 67B7A1FC 67B7A308 13 TX 393245 450 6 0 110 0 682BED20 682BED30 13 TX 589870 447 0 4 3 0 67BAB4A8 67BAB5B4 14 TX 65545 455 6 0 39 0 Выбрано: 4 строки
Тут мы действительно видим во втором сеансе (13) ожидающую TX блокировку в разделяемом режиме. Причем, судя по значению столбцов ID1 и ID2 , второй сеанс ожидает освобождения слота занятого первым сеансом (12). Выбор Oracle в качестве блокирующего, первого сеанса, не носит какого-то обязательного характера. Если мы попробуем смоделировать это ожидание снова, то блокировать у нас уже будет третий сеанс. Если в этом отличие для нашей моделируемой ситуации? По большому счёту нет, поэтому продолжим наши изменения, и в третьем сеансе изменим строку в таблице t4:
ZH@ALFA9I(14)> UPDATE t4 SET c2 = 'Строка3' WHERE c1 = 3;
Ожидание…
Возникает бесконечное ожидание. Вследствие этого, во втором сеансе мы наблюдаем ошибку взаимного блокирования:
ZH@ALFA9I(14)> UPDATE t5 SET c2 = 'Строка2' WHERE c1 = 2;
UPDATE t5 SET c2 = 'Строка2' WHERE c1 = 2
*
Ошибка в строке 1:
ORA-00060: deadlock detected while waiting for resource
Отчего произошла взаимная блокировка? Почему Oracle посчитал, что данная ситуация является тупиковой? Попробуем ответить на эти вопросы. Как мы рассмотрели ранее, у нас возникло ожидание в одном из сеансов из-за того, что все слоты таблицы транзакций в блоке таблицы t5 были заняты первым и третьим сеансами. То есть второй сеанс ожидал освобождения свободного слота ITL для того чтобы заблокировать строку таблицы t5 для изменения. Пытаясь обновить строку в таблице t4, мы создали идентичное ожидание в третьем сеансе, но уже для блока таблицы t4. В результате, на момент времени возникновения взаимоблокировки, у нас получилось два ожидающих сеанса, второй и третий. Причём второй сеанс ожидал свободного слота в ITL блоке таблицы t5, все слоты которой были заняты первым и третьим сеансами, а третий сеанс свободного слота в ITL блоке таблицы t4, занятой первым и вторым сеансами. Из этой ситуации выходило, что второй и третий сеансы не могли освободить слоты ITL в блоках таблиц, так как сами находились в ожидании друг друга. Вроде бы складывается ситуация взаимного блокирования. Но мы забыли про первый сеанс. Ведь он находился не в режиме ожидания. Что если мы бы попробовали откатить или зафиксировать изменения в нём. В этом случае в ITL блоков таблиц t4 и t5 обязаны освободиться два слота, вследствие чего второй и третий сеансы должны были выйти из ожидания. На самом деле всё не так просто. СУБД Oracle в этой сложившейся ситуации, как бы ни берёт во внимание возможность освобождения слотов таблицы транзакций блока первым сеансом и определяет такое блокирование как взаимное. Вполне возможно это связано с особенностью построения и анализа графа ожидания транзакций. Посмотрим, как он будет выглядеть в нашем случае.
Так и есть, у нас образуется цикл (показан красным цветом) между двумя транзакциями второго и третьего сеансов. Этот цикл Oracle сразу интерпретирует как ситуацию взаимного блокирования, даже не вникая в то, что она может разрешиться сама собой.
Что же нам в этом случае покажет файл трассировки взаимной блокировки? Заглянем в его содержимое:
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE t5 SET c2 = 'Строка2' WHERE c1 = 2
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0006001d-000001c2 15 13 X 16 14 S
TX-00010009-000001c7 16 14 X 15 13 S
Rows waited on:
Session 14: no row
Session 13: no row
Ничего особенного. Все секции файла трассировки похожи на предыдущие сценарии. Правда единственное, что следует отметить, в файле отсутствует хоть какое-то упоминание о первом сеансе, что ещё раз подтверждает то, что он не участвует в ситуации взаимного блокирования, хотя содержимое представления v$lock явно указывало, что именно этот сеанс осуществляет блокировку.
Если вам в будущем придётся анализировать файл с подобным содержимым, обращайте своё внимание в первую очередь на версию Oracle. Такие взаимные блокировки на старших релизах Oracle маловероятны. Кроме этого, такая взаимная блокировка никогда не возникнет на операторах вставки INSERT. Ясно, что если сеанс при вставке столкнётся с нехваткой слотов в ITL, то он просто осуществит вставку в другой блок.
Как сделать, чтобы подобные ситуации взаимных блокировок не возникали? Рецепт довольно прост. Первое, старайтесь не изменять параметры INITRANS и MAXTRANS в сторону уменьшения без острой необходимости. Второе, если всё же параметр был изменён, увеличивайте его до полного пропадания взаимных блокировок.
Блокировки на уровне таблицы.
В предыдущих примерах мы рассмотрели варианты возникновения взаимных блокировок в результате блокирования строк в таблицах или индексах. Пришло время рассмотреть случаи взаимоблокировок, которые возникают при блокировании на уровне объектов. Такие события обычно связаны с внутренним механизмом работы 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 файла операторы вставки.
Рецепт предотвращения взаимного блокирования при неиндексированных внешних ключах уже лежит в названии условия возникновения взаимоблокировки. Надо взять в правило всегда создавать индексы для внешних ключей. Это предотвратит блокирование дочерней таблицы в жестких режимах. В случаях, когда индексирование делать невозможно или нежелательно следует обратиться к общему правилу последовательной обработки ресурсов. К примеру, обрабатывать вначале родительскую таблицу и только затем переходить к дочерней таблице.
Вставка методом прямой загрузки
Эта возможность образования взаимного блокирования встречается в 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)
Впрочем, практического значения содержимое этой секции в большинстве случаев не имеет. Не стоит забывать, что данный вид взаимного блокирования представляет собой блокирование на уровне объектов, но не как не строк.
Как не допустить возникновение взаимных блокировок при прямой загрузке? Первое, это конечно в виду того, что данный режим вставки накладывает на таблицу самую жесткую из известных блокировок, надо по возможности ограничить его использование. Второе, транзакция, в которой присутствует прямая загрузка должна быть по возможности короткой, это уменьшит время в течении которого может возникнуть взаимное блокирование. И наконец, последнее, можно попытаться организовать одинаковую последовательность обработки ресурсов в сеансах, что просто превратит взаимоблокировку в обыкновенную очередь.
Хранимые PL/SQL объекты
Все сценарии возникновения взаимоблокировки, которые мы рассматривали ранее, происходили при выполнении отдельных SQL команд. Это самый простой и быстрый путь для изучения механизмов взаимного блокирования. В действительности, большинство случаев взаимоблокировок происходит при выполнении не отдельных SQL команд, а хранимых PL/SQL объектов: процедур, пакетов или триггеров. Поэтому ниже мы попытаемся определить, существуют ли какие-нибудь отличия в том, где возникает блокировка.
Процедура
Смоделируем следующую ситуацию. Создадим процедуру p1 с единственной командой, которая будет обновлять нужную нам строку таблицы t1 в зависимости от входных параметров:
SQL> CREATE PROCEDURE p1(v1 in integer, v2 in VARCHAR2) 2> AS 3> BEGIN 4> UPDATE t1 SET c2 = v2 WHERE c1 = v1; 5> END; Процедура изменена
Образуем два сеанса. В первом сеансе изменим первую строку:
ZH@XE(31)> EXECUTE p1(1, 'Строка1'); PL/SQL procedure successfully completed
Во втором сеансе изменим вторую строку:
ZH@XE(23)> EXECUTE p1(2, 'Строка2'); PL/SQL procedure successfully completed
Вернёмся в первый сеанс, и изменим вторую строку:
ZH@XE(31)> EXECUTE p1(2, 'Строка2');
Ожидание…
Возникло ожидание. И это естественно, ведь мы следуем по пути первого сценария образования взаимной блокировки. Единственное отличие для нашего случая, это только то, что SQL команды здесь спрятаны в PL/SQL процедуре. Поэтому, если дальше следовать по сценарию, выполнение следующей команды изменяющей первую строку, должно привести к взаимному блокированию:
ZH@XE(23)> EXECUTE p1(1, 'Строка1');
Ожидание…
Так и есть, в первом сеансе происходит ошибка:
ZH@XE(31)> EXECUTE p1(2, 'Строка2');
BEGIN
*
Ошибка в строке 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "ZH.P1", line 4
ORA-06512: at line 2
Возникшая взаимоблокировка, подобна самому первому случаю, который мы рассматривали в самом начале. Первый сеанс установил TX блокировку в монопольном режиме на первую строку и ожидает установки такой же блокировки на вторую строку. Второй сеанс наоборот, установил TX блокировку в монопольном режиме на вторую строку и ожидает установки на первую строку. Всё как при обычном сценарии с SQL командами.
Рассмотрим далее содержимое трассировочного файла взаимной блокировки. Есть ли здесь, какие либо изменения. Первая секция содержит отменённый в результате ошибки оператор первого сеанса:
Current SQL statement for this session: UPDATE T1 SET C2 = :B2 WHERE C1 = :B1
Как видим это команда UPDATE содержащаяся в нашей процедуре p1. Правда, это мы знаем, что данная SQL команда принадлежит этой процедуре. Но обычно бывает трудно идентифицировать PL/SQL объект, которому принадлежит данный курсор. И в этом нам может помочь новый блок трассировочного файла, содержащий информацию стека вызовов PL/SQL. Он располагается сразу после первой секции вслед за словами PL/SQL Call Stack:
----- PL/SQL Call Stack ----- object line object handle number name 29CDE8E4 4 procedure ZH.P1 2670C20C 2 anonymous block
В нашем случае мы сразу можем определить, что отменённая команда принадлежит процедуре ZH.P1. Но иногда здесь может оказаться сразу несколько объектов, и в этом случае установить, кому из них принадлежит отменённый SQL оператор, бывает трудно.
Остальные секции трассировочного файла содержат обычную для такого сценария информацию, поэтому мы не будем подробно её разбирать:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00070008-000000e1 21 31 X 25 23 X
TX-0009002c-000000d9 25 23 X 21 31 X
Rows waited on:
Session 23: obj - rowid = 0000360E - AAADYOAAEAAAAGfAAA
(dictionary objn - 13838, file - 4, block - 415, slot - 0)
Session 31: obj - rowid = 0000360E - AAADYOAAEAAAAGfAAB
(dictionary objn - 13838, file - 4, block - 415, slot - 1)
И так, как мы убедились, взаимная блокировка при выполнении процедур ничем кардинально не отличается от сценариев с отдельными SQL командами. Разве только в трассировочном файле появилась новая секция о стеке вызовов PL/SQL. Правда, в процессе моделирования ситуации, мы забыли упомянуть об одной важной вещи. Если при возникновении взаимного блокирования с SQL командами Oracle для нормализации ситуации отменяет одну из SQL команд, то, что он отменит в нашем случае взаимоблокировки с PL/SQL объектами?
Исключения
Как мы сказали выше, Oracle в процессе возникновения взаимной блокировки откатывает один из SQL операторов к неявной точке сохранения, сделанной перед этим оператором. Это справедливо для отдельных SQL команд. Команда же помещённая в PL/SQL блок подчиняется правилам обработки исключений в PL/SQL коде. Если происходит необработанное исключение, то управление передается внешней среде. Что бы прояснить данную ситуацию попробуем её смоделировать. Для этого нам надо немного изменить процедуру p1:
ZH@XE(31)> CREATE OR REPLACE PROCEDURE zh.p1(v1 in integer, v2 in integer, v3
in VARCHAR2)
2> AS
3> BEGIN
4> INSERT INTO t1 (c1) VALUES (v1);
5> UPDATE t1 SET c2 = v3 WHERE c1 = v2;
6> END;
Процедура изменена
Теперь процедура не только изменяет указанную во входных параметрах строку, но и вставляет ещё одну новую. Причём вставка идёт до обновления.
Повторим все те же действия, что и в предыдущем примере с учётом вставки. Для начала в первом сеансе вставим третью строку и изменим первую:
ZH@XE(31)> EXECUTE p1(3, 1, 'Строка1'); PL/SQL procedure successfully completed
Посмотрим, занеслась ли строка в таблицу t1:
ZH@XE(31)> SELECT * FROM t1 C1 C2 -- ------- 3 1 Строка1 2 Строка2 Выбрано: 3 строки
Всё нормально. Во втором сеансе, вставим четвёртую строку и обновим вторую:
ZH@XE(24)> EXECUTE p1(4, 2, 'Строка2'); PL/SQL procedure successfully completed
Вернёмся в первый сеанс и вставим пятую строку с изменением второй.
ZH@XE(31)> EXECUTE p1(5, 2, 'Строка2');
Ожидание…
Возникло ожидание. Далее, создадим взаимоблокировку, изменив во втором сеансе первую строку:
ZH@XE(24)> EXECUTE p1(6, 1, 'Строка1'); PL/SQL procedure successfully completed
В первом сеансе возникает ошибка:
ZH@XE(31)> EXECUTE p1(5, 2, 'Строка2');
BEGIN
*
Ошибка в строке 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "ZH.P1", line 5
ORA-06512: at line 2
Выведем в этом же сеансе содержимое таблицы t1:
ZH@XE(31)> SELECT * FROM t1 C1 C2 -- ------- 3 1 Строка1 2 Строка2 Выбрано: 3 строки
Вместо четырёх строк мы видим только три. Где же строка со значением первичного ключа равного пяти? Ведь ошибка взаимоблокировки должна отменить только последний оператор. Всё так бы и было в случае с отдельными SQL командами. Но операторы у нас находятся в PL/SQL объекте, и как было сказано выше, обработка исключений, а ошибка взаимной блокировки вызывает именно исключение, происходит по определённым правилам. В нашем случае у нас не установлено в процедуре обработчика исключений, следовательно, ошибка взаимоблокировки вызывает необработанное исключение в PL/SQL блоке, что вызывает немедленную передачу управления во внешнюю среду с отменой всех незафиксированных изменений сделанных в пределах этого блока.
Попробуем ввести обработчик исключительных ситуаций в нашу процедуру, немного изменив её:
ZH@XE(31)> CREATE OR REPLACE PROCEDURE zh.p1(v1 in integer, v2 in integer, v3
in VARCHAR2)
2> AS
3> BEGIN
4> INSERT INTO t1 (c1) VALUES (v1);
5> BEGIN
6> UPDATE t1 SET c2 = v3 WHERE c1 = v2;
7> EXCEPTION when others THEN NULL;
8> END;
9> END;
Процедура изменена
Теперь ошибка взаимного блокирования происшедшая при выполнении команды UPDATE не будет приводить к прерыванию процедуры и откату всех изменений сделанных в ней. Убедимся в этом:
Первый сеанс:
ZH@XE(31)> EXECUTE p1(3, 1, 'Строка1'); PL/SQL procedure successfully completed
Второй сеанс:
ZH@XE(24)> EXECUTE p1(4, 2, 'Строка2'); PL/SQL procedure successfully completed
Первый сеанс:
ZH@XE(31)> EXECUTE p1(5, 2, 'Строка2'); PL/SQL procedure successfully completed
Второй сеанс:
ZH@XE(24)> EXECUTE p1(6, 1, 'Строка1'); PL/SQL procedure successfully completed
Как мы видим, хотя и ошибка взаимоблокировки возникла, она не привела к прерыванию выполнения процедуры в первом сеансе. Так же не были отменены изменения, сделанные в этой процедуре до оператора, вызвавшего исключения:
ZH@XE(31)> SELECT * FROM t1 C1 C2 -- ------- 3 5 1 Строка1 2 Строка2 Выбрано: 4 строки
Какой вывод можно сделать из всего вышесказанного? Никогда не стоит пренебрегать обработкой исключительной ситуации взаимного блокирования в хранимом PL/SQL объекте. Потерянные изменения могут быть больше чем при выполнении отдельных SQL команд.
Заключение
Подведём небольшой итог изучения взаимоблокировок:
- Взаимная блокировка может возникать только между двумя и более сеансами. Всё остальное это баги (ошибки) Oracle.
- В каждом сеансе обязательно должна быть открыта транзакция.
- В пределах каждой транзакции должны присутствовать как минимум две блокировки исключительных режимах. Причём одна из них должна быть установленной, а другая ожидающей.
- Из этих установленных и ожидающих блокировок должна быть выстроена цепочка, при которой ожидающая блокировка в одном сеансе ссылалась на установленную блокировку в другом сеансе.
- Данная цепочка должна замыкаться. То есть, последняя ожидающая блокировка в цепочке должна ожидать первую установленную блокировку.
- Транзакционные блокировки должны быть установлены или ожидать строки, ранее зафиксированные транзакциями. То есть заблокированные строки должны быть видимы для других сеансов, что, к примеру, не возможно при вставках строк.
- Одинаковая последовательность обработки ресурсов в большинстве случаев предотвращает взаимное блокирование.
- Поиск ситуаций взаимного блокирования осуществляется всегда, при этом используется граф ожидания транзакций.
- Цикл графа ожидающих транзакций соответствует взаимной блокировке, не зависимо от того, может ли ситуация разрешиться другими способами.
- Взаимная блокировка иногда может приводить к зависанию приложений, вместо вызова исключения.
Список литературы
- Bobak, M. J. (б.д.). Understanding and Interpreting Deadlocks or What to do When You Encounter ORA-00060.
- Cyran, M. (October 2005). Oracle® Database Concepts 10g Release 2 (10.2).
- Richmond Shee, K. D. Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning.
- Кайт, Т. (2003). Oracle для профессионалов. DiaSoft.
Комментариев нет:
Отправить комментарий