Печать

Блокировки транзакций. Захват в исключительном и ожидание в разделяемом режимах.

Наконец-то пришло время рассмотреть второй сценарий возникновения ситуации взаимных блокировок. Его отличие от предыдущего заключается в том, что одна из TX блокировок здесь находиться в исключительном режиме, а вторая, ожидающая, в разделяемом. Условий для возникновения такой взаимной блокировки на самом деле не так уж много, вернее их всего три. Это нехватка места в таблице транзакций, перекрытие фрагментов индекса на основе битовых карт и наложение значений первичного или уникального ключа при вставке. В какой-то мере, к последнему условию, можно отнести и DML операции с таблицами организованными по индексу.

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

Наложение значений первичного или уникального ключа при вставке

Для начала образуем два сеанса. Очистим таблицу t1 и для наглядности исследования процесса взаимного блокирования включим трассировку в первом сеансе. Далее, в этом же сеансе будем последовательно вставлять в таблицу t1 две строки с ключами 1 и 2, а во втором проделаем те же действия, но только в обратном направлении.

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

ZH@XE(28)> TRUNCATE TABLE t1;  

Таблица очищена

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

Session altered

ZH@XE(28)> INSERT INTO t1 (c1, c2) VALUES(1, ‘Строка1’);

Вставлено: 1 строка

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

ZH@XE(38)> INSERT INTO t1 (c1, c2) VALUES(2, ‘Строка2’);

Вставлено: 1 строка

В результате выполненных выше действий, в каждом из сеансов нами были установлены по одной транзакционной блокировке в исключительном режиме (LMODE=6):

SYSTEM@XE> SELECT * FROM v$lock WHERE sid IN (28, 38) AND type = 'TX';
 
ADDR     KADDR    SID TYPE ID1    ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- ---- ------ --- ----- ------- ----- -----
296EF030 296EF14C 28  TX   524323 168 6     0       84    0    
296F6648 296F6764 38  TX   65564  175 6     0       72    0    

Выбрано: 2 строки

Далее, в первом сеансе, попытаемся вставить строку с ключом 2. В связи с тем, что таблица t1 имеет ограничения первичного ключа, и строка с таким же значением данного ключа уже вставлена в незавершённой транзакции второго сеанса, выполнение этого оператора должно привести к ожиданию:

ZH@XE(28)> INSERT INTO t1 (c1, c2) VALUES(2, 'Строка2');

Ожидание …

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

WAIT #2: nam='enq: TX - row lock contention' ela= 3001240 name|mode=1415053316 
usn<<16 slot="65564" sequence="175" obj="" 13766="" tim="3276462301" pre="">

Но какой строки? Вставленные в таблицу строки не видны сеансам, так как транзакции не зафиксированы. Следовательно, они не могут являться причиной ожидания. Для прояснения ситуации заглянем в системное представление v$lock:

SYSTEM@XE> SELECT * FROM v$lock WHERE sid IN (28, 38) AND type = 'TX';
 
ADDR     KADDR    SID TYPE ID1    ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- ---- ------ --- ----- ------- ----- -----
2A201720 2A201734 28  TX   65564  175 0     4       1146  0    
296EF030 296EF14C 28  TX   524323 168 6     0       1893  0    
296F6648 296F6764 38  TX   65564  175 6     0       1881  1    

Выбрано: 3 строки

В представлении мы обнаруживаем три записи о TX блокировках. Две из них установлены в исключительном режиме на вновь вставленные строки. Третья блокировка, ожидающая, имеет в поле REQUEST значение 4, что соответствует разделяемому режиму. Значения полей ID1 и ID2 этой блокировки явно указывают на транзакцию во втором сеансе. Из этого ясно, что первый сеанс ожидает освобождения какой-то строки заблокированной вторым сеансом. Этой строкой в нашем случае является строка индекса первичного ключа соответствующая значению ключа равного двум. Данное значение ранее было добавлено сеансом 38 в индекс в момент вставки второй строки в таблицу. После чего, для защиты уникальности первичного ключа на эту строку индекса была наложена TX блокировка в исключительном режиме. Если теперь второй сеанс (38) отменит транзакцию, то в первом сеансе для вновь вставленной строки таблицы будет установлена TX блокировка в исключительном режиме. Если же второй сеанс зафиксирует транзакцию, то в первом сеансе будет наблюдаться ошибка нарушения ограничения первичного ключа:

INSERT INTO t1 (c1, c2) VALUES(2, 'Строка2')
*
Ошибка в строке 1:
ORA-00001: unique constraint (ZH.SYS_C004053) violated

А что произойдет, если второй сеанс попытается вставить строку со значением первичного ключа равным единице:

ZH@XE(38)> INSERT INTO t1 (c1, c2) VALUES(1, 'Строка1');

Ожидание…

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

ZH@XE(28)> INSERT INTO t1 (c1, c2) VALUES(2, 'Строка2');

INSERT INTO t (c1, c2) VALUES(2, 'Строка2')
            *
Ошибка в строке 1:
ORA-00060: deadlock detected while waiting for resource

И так, взаимоблокировка произошла. Настало самое время заглянуть в сгенерированный в результате ошибки трассировочный файл первого сеанса. В первой секции файла мы видим, оператор вставки INSERT который был отменён:

Current SQL statement for this session:
INSERT INTO t1 (c1, c2) VALUES(2, 'Строка2')

Граф взаимоблокировки имеет небольшие отличия от предыдущего сценария:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00080023-000000a8        20      28     X             18      38           S
TX-0001001c-000000af        18      38     X             20      28           S

В столбце wait появилось значение S. Это означает, что ожидаемая блокировка находиться в разделяемом режиме. В остальном разбор графа не изменился и соответствует первому сценарию. Поэтому сразу перейдём к секции ожидания строк:

Rows waited on:
Session 38: no row
Session 28: obj - rowid = 000035C6 - AAADXSAAEAAAAFiAAA
  (dictionary objn - 13766, file - 4, block - 354, slot - 0)

В секции присутствует информация только по одной ожидающей строке, да и то это ожидание блокировки, которое было отменено. Если вам попались эти данные, то считайте, что вам повезло. Обычно информации здесь нет. Это лишний раз доказывает, что рассматриваемый случай взаимной блокировки не является ситуацией взаимного блокирования на уровне строк таблицы. Поэтому, для того чтобы распознать в трассировочном файле блокирование при наложении значений первичного или уникального ключей, необходимо в первую очередь обратиться к секции «Current SQL statement for this session». Если это оператор INSERT и таблица, в которую он вставляет значения, имеет первичные или уникальные ключи, то с большой долей вероятности можно судить о том, что произошел второй сценарий взаимного блокирования.

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

Выполнение DML операторов над таблицами организованными по индексу

Этот сценарий взаимной блокировки можно в какой-то мере отнести к предыдущему случаю. Здесь также, ожидающие сеансы запрашивают блокировки транзакций в разделяемом режиме, и также TX блокировки в исключительном режиме выставляются на строки индекса. Но в отличие от наложения первичных ключей при вставке эта взаимная блокировка может проявляться при выполнении любого DML оператора. Продемонстрируем это на примере. Организуем два сеанса. В первом сеансе создадим индекс организованную таблицу, вставим в неё две строки и включим трассировку:

ZH@XE(31)> CREATE TABLE t2 (c1 NUMBER PRIMARY KEY, c2 VARCHAR2(50)) 
ORGANIZATION INDEX;

Таблица создана

ZH@XE(31)> INSERT INTO t2 (c1) VALUES(1);

Вставлено: 1 строка

ZH@XE(31)> INSERT INTO t2 (c1) VALUES(2);

Вставлено: 1 строка

ZH@XE(31)> COMMIT;
 
Commit complete

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

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

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

ZH@XE(31)> UPDATE t2 SET c2 = 'Строка1' WHERE c1 = 1;

Изменено: 1 строка

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

ZH@XE(38)> UPDATE t2 SET c2 = 'Строка2' WHERE c1 = 2;

Изменено: 1 строка

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

ZH@XE(31)> UPDATE t2 SET c2 = 'Строка2' WHERE c1 = 2;

Ожидание…

В трассировочном файле первого сеанса наблюдаем в это время ожидание “конкуренция блокировки строки”:

WAIT #1: nam='enq: TX - row lock contention' ela= 3000041 name|mode=1415053316 
usn<<16 slot="262149" sequence="180" obj="" -1="" tim="6315257799" 

Если после этого мы заглянем в представление v$lock, то обнаружим там точно такую же картину, как и при наложении значений первичных или уникальных ключей:

SYSTEM@XE> SELECT * FROM v$lock WHERE sid IN (31, 38) AND type = 'TX';
 
ADDR     KADDR    SID TYPE ID1    ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- ---- ------ --- ----- ------- ----- -----
2A201668 2A20167C 31  TX   262149 180 0     4       114   0    
296F5604 296F5720 31  TX   196615 179 6     0       447   0    
296F6648 296F6764 38  TX   262149 180 6     0       339   1  

Продолжим изменения и во втором сеансе поправим первую строку таблицы:

ZH@XE(38)> UPDATE t2 SET c2 = 'Строка1' WHERE c1 = 1;

Ожидание…

И снова, как и раньше, у нас возникло взаимное блокирование:

ZH@XE(31)> UPDATE t2 SET c2 = 'Строка2' WHERE c1 = 2;

UPDATE t2 SET c2 = 'Строка2' WHERE c1 = 2
       *
Ошибка в строке 1:
ORA-00060: deadlock detected while waiting for resource

Рассмотрим содержимое трассировочного файла взаимной блокировки.

Текущий отменённый оператор:

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

Граф взаимоблокировки:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00030007-000000b3        21      31     X             18      38           S
TX-00040005-000000b4        18      38     X             21      31           S

Сеансы, ожидающие строки:

Rows waited on:
Session 38: no row
Session 31: no row

Как видим, в секции «Rows waited on» нет значений. Это связано с тем, что таблица, организованная по индексу представляет по организационной структуре в некотором смысле слова индекс. И хотя в данной таблице представлен идентификатор строки, но он, по сути, является логическим идентификатором, построенным на основе значений первичного ключа, а не на основе физического размещения. Поэтому если в предыдущем случае взаимной блокировки в данной секции трассировочного файла иногда и могла появиться информация о сеансе, ожидающем строку, здесь он будет отсутствовать в любых случаях.

В остальном все секции трассировочного файла очень похожи на предыдущий случай. Поэтому для того чтобы определить, что произошло именно взаимное блокирование при выполнении DML операторов над таблицами организованными по индексу, необходимо обратиться в первую очередь в секции Current SQL statement for this session. Если среди объектов, включённых в отменённый SQL оператор, присутствует таблица этого типа, то вполне возможно произошёл именно данный сценарий взаимного блокирования.

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

Наложение фрагментов индекса на основе битовых карт

Случай взаимной блокировки с разделяемым режимом может возникать и в тех случаях, когда несколько сеансов пытаются обновить или удалить строки в таблицах с битовыми индексами. Правда, для этого необходимо наличие одного условия. Изменения в индексируемом столбце должны приводить к наложению строк в этом битовом индексе. Попробуем продемонстрировать это на примере. Для этого нам придётся взять экземпляр редакции Oracle Enterprise Edition, так как в используемой нами до этого редакции Express Edition отсутствует опция битовые индексы. Для начала, в первом сеансе, создадим таблицу t3 и заполним её данными:

Подключение к: 
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production 
With the Partitioning, OLAP and Data Mining options

ZH@XE(146)> CREATE TABLE t3 (c1 NUMBER PRIMARY KEY, c2 VARCHAR2(1)); 

Таблица создана

ZH@XE(146)> INSERT INTO t3 (c1, c2) VALUES(1, 'А');

Вставлено: 1 строка

ZH@XE(146)> INSERT INTO t3 (c1, c2) VALUES(2, 'Б');

Вставлено: 1 строка

ZH@XE(146)> INSERT INTO t3 (c1, c2) VALUES(3, 'В');

Вставлено: 1 строка

ZH@XE(146)> INSERT INTO t3 (c1, c2) VALUES(4, 'Г');

Вставлено: 1 строка

ZH@XE(146)> INSERT INTO t3 (c1, c2) VALUES(5, 'Д');

Вставлено: 1 строка

ZH@XE(146)> COMMIT;
 
Commit complete

Создадим битовый индекс по столбцу c2:

ZH@ALFA(146)> CREATE BITMAP INDEX t3_bmp_idx ON t3(c2); 

Индекс создан

Включим трассировку для первого сеанса на уровне ожидания событий:

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

Далее, попробуем изменить значение столбца «с2» второй строки на значение «В»:

ZH@ALFA(146)> UPDATE t3 SET c2 = 'В' where c1 = 2;

Изменено: 1 строка

Во втором сеансе в это время изменяем значение столбца «с2» четвёртой строки на значение «Д»:

ZH@ALFA(144)> UPDATE t3 SET c2 = 'Д' where c1 = 4;

Изменено: 1 строка

Если теперь в первом сеансе изменить значение столбца «с2» пятой строки на значение «Е», то возникнет ожидание:

ZH@ALFA(146)> UPDATE t3 SET c2 = 'Е' where c1 = 5;

Ожидание…

Отчего возникло ожидание? Ведь мы изменяли разные строки таблицы, никак не связанные друг с другом. Может быть, дело здесь вовсе не в таблице, а в битовом индексе? На самом деле, данное ожидание происходит из-за того, что при изменении значения индексируемого столбца таблицы, блокируется не только строка битового индекса соответствующая текущему значению столбца, но и строка индекса соответствующая его новому значению. Проще говоря, в нашем случае, второй сеанс при обновлении четвёртой строки таблицы заблокировал строки битового индекса, значения которых соответствовали значению «Г» и «Д». Поэтому первый сеанс, пытающийся обновить столбец c2 пятой строки таблицы, с находящимся в нём значением «Д», будет ожидать освобождения необходимой ему строки битового индекса. Проверим это. Для начала заглянем в трассировочный файл первого сеанса:

WAIT #4: nam='enq: TX - row lock contention' ela= 2999504 name|mode=1415053316 
usn<<16 slot="262154" sequence="2992" obj="" 59268="" tim="954083404" 

В файле наблюдаем привычное ожидание “конкуренция блокировки строки”. Похожая ситуация отображена и в системном представлении v$lock:

SYSTEM@ALFA> SELECT * FROM v$lock WHERE type = 'TX';
 
ADDR     KADDR    SID TYPE ID1    ID2  LMODE REQUEST CTIME BLOCK
-------- -------- --- ---- ------ ---- ----- ------- ----- -----
338343A8 338343BC 146 TX   589842 3019 0     4       9     0    
32E01ABC 32E01AE0 146 TX   393235 2995 6     0       36    0    
32E37268 32E3728C 144 TX   589842 3019 6     0       24    1    

Выбрано: 3 строки

Первый сеанс (146) сделал запрос на установку TX блокировки в разделяемом режиме (REQUEST = 4) и ожидает освобождения строки индекса от блокировки транзакций исключительного режима во втором сеансе (144).

Продолжим изменение и попробуем во втором сеансе изменить значение столбца «с2» третьей строки на значение «Г»:

ZH@ALFA(144)> UPDATE t3 SET c2 = 'Г' where c1 = 3;

Ожидание…

По идее, мы сейчас пытаемся обновить столбец c2 в строке, где предыдущее его значение было равно «В». Следовательно, в битовом индексе на строку соответствующую этому значению должна быть выставлена блокировка. Но строка уже была заблокирована первым сеансом при обновлении значения столбца c2 второй строки на значение «В». Поэтому второй сеанс будет ждать её освобождения. В тоже время первый сеанс так же ждёт освобождения строки индекса соответствующей значению «Д», которая была захвачена вторым сеансом. Происходит бесконечное ожидание, и у нас возникает ситуация взаимной блокировки, о которой Oracle сигнализирует в первом сеансе:

ZH@ALFA(146)> UPDATE t3 SET c2 = 'Е' where c1 = 5;

UPDATE t3 SET c2 = 'Е' where c1 = 5
       *
Ошибка в строке 1:
ORA-00060: deadlock detected while waiting for resource

Изучим содержимое образовавшегося трассировочного файла взаимной блокировки. Первые две секции содержат обычную для таких случаев информацию:

Current SQL statement for this session:
UPDATE t3 SET c2 = 'Е' where c1 = 5

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00060013-00000bb3        19     146     X             18     144           S
TX-00090012-00000bcb        18     144     X             19     146           S

А вот в секции сеансов ожидающих строки появились нужные нам данные:

Rows waited on:
Session 144: obj - rowid = 0000E784 - AAAOeEAAAAAAAAAAAA
  (dictionary objn - 59268, file - 0, block - 0, slot - 0)
Session 146: obj - rowid = 0000E784 - AAAOeEAAAAAAAAAAAA
  (dictionary objn - 59268, file - 0, block - 0, slot - 0)

По номеру objn мы легко находим объект, строки которого блокируются. В нашем случае это битовый индекс нашей таблицы:

SYSTEM@ALFA> SELECT owner, object_name FROM dba_objects WHERE object_id = 
59268;
 
OWNER OBJECT_NAME
----- -----------
ZH    T3_BMP_IDX 

Выбрано: 1 строка

Наложение фрагментов индекса на основе битовых карт это, пожалуй, единственный случай из сценариев взаимной блокировки транзакций с разделяемым режимом, который содержит полную секцию Rows waited on, поэтому определить его по содержимому файлу трассировки не составляет труда.

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

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

Последний случай возникновения взаимного блокирования с ожидающей блокировкой в разделяемом режиме можно с уверенность назвать вырождающимся. Он связан с ожиданием, которое возникает при нехватке свободного слота в таблице транзакций блока (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 в сторону уменьшения без острой необходимости. Второе, если всё же параметр был изменён, увеличивайте его до полного пропадания взаимных блокировок.