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

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

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

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

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

Но какой строки? Вставленные в таблицу строки не видны сеансам, так как транзакции не зафиксированы. Следовательно, они не могут являться причиной ожидания. Для прояснения ситуации заглянем в системное представление 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

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

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, поэтому определить его по содержимому файлу трассировки не составляет труда.

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

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