Перевод deadlock detected while waiting for resource
Пришло время рассмотреть второй сценарий возникновения ситуации взаимных блокировок. Его отличие от предыдущего заключается в том, что одна из TX-блокировок здесь находится в исключительном режиме, а вторая, ожидающая, – в разделяемом. Условий для возникновения такой взаимной блокировки на самом деле не так уж много, вернее, их всего три: нехватка места в таблице транзакций, перекрытие фрагментов индекса на основе битовых карт и наложение значений первичного или уникального ключа при вставке. В какой-то мере к последнему условию можно отнести и DML-операции с таблицами, организованными по индексу.
Ниже мы попробуем на примерах рассмотреть ситуации взаимного блокирования со всеми перечисленными выше условиями. Но сделать это будет не просто из-за особенностей установки разделяемого режима транзакционной блокировки. В связи с этим моделирование начнем с самого простого случая, с наложения значений первичного или уникального ключа при вставке.
Наложение значений первичного или уникального ключа при вставке
Для начала образуем два сеанса. Очистим таблицу t1 и для наглядности исследования процесса взаимного блокирования включим трассировку в первом сеансе. Далее в этом же сеансе будем последовательно вставлять в таблицу t1 две строки с ключами 1 и 2, а во втором проделаем те же действия, но только в обратном направлении.
В результате выполненных выше действий в каждом из сеансов нами были установлены по одной транзакционной блокировке в исключительном режиме (LMODE=6):
Теперь в первом сеансе попытаемся вставить строку с ключом 2. В связи с тем, что таблица t1 имеет ограничение первичного ключа, и строка с таким же значением данного ключа уже вставлена в незавершённой транзакции второго сеанса, выполнение этого оператора должно привести к ожиданию:
Если в это время заглянуть в трассировочный файл первого сеанса, можно увидеть, что в сеансе постоянно возникает ожидание “конкуренция блокировки строки”:
Но какой строки? Вставленные в таблицу строки не видны сеансам, так как транзакции не зафиксированы. Следовательно, они не могут являться причиной ожидания. Для прояснения ситуации заглянем в системное представление v$lock:
В представлении мы обнаруживаем три записи о TX-блокировках. Две из них установлены в исключительном режиме на вновь вставленные строки. Третья, ожидающая блокировка имеет в поле REQUEST значение 4, что соответствует разделяемому режиму. Значения полей ID1 и ID2 этой блокировки явно указывают на транзакцию во втором сеансе. Из этого ясно, что первый сеанс ожидает освобождения какой-то строки, заблокированной вторым сеансом. Этой строкой в нашем случае является строка индекса первичного ключа, соответствующая значению ключа, равного двум. Данное значение ранее было добавлено сеансом 38 в индекс в момент вставки второй строки в таблицу. После чего для защиты уникальности первичного ключа на эту строку индекса была наложена TX-блокировка в исключительном режиме. Если теперь второй сеанс (38) отменит транзакцию, то в первом сеансе для вновь вставленной строки таблицы будет установлена TX-блокировка в исключительном режиме. Если же второй сеанс зафиксирует транзакцию, то в первом сеансе будет наблюдаться ошибка нарушения ограничения первичного ключа:
А что произойдет, если второй сеанс попытается вставить строку со значением первичного ключа, равным единице:
Так как первый сеанс уже ранее вставил в таблицу строку с данным значением первичного ключома и выставил на строку индекса TX-блокировку в исключительном режиме, второй сеанс должен ждать освобождения этой блокировки. В тоже время первый сеанс уже ожидает окончания транзакции во втором сеансе и, следовательно, не может освободить эту блокировку. В результате у нас снова образуется бесконечное ожидание, которое приводит в первом сеансе к исключению:
Итак, взаимная блокировка произошла. Настало самое время заглянуть в сгенерированный в результате ошибки трассировочный файл первого сеанса. В первой секции файла мы видим оператор вставки INSERT, который был отменён:
В графе взаимной блокировки имеются небольшие отличия от предыдущего сценария:
В столбце wait появилось значение S. Это означает, что ожидаемая блокировка находится в разделяемом режиме. В остальном разбор графа не изменился и соответствует первому сценарию. Поэтому сразу перейдём к секции ожидания строк:
В секции присутствует информация только об одной ожидающей строке, да и то это ожидание блокировки, которое было отменено. Если вам попались эти данные, то считайте, что вам повезло. Обычно информации здесь нет. Это лишний раз доказывает, что рассматриваемый случай взаимной блокировки не является ситуацией взаимного блокирования на уровне строк таблицы. Поэтому, для того чтобы распознать в трассировочном файле блокирование при наложении значений первичного или уникального ключей, необходимо в первую очередь обратиться к секции «Current SQL statement for this session». Если это оператор INSERT, и таблица, в которую он вставляет значения, имеет первичные или уникальные ключи, то с большой долей вероятности можно судить о том, что произошел второй сценарий взаимного блокирования.
Обобщая всё вышеперечисленное, можно сказать, что ситуация с взаимным блокированием при вставке первичного и уникального ключей обычно складывается из-за неправильно разработанного приложения, когда значения этих ключей имеют одинаковые значения в разных сеансах. Частично этого можно было бы избежать, если, к примеру, использовать генерацию неповторяющихся значений ключей с помощью последовательностей. В других случаях, когда такой вариант не подходит, необходимо просто обеспечить одинаковую последовательность действий при вставке в таблицу или использовать пакет DBMS_LOCK, как указано у Тома Кайта.
Выполнение DML операторов над таблицами, организованными по индексу
Этот сценарий взаимной блокировки можно в какой-то мере отнести к предыдущему случаю. Здесь ожидающие сеансы также запрашивают блокировки транзакций в разделяемом режиме и TX-блокировки в исключительном режиме также выставляются на строки индекса. Но, в отличие от наложения первичных ключей при вставке, эта взаимная блокировка может проявляться при выполнении любого DML-оператора. Продемонстрируем это на примере. Организуем два сеанса. В первом сеансе создадим индекс-организованную таблицу, вставим в неё две строки и включим трассировку:
Теперь в первом сеансе изменим первую и вторую строки. Во втором сеансе проделаем эти же действия, но в обратной последовательности:
Далее в первом сеансе изменим вторую строку таблицы. Так как на эту строку во втором сеансе выставлена TX-блокировка в исключительном режиме, данные действия приведут к ожиданию:
В трассировочном файле первого сеанса в это время наблюдаем ожидание “конкуренция блокировки строки”:
Если после этого мы заглянем в представление v$lock, то обнаружим там точно такую же картину, как и при наложении значений первичных или уникальных ключей:
Продолжим изменения, и во втором сеансе поправим первую строку таблицы:
И снова, как и раньше, у нас возникло взаимное блокирование:
Рассмотрим содержимое трассировочного файла взаимной блокировки.
Текущий отменённый оператор:
Сеансы, ожидающие строки:
Как видим, в секции «Rows waited on» нет значений. Это связано с тем, что таблица, организованная по индексу, представляет по своей организационной структуре в некотором смысле индекс. И хотя в данной таблице представлен идентификатор строки, он, по сути, является логическим идентификатором, построенным на основе значений первичного ключа, а не на основе физического размещения. Поэтому, если в предыдущем случае взаимной блокировки в данной секции трассировочного файла иногда и могла появиться информация о сеансе, ожидающем строку, здесь он будет отсутствовать в любых случаях.
В остальном все секции трассировочного файла очень похожи на предыдущий случай. Поэтому, для того чтобы определить, что произошло именно взаимное блокирование при выполнении DML-операторов над таблицами, организованными по индексу, необходимо обратиться в первую очередь в секции Current SQL statement for this session. Если среди объектов, включённых в отменённый SQL-оператор, присутствует таблица этого типа, то вполне возможно, что произошёл именно данный сценарий взаимного блокирования.
Как избежать возникновения взаимной блокировки на таблицах организованных по индексу? Рекомендации здесь такие же, как и в первых двух случаях: последовательность обработки, сокращение времени транзакции и генерация уникальных значений первичных ключей при вставке.
Наложение фрагментов индекса на основе битовых карт
Случай взаимной блокировки с разделяемым режимом может возникать и в тех случаях, когда несколько сеансов пытаются обновить или удалить строки в таблицах с битовыми индексами. Правда, для этого необходимо наличие одного дополнительного условия: изменения в индексируемом столбце должны приводить к наложению строк в этом битовом индексе. Попробуем продемонстрировать это на примере. Для этого нам придётся взять экземпляр редакции Oracle Enterprise Edition, так как в используемой нами до этого редакции Express Edition отсутствует опция битовых индексов. Для начала в первом сеансе создадим таблицу t3 и заполним её данными:
Создадим битовый индекс по столбцу c2:
Включим трассировку для первого сеанса на уровне ожидания событий:
Далее попробуем изменить значение столбца «с2» второй строки на «В»:
Во втором сеансе в это время изменяем значение столбца «с2» четвёртой строки значение «Д»:
Если теперь в первом сеансе изменить значение столбца «с2» пятой строки на «Е», то возникнет ожидание:
Отчего возникло ожидание? Ведь мы изменяли разные строки таблицы, никак не связанные друг с другом. Может быть, дело здесь вовсе не в таблице, а в битовом индексе? На самом деле, данное ожидание происходит из-за того, что при изменении значения индексируемого столбца таблицы, блокируется не только строка битового индекса, соответствующая текущему значению столбца, но и строка индекса, соответствующая его новому значению. Проще говоря, в нашем случае второй сеанс при обновлении четвёртой строки таблицы заблокировал строки битового индекса, значения которых соответствовали значениям «Г» и «Д». Поэтому первый сеанс, пытающийся обновить столбец c2 пятой строки таблицы с находящимся в нём значением «Д», будет ожидать освобождения необходимой ему строки битового индекса. Проверим это. Для начала заглянем в трассировочный файл первого сеанса:
В файле наблюдаем привычное ожидание “конкуренция блокировки строки”. Похожая ситуация отображена и в системном представлении v$lock:
Первый сеанс (146) сделал запрос на установку TX-блокировки в разделяемом режиме (REQUEST = 4) и ожидает освобождения строки индекса от блокировки транзакций исключительного режима во втором сеансе (144).
Продолжим изменения и попробуем во втором сеансе изменить значение столбца «с2» третьей строки на «Г»:
По идее, мы сейчас пытаемся обновить столбец c2 в строке, где предыдущее его значение было равно «В». Следовательно, в битовом индексе на строку соответствующую этому значению должна быть выставлена блокировка. Но строка уже была заблокирована первым сеансом при обновлении значения столбца c2 второй строки на значение «В». Поэтому второй сеанс будет ждать её освобождения. В то же время первый сеанс также ждёт освобождения строки индекса, соответствующей значению «Д», которая была захвачена вторым сеансом. Происходит бесконечное ожидание, и у нас возникает ситуация взаимной блокировки, о которой Oracle сигнализирует в первом сеансе:
Изучим содержимое образовавшегося трассировочного файла взаимной блокировки. Первые две секции содержат обычную для таких случаев информацию:
А вот в секции сеансов, ожидающих строки, появились нужные нам данные:
По номеру objn мы легко находим объект, строки которого блокируются. В нашем случае это битовый индекс нашей таблицы:
Наложение фрагментов индекса на основе битовых карт это, пожалуй, единственный случай из сценариев взаимной блокировки транзакций с разделяемым режимом, который содержит полную секцию Rows waited on, поэтому определить его по содержимому файлу трассировки не составляет труда.
Теперь поговорим немного о том, как избежать возникновения взаимных блокировок в битовых индексах. Прежде всего, конечно, надо по возможности ограничить обновления значений столбца, по которому построен битовый индекс. Это убережёт от лишних блокировок строк индекса. Во-вторых, можно подобрать обновления строк таким образом, чтобы они, к примеру, происходили последовательно в соответствии с упорядоченными значениями столбца, по которому построен битовый индекс. В этом случае будут просто возникать очереди ожиданий.
Взаимоблокировки deadlock-сессий в Oracle
Взаимоблокировки deadlock-сессий в OracleАрхив номеров / 2016 / Выпуск №03 (160) / Взаимоблокировки deadlock-сессий в Oracle
|
ВАЛЕРИЙ МИХЕИЧЕВ, эксперт Oracle, ОСАО «Ингосстрах», Valery.Mikheitchev@ingos.ru