Мониторинг блокировок

dbstalker, 03 июня

Как можно отслеживать блокировки, получать необходимую информацию о блокировках. Об этом в нашей статье.

Способы прекращения блокировок; представления, дающие информацию о блокировках (DBA_WAITERS,DBA_LOCKS,DBA_DML_LOCKS, DBA_BLOCKERS,V$SESSION_WAIT,DBA_DDL_LOCKS,V$SESSION, V$LOCK) – темы этой статьи.

В ORACLE для снятия блокировок существует следующие методы:

  • Заставить пользователя выполнить commit или rollback
  • Уничтожить сеанс владеющий блокировкой

Есть несколько представлений которые могут помочь разобраться с блокировками. Рассмотрим некоторые из них.

V$LOCK: Информация о блокировках и ресурсах. Не включает блокировки DDL

ADDR  RAW(4)   Адрес объекта состояния блокировки
KADDR RAW(4)   Адрес блокировки
SID   NUMBER   Идентификатор процесса, удерживающего блокировку
                           (см представление v$session)
TYPE  VARCHAR2 Тип блокировки
ID1   NUMBER   Идентификатор блокировки #1 ( номер сегмента отката,слот)
ID2   NUMBER   Идентификатор блокировки #2 (номер изменения,т.е. число 
                              повторных использований сегмента отката)
LMODE NUMBER   Режим блокировки:1 (null),
                               2 (row share), 3 (row exclusive),
                               4 (share), 5 (share row exclusive),
                               6 (exclusive)
REQUEST NUMBER Запрошенный режим блокировки (те же значения, что ивыше)

Если в столбце lmode стоит значение, отличное от 0 или 1, то это означает, что сеанс использует блокировку. Если в столбце request стоит значение, отличное от 0 или 1, то это означает, что сеанс запрашивает блокировку (ждет освобождения блокировки другим процессом). Если в столбце lmode стоит значение 0, то это значит, что блокировка не установлена.

Select count(*) from v$lock where lmode=0

- количество сеансов ждущих применения блокировок

Столбец id1 для блокировок типа TM содержит идентификатор объекта ( см all_objects) или sys.obj$); для блокировки типа TX – номер сегмента отката trunc(id1/power(2,16)) , слот (bitand (id1,power(2,16)-1)+0). Столбец id2 - для TM всегда равен 0; для TX – содержит число возвратов (sequence number), т.е. число повторных использований сегмента отката . Если выполнить запрос

select XIDUSN, XIDSLOT, XIDSQN  from v$transaction

то мы получим для нашей транзакции такое же значение номера сегмента отката, слот, номер изменений. Эти три значения однозначно определяют транзакцию.

Общие замечания:

  • представление v$lock содержит не сами блокировки (список заблокированных строк), а очередь на них. Поэтому, чтобы узнать или заблокирована строка, её нужно прочитать.
  • Если в столбце request есть значение (ожидается блокировка), то в столбцах id1 и id2 находится XIDUSN, XIDSLOT, XIDSQN сеанса удерживающего блокировку.
  • V$SESSION: Информация о сессии для каждой текущей сессии

    SADDR          RAW(4)   Адрес сессии
    SID            NUMBER   Идентификатор сессии
    SERIAL#        NUMBER   Регистрационный номер сессии.Служит для 
                            уникальной идентификации  объектов сессии. 
                            Гарантирует, что команды уровня сессии
                            применяются к корректным объектам, в 
                            случае,если  сессия закончится, и будет 
                            запущена другая сессия с таким же SID
    AUDSID         NUMBER   Аудиторский идентификатор сессии
    PADDR          RAW(4)   Адрес процесса, которому принадлежит эта сессия
    USER#          NUMBER   Идентификатор пользователя ORACLE
    USERNAME       VARCHAR2 Имя пользователя ORACLE
    COMMAND        NUMBER   Выполняющаяся команда; 
    TADDR          VARCHAR2 Адрес объекта состояния транзакции
    LOCKWAIT       VARCHAR2 Адрес блокировки, которую ожидает сессия;  
                            пусто, если нет
    STATUS         VARCHAR2 Состояние сессии: ACTIVE, INACTIVE, KILLED
    SERVER         VARCHAR2 Тип сервера:DEDICATED, SHARED, PSEUDO, NONE
    SCHEMA#        NUMBER   Идентификатор схемы пользователя
    SCHEMANAME     VARCHAR2 Имя схемы пользователя
    OSUSER         VARCHAR2 Имя пользователя клиента операционной системы
    PROCESS        VARCHAR2 Идентификатор процесса клиента операционной    
                            системы
    MACHINE        VARCHAR2 Имя машины операционной системы
    TERMINAL       VARCHAR2 Имя терминала операционной системы
    PROGRAM        VARCHAR2 Имя программы операционной системы
    TYPE           VARCHAR2 Тип сессии
    SQL_ADDRESS    RAW      Используется с SQL_HASH_VALUE, чтобы уникально 
                            идентифицировать предложение SQL, выполняемое в 
                            сессии
    SQL_HASH_VALUE NUMBER   Используется с SQL_ADDRESS, чтобы уникально
                            идентифицировать предложение SQL, выполняемое в 
                            сессии
    

    Используя это представление можно найти задачи, находящиеся в ожидании и увидеть какие именно запросы вызвали конфликт интересов:

    Select b.username username, c.sid sid, c.owner object_owner, c.object object,b.lockwait,a.sql_text sql from v$sqltext a,v$session b,v$access c where a.address=b.sql_address and a.hash_value=b.sql_hash_value and b.sid=c.sid and c.owner !='SYS' and b.lockwait is not null
    

    DBA_DDL_LOCKS: Все блокировки DDL, удерживаемые в базе данных, и все запросы, ожидающие блокировок DDL

    SESSION_ID     Идентификатор сессии
    OWNER          Владелец блокировки
    NAME           Имя блокировки
    TYPE           Тип блокировки: Cursor, Table/Procedure,Body, Trigger, 
                   Index, Cluster
    MODE_HELD      Режим блокировки: None, Null, Share,Exclusive
    MODE_REQUESTED Режим запроса блокировки: None, Null,Share, Exclusive
    
    SELECT SESSION_ID, OWNER, NAME, TYPE, MODE_HELD, MODE_REQUESTED
    FROM SYS.DBA_DDL_LOCKS
    

    DBA_DML_LOCKS: Все блокировки DML, удерживаемые в базе данных, и все запросы, ожидающие блокировок DML

    SESSION_ID     Сессия, удерживающая или запрашивающая блокировку
    OWNER          Владелец блокировки
    NAME           Имя блокировки
    MODE_HELD      Режим блокировки (см. ниже)
    MODE_REQUESTED Режим запроса блокировки. Режим блокировки означает
                   ROW-S(SS)         Разделяемая для строк
                   ROW-X(SX)         Монопольная для строк
                   SHARE             Разделяемая
                   S/ROW-X(SSX)      Разделяемая для строк монопольн.
                   EXCLUSIVE         Монопольная
                   NONE MODE_HELD:   Блокировка запрошена, но еще не
                                     получена
                        MODE_REQUESTED: Получен идентиф. блокировки,
                                     блокировка не удерживалась или не 
                                     запрошена
    

    SELECT SESSION_ID, OWNER, NAME,MODE_HELD, MODE_REQUESTED FROM SYS.DBA_DML_LOCKS
    
    

    DBA_LOCKS: Все блокировки или замки, удерживаемые в базе данных, и все запросы,ожидающие блокировок или замков. Этот запрос включает блокировки DML и блокировки DDL.

    SESSION_ID     Сессия, удерживающая или запросившая блокировку
    TYPE           Тип блокировки 
    MODE_HELD      Режим блокировки.
    MODE_REQUESTED Режим запроса блокировки
    LOCK_ID1       Зависящий от типа идентификатор блокировки, часть 1
    LOCK_ID2       Зависящий от типа идентификатор блокировки, часть 2
    

    SELECT SESSION_ID, LOCK_TYPE, MODE_HELD, MODE_REQUESTED, LOCK_ID1, LOCK_ID2 FROM SYS.DBA_LOCKS
    

    DBA_WAITERS: Все сессии, ожидающие блокировок, и все сессии,удерживающие блокировки

    WAITING_SESSION Сессия, ожидающая блокировку
    HOLDING_SESSION Сессия, удерживающая блокировку
    TYPE            Тип блокировки
    MODE_HELD       Режим блокировки: Row-S(SS), Row-X(SX),Share, S/Row-X
                    (SSX),Exclusive
    MODE_REQUESTED  Режим запроса блокировки: Null,Row-S(SS), Row-X(SX), 
                    Share,S/Row-X(SSX), Exclusive
    LOCK_ID1        Зависящий от типа идентификатор блокировки, часть 1
    LOCK_ID2        Зависящий от типа идентификатор блокировки, часть 2
    

    select waiting_session,holding_session,lock_type, mode_held, mode_requested,lock_id1,lock_id2 from dba_waiters
    

    DBA_BLOCKERS: Все сессии, для которых есть кто-нибудь, ожидающий удерживаемых ими блокировок, но сами не ожидающие

    holding_SESSION   Сессия, удерживающая блокировку
    

    SELECT HOLDING_SESSION FROM SYS.DBA_BLOCKERS
    

    V$SESSION_WAIT: Ресурсы или события, которых ждут активные сессии

    SID       NUMBER   Идентификатор сессии
    SEQ#      NUMBER   Порядковый номер, который уникально идентифицирует 
                       это ожидание. Наращивается для каждого ожидания
    EVENT     VARCHAR2 Ресурс или событие, которого ожидает  сессия
    P1TEXT    VARCHAR2 Описание первого дополнительного параметра
    P1        VARCHAR2 Первый дополнительный параметр
    P2TEXT    VARCHAR2 Описание второго дополнительного параметра
    P2        VARCHAR2 Второй дополнительный параметр
    P3TEXT    VARCHAR2 Описание третьего дополнительного параметра
    P3        VARCHAR2 Третий дополнительный параметр
    WAIT_TIME NUMBER   Ненулевое значение отражает последнее время ожидания 
                       сессии.Нулевое значение означает, что сессия в
                       данный момент ожидает
    SECONDS_IN_WAIT    ожидание в миллисекундах
    STATE              статус
    

    Столбцы P1 и P2 соответствуют id1 и id2 представления v$lock Столбец state принимает следующие значения:

    • Waiting – система в данный момент ожидает события и столбец SECONDS_IN_WAIT показывает в миллисекундах ожидание этого события
    • Waited unknow time Это значение устанавливается если параметр timed_statistics установлен равным false.
    • Waited short time указывает , что сеанс ожидает в течении непродолжительного времени
    • Waited known time Означает, что в сеансе обнаружено определённое событие и в столбце WAIT_TIME Указывается время, в течении которого сеанс ожидал этого события.

 

Новый комментарий

Я не спамер: введите суму 2+2



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

средства для рук
17 августа, 3 ответа
(Без темы)Шкаф купе
17 августа, 2 ответа
Страховая компания
17 августа, 1 ответа