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