Обнаружена взаимоблокировка из-за процедуры автономной транзакции Pragma

У меня есть приведенная ниже процедура, в которой есть прагма-предложение автономной_транзакции. Теперь эта процедура вызывается в коде Java после проверки пришедшей бизнес-логики. После выполнения этого процесса он начинается с некоторых материалов Java...

create or replace procedure UPDATE_INSTRUMENT 
is
  pragma autonomous_transaction;

begin

  begin
    update abc
       set AUTHSTATUS   = p_AUTHSTATUS,
           STATUS       = p_STATUS,
           USERID       = p_USERID,
           LASTUPDATED  = TO_DATE(p_LASTUPDATED, 'DD/MM/YYYY'),
           USERDATETIME = TO_DATE(p_USERDATETIME, 'DD/MM/YYYY')
    where  TRANSACNO = p_TRANSACNO;
    commit;
  end;

  begin
    update xyz
       set AUTHSTATUS = p_AUTHSTATUS,
           USERID      = p_USERID,
           AUTHDATE    = TO_DATE(SYSDATE, 'DD/MM/YYYY'),
           LASTUPDATED = TO_DATE(SYSDATE, 'DD/MM/YYYY'),
    where  TRANSACNO = p_TRANSACNO;
    commit;
  end;

end UPDATE_INSTRUMENT;

Таблица «xyz» имеет три триггера, из которых 1 — «Вставка», а 2 — «Перед обновлением».

PN: - Таблица «xyz» нигде не обновляется и не блокируется перед вызовом этой процедуры.

Я получаю следующие ошибки.

ORA-00060: обнаружена взаимоблокировка при ожидании ресурса

ORA-06512: в "ADTTRG_xyz", строка 277.

ORA-04088: ошибка при выполнении триггера «ADTTRG_xyz»

Таблица abc обновляется правильно, но не удается обновить таблицу xyz.

Пожалуйста, объясните, почему возникает этот тупик.


oracle plsql database-deadlocks
person Vidhan Jadhav    schedule 18.12.2017    source источник
comment
ORA-06512: at "ADTTRG_xyz", line 277 Что находится вокруг этой строки в триггере? Вот в чем подсказка..   -  person pOrinG    schedule 18.12.2017
comment
Пожалуйста, добавьте к своему триггеру вопроса ADTTRG_xyz   -  person user7294900    schedule 18.12.2017
comment
прежде всего, пожалуйста, не используйте commit внутри триггера (или внутри процедуры) ради целостности транзакции.   -  person Barbaros Özhan    schedule 18.12.2017
comment
поэтому в строке 277 ничего нет ... IF NVL(:OLD.FSYSSETFEE, 0) ‹› NVL(:NEW.FSYSSETFEE, 0) THEN M_FIELDS := M_FIELDS || '|' || 'СИСТЕМНАЯ ПЛАТА'; M_OLDVALUES := M_OLDVALUES || '|' || :СТАРЫЙ.FSYSSETFEE; M_NEWVALUES := M_NEWVALUES || '|' || :NEW.FSYSSETFEE; КОНЕЦ ЕСЛИ; IF NVL(:OLD.SZNEGYN, ' ') ‹› NVL(:NEW.SZNEGYN, ' ') THEN M_FIELDS := M_FIELDS || '|' || 'НЕГИН'; M_OLDVALUES := M_OLDVALUES || '|' || :СТАР.ШНЕГИН; M_NEWVALUES := M_NEWVALUES || '|' || :NEW.SZNEGYN; КОНЕЦ ЕСЛИ;   -  person Vidhan Jadhav    schedule 18.12.2017
comment
При устранении взаимоблокировок всегда следует просматривать файл трассировки, сгенерированный Oracle. Этот файл трассировки точно скажет вам, какие операторы и объекты вызвали взаимоблокировку. Иногда это не то, что вы думаете. Возможно, есть неиндексированный внешний ключ или растровый индекс в транзакционной таблице и т. д.   -  person Jon Heller    schedule 18.12.2017
comment
Кстати, TO_DATE(SYSDATE, 'DD/MM/YYYY') это ошибка, потому что sysdate уже является датой, а to_date() принимает только строку. Если ваш nls_date_format имеет двузначный год, вы потеряете столетие и получите год 0017. Возможно, целью было trunc(sysdate).   -  person William Robertson    schedule 18.12.2017
comment
Кроме того, две внутренние пары begin/end ничего не делают и могут быть удалены. Я считаю, что лишний беспорядок может скрыть проблемы, затрудняя просмотр реальной структуры кода. И вам действительно нужен commit на полпути? Обычно имеет смысл позволить обеим операциям завершиться успешно или потерпеть неудачу вместе.   -  person William Robertson    schedule 18.12.2017

Ответы (1)


arrow_upward
2
arrow_downward

«Как происходит тупик».

Взаимоблокировки возникают, когда два сеанса одновременно пытаются изменить общий ресурс, такой как таблица или уникальный индекс, таким образом, что ни один сеанс не может зафиксироваться без того, чтобы другой сеанс не зафиксировался раньше. Это всегда недостаток дизайна приложения, поскольку взаимоблокировки являются результатом сложного потока и плохо реализованных логических стратегий.

Есть несколько признаков того, что здесь дело обстоит именно так.

ORA-06512: в "ADTTRG_xyz", строка 277.

Во-первых, триггер с несколькими сотнями строк кода — это запах кода. Это очень много кода для триггера. Похоже, здесь есть возможность для конкуренции. Особенно как...

Таблица 'xyz' имеет три триггера и... 2 активны перед событием обновления.

У вас есть два триггера BEFORE UPDATE в таблице «xyz», и событие, вызывающее взаимоблокировку, — это обновление «xyz». Это не может быть совпадением.

Вы должны исследовать эти два триггера и установить, какие таблицы и индексы им нужны, чтобы вы могли определить, конфликтуют ли они.

pragma autonomous_transaction;

В документации по PL/SQL говорится "Если автономная транзакция пытается получить доступ к ресурсу, удерживаемому основной транзакцией, может возникнуть взаимоблокировка." Автономные транзакции - это еще один запах кода. Существует очень мало допустимых вариантов использования автономных транзакций; чаще они используются для того, чтобы представить неверную модель данных.

Так что у вас есть много вещей для расследования. Oracle предлагает диагностику, чтобы помочь в этом.

Когда происходят взаимоблокировки, Oracle создает файл трассировки. Этот файл будет записан в каталог ОС. Если вы не знаете, где это находится, вы можете запросить представление V$DIAG_INFO. Подробнее. Файл трассировки сообщит вам два идентификатора строки, вызвавшие взаимоблокировку; вы можете узнать идентификатор объекта с помощью dbms_rowid.rowid_object() и подключить его в select object_name from all_objects where object_id = :oid;. В зависимости от того, как устроена ваша организация, у вас может не быть доступа к каталогу файла трассировки, и в этом случае вам придется обратиться за помощью к администратору баз данных.

Когда вы знаете, какая таблица находится в тупике, вы знаете, что вы должны изменить в логике вашего приложения. Потенциально это довольно большое изменение, так как ваш код имеет ряд красных флажков (длинные тела триггеров, два триггера на одно и то же событие, автономная транзакция). Удачи!

person APC    schedule 18.12.2017