У меня есть процедура PL/SQL, которая отправляет задания в цикле, обрабатывая различные параметры (ветви). Каждое задание выполняет вставку и обновление большой таблицы. Из-за размера таблицы требуется ее блокировка, и, следовательно, я часто сталкиваюсь с ошибками взаимоблокировки, приводящими к сбою как минимум одного задания каждый день.
Учитывая эту ситуацию, мне нужен совет, как управлять этими заданиями, чтобы обеспечить их выполнение без взаимоблокировок. Конкретно:
Как я могу заставить каждое задание ждать завершения предыдущего задания, прежде чем начинать?
Есть ли способ выполнять эти задания параллельно, не сталкиваясь с проблемами взаимоблокировки, особенно если размер таблицы продолжает увеличиваться?
Будем очень признательны за любые рекомендации по улучшению стратегии выполнения заданий во избежание тупиков.
🤔 А знаете ли вы, что...
Oracle Database использует язык SQL (Structured Query Language) для работы с данными.
Надеюсь, под «блокировкой стола» вы не имеете в виду буквально «lock table...». Если вы это делаете, удалите это, и вы можете решить свою проблему прямо здесь. Если предположить, что это не так...
Oracle использует блокировку на уровне строк (если у вас нет Exadata с включенным сжатием HCC). Это означает, что блокировки DML таблицы снимаются с отдельных строк (в каждой строке блоков данных есть бит блокировки). Таким образом, если вы получаете ожидание блокировки, у вас есть более одного задания, пытающегося заблокировать одну и ту же строку. Первое правило параллелизма — разделить рабочую нагрузку таким образом, чтобы двум параллельным заданиям никогда не приходилось работать над одной и той же строкой.
Взаимная блокировка — это ситуация, когда сеанс A и сеанс B блокируют разные строки, а затем в одной транзакции пытаются получить блокировку строк, которые другой уже заблокировал. Это создает циклическую зависимость, которая никогда не разрешится, поэтому Oracle выбирает один неудачный сеанс и уничтожает его. Чтобы уменьшить вероятность этого, программистам следует следовать следующим принципам проектирования:
Делайте свои транзакции как можно более короткими. Если можете, фиксируйте после каждой операции DML. Более короткие транзакции означают гораздо меньше возможностей для возникновения цепочки циклических блокировок.
Работайте с отдельными строками одновременно, а не с наборами строк. В цикле обновите строку, зафиксируйте, обновите следующую строку, зафиксируйте и т. д., а не обновляйте 1000 строк в одном операторе. Взаимная блокировка может возникнуть в одном DML, если он блокирует несколько строк — циклическая цепочка блокировок может развиваться, поскольку она находится в процессе блокировки последующих строк, удерживая при этом блокировки, которые она уже обработала на более ранних. Итак, еще раз, мы хотим удерживать эту блокировку как можно короче и как можно более атомарно. Для этого потребуется одна транзакция на каждую затронутую строку.
Если у вас есть последовательность операций в каждом задании, убедитесь, что последовательность каждый раз идет в одном и том же направлении. Если вы коснетесь таблицы A, затем B, затем C, убедитесь, что все ваши одновременные задания выполняются в одном и том же порядке. Обычно это так, но проверьте на всякий случай.
Тщательно подумайте о внешних ключах. Вставка дочерней строки заблокирует ее родительскую строку. Удаление родительской строки снимет блокировку дочерней таблицы. Если вы работаете как с родительской, так и с дочерней таблицами и у вас есть внешние ключи, это может привести к взаимоблокировкам. Если вы не можете решить эту проблему с помощью изменений в программном обеспечении и вас устраивает отсутствие защиты ссылочной целостности, в какой-то момент вам может даже потребоваться рассмотреть возможность отказа от ограничения FK.
Взаимные блокировки также могут возникать из-за того, что в индексном блоке или блоке таблицы недостаточно места (менее 23 байтов), чтобы Oracle мог добавить еще один слот ITL для обработки дополнительной параллельной транзакции. Убедитесь, что у вас достаточно слотов ITL, либо используя исправный PCTFREE (обычно достаточно значения по умолчанию, равного 10) как для таблиц, так и для индексов, и/или установив для INITRANS более высокое значение. Если я знаю, что у меня будет 10 потоков, работающих с одной и той же таблицей одновременно, я предпочитаю установить INITRANS 10 для таблицы и для каждого индекса (вы должны переместить/перестроить, чтобы это вступило в силу), просто чтобы быть уверенным, что никогда не произойдет нехватка ИТЛ.
Есть и другие способы возникновения тупиковой ситуации, и их больше, чем я могу здесь рассмотреть. Когда происходит взаимоблокировка, Oracle создает файл трассировки, показывающий цепочку блокировок. Попросите своего администратора базы данных найти один из этих файлов трассировки и предоставить его вам. Это поможет вам понять, как вы зашли в тупик, и сможете лучше нацелить свое решение.
Наконец,