oracle: мониторинг блокировок

oracle: блокировки (locks), защёлки (latches), enqueues
Мониторинг блокировок (http://my-oracle.it-blogs.com.ua/)

Запросы для мониторинга блокировок в базе.

-- смотрим блокировки, установленные текущими транзакциями в базе,
-- которые блокируют запросы на блокировку от других сессий (where block=1)

SELECT l.SID,s.username,s.program,l.TYPE,l.LMODE,ROUND(CTIME/60) "Time(Min)",
(SELECT object_name FROM dba_objects WHERE object_id=lo.object_id) obj,
DECODE(lo.locked_mode,
   0, 'None',           /* Mon Lock equivalent */
   1, 'Null',           /* N */
   2, 'Row-S (SS)',     /* L */
   3, 'Row-X (SX)',     /* R */
   4, 'Share',          /* S */
   5, 'S/Row-X (SSX)',  /* C */
   6, 'Exclusive',      /* X */
   TO_CHAR(lo.locked_mode)) mode_held
FROM V$LOCK l, v$locked_object lo, v$session s
WHERE block=1
AND l.sid=lo.session_id AND l.sid=s.sid;

-- смотрим активные сессии, которые чего то ждут, и чего они собственно ждут

SELECT s.program,s.username,s.osuser,s.machine,sw.* FROM v$session_wait sw,v$session s
WHERE sw.sid=s.sid
AND sw.event<>'rdbms ipc message' AND sw.event NOT LIKE 'SQL*Net%'
ORDER BY sw.event ASC;

-- смотрим блокировки, установленные текущими транзакциями в базе,
-- кто и на что и какой тип блокировки установил

SELECT  oracle_username, session_id,s.program,object_name,DECODE(a.locked_mode,
   0, 'None',           /* Mon Lock equivalent */
   1, 'Null',           /* N */
   2, 'Row-S (SS)',     /* L */
   3, 'Row-X (SX)',     /* R */
   4, 'Share',          /* S */
   5, 'S/Row-X (SSX)',  /* C */
   6, 'Exclusive',      /* X */
   TO_CHAR(a.locked_mode)) mode_held
FROM V$LOCKED_OBJECT a,DBA_OBJECTS b,v$session s WHERE a.object_id = b.object_id
AND a.session_id=s.sid;

oracle: Ожидания, статистики, коэффициенты

Замечательная статья на блоге Oracle mechanics : Ожидания, статистики, коэффициенты
Таблица ожиданий и их возможных причин
Oracle: IO Waits

oracle: блокировки (locks), защёлки (latches), enqueues

Блокировки 
Типы блокировок
Где хранится информация о блокировках? ITL, INITRANS, MAXTRANS 
Выполнение блокировок с помощью защелок 

Принципы блокирования в СУБД Oracle. 

---

Разница между блокировками и защёлками.

Защелка (latch) – устанавливается, если кто-то работает с определенным участком памяти, и, как следствие, никто другой теперь в этот участок не попадет.

Блокировка – никого не допускает к строкам таблицы, если Вы меняете ее.

Блокировка реализуется с помощью защёлки 
Мы работаем с записью. Она уже находится в буфере в SGA. Запись эту нужно заблокировать, чтобы никакая другая сессия не заблокировала ее.

Наш серверный процесс вешает защелку на буфер с записью, читает буфер, находит нужную запись, ставит отметочку о том, что эта запись заблокирована нами. Таким образом, блокировка создана. Снимаем защелку. Работаем с записью.

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

---

Типы блокировок

0 - none
1 - null (NULL)
2 - row-S (SS) разделяемая блокировка строки (RS) при выполнении select for update;
3 - row-X (SX) монопольная блокировка строки (RX) при выполнении операторов insert, delete, update;
4 - share (S) разделяемая блокировка таблицы share mode, генерируется, например, оператором lock table <…> in share mode;
5 - S/Row-X (SSX) (разделяемая блокировка таблицы и монопольная блокировка строк share row exclusive; генерируется, например, оператором lock table <…..> in share row exclusive mode;
6 - exclusive (X) монопольная блокировка ( X) при выполнении lock table.

Режимы  4 - share (S) и 5 - S/Row-X (SSX) встречаются крайне редко.

---

Блокировки DML


TX - блокировка транзакций
устанавливается при выполнении следующих операторов insert, delete, update, select for update. Блокировки транзакций работают только в режиме (lmode) - 6 exclusive (X) монопольная блокировка. Блокировки транзакций всегда осуществляются на уровне строк: блокируется строка и предотвращается изменение строки другими транзакциями до тех пор, пока не буде выполнен откат текущей транзакции или транзакция не будет зафиксирована. Чтобы была установлена блокировка TX, сначала устанавливается блокировка TM для таблицы в режиме 3 (RX). Затем устанавливается блокировка TX в режиме 6 (X). Блокировка TX не будет установлена, если другая транзакция установила блокировку TX на эту же строку.

TM - блокировки очерёдности DML
применяются, если в транзакции выдается один из операторов insert, delete, update, select for update, lock table. Таблицы блокируются ораклом, чтобы зарезервировать доступ к таблице и предотвратить конкуренцию между операторами DDL за таблицу. Проще говоря, блокировка таблиц (TM) позволяет быть уверенным, что структура таблицы не изменится при изменении её содержимого.

Блокировки TM могут быть в следующих режимах (lmode) : 3- row exclusive монопольная блокировка строки (RX) при выполнении операторов insert, delete, update; 2- row share разделяемая блокировка строки (RS) при выполнении select for update; 6 – exclusive монопольная блокировка ( X) при выполнении lock table. 

На рисунке показаны режимы блокировки таблиц и операции, которые эти блокировки разрешают и запрещают

Следующий рисунок показывает, что будет если один процесс держит блокировку, а второй пытается её получить на один и тот же ресурс:

Блокировки DDL 

oracle: locks

Блокировки (locks)


oracle: блокировки (locks), защёлки (latches), enqueues


Принципы блокирования в СУБД Oracle.
• Oracle блокирует данные на уровне строк и только при изменении. Эскалация блокировок до уровня блока или таблицы никогда не выполняется.
• Oracle никогда не блокирует данные с целью считывания. При обычном чтении блокировки на строки не устанавливаются.
• Сеанс, записывающий данные, не блокирует сеансы, читающие данные.
• Сеанс записи данных блокируется, только если другой сеанс записи уже заблокировал строку, которую предполагается изменять. Сеанс считывания данных никогда не блокирует сеанс записи.