Мониторинг блокировок в Oracle. Методы предупреждения и автоматического устранения

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

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

Наиболее характерными типами являются блокировки DML-операций, блокировки DDL-операций, а также защелки и внутренние блокировки.

• Блокировки данных DML. Они защищают данные таблиц. DML-операции (insert, update, delete и select for update) устанавливают монопольную блокировку для строк, которые обрабатываются DML-операцией, т.е. добавляются, удаляются или изменяются внутри транзакции. Всякий раз, когда DML-операция собирается модифицировать строки в таблице (например, по update или delete), Oracle автоматически устанавливает монопольную блокировку уровня строки (тип TX) на каждую модифицированную строку таблицы. Блокировки DML удерживаются Oracle до тех пор, пока транзакция не завершится, явно (операциями commit или rollback) или неявно. Неявно транзакция завершается после отсоединения пользователя от Oracle или в случае аварийного завершения процесса. Следует заметить, что наличие монопольной блокировки на строки, установленные модифицирующей транзакцией, не запрещает другим транзакциям изменять и удалять не затронутые модифицирующей транзакцией строки той же таблицы.
• Блокировки словаря DDL. Блокировки словаря защищают структуру объектов, например, определения таблиц и представлений. DDL-операции (create, drop, alter, truncate и т.д.) предохраняют объекты Oracle от изменения их структуры или от уничтожения. Так, DDL-блокировки устанавливаются для защиты структуры объекта от модификации другими DDL-операциями.
• Защелки (latch) – это блокировки, предназначенные для установки их на короткие интервалы времени, служат для защиты определенных структур памяти (например, буферного кэша блока БД или библиотечного кэша). Следует заметить, что внутренние блокировки и защелки не могут управляться пользователем.
• Внутренние блокировки – это блокировки разных типов, например, блокировки кэша словаря данных, блокировки управления файлами, блокировки табличного пространства и сегментов отката.

Блокировки можно разделить на две группы: создаваемые автоматически и явные (ручные) блокировки. К ручным блокировкам относятся созданные командами lock table, select for update, или с использованием пакета dbms_lock.

Причины возникновения блокировок и методы их предупреждения

Опыт работы с БД позволил сформулировать следующие наиболее часто встречающиеся причины блокировок:

• Отсутствие завершения транзакций по окончании DML-операции через commit (rollback), в силу чего другие транзакции оказываются заблокированными (им приходится ждать завершения транзакции).
• Отложенные во времени завершения транзакции, состоящей из DML-операций, когда транзакция завершается по commit (rollback) только после выполнения нескольких DML-операций или после автоматического завершения транзакции.
• Длительное время выполнения DML-операций в транзакции, в том числе в силу длительного выполнения запроса, участвующего в DML-операции, или длительного выполнения цикла, содержащего DML-операции.
• Отсутствие commit при модификации строк (например, по update или delete) в одной и той же таблице в цепочке процедур внутри сессии. Что приводит к блокировкам внутри сессии.
• Принудительные ручные блокировки (например, оператором lock table).
• Одновременно ввод в таблицу разными сессиями строк, содержащих одни и те же значения в первичном ключе или уникальном индексе (блокировки по insert).
• Использование битовых индексов в таблицах, подвергающихся одновременному изменению со стороны нескольких сессий.
• Блокировки, возникающие в дочерней таблице, обусловленные изменениями в родительской таблице.