Взаимные блокировки в Oracle

2010 г.

Взаимные блокировки в Oracle

Сергей Жилин, январь – март 2010 года
http://dba.ucoz.ru

Хранимые PL/SQL объекты

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

Процедура

Смоделируем следующую ситуацию. Создадим процедуру p1 с единственной командой, которая будет обновлять нужную нам строку таблицы t1 в зависимости от входных параметров:

SQL> CREATE PROCEDURE p1(v1 in integer, v2 in VARCHAR2)
  2> AS
  3> BEGIN
  4>   UPDATE t1 SET c2 = v2 WHERE c1 = v1;
  5> END;
 
Процедура изменена

Образуем два сеанса. В первом сеансе изменим первую строку:

ZH@XE(31)> EXECUTE p1(1, 'Строка1');
 
PL/SQL procedure successfully completed

Во втором сеансе изменим вторую строку:

ZH@XE(23)> EXECUTE p1(2, 'Строка2');
 
PL/SQL procedure successfully completed

Вернёмся в первый сеанс и изменим вторую строку:

ZH@XE(31)> EXECUTE p1(2, 'Строка2');

Ожидание…

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

ZH@XE(23)> EXECUTE p1(1, 'Строка1');

Ожидание…

Так и есть, в первом сеансе происходит ошибка:

ZH@XE(31)> EXECUTE p1(2, 'Строка2');
 
BEGIN
*
Ошибка в строке 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "ZH.P1", line 4
ORA-06512: at line 2

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

Рассмотрим теперь содержимое трассировочного файла взаимной блокировки. Есть ли здесь какие-либо изменения? Первая секция содержит отменённый в результате ошибки оператор первого сеанса:

Current SQL statement for this session:
UPDATE T1 SET C2 = :B2 WHERE C1 = :B1

Как видим, это команда UPDATE, содержащаяся в нашей процедуре p1. Правда, это мы знаем, что данная SQL-команда принадлежит этой процедуре. Но обычно бывает трудно идентифицировать PL/SQL-объект, которому принадлежит данный курсор. И в этом нам может помочь новый блок трассировочного файла, содержащий информацию стека вызовов PL/SQL. Он располагается сразу после первой секции вслед за словами PL/SQL Call Stack:

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
29CDE8E4         4  procedure ZH.P1
2670C20C         2  anonymous block

В нашем случае мы сразу можем определить, что отменённая команда принадлежит процедуре ZH.P1. Но иногда здесь может оказаться сразу несколько объектов, и в этом случае установить, кому из них принадлежит отменённый SQL-оператор, бывает трудно.

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

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00070008-000000e1        21      31     X             25      23           X
TX-0009002c-000000d9        25      23     X             21      31           X

Rows waited on:
Session 23: obj - rowid = 0000360E - AAADYOAAEAAAAGfAAA
  (dictionary objn - 13838, file - 4, block - 415, slot - 0)
Session 31: obj - rowid = 0000360E - AAADYOAAEAAAAGfAAB
  (dictionary objn - 13838, file - 4, block - 415, slot - 1)

Итак, как мы убедились, взаимная блокировка при выполнении процедур ничем кардинально не отличается от сценариев с отдельными SQL-командами. Разве только в трассировочном файле появилась новая секция о стеке вызовов PL/SQL. Правда, в процессе моделирования ситуации мы забыли упомянуть об одной важной вещи. Если при возникновении взаимного блокирования с SQL-командами Oracle для нормализации ситуации отменяет одну из SQL-команд, то что он отменит в нашем случае взаимной блокировки с PL/SQL объектами?

Исключения

Как говорилось выше, Oracle в процессе возникновения взаимной блокировки откатывает один из SQL-операторов к неявной точке сохранения, сделанной перед этим оператором. Это справедливо для отдельных SQL-команд. Команда же, помещённая в PL/SQL блок, подчиняется правилам обработки исключений в PL/SQL коде. Если происходит необработанное исключение, то управление передается внешней среде. Чтобы прояснить данную ситуацию, попробуем её смоделировать. Для этого нам надо немного изменить процедуру p1:

ZH@XE(31)> CREATE OR REPLACE PROCEDURE zh.p1(v1 in integer,  v2 in integer, v3 in VARCHAR2)
        2> AS
        3> BEGIN
        4>   INSERT INTO t1 (c1) VALUES (v1);
        5>   UPDATE t1 SET c2 = v3 WHERE c1 = v2;
        6> END;
 
Процедура изменена

Теперь процедура не только изменяет строку, указанную во входных параметрах, но и вставляет ещё одну новую строку. Причём вставка происходит до обновления.

Повторим все те же действия, что и в предыдущем примере с учётом вставки. Для начала в первом сеансе вставим третью строку и изменим первую:

ZH@XE(31)> EXECUTE p1(3, 1, 'Строка1');

PL/SQL procedure successfully completed

Посмотрим, занеслась ли строка в таблицу t1:

ZH@XE(31)> SELECT * FROM t1
 
C1 C2     
-- -------
3         
1  Строка1
2  Строка2
 
Выбрано: 3 строки

Всё нормально. Во втором сеансе вставим четвёртую строку и обновим вторую:

ZH@XE(24)> EXECUTE p1(4, 2, 'Строка2');
 
PL/SQL procedure successfully completed

Вернёмся в первый сеанс и вставим пятую строку с изменением второй.

ZH@XE(31)> EXECUTE p1(5, 2, 'Строка2');

Ожидание…

Возникло ожидание. Теперь создадим взаимную блокировку, изменив во втором сеансе первую строку:

ZH@XE(24)> EXECUTE p1(6, 1, 'Строка1');
 
PL/SQL procedure successfully completed

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

ZH@XE(31)> EXECUTE p1(5, 2, 'Строка2');
 
BEGIN
*
Ошибка в строке 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "ZH.P1", line 5
ORA-06512: at line 2

Выведем в этом же сеансе содержимое таблицы t1:

ZH@XE(31)> SELECT * FROM t1
 
C1 C2     
-- -------
3         
1  Строка1
2  Строка2
 
Выбрано: 3 строки

Вместо четырёх строк мы видим только три. Где же строка со значением первичного ключа, равным пяти? Ведь ошибка взаимной блокировки должна отменить только последний оператор. Всё так и было бы в случае с отдельными SQL-командами. Но операторы у нас находятся в PL/SQL-объекте, и, как говорилось ранее, обработка исключений (а ошибка взаимной блокировки вызывает именно исключение) происходит по определённым правилам. В нашем случае в нашей процедуре не установлен обработчик исключений, и поэтому ошибка взаимой блокировки вызывает необработанное исключение в PL/SQL блоке, что приводит к немедленной передаче управления во внешнюю среду с отменой всех незафиксированных изменений, сделанных в пределах этого блока.

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

ZH@XE(31)> CREATE OR REPLACE PROCEDURE zh.p1(v1 in integer,  v2 in integer, v3 in VARCHAR2)
        2> AS
        3> BEGIN
        4>   INSERT INTO t1 (c1) VALUES (v1);
        5>   BEGIN
        6>     UPDATE t1 SET c2 = v3 WHERE c1 = v2;
        7>   EXCEPTION when others THEN NULL;
        8>   END;  
        9> END;
 
Процедура изменена

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

Первый сеанс:

ZH@XE(31)> EXECUTE p1(3, 1, 'Строка1');
 
PL/SQL procedure successfully completed

Второй сеанс:

ZH@XE(24)> EXECUTE p1(4, 2, 'Строка2');
 
PL/SQL procedure successfully completed

Первый сеанс:

ZH@XE(31)> EXECUTE p1(5, 2, 'Строка2');
 
PL/SQL procedure successfully completed

Второй сеанс:

ZH@XE(24)> EXECUTE p1(6, 1, 'Строка1');
 
PL/SQL procedure successfully completed

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

ZH@XE(31)> SELECT * FROM t1
 
C1 C2     
-- -------
3         
5         
1  Строка1
2  Строка2
 
Выбрано: 4 строки

Какой вывод можно сделать из всего вышесказанного? Никогда не стоит пренебрегать обработкой исключительной ситуации взаимного блокирования в хранимом PL/SQL объекте. Потерянные изменения могут быть больше, чем при выполнении отдельных SQL-команд.

Заключение

Подведём небольшой итог изучения взаимных блокировок:

Взаимная блокировка может возникать только между двумя и более сеансами. Всё остальное – это баги (ошибки) Oracle.
В каждом сеансе обязательно должна быть открыта транзакция.
В пределах каждой транзакции должны присутствовать как минимум две блокировки в исключительных режимах. Причём одна из них должна быть установленной, а другая ожидающей.
Из этих установленных и ожидающих блокировок должна быть выстроена цепочка, в которой ожидающая блокировка в одном сеансе ссылалается на установленную блокировку в другом сеансе.
Данная цепочка должна замыкаться. То есть последняя ожидающая блокировка в цепочке должна ожидать первую установленную блокировку.
Транзакционные блокировки должны быть установлены или ожидать строки ранее зафиксированные транзакциями. То есть заблокированные строки должны быть видимы для других сеансов, что, к примеру, невозможно при вставках строк.
Одинаковая последовательность обработки ресурсов в большинстве случаев предотвращает взаимное блокирование.
Поиск ситуаций взаимного блокирования осуществим всегда, при этом используется граф ожидания транзакций.
Цикл графа ожидающих транзакций соответствует взаимной блокировке, независимо от того, может ли ситуация разрешиться другими способами.
Взаимная блокировка иногда может приводить к зависанию приложения, а не к вызову исключения.

Список литературы

Bobak, M. J. (б.д.). Understanding and Interpreting Deadlocks or What to do When You Encounter ORA-00060.
Cyran, M. (October 2005). Oracle® Database Concepts 10g Release 2 (10.2).
Richmond Shee, K. D. Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning.
Кайт, Т. (2003). Oracle для профессионалов. DiaSoft.

Назад Содержание