Выявление и устранение взаимоблокировки Oracle ITL

У меня есть пакет Oracle DB, который регулярно вызывает то, что я считаю тупиком ITL (список заинтересованных транзакций). Соответствующая часть файла трассировки приведена ниже.

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-0000cb52-00000000        22     131           S       23     143          SS
TM-0000ceec-00000000        23     143    SX             32     138    SX   SSX
TM-0000cb52-00000000        30     138    SX             22     131           S
session 131: DID 0001-0016-00000D1C session 143: DID 0001-0017-000055D5
session 143: DID 0001-0017-000055D5 session 138: DID 0001-001E-000067A0
session 138: DID 0001-001E-000067A0 session 131: DID 0001-0016-00000D1C
Rows waited on:
Session 143: no row
Session 138: no row
Session 131: no row

В этой таблице нет битовых индексов, так что причина не в этом. Насколько я могу судить, отсутствие «ожидаемых строк» ​​плюс «S» в столбце ожидания официанта, вероятно, указывает на то, что это тупиковая ситуация ITL. Кроме того, запись в таблицу выполняется довольно часто (примерно 8 вставок или обновлений одновременно, до 240 раз в минуту), поэтому взаимоблокировка ITL кажется вполне возможной.

Я увеличил параметр INITRANS таблицы и ее индексов до 100 и увеличил PCT_FREE для таблицы с 10 до 20 (затем перестроил индексы), но взаимоблокировки все еще происходят. Взаимная блокировка чаще всего происходит во время обновления, но это может быть просто совпадением, так как я отследил это всего пару раз.

У меня два вопроса:
1) Действительно ли это тупиковая ситуация с МРЖО?
2) Если это тупиковая ситуация с МРЖО, что еще можно сделать, чтобы ее избежать?

Оказывается, это вовсе не проблема взаимоблокировки ITL, а скорее проблема с неиндексированными внешними ключами. Я обнаружил это благодаря ответу dpbradley, который подсказал мне, что это не проблема ITL, и побудил меня выяснить, каковы другие причины взаимоблокировки с «отсутствием строк».


oracle oracle10g database-deadlocks
person Allan    schedule 24.05.2010    source источник
comment
Возможно, вы захотите опубликовать это на serverfault. Моя первоначальная реакция заключалась в том, что это то, к чему это относится, но в этом вопросе есть и программный оттенок. В любом случае вы можете поймать там другую пару глаз, которых здесь нет.   -  person DCookie    schedule 24.05.2010

Ответы (2)


arrow_upward
5
arrow_downward

Лучшее указание на давление ITL — это представление производительности:

select event, total_waits, time_waited, average_wait
 from v$system_event
 where event like 'enq: TX%'
 order by 2 desc;

показывает ожидание конфликта TX и

select OBJECT_NAME, SUBOBJECT_NAME, TABLESPACE_NAME, 
       OBJECT_TYPE, STATISTIC_NAME, VALUE
  from v$segment_statistics 
  where statistic_name = 'ITL waits'
  and value > 0
  order by value desc;

показывает задействованные таблицы и индексы.

(Как и во всех представлениях v$, результаты относятся к моменту времени запуска экземпляра.)

Если это показывает, что у вас действительно есть ожидания ITL, тогда параметры INITRANS и PCTFREE являются основными ручками для поворота (но INITRANS = 100 звучит для меня довольно высоко, и это требует места).

Если ожидания ITL не являются проблемой, необходимо проверить код приложения.

person dpbradley    schedule 25.05.2010
comment
Первый запрос показывает 23000+ enq: TX - ожидания конкуренции, но второй запрос показывает только 1 ожидание ITL (по индексу для таблицы, в которой я не видел взаимоблокировки). Если я правильно понимаю, это, кажется, предполагает, что на самом деле это не тупик ITL? - person Allan; 25.05.2010
comment
Да, и я вижу из вашего редактирования, что с тех пор вы обнаружили основную проблему. Неиндексированные FK, безусловно, могут быть проблемой блокировки - существует множество скриптов, которые будут сообщать о неиндексированных внешних ключах в схеме. - person dpbradley; 25.05.2010

arrow_upward
2
arrow_downward

Лучший вариант — увеличить его по мере необходимости (начать с 10 по умолчанию и увеличить на 10). Если вы видите сокращение ожидания ITL, все готово. Обычно эти связанные параметры корректируются методом проб и ошибок как в Oracle, так и в SQL Server. Настройка этих параметров в режиме реального времени не будет проблемой, если только ресурс не будет сильно загружен. Вы можете использовать следующий запрос, чтобы увидеть после каждого приращения, если ожидания ITL либо исчезают, либо сильно сокращаются:

SELECT t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.STATISTIC_NAME, t.VALUE
  FROM v$segment_statistics t
  WHERE t.STATISTIC_NAME = 'ITL waits' AND t.VALUE > 0
  ORDER BY t.value desc;

С помощью этого метода мы выполнили несколько настроек для сценариев взаимоблокировки Oracle из-за ожидания ITL. ПРИМЕЧАНИЕ. Убедитесь, что индекс перестроен, если initrans изменен для индексов. Также убедитесь, что статистика не устарела.

Для быстрой проверки можно использовать помощник по настройке SQL, чтобы увидеть полное состояние запроса/индекса и статистику.

person Applaya Technologies    schedule 28.05.2013