Что блокирует мое резервирование(lock)?
Submitted by Natalka Roshak on Sat, 2006-04-01 18:00Если вы уже получали звонок от раздраженного пользователя, чья транзакция не выполняется или вам позвонил разработчик, которая не может понять почему ее пользовательская сессия блокирует друг друга, то вы знаете как полезно знать не только кто кого блокирует, но и какие при этом объекты блокируются. Еще лучше, если вы можете определить конкретно заблокированную строку, которую ждет сессия.
Создадим блокировку
Для начала, создадим ситуацию, когда один пользователь активно блокирует другого. Откроем две сессии. Введем следующие команды в Сессии 1 для создания тестовой таблице:SQL> create table tstlock (foo varchar2(1), bar varchar2(1));Table created.
SQL> insert into tstlock values (1,'a'); 1 row created.
SQL> insert into tstlock values (2, 'b');1 row created.
SQL> select * from tstlock ;FOO BAR - - 1 a 2 b 2 rows selected.
SQL> commit ;Commit complete.
Теперь установим блокировку на всю таблицу в Сессии 1:
SQL> select * from tstlock for update ;А в Сессии 2, попытаемся обновить запись:
SQL> update tstlock set bar= 'a' where bar='a' ;Это предложение будет висеть, из за блокировки, наложенной сессией 1 на нужную нам таблицу.
Определение блокирующей сессии
В Oracle есть представление DBA_BLOCKERS, которое выводит списки SID всех сессий с блокировками. Правда из своего опыта скажу вам, что быстрее использовать представление V$LOCK. Использование представления V$LOCK в запросе гораздо быстрее и упрощает определение блокирующей сессии. Это представление содержит много полезной информации.SQL> select * from v$lock ;ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
- - - - - - - - - -
AF9E2C4C AF9E2C60 479 TX 131078 16739 0 6 685 0
ADDF7EC8 ADDF7EE0 422 TM 88519 0 3 0 697 0
ADDF7F74 ADDF7F8C 479 TM 88519 0 3 0 685 0
ADEBEA20 ADEBEB3C 422 TX 131078 16739 6 0 697 1
.... .... ... ... .... .... .... .... .... ....
Обратите внимание на колонку BLOCK. Если сессия выдала запрет на использование объекта, нужного другой сессии, то BLOCK=1. Кроме того, вы можете определить какая сессия блокируется, сравнивая значения в колонках ID1 и ID2. Заблокированная сессия будет иметь те же значения в ID1 and ID2 что и блокирующая сессия, но так как запрашиваемый объект заблокирован, то количество запросов будет больше 0 (REQUEST >0).
В приведенном запросе SID 422 блокирует SID 479. В нашем примере SID 422 связан с Сессией 1, а SID 479 это наша заблокированная Сессия 2.
Что бы избежать изучения таблицы и сравнения колонок ID1 и ID2, напишем запрос:
SQL> select l1.sid, ' IS BLOCKING ', l2.sid
2 from v$lock l1, v$lock l2
3 where l1.block =1 and l2.request > 0
4 and l1.id1=l2.id1
5 and l1.id2=l2.id2
SQL> /SID 'ISBLOCKING' SID
- - -
422 IS BLOCKING 479
1 row selected.
Еще лучше, если мы добавим представление v$session и результат будет легко понимаем:
SQL> select s1.username || '@' || s1.machine
2 || ' ( SID=' || s1.sid || ' ) is blocking '
3 || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
4 from v$lock l1, v$session s1, v$lock l2, v$session s2
5 where s1.sid=l1.sid and s2.sid=l2.sid
6 and l1.BLOCK=1 and l2.request > 0
7 and l1.id1 = l2.id1
8 and l2.id2 = l2.id2 ;BLOCKING_STATUS - BULKLOAD@yttrium ( SID=422 ) is blocking BULKLOAD@yttrium ( SID=479 ) 1 row selected.
В представлении v$lock информации гораздо больше, но для того чтобы читать эту информацию мы должны иметь представление о типах блокировки и о назначении колонок ID1 и ID2.
Тип блокировки и колонки ID1 / ID2
В нашем случае мы знаем точно, что блокировка вызвана предложением DML, так как сами её и вызвали. Но в большинстве случаев нам не будет так вести. К счастью вы можете легко прочитать эту информацию из таблицы v$lock.Сначала вы должны изучить колонку TYPE. Существуют десятки типов блокировок, но подавляющее большинство из них системные. Обычно системные блокировки держатся очень короткое время и не самая лучшая идея настраивать ваши системные настройки на основании v$lock! (Смотрите часть V$LOCK в Oracle Database Reference.)
Существует только 3 типа пользовательских блокировок, TX, TM и UL.
UL (user-defined lock) - блокировка, определяемая с помощью пакета DBMS_LOCK.
TX блокировка это блокировка строки транзакцией; она выполняется один раз для каждой транзакции, изменяющей данные, не имеет значения как много объектов вы изменяете в этой транзакции. Колонки ID1 и ID2 указывают на сегмент отката и на элементы таблицы транзакций.
TM блокировка это блокировка предложением DML. Она выполняется один раз для каждого изменяемого объекта. Колонка ID1 показывает изменяемый объект.
Режимы блокировок
Вы можете о блокировках типа TM и TX изучив режимы блокировок. Колонки LMODE и REQUEST обе используют один и тот же метод числовой оценки режима блокировки: чем больше число, тем больше блокировка: от 0 для отсутствия блокировки до 6 для исключительной блокировки. Если сессия должна получить исключительную блокировку TX в случае изменения данных; LMODE будет равно 6. Если эта сессия не может получить исключительную блокировку так как некоторые строки хочет изменить другая сессия и она уже выставила свою блокировку, тогда наша сессия будет периодически запрашивать блокировку TX в исключительном режиме; LMODE будет равно 0 пока не получит блокировку, а REQUEST будет равно 6. Вы можете это видеть в этом выводе из v$lock:ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK - - - - - - - - - - AF9E2C4C AF9E2C60 479 TX 131078 16739 0 6 685 0 ADEBEA20 ADEBEB3C 422 TX 131078 16739 6 0 697 1
Обратите внимание на то, что колонки ID1 and ID2 в Сессии 2, которая запрашивает TX блокировку (LMODE=0, REQUEST=6), указывают на точки отката и транзакции из Сессии 1. Эта информация помогает нам определить блокирующую сессию для Сессии 2.
Иногда вы можете увидеть блокировку TX в режиме 4, режим разделения. Если в блоке, содержащем изменяемые строки нет свободного места в списке объектов транзакции (interested transaction list - ITL), тогда сессия выдает блокировку типа TX режим 4 до тех пор, пока не освободится место в ITL. Если вы видите блокировку TX-4 для какого-нибудь объекта, то возможно нужно увеличить параметр INITRANS для этого объекта.
Блокировки TM, как правило запрашиваются и получаются в режиме 3, что-то похожее эксклюзивную блокировку для разделяемых строк. DDL требует эксклюзивной блокировки для блокировки типа TM. (Заметьте, что предложение CREATE TABLE не требует TM блокировки - не надо блокировать ни каких объектов, ведь их еще нет!) DML требует эксклюзивную блокировку разделяемых записей. Итак, в селекте мы видели, что для резервирования DML были выданы блокировки типа TM с LMODE=3:
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK - - - - - - - - - - ADDF7EC8 ADDF7EE0 422 TM 88519 0 3 0 697 0 ADDF7F74 ADDF7F8C 479 TM 88519 0 3 0 685 0
Определение заблокированного объекта
Теперь, когда мы знаем, что каждая строка типа TM указывает на заблокированный объект, мы можем использовать значение в колонке ID1 для определения самого объекта.
SQL> select object_name from dba_objects where object_id=88519 ;OBJECT_NAME - TSTLOCK
Иногда знание объекта достаточно, но мы пойдем дальше. Мы можем определить не только блок, но строку в нем, которую ждет Сессия 2.
Определение заблокированной записи
Мы можем получить эту информацию из представления v$session для заблокированной сессии:SQL> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# from v$session where sid=479 ;ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
- - - -
88519 16 171309 0
Это дает нам ID объекта, относительный номер файла, номер блока в этом файле и строку, которую ждет наша сессии. Если перечень этих параметров звучит для вас знакомо, это потому, что эти четыре компонента и составляют расширенный ROWID. Мы можем построить реальный расширенный ROWID из этих компонент при помощи пакета DBMS_ROWID. Функция ROWID_CREATE принимает эти аргументы и возвращает ROWID:
SQL> select do.object_name,
2 row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
3 dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
4 from v$session s, dba_objects do
5 where sid=543
6 and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;OBJECT_NAME ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C - - - - - - TSTLOCK 88519 16 171309 0 AAAVnHAAQAAAp0tAAA
И конечно, это позволяет посмотреть эту запись напрямую.
SQL> select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;FOO BAR - - 1 a
Заключение
Мы увидели, как определить блокирующую сессию, и как посмотреть на записи, которые ждет сессияWe've seen how to identify a blocking session, and how to inspect the very row that the waiting session is waiting for. And, I hope, learned a bit about v$lock in the process.
