Хранимые 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.
Какой цифровой диктофон выбрать: надежный Гном или зеленоградские Edic Mini?
ремонт принтеров в уфе
Смотрите также:
|