Oracle deadlock как найти
Обновлено: 05.07.2024
Наложение значений первичного или уникального ключа при вставке
В результате выполненных выше действий в каждом из сеансов нами были установлены по одной транзакционной блокировке в исключительном режиме (LMODE=6):
Но какой строки? Вставленные в таблицу строки не видны сеансам, так как транзакции не зафиксированы. Следовательно, они не могут являться причиной ожидания. Для прояснения ситуации заглянем в системное представление v$lock:
А что произойдет, если второй сеанс попытается вставить строку со значением первичного ключа, равным единице:
Так как первый сеанс уже ранее вставил в таблицу строку с данным значением первичного ключома и выставил на строку индекса TX-блокировку в исключительном режиме, второй сеанс должен ждать освобождения этой блокировки. В тоже время первый сеанс уже ожидает окончания транзакции во втором сеансе и, следовательно, не может освободить эту блокировку. В результате у нас снова образуется бесконечное ожидание, которое приводит в первом сеансе к исключению:
Итак, взаимная блокировка произошла. Настало самое время заглянуть в сгенерированный в результате ошибки трассировочный файл первого сеанса. В первой секции файла мы видим оператор вставки INSERT, который был отменён:
В графе взаимной блокировки имеются небольшие отличия от предыдущего сценария:
В столбце wait появилось значение S. Это означает, что ожидаемая блокировка находится в разделяемом режиме. В остальном разбор графа не изменился и соответствует первому сценарию. Поэтому сразу перейдём к секции ожидания строк:
Выполнение DML операторов над таблицами, организованными по индексу
Далее в первом сеансе изменим вторую строку таблицы. Так как на эту строку во втором сеансе выставлена TX-блокировка в исключительном режиме, данные действия приведут к ожиданию:
Если после этого мы заглянем в представление v$lock, то обнаружим там точно такую же картину, как и при наложении значений первичных или уникальных ключей:
Продолжим изменения, и во втором сеансе поправим первую строку таблицы:
Рассмотрим содержимое трассировочного файла взаимной блокировки.
Текущий отменённый оператор:
Сеансы, ожидающие строки:
Как видим, в секции «Rows waited on» нет значений. Это связано с тем, что таблица, организованная по индексу, представляет по своей организационной структуре в некотором смысле индекс. И хотя в данной таблице представлен идентификатор строки, он, по сути, является логическим идентификатором, построенным на основе значений первичного ключа, а не на основе физического размещения. Поэтому, если в предыдущем случае взаимной блокировки в данной секции трассировочного файла иногда и могла появиться информация о сеансе, ожидающем строку, здесь он будет отсутствовать в любых случаях.
В остальном все секции трассировочного файла очень похожи на предыдущий случай. Поэтому, для того чтобы определить, что произошло именно взаимное блокирование при выполнении DML-операторов над таблицами, организованными по индексу, необходимо обратиться в первую очередь в секции Current SQL statement for this session. Если среди объектов, включённых в отменённый SQL-оператор, присутствует таблица этого типа, то вполне возможно, что произошёл именно данный сценарий взаимного блокирования.
Как избежать возникновения взаимной блокировки на таблицах организованных по индексу? Рекомендации здесь такие же, как и в первых двух случаях: последовательность обработки, сокращение времени транзакции и генерация уникальных значений первичных ключей при вставке.
Наложение фрагментов индекса на основе битовых карт
Создадим битовый индекс по столбцу c2:
Далее попробуем изменить значение столбца «с2» второй строки на «В»:
Если теперь в первом сеансе изменить значение столбца «с2» пятой строки на «Е», то возникнет ожидание:
А вот в секции сеансов, ожидающих строки, появились нужные нам данные:
По номеру objn мы легко находим объект, строки которого блокируются. В нашем случае это битовый индекс нашей таблицы:
Наложение фрагментов индекса на основе битовых карт это, пожалуй, единственный случай из сценариев взаимной блокировки транзакций с разделяемым режимом, который содержит полную секцию Rows waited on, поэтому определить его по содержимому файлу трассировки не составляет труда.
Долго рыл форум на предмет ORA-000060: Deadlock detected.
Но честно говоря, трэйс все равно не понятен.
Если не сложно подскажите, плиз:
*** 2009-03-07 17:27:47.757
*** SESSION ID:(11.2545) 2009-03-07 17:27:47.757
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE RNTS_OPERATIONS_C.TD_NUMBER SET DOCUMENTDATE = :1 WHERE ID_TDNUMBER = 123894
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TM-00007ce9-00000000 11 11 SX 10 13 SX SSX
TX-000a0026-00000856 10 13 X 11 11 X
session 11: DID 0001-000A-000000B5 session 13: DID 0001-000A-000000B1
session 13: DID 0001-000A-000000B1 session 11: DID 0001-000A-000000B5
Rows waited on:
Session 13: no row
Session 11: obj - rowid = 00007CE9 - AAAHzpAAHAAAADYAAF
(dictionary objn - 31977, file - 7, block - 216, slot - 5)
Information on the OTHER waiting sessions:
Session 13:
pid=10 serial=5280 audsid=28181 user: 55/CONNECT
O/S info: user: . term: SUPPORT02, ospid: 3084:2972, machine: TEST\SUPPORT02
program: ast.exe
application name: ast.exe, hash value=0
Current SQL Statement:
DELETE FROM RNTS_OPERATIONS_C.DOCUMENT_HEADER WHERE ID_DOCHEADER = 124326
End of information on OTHER waiting sessions.
Ниже еще вот такая запись:
11 UPDATE RNTS_OPERATIONS_C.TD_NUMBER SET DOCUMENTDATE = :1 WHERE ID_TDNUMBER = 123894
obj - rowid = 00007CE9 - AAAHzpAAHAAAADYAAF
(dictionary objn - 31977 , file - 7, block - 216, slot - 5)
Сессия 13 уже держит строку rowid='AAAHzpAAHAAAADYAAF' в e X clusive режиме, видима она изменяла или удаляла ее ранее.
И пытается выполнить операцию удаления:
При этом пытается эскалировать имеющуюся dml блокировку(TM) на объекте 31977 с режима SX до SSX , что невозможно ввиду того что сессия 11 так же держит на нем блокировку SX .
13 DELETE FROM RNTS_OPERATIONS_C.DOCUMENT_HEADER WHERE ID_DOCHEADER = 124326
Объект 31977 это действительно таблица TD_NUMBER.
Таблицы TD_NUMBER и DOCUMENT_HEADER связаны между собой через другую таблицу DOCUMENTS_IN_TD (многие ко многим).
Т.е. таблицы выглядят так:
На таблице DOCUMENTS_IN_TD созданы два индекса на FK:
Т.е. отдельного индекса на ID_TDNUMBER нет.
Не спрашивайте почему – такая БД досталась, а спросить некого :)
Таким образом, получаем потенциальную проблему: при попытке обновить или удалить запись в таблице TD_NUMBER будет заблокирована вся таблица DOCUMENTS_IN_TD (SSX блокировка из-за отсутствия индекса на FK).
Пытаюсь привязать теорию собственно к логу: сессия 11 при попытке заапдейтить запись в таблице TD_NUMBER наложила SX блокировку на соответствующие записи. Сессия 13 пытается удалить запись в таблице DOCUMENT_HEADER, что приводит к дидлоку. Почему? Раз мы имеем дидлок на объекте 31977 (TD_NUMBER), то цепочка выглядит как-то так:
1. Собственно удаление записи в таблице DOCUMENT_HEADER
2. Это приводит к наложению SX-блокировки на таблицу DOCUMENTS_IN_TD (раз есть индекс на внешнем ключе ID_DOCHEADER)
3. Удаление записи из таблицы DOCUMENTS_IN_TD приводит к необходимости SSX-блокировки на таблицу TD_NUMBER (т.к. индекса на внешнем ключе ID_TDNUMBER нет). А на таблице TD_NUMBER уже есть SX-блокировка сессии 11.
Т.е. получается, что и дочерняя таблица(DOCUMENTS_IN_TD) может наложить SSX-блокировку на родительскую(TD_NUMBER)?
Таким образом, если сделать индекс на внешнем ключе ID_TDNUMBER таблицы TD_NUMBER, это предотвратит возникновение SSX-блокировок таблиц, следовательно предотвратит возниконовение дидлока?
потенциально в
UPDATE emp set sal=sal where depno=10 .
возможен деадлок
Так все-таки получается, что индексирование внешнего ключа НЕ влияет на дидлок. Т.е. вообще не влияет.
Собственно это очень важный вывод.
Нужно копать логику приложения, не пытаясь выкрутиться созданием индексов на внешние ключи, т.к. это не поможет.
В любой многопользовательской среде неизбежно ситуация, когда два пользователя захотят работать с одними и теми же данными в одно и то же время. База данных должна обеспечивать отсутствие такой возможности. Принцип изоляции из ACID теста гласит что база данных должна гарантировать что сессии не должны видеть результаты выполнения транзакций других сессий до тех пор, пока транзакция не завершена. Для обеспечения такого результата БД должна упорядочить параллельный доступ к данным; т.е. убедиться, что даже если сессии затребовали доступ к одним и тем же строкам, в реальности запросы выстраиваются в очередь и ждут своей очереди.
Упорядочивание доступа происходит за счёт работы механизма табличных и строчных блокировок. Блокировка в Oracle происходит полностью автоматически. Грубо говоря, проблемы возникают только если программа пытается взаимодействовать с механизмом блокировок с помощью плохо написанного кода, или если бизнес аналитики и бизнес-модель разработана неправильно где сессии будут пересекаться.
Общие и исключительные блокировки
Стандартный уровень блокировок в Oracle гарантирует максимальный уровень параллелизма. Это значит что если сессия обновляет одну строку – блокируется одна строка, ничего больше. Более того, строка блокируется только для изменения – остальные сессии могут считывать эту строку. Блокировка существует пока транзакция не завершена, либо COMMIT либо ROLLBACK. Это исключительная (exclusive) блокировка: первая сессия затребовала блокировку строки на изменение, остальные сессии которые хотят заблокировать строку для изменения должны ждать. Доступ для чтения разрешён – несмотря на то что строка изменяется сессией заблокировавшей строку, операции чтения будут использовать данные отката чтобы гарантировать недоступность данных неподтверждённых транзакций.
Только одна сессия может блокировать строку или таблицу исключительной блокировкой – но общие (shared) блокировки могут накладываться на один объект многими сессиями. Нет никакого смысла использовать общую блокировку для строки, так как единственной целью блокировки стрки это получение уникального доступа к данным для изменения. Общие блокировки накладываются на всё таблицу, и многие сессии могут наложить общую блокировку на одну и ту же таблицу. Общие блокировки нужны для того, чтобы предотвратить исключительную блокировку таблицы другими сессииями: вы не можете получить исключительную блокировку если уже существует общая блокировка. Эксклюзивная блокировка таблицы нужна для выполнения DDL команд. Вы не можете выполнить запрос которые изменяет объект (к примеру удалить столбец из таблицы) если хотя бы одна другая сессия заблокировала таблицу общей блокировкой.
Для выполнения DML команда над строками, сессия должна получить исключительные блокировки всех строк которые будут изменяться и общую блокировку для таблицы. Если другая сессия уже наложила исключительную блокировку на строки, сессия будет висеть пока блокировки не будут убраны командой COMMIT или ROLLBACK. Если другая сессия заблокировала таблицу общей блокировкой но исключительная блокировка наложена на другие строки – то всё в порядке оба запроса могут работать. Исключительная блокировка для таблицы может быть, но по умолчанию механизм блокировок не блокирует всю таблицу пока это не необходимо для DDL команды.
Все DML команды требуют минимум две блокировки: исключительная блокировка строки которая изменяется и общая блокировка для таблицы содержащей строку. Исключительная блокировка предотвращает взаимодействие других сессий с изменяемой строкой а общая блокировка предотвращает другие сессии от изменения таблицы с помощью DDL команды. Эти блокировки запрашиваются автоматически. Если DML запрос не может получить необходимые блокировки – сессия будет висеть до момента получения этих блокировок.
Для выполнения DDL команды – необходимо исключительная блокировка всего объекта. Эта блокировка не может быть получена пока не завершены все DML транзакции к таблице и не освобождена исключительные блокировки строк и общие блокировки таблицы. Исключительная блокировка необходимая для DDL команды запрашивается автоматически, но если нельзя заблокировать объект в текущий момент – обычно потому что другие сессии наложили общую блокировку – то запрос прекратит выполнение с ошибкой вместо ожидания.
Механизм размещения в очереди
Запросы на блокировку помещаются в очередь. Если сессия запросила блокировку и не может получить её так как другая сессия уже заблокировала строку или объект, сессия будет ждать. Может случить что несколько сессий ждут доступа для одной и той же строки или объекта – в этом случае, Oracle будет отслеживать порядок в котором сессии запрашивали блокировку. Когда сессия заблокировавшая объек или строку освобождает его – блокировка разрешается следующей сессии и так далее. Это называется механизм размещения в очереди (enqueue mechanism).
Если вы не хотите чтобы сессия ждала в очереди если нет возможности заблокировать объект, то единственным способом избежать этого будет использование WAIT или NOWAIT директивы в команде SELECT … FOR UPDATE. Обычный SELECT всегда выполнится успешно так как SELECT не требует каких-либо блокировок – но DML команда будет висеть. Команда SELECT… FOR UPDATE вернёт набор строк и заблокирует их исключительной блокировкой. Если строки уже заблокированы – команда будет ждать в очереди пока не освободятся блокировки, как обычная DML команда. Для того чтобы избежать зависания сессии можно использовать SELECT… FOR UPDATE NOWAIT или SELECT… FOR UPDATE WAIT <n> где n это количетсво секунд ожидания. После получения блокировок с помощью команды SELECT FOR UPDATE вы можете выполнять DML команды без возможности зависания сессии.
Возможно добавить директиву SKIP LOCKED к команде SELECT FOR UPDATE, тогда запрос вернёт только те строки которые не заблокированы другими сессиями. Эта команда существовала и раньше но поддерживается только с версии 11g
Конкуренция блокировок
Когда сессия запрашивает блокировку для строки или объекта и не может её получить потому что объекты уже заблокированы другой сессией – сессия зависает. Это конкуренция блокировок, и она может плохо сказыватьяс на производительности базы данных так как все сессии в очереди ожидают блокировки. Некоторые блокировки могут быть необходимыми: природа приложения предполагает доступ разными пользователями к одним и тем же данным в один и тот же момент времени в результате работы. Некоторые же параллельные блокировки могут быть вызваны неправильно написанной программой.
Oracle предоставляет способ для обнаружения конкурирующих блокировок и также возможность разрешить проблему конкуренции в случае необходимости. Отдельным видом конкурирующих транзакций является deadlock. В случае возникновения таких ситуаций БД устраняет их автоматически.
Причины конкурирующих транзакций
Иногда бывает что бизнес-пользователям необходимо изменять данные в одних и тех же строках одновременно. Если это тормозит производительность системы, единственных выходом будет разработка новой бизнес-модели и новых бизнес-процессов. Но несмотря на то, что часть конкурирующих блокировок это правильное поведение системы – иногда конкуренция возникает из-за плохо разработанного приложения, что усугубляет проблему.
Длительные транзакции всегда будут вызывать проблемы. Типичным примером является случай, когда пользователь обновляет строку и не подтверждает транзакцию. Возможно пользователь даже ушёл на обед и не подтвердил транзакцию. Вы не сможете контролировать это если пользователи работают с помощью таких инструментов как SQL *Plus, но этого никогда не должно происходить в хорошо продуманном приложении. Программа должна учитывать что блокировка должна запрашиваться непосредственно перед изменением данных и освобождаться немедленно после завершения работы.
Некоторые программы запрашивают больше блокировок чем им необходимо. Например в некоторых программах для разработки всегда используется SELECT.. FOR UPDATE чтобы избежать необходимости перечитывать данные и проверять их на изменения. Некоторые инструменты для разработки не используют блокировку строки: если пользователь хочет обновить всего одну строку – компонент запрашивает блокировку для нескольких десятков или даже сотен строк. Если ваша программа написана с использованием таких компонентов – база данных Oracle будет именно то что вы сказали ей делать: блокировть много строк вместо одной, что не необходимо с точки зрения бизнес-логики.
Обнаружение и разрешение проблем вызванных конкурирующими транзакциями
Конкуренция блокировок это обычное последствие одновременного доступа разных пользователей к одним и тем же данным. Эта проблема может усиливаться из-за плохого приложения, но в принципе конкуренция блокировок это часть нормальной работы базы данных. Поэтому DBA не может решить эту проблему полностью – он может только указать что существует проблема, и предложить учитывать эту информацию при проектировании системы: разработке структур данных и процессов.
В экстренных случаях, DBA может разрешить конкретную ситуацию – отключив сессию, или сессии которые задерживают много блокировок слишком долго. Когда сессия отключается принудительно – все блокировки вызванные этой сессией освобождаются и транзакция отменяется. Заблокированные сессии смогут продолжать работу. Для отключения сессии можно использовать Database Control или команду ALTER SYSTEM KILL SESSION. В нашем примере если мы решим отключить пользователя SCOTT, можно выбрать эту строку и нажать кнопку KILL SESSION.
Deadlock
Возможно возникновение ситуации, когда две сессии блокируют друг друга таким образом что обе будут висеть вечно, каждая будет ожидать освобождения блокировок другой. Это называется deadlock. Deadlock-и это не проблема DBA: они могут быть вызваны только плохой архитектурой системы и разрешаются автоматически базой данных. Информация о возникновении deadlock-ов записываетя в системный журнал, с подробным описание в файле трассировки – частью обычного мониторинга базы данных является проверка на возникновение deadlock-ов и предоставление разработчиками подробной информации при каких обстоятельствах это произошло.
Вы должны понимать что deadlock-и возникают только по вине неправильно спроектированной программы. Код пытается выполнить что-то, что логически невозможно. Хорошо написанное приложени всегда вызывает блокировки последовательно таким образом, чтобы исключить возникновение deadlock-ов или будет проверять наличие несовместимых блокировок перед тем как их запрашивать.
Блокировки транзакций. Захват и ожидание в исключительном режиме
Механизм взаимоблокировки
Вначале создадим тестового пользователя zh и выдадим ему все необходимые привилегии:
Образуем два сеанса и для наглядности разбора ситуации включим в первом из них трассировку на уровне событий ожидания. В этом же сеансе будем последовательно изменять первую и вторую строки, а во втором проделаем те же действия, но в обратном порядке.
Рассмотрим более подробно содержимое этого запроса. Столбец SID здесь содержит идентификаторы первого и второго сеансов (28 и 24). Содержимое столбца TYPE указывает на тип блокировки, в нашем случае блокировки транзакции (TX). Столбец LMODE хранит значение 6, что соответствует установившемуся исключительному режиму блокировки.
В результате мы видим, что в каждом из сеансов имеется по одной TX-блокировке в установившемся исключительном режиме. Определить, к какой транзакции относится каждая из этих TX-блокировок, можно с помощью содержимого дополнительных столбцов ID1 и ID2. В них находятся составные части идентификаторов транзакций, декодировать которые можно с помощью следующего запроса:
Данный идентификатор нам ещё встретится в дальнейшем, когда мы будем разбирать содержимое трассировочного файла взаимной блокировки, а пока продолжим последовательность начатых нами действий и изменим в первом сеансе содержимое второй строки:
Сеанс находиться в ожидании. Оно возникло от того, что первый сеанс пытается установить TX-блокировку в исключительном режиме второй строки, которая уже захвачена TX-блокировкой второго сеанса. Если в это время заглянуть в файл трассировки первого сеанса, то мы увидим там следующие строки:
Итак, на данный момент мы имеем классическую картину ожидания. Но что произойдет, если мы изменим во втором сеансе первую строку? Ведь она уже захвачена первым сеансом:
Убедимся в том, что транзакции отменены не были. Для этого выполним следующий запрос:
Как видно из результатов запроса, транзакции по-прежнему активны. Отменять их полностью у Oracle нет необходимости, достаточно лишь просто вернуться в одном из сеансов к неявной точке сохранения, которая делается перед каждым DML-оператором.
Что же касается самой Oracle, то в результате всех перечисленных выше действий, приведших к возникновению взаимной блокировки, в файл журнала alert_xe.log будет занесена запись следующего вида:
Трассировочный файл взаимной блокировки
Вспомним, как Oracle обнаруживает блокировки. Для этого он постоянно строит граф ожидания транзакций. Если в этом графе обнаружен цикл, то это означает, что возникла взаимная блокировка. Так вот именно этот цикл и отображается в секции Deadlock graph, правда в очень специфическом виде.
Итак, граф расшифрован. Он дал нам описание цепочки захватов и ожиданий TX блокировок в сеансах. Но по этой цепочке мы можем судить только об общей картине возникновения взаимной блокировки. Если же нам потребуется найти конкретные ресурсы, из-за которых возникают ожидания, сделать нам это будет затруднительно. К счастью Oracle сам позаботился об этом, записав в файл трассировки информацию о строках, освобождения которых от TX-блокировок ожидают сеансы. Рассмотрим более подробно эту секцию. Найти её можно сразу после графа, по ключевой строке Rows waited on:
В этой секции для каждого ожидающего сеанса, который перечислен в графе, указана строка, TX-блокировку которой пытается получить этот сеанс. Строка идентифицируется номером объекта, которому она принадлежит, и идентификатором ROWID. Чуть ниже дана их полная расшифровка в десятичном виде. Это позволяет с лёгкостью, обратившись, например, к системному представлению dba_objects, идентифицировать объект, которому принадлежит данная строка:
Следующая секция трассировочного файла, которую мы рассмотрим, хотя и не столь важна, позволяет дополнить картину взаимной блокировки. Она располагается сразу за секцией Rows waited on и находится по следующим ключевым словам:
Из этой секции можно получить информацию о пользователе, приложении и текущем SQL-курсоре ожидающих сеансов, вовлеченных в процесс взаимной блокировки. Иногда это может быть полезно при поиске причин образования ситуации взаимного блокирования.
Как определить по содержимому трассировочного файла, что возник первый сценарий взаимного блокирования? Для ответа на этот вопрос обратимся в первую очередь к графу взаимной блокировки. Для начала мы должны определить, с какого идентификатора начинаются имена ресурсов графа в столбце «Resource Name». В нашем случае это всегда будет идентификатор TX, то есть блокировка транзакции. Далее нам следует проверить значения режимов блокировок, отображаемые в столбцах holds и waits. Они должны иметь одинаковое значение, равное символу X. Не следует также забывать, что данный сценарий взаимного блокирования возникает на уровне строк, и, следовательно, в секции «Rows waited» всегда будут присутствовать данные об ожидающих строках. Отсюда следует непреложное правило о том, что в первой секции «Current SQL statement for this session» при данном сценарии вы никогда не встретите оператора INSERT, так как строки, вставленные в одном из сеансов, никогда не будут доступны для другого сеанса до фиксации транзакции.
Выводы
Пришло время обобщить информацию, которую мы получили, изучая взаимную блокировку, образующуюся по первому сценарию. Обычно такая взаимная блокировка возникает из-за неодинаковой последовательности обработки строк в пределах транзакций разных сеансов. Если бы мы, к примеру, в нашем случае проводили бы обновление строк таблицы в каждом из сеансов последовательно в соответствии со значением ключевого столбца «c1», то мы могли бы избежать взаимных блокировок. Образовалась бы простая очередь ожидания ресурса. Правда, бывают ситуации, когда одинаковую последовательность обработки строк в разных сеансах сделать трудно или даже невозможно. Как тогда выходить из ситуации?
Самое простое решение, которое приходит на ум, и о котором я уже упоминал выше, состоит в том, чтобы обрабатывать ошибку в приложении, и в случае её возникновения в одном из сеансов откатывать транзакцию, повторяя все необходимые действия позже. Велика вероятность, что транзакция в другом сеансе за это время завершиться и не будет мешать производить изменения в данных. Вообще, по возможности надо всегда уменьшать время длительности транзакций в приложениях. Если транзакция будет короткой, то это уменьшает время, в течение которого сеансы могут попасть в неприятную ситуацию с взаимным блокированием. Это справедливо и для длинной цепочки каскадного срабатывания триггеров, которым так грешат некоторые сложные системы. По возможности эту цепочку надо укорачивать.
Читайте также: