Хранимые 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 команд.

Заключение

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

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

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

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