Oracle как посмотреть блокировки

Обновлено: 15.07.2024

Многолетний опыт эксплуатации баз данных в 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).
Использование битовых индексов в таблицах, подвергающихся одновременному изменению со стороны нескольких сессий.
Блокировки, возникающие в дочерней таблице, обусловленные изменениями в родительской таблице.

Статью целиком читайте в журнале «Системный администратор», №4 за 2015 г. на страницах 30-34.

Иногда что-то удерживает длинную блокировку (скажем, более 30 секунд) в строке таблицы счетов, что приводит к сбою одной из постоянных фоновых задач. Фоновая задача, о которой идет речь, перезапускается сама по себе после истечения времени обновления. Мы узнаем об этом через несколько минут после того, как это произойдет, но к тому времени замок будет снят.

У нас есть основания полагать, что это может быть неправильный пользовательский интерфейс, но мы не смогли найти «дымящийся пистолет».

Я нашел несколько запросов, которые перечисляют блоки, но это для случая, когда у вас есть две работы, борющиеся за строку. Я хочу знать, какие строки имеют блокировки, когда не обязательно вторая работа, пытающаяся получить блокировку.

Мы находимся на 11g, но проблема с 8i.

Oracle сильно отличается от концепции других систем.

Когда строка в Oracle блокируется, сама запись обновляется новым значением (если есть) и, кроме того, блокировка (которая по сути является указателем на блокировку транзакции, находящуюся в сегменте отката) помещается прямо в запись.

Это означает, что блокировка записи в Oracle означает обновление метаданных записи и выдачу записи логической страницы. Например, вы не можете выполнить SELECT FOR UPDATE в табличном пространстве только для чтения.

Более того, сами записи не обновляются после фиксации: вместо этого обновляется сегмент отката.

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

Oracle не имеет традиционного менеджера блокировок, а это означает, что получение списка всех блокировок требует сканирования всех записей во всех объектах. Это займет слишком много времени.

Вы можете получить некоторые специальные блокировки, такие как заблокированные объекты метаданных (используя v$locked_object ), ожидания блокировки (используя v$session ) и т. Д., Но не список всех блокировок для всех объектов в базе данных.

Посмотрите на dba_blockers, dba_waiters и dba_locks для блокировки. Имена должны быть самоочевидными.

Вы можете создать задание, которое запускается, скажем, раз в минуту и регистрирует значения в dba_blockers и текущий активный sql_id для этого сеанса. (через v $ session и v $ sqlstats).

Вы также можете посмотреть в v $ sql_monitor. Это будет журнал по умолчанию для всего SQL, который занимает более 5 секунд. Он также отображается на странице мониторинга SQL в Enterprise Manager.

Вместо блокировок я предлагаю вам взглянуть на долгосрочные транзакции , используя v$transaction . Оттуда вы можете присоединиться к v$session , что должно дать вам представление об интерфейсе пользователя (попробуйте столбцы программы и машины), а также пользователь.

вы можете найти заблокированные таблицы в устной форме, запросив следующий запрос

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

(Однако вам, вероятно, не нужно запускать этот код. Если у вас возникла проблема с блокировкой, обычно легче найти виновного с помощью GV$SESSION.BLOCKING_SESSION и другие связанные словари данных. Пожалуйста, попробуйте другой подход, прежде чем запускать этот ужасно медленный код.)

Сначала давайте создадим пример таблицы и некоторые данные. Запустите это в сессии № 1.

В сеансе № 2 создайте таблицу для хранения заблокированных идентификаторов ROWID.

В сеансе № 2 запустите этот блок PL /SQL, чтобы прочитать всю таблицу, проверить каждую строку и сохранить заблокированные ROWID. Имейте в виду, это может быть смехотворно медленным. В вашей реальной версии этого запроса измените обе ссылки на TEST_LOCKING на свою собственную таблицу.

Наконец, мы можем просмотреть заблокированные строки, присоединившись к таблице LOCKED_ROWIDS.

Запрос таблицы блокировки Oracle и метод разблокировки

Классификация операторов операций с базой данных

DDL: язык определения схемы базы данных, ключевое слово: create
DML: язык обработки данных, ключевые слова: вставка, удаление, обновление
DCL: язык управления базой данных, ключевые слова: предоставить, удалить
DQL: язык запросов к базе данных, ключевое слово: выберите

При каких обстоятельствах таблица оракула будет заблокирована

Блокировки DML можно разделить на блокировки строк, блокировки таблиц и взаимоблокировки.

Блокировка строки: когда транзакция выполняет операции вставки, обновления или удаления базы данных, транзакция автоматически получает монопольную блокировку строки операции в таблице операций.

Блокировка на уровне таблицы: когда транзакция получает блокировку строки, транзакция также автоматически получает блокировку таблицы (разделяемую блокировку), чтобы другие транзакции не влияли на обновление строки записи операторами DDL. Транзакция также может получить общую блокировку или эксклюзивную блокировку в процессе.Только когда транзакция показывает, что эксклюзивная блокировка определена с помощью оператора LOCK TABLE, транзакция может получить эксклюзивную блокировку для таблицы или ее можно отобразить с помощью LOCK TABLE Определите общую блокировку на уровне таблицы (см. Соответствующие документы для конкретного использования LOCK TABLE).

Блокировки DDL можно разделить на: эксклюзивные блокировки DDL, общие блокировки DDL, блокировки анализа.

Эксклюзивная блокировка DDL: операторы DDL, которые создают, изменяют и удаляют объект базы данных, получают монопольную блокировку рабочего объекта. Например, при использовании оператора alter table для поддержания полноты, согласованности и законности данных транзакция получает исключительную блокировку DDL.

Общие блокировки DDL: операторы DDL, которые должны устанавливать взаимные зависимости между объектами базы данных, обычно должны использоваться совместно для получения блокировок DDL.
Если пакет создан, процедуры и функции в пакете ссылаются на разные таблицы базы данных.Когда пакет компилируется, транзакция получает общую блокировку DDL указанной таблицы.

Блокировка анализа: ORACLE использует общий пул для хранения проанализированных и оптимизированных операторов SQL и программ PL / SQL, чтобы приложения, выполняющие один и тот же оператор, работали быстрее. Объект, кэшированный в общем пуле, получает блокировку анализа на объект базы данных, на который он ссылается. Блокировка анализа - это уникальный тип блокировки DDL, Oracle использует ее для отслеживания зависимостей между объектами общего пула и объектами базы данных, на которые он ссылается. Когда транзакция изменяет или удаляет объект базы данных, удерживающий блокировку анализа в разделяемом пуле, ORACLE делает недействительным объект в разделяемом пуле. В следующий раз, когда на этот оператор SQL / PLSQL будет сделана ссылка, ORACLE повторно проанализирует и компилирует этот оператор.

Запрос и разблокировка таблицы блокировки Oracle

Объясняется это решением следующих задач:
При выполнении пакетных операций DML программа была прервана и больше не выполнялась, а определенная таблица была заблокирована для запроса, поэтому дальнейшее выполнение не выполнялось.

Первый шаг: запросить информацию о заблокированной таблице через пользователя с полномочиями администратора

Если вы подозреваете, что таблица заблокирована или транзакция не была закрыта нормально, мы можем использовать следующий оператор для запроса, чтобы получить соответствующую информацию в базе данных Oracle:

например: теперь я выполняю оператор DML через пользователя scott (например: select * from emp для обновления; или update scott.emp set sal = '2000', где empno = '7788';) после того, как не отправляю его, а затем выполняю его через системного пользователя Оператор SQL заблокированной таблицы в приведенном выше запросе Oracle найдет следующие записи:

Описание:
имя пользователя: имя пользователя oracle
sid: идентификатор процесса
серийный номер: серийный номер
имя_объекта: имя таблицы
osuser: имя пользователя операционной системы
machine: имя компьютера
программа: рабочий инструмент
logon_time: время входа в систему
lockwait: указывает, ожидает ли эта таблица в настоящее время, пока другие пользователи разблокируют эту таблицу.
locked_mode: режим блокировки счетчика (подробно описано ниже)

Шаг 2: Разблокируйте заблокированную таблицу (SID, SERIAL) в базе данных пользователем с правами администратора

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

например: разблокировать стол на первом шаге

Теперь выполните оператор DML (например: select * from scott.emp для обновления; или update scott.emp set sal = ‘2000’, где empno = ‘7788’;) снова через систему.

Режим блокировки

Также есть более практичный SQL

-Проверьте инструкцию SQL, выполняемую сеансом, чтобы вы могли быстро определить, какие операции или коды вызывают продолжение транзакции без завершения .:

иногда что-то удерживает длинную блокировку (скажем, более 30 секунд) в строке таблицы учетных записей, что приводит к сбою одной из постоянных фоновых задач. Фоновая задача в вопрос перезапускается после истечения времени обновления. Мы узнаем об этом через несколько минут после того, как это произойдет, но тогда замок был освобожден.

У нас есть основания полагать, что это может быть неправильный пользовательский интерфейс, но не удалось найти "дымящийся пистолет".

Я нашел некоторые запросы, которые перечисляют блоки, но это для того, когда у вас есть две работы, конкурирующие за строку. Я хочу знать, какие строки имеют блокировки, когда не обязательно вторая работа пытается получить замок.

мы находимся на 11g, но испытываем проблему с 8i.

Oracle концепция блокировки сильно отличается от концепции других систем.

когда строку Oracle блокируется, сама запись обновляется новым значением (если таковое имеется) и, кроме того, блокировка (которая по существу является указателем на блокировку транзакции, которая находится в сегменте отката) помещается прямо в запись.

это означает, что блокировка записи в Oracle означает обновление метаданных записи и выдачу логической записи страницы. Например, вы не могу сделать SELECT FOR UPDATE на табличном пространстве только для чтения.

более того, сами записи не обновляются после фиксации: вместо этого обновляется сегмент отката.

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

Oracle не имеет традиционного менеджера блокировок, и это означает, что получение списка всех блокировок требует сканирования всех записей во всех объектах. Это займет слишком много времени.

вы можете получить некоторые специальные блокировки, такие как заблокированные объекты метаданных (используя v$locked_object ), lock waits (используя v$session ) и т. д., Но не Список всех блокировок на все объекты в базе данных.

посмотрите на dba_blockers, dba_waiters и dba_locks для блокировки. Имена должны быть понятными.

вы можете создать задание, которое выполняется, скажем, раз в минуту и регистрирует значения в dba_blockers и текущий активный sql_id для этого сеанса. (через V$session и v$sqlstats).

вы также можете посмотреть в v$sql_monitor. Это будет журнал по умолчанию для всех SQL, который занимает более 5 секунд. Он также отображается на странице мониторинга SQL в Enterprise Менеджер.

вместо замков, я предлагаю вам посмотреть на долгосрочные транзакции, используя v$transaction . Оттуда вы можете присоединиться к v$session , что должно дать вам представление о пользовательском интерфейсе (попробуйте столбцы программы и машины), а также пользователя.

вы можете найти заблокированные таблицы в oralce, запросив следующий запрос

Читайте также: