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

2010 г.

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

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

Нехватка слотов в таблице транзакций блока

Последний случай возникновения взаимного блокирования с ожидающей блокировкой в разделяемом режиме можно с уверенностью назвать вырождающимся. Он связан с ожиданием, которое возникает при нехватке свободного слота в таблице транзакций (ITL). Начиная с десятой версии Oracle данный случай взаимной блокировки очень трудно воспроизвести. Это связано с тем, что максимальное количество слотов, которое может быть в таблице транзакций стало фиксированным и составляет на данный момент времени 255.

В ранних версиях Oracle начальное и максимальное количество слотов таблицы транзакций можно было задавать с помощью параметров таблицы INITRANS и MAXTRANS. Теперь изменение этих параметров не имеет никакого значения. В связи с этим, для того чтобы смоделировать данный сценарий взаимного блокирования, нам пришлось бы организовать одновременно более 255 транзакций. Сделать это было бы затруднительно, поэтому для моделирования ситуации мы будем использовать экземпляр Oracle версии 9i, где изменение одного из перечисленных параметров всё ещё было возможно.

Для начала создадим две одинаковые таблицы t4 и t5, с искусственно ограниченным максимальным размером таблицы транзакций блока, равным двум слотам:

 Подключение к:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 – Production

ZH@ALFA9> CREATE TABLE t4 (c1 NUMBER PRIMARY KEY, c2 VARCHAR2(50)) PCTFREE 0 INITRANS 2 MAXTRANS 2;
 
Таблица создана
 
ZH@ALFA9> CREATE TABLE t5 (c1 NUMBER PRIMARY KEY, c2 VARCHAR2(50)) PCTFREE 0 INITRANS 2 MAXTRANS 2;
 
Таблица создана

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

ZH@ALFA9> INSERT INTO t4 (c1) VALUES(1);
 
Вставлено: 1 строка

ZH@ALFA9> INSERT INTO t4 (c1) VALUES(2);
 
Вставлено: 1 строка

ZH@ALFA9> INSERT INTO t4 (c1) VALUES(3);
 
Вставлено: 1 строка

ZH@ALFA9> INSERT INTO t5 (c1) VALUES(1);
 
Вставлено: 1 строка

ZH@ALFA9> INSERT INTO t5 (c1) VALUES(2);
 
Вставлено: 1 строка

ZH@ALFA9> INSERT INTO t5 (c1) VALUES(3);
 
Вставлено: 1 строка

ZH@ALFA9> COMMIT;
 
Commit complete

Далее образуем три сеанса и в каждом из них изменим следующие строки.

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

ZH@ALFA9I(12)> UPDATE t4 SET c2 = 'Строка1' WHERE c1 = 1;
 
Изменено: 1 строка
 
ZH@ALFA9I(12)> UPDATE t5 SET c2 = 'Строка1' WHERE c1 = 1;
 
Изменено: 1 строка

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

ZH@XE(13)> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
 
Session altered

ZH@ALFA9I(13)> UPDATE t4 SET c2 = 'Строка2' WHERE c1 = 2;
 
Изменено: 1 строка

Третий сеанс:

ZH@ALFA9I(14)> UPDATE t5 SET c2 = 'Строка3' WHERE c1 = 3;
 
Изменено: 1 строка

Теперь снова вернёмся ко второму сеансу:

ZH@ALFA9I(13)> UPDATE t5 SET c2 = 'Строка2' WHERE c1 = 2;

Ожидание …

Почему возникло ожидание, ведь изменяются совершенно разные и не зависящие друг от друга строки? На самом деле, в этом нет ничего неожиданного. Ещё при создании таблиц мы указали, что слотов в таблице транзакций блока не может быть больше двух. Так как данные в каждой из созданных нами таблиц занимают по одному блоку, то выполнив команды, перечисленные выше, мы исчерпали лимит слотов в блоке таблицы t5. В результате этого, при попытке обновить данные в блоке таблицы второй сеанс вынужден ждать появления свободного слота. Чтобы убедиться в этом, как говорится, наглядным образом, заглянем в трассировочный файл второго сеанса, а также в системное представление v$lock.

В файле трассировки у нас довольно скудная информация. Здесь мы видим только то, что в сеансе постоянно возникает непонятное ожидание «очередь»:

WAIT #1: nam='enqueue' ela= 3077805 p1=1415053316 p2=65545 p3=455

А вот представление v$lock даёт нам гораздо больше информации:

SYSTEM@ALFA9I> SELECT * FROM v$lock WHERE type = 'TX';
 
ADDR     KADDR    SID TYPE ID1    ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- ---- ------ --- ----- ------- ----- -----
67B7F044 67B7F150 12  TX   589870 447 6     0       211   1    
67B7A1FC 67B7A308 13  TX   393245 450 6     0       110   0    
682BED20 682BED30 13  TX   589870 447 0     4       3     0    
67BAB4A8 67BAB5B4 14  TX   65545  455 6     0       39    0    
 
Выбрано: 4 строки

Тут мы действительно видим во втором сеансе (13) ожидающую TX-блокировку в разделяемом режиме. Причем, судя по значениям столбцов ID1 и ID2 , второй сеанс ожидает освобождения слота, занятого первым сеансом (12). Выбор Oracle первого сеанса в качестве блокирующего не носит какого-то обязательного характера. Если мы попробуем смоделировать это ожидание снова, то блокировать у нас будет уже третий сеанс. Есть ли в этом отличие для нашей моделируемой ситуации? По большому счёту – нет, поэтому продолжим наши изменения, и в третьем сеансе изменим строку в таблице t4:

ZH@ALFA9I(14)> UPDATE t4 SET c2 = 'Строка3' WHERE c1 = 3;

Ожидание …

Возникает бесконечное ожидание. Вследствие этого, во втором сеансе мы наблюдаем ошибку взаимного блокирования:

ZH@ALFA9I(14)> UPDATE t5 SET c2 = 'Строка2' WHERE c1 = 2;
  
UPDATE t5 SET c2 = 'Строка2' WHERE c1 = 2
       *
Ошибка в строке 1:
ORA-00060: deadlock detected while waiting for resource

Отчего произошла взаимная блокировка? Почему Oracle посчитала, что данная ситуация является тупиковой? Попробуем ответить на эти вопросы. Как мы видели ранее, у нас возникло ожидание в одном из сеансов из-за того, что все слоты таблицы транзакций в блоке таблицы t5 были заняты первым и третьим сеансами. То есть второй сеанс ожидал освобождения свободного слота ITL, чтобы заблокировать строку таблицы t5 для изменения. Пытаясь обновить строку в таблице t4, мы создали идентичное ожидание в третьем сеансе, но уже для блока таблицы t4. В результате на момент времени возникновения взаимной блокировки у нас получилось два ожидающих сеанса – второй и третий. Причём второй сеанс ожидал свободного слота в ITL блоке таблицы t5, все слоты которой были заняты первым и третьим сеансами, а третий сеанс – свободного слота в ITL блоке таблицы t4, занятой первым и вторым сеансами.

Из этой ситуации выходило, что второй и третий сеансы не могли освободить слоты ITL в блоках таблиц, так как сами находились в ожидании друг друга. Вроде бы складывается ситуация взаимного блокирования. Но мы забыли про первый сеанс. Ведь он находился не в режиме ожидания. Что если мы бы попробовали откатить или зафиксировать изменения в нём? В этом случае в ITL блоков таблиц t4 и t5 обязаны освободиться два слота, вследствие чего второй и третий сеансы должны были выйти из ожидания. Но на самом деле всё не так просто. СУБД Oracle в этой сложившейся ситуации как бы не принимает во внимание возможность освобождения слотов таблицы транзакций блока первым сеансом и определяет такое блокирование как взаимное. Вполне возможно, это связано с особенностью построения и анализа графа ожидания транзакций. Посмотрим, как он будет выглядеть в нашем случае.

Так и есть, у нас образуется цикл (показан красным цветом) между двумя транзакциями второго и третьего сеансов. Этот цикл Oracle сразу интерпретирует как ситуацию взаимного блокирования, даже не вникая в то, что она может разрешиться сама собой.

Что же нам в этом случае покажет файл трассировки взаимной блокировки? Заглянем в его содержимое:

DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE t5 SET c2 = 'Строка2' WHERE c1 = 2

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0006001d-000001c2        15      13     X             16      14           S
TX-00010009-000001c7        16      14     X             15      13           S

Rows waited on:
Session 14: no row
Session 13: no row

Ничего особенного. Все секции файла трассировки похожи на предыдущие сценарии. Правда, следует отметить, что в файле отсутствует хоть какое-то упоминание о первом сеансе, и это ещё раз подтверждает то, что он не участвует в ситуации взаимного блокирования, хотя содержимое представления v$lock явно указывало, что именно этот сеанс осуществляет блокировку.

Если вам в будущем придётся анализировать файл с подобным содержимым, обращайте своё внимание в первую очередь на версию Oracle. Такие взаимные блокировки на старших релизах Oracle маловероятны. Кроме этого, такая взаимная блокировка никогда не возникнет на операторах вставки INSERT. Ясно, что если сеанс при вставке столкнётся с нехваткой слотов в ITL, он просто осуществит вставку в другой блок.

Как сделать, чтобы подобные ситуации взаимных блокировок не возникали? Рецепт довольно прост. Во-первых, старайтесь не изменять параметры INITRANS и MAXTRANS в сторону уменьшения без острой необходимости. Во-вторых, если параметр был всё же изменён, увеличивайте его до полного исчезновения взаимных блокировок.

Назад Содержание Вперёд