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

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

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

Блокировки на уровне таблицы.

В предыдущих примерах мы рассмотрели варианты возникновения взаимных блокировок в результате блокирования строк в таблицах или индексах. Пришло время рассмотреть случаи взаимоблокировок, которые возникают при блокировании  на уровне объектов. Такие события обычно связаны с внутренним механизмом работы Oracle и в большинстве случаев не относятся напрямую к плохо спроектированному коду приложения.

Неиндексированный внешний ключ

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

Для начала создадим две таблицы:

ZH@XE> CREATE TABLE t6(c1 NUMBER PRIMARY KEY, c2 VARCHAR2(50));
 
Таблица создана

ZH@XE> CREATE TABLE t7(c1 NUMBER PRIMARY KEY, c2 VARCHAR2(50), c3 NUMBER REFERENCES t6(c1) ON DELETE CASCADE);
 
Таблица создана

Созданная нами дочерняя таблица t7 имеет неиндексированный внешний ключ с опцией ON DELETE CASCADE на родительскую таблицу t6 . Вставим в эти таблицы строки:

ZH@XE> INSERT INTO t6 (c1, c2) VALUES(1, 'Строка1');
 
Вставлено: 1 строка

ZH@XE> INSERT INTO t6 (c1, c2) VALUES(2, 'Строка2');
 
Вставлено: 1 строка

ZH@XE> INSERT INTO t6 (c1, c2) VALUES(3, 'Строка3');
 
Вставлено: 1 строка

ZH@XE> INSERT INTO t7 (c1, c2, c3) VALUES(1, 'Строка1', 1);
 
Вставлено: 1 строка

ZH@XE> INSERT INTO t7 (c1, c2, c3) VALUES(2, 'Строка2', 2);
 
Вставлено: 1 строка

Далее удалим в первом и во втором сеансе из подчинённой таблицы t7 по одной строке.

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

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

ZH@XE(27)> DELETE FROM t7 WHERE c1 = 1;
 
Удалено: 1 строка

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

ZH@XE>(21) DELETE FROM t7 WHERE c1 = 2;
 
Удалено: 1 строка

В результате удаления строк на таблицу t7 оказались выставлены две блокировки таблицы (TM).  Данные блокировки всегда выставляются перед  установкой блокировок транзакций (TX), чтобы предотвратить изменение структуры таблицы. Посмотрим, как всё это отображается в представлении v$lock:

SQL> SELECT * FROM v$lock WHERE sid IN (27, 21) AND type = 'TM'
 
ADDR     KADDR    SID TYPE ID1   ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- ---- ----- --- ----- ------- ----- -----
296DAA90 296DAAA8 27  TM   13986 0   2     0       81    0    
296DAB54 296DAB6C 27  TM   13988 0   3     0       81    0    
296DAC18 296DAC30 21  TM   13986 0   2     0       39    0    
296DACDC 296DACF4 21  TM   13988 0   3     0       39    0    
 
Выбрано: 4 строки

Вместо двух TM-блокировок на таблицу t7 в представлении мы наблюдаем все четыре блокировки. Проверим, каким объектам они принадлежат. Для этого выполним следующий запрос  к представлению dba_objects, подставив в качестве номера объекта содержимое столбца ID1:

SQL> SELECT object_id, owner, object_name FROM dba_objects WHERE object_id IN (13986, 13988);
 
OBJECT_ID OWNER OBJECT_NAME
--------- ----- -----------
13986     ZH    T6         
13988     ZH    T7         
 
Выбрано: 2 строки

Судя по содержимому представления v$lock, сейчас у нас действительно выставлено две блокировки ТМ на дочернюю таблицу t7 в монопольном строчном режиме (LMODE=3 - SX).  К тому же, так как эта таблица связана внешним неиндексированным ключом с родительской таблицей t6, мы наблюдаем дополнительно ещё две блокировки TM в минимальном разделяемом строчном режиме (LMODE=2 - SS) на таблицу t6. Эти две последние блокировки в принципе безобидны. Они  просто не дают заблокировать другим сеансам родительскую таблицу t6 в монопольном режиме на время изменения строк в дочерней таблице. Это предохранит изменение структуры данных родительской таблицы. В остальном над таблицей можно выполнять любые действия: выбирать, добавлять,  обновлять и удалять строки этой же таблицы. Попробуем,  к примеру, в первом сеансе удалить вторую строку таблицы t6:

ZH@XE(27)> DELETE FROM t6 WHERE c1 = 2;

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

WAIT #6: nam='enq: TM - contention' ela= 2999804 name|mode=1414332421 object #=13988 table/partition=0 obj#=-1 tim=9484943543

В первом сеансе у нас постоянно возникает ожидание «Конкуренция TM блокировки».  В принципе, данное ожидание  можно было бы объяснить тем, что при выполнении оператора удаления первый сеанс попытался выставить TM-блокировку в режиме SX на родительскую таблицу t6. Но как мы выяснили раньше, выставленные ранее на неё блокировки таблицы в разделяемом строчном режиме (SS) не должны приводить к ожиданию. Так какая очередь возникла? Чтобы разобраться в этой ситуации, заглянем в представление v$lock:

SQL> SELECT * FROM v$lock WHERE sid IN (27, 21) AND type = 'TM'
 
ADDR     KADDR    SID TYPE ID1   ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- ---- ----- --- ----- ------- ----- -----
296DAA90 296DAAA8 27  TM   13986 0   3     0       15    0    
296DAB54 296DAB6C 27  TM   13988 0   3     5       162   0    
296DAC18 296DAC30 21  TM   13986 0   2     0       120   0    
296DACDC 296DACF4 21  TM   13988 0   3     0       120   1    
 
Выбрано: 4 строки

И действительно, мы видим, что первый сеанс преобразовал TM-блокировку для таблицы t6 из разделяемого строчного режима  (LMODE=2 - SS) в монопольный строчной режим (LMODE=3 - SX), что говорит о фактическом удалении строки из родительской таблицы t6. В тоже время, мы наблюдаем ожидающий запрос на установку TM-блокировки в разделяемом монопольно - строчном режиме (REQUEST=5 - SSX) на таблицу t7. Не забываем, что внешний ключ у нас создан с опцией ON DELETE CASCADE, поэтому данная блокировка всегда накладывается на дочернюю таблицу при каскадном удалении из неё строк, что фактически не позволяет совершать какие-либо действия над данными этой таблицы, кроме текущей транзакции.

В нашем случае, чтобы выполнить каскадное удаление в дочерней таблице, ожидающий запрос в первом сеансе пытается преобразовать TM-блокировку на дочернюю таблицу t7 из ранее установленного монопольно-строчного режима (SX) в  разделяемый монопольно-строчный режим (SSX). Но второй сеанс ранее уже выставил на данную таблицу  блокировку в монопольно-строчном режиме (SX), и это препятствует преобразованию блокировок. Данная ситуация чётко прослеживается в представлении v$lock, где мы видим,  что столбец BLOCK установившейся TM-блокировки второго сеанса содержит значение 1. Это явно указывает на то, что каскадное удаление ожидает освобожденим именно этой блокировки.

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

ZH@XE(21)> DELETE FROM t6 WHERE c1 = 3;
 
Удалено: 1 строка

Возникло бесконечное ожидание, что привело к ошибке взаимной блокировки в первом сеансе:

ZH@XE(27)> DELETE FROM t6 WHERE c1 = 2;
 
DELETE FROM t6 WHERE c1 = 2
            *
Ошибка в строке 1:
ORA-00060: deadlock detected while waiting for resource

Почему произошло взаимное блокирование? Ведь ссылающих записей в дочерней таблице нет, и, следовательно, второй сеанс не мог осуществлять каскадное удаление строк в дочерней таблице. Единственное, чем можно было бы объяснить такое поведение это то, что когда происходит удаление строки из  родительской таблицы, оператор удаления применяется также и  к дочерней таблице, причем независимо от того, есть ли там что удалять.  В нашем случае второй сеанс, пытаясь удалить третью строку родительской таблицы t6,  столкнулся с ситуацией, когда он не смог установить блокировку в разделяемом монопольно-строчном режиме (SSX) на дочернюю таблицу t7, так как на неё ранее уже была выставлена TM-блокировка в SX режиме в первом сеансе. В тоже время, первый сеанс уже ожидал преобразования этой TM-блокировки, и поэтому он никогда не смог бы самостоятельно освободить её. Таким образом, у нас возникла ситуация взаимного блокирования между двумя сеансами. Чтобы визуально её представить, построим схему графа ожидающих транзакций:

В качестве вершин ресурсов в графе выступают блокировки таблицы TM. Красными стрелками  отображен цикл ожиданий или взаимная блокировка. Сплошные линии показывают рёбра, образованные в результате выполнения SQL операторов. Пунктирные стрелки отображают рёбра, возникающие в результате выполнения правил ссылочной целостности внешнего ключа. Как видно из графа, в процесс взаимной блокировки вовлечён всего лишь один ресурс – это блокировка TM дочерней таблицы t7. И это не ошибка. В этом мы убедимся, когда будем рассматривать содержимое трассировочного файла взаимной блокировки. Что мы сейчас и сделаем.

В первой секции файла мы видим отменённый оператор удаления:

Current SQL statement for this session:
DELETE FROM t6 WHERE c1 = 2

Содержимое графа кардинально изменилось по сравнению с предыдущими сценариями взаимной блокировки:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-000036a4-00000000        22      27    SX   SSX       26      21    SX   SSX
TM-000036a4-00000000        26      21    SX   SSX       22      27    SX   SSX

В первом столбце мы наблюдаем ресурсы, участвующие во  взаимном блокировании. В нашем случае это только один ресурс, представляющий собой TM-блокировку. Имя ресурса состоит из буквенного идентификатора TM и шестнадцатеричного значения номера объекта, то есть таблицы. Переведя это значение в десятичный вид, мы увидим, что в нашем случае это будет таблица t7. Далее видно, что в столбцах hold и waits присутствуют символьные обозначения режимов блокировок SX и SSX. Эти режимы встречались нам ранее при разборе ситуации ожидания. Наличие их одновременно в столбцах holds и waits не должно нас смущать. В принципе, это только обозначает, что сеанс пытается преобразовать режим ранее установленной блокировки, отображённый в столбце holds, в режим, указанный в столбце waits.

Граф читается следующим образом. Сеанс 27 выставил TM-блокировку в SX-режиме и пытается преобразовать её в SSX-режим, чтобы выполнить каскадное удаление. Его ожидает сеанс 21, который выставил ранее TM-блокировку в SX-режиме и также пытается преобразовать её в SSX-режим. Во второй строке графа ситуация повторяется с точностью наоборот, здесь в качестве ожидающего уже будет сеанс 27. Преобразования блокировок в нашем случае невозможны из-за того,  что каждый конкурирующий сеанс уже держит на таблице  блокировку TM в SX-режиме.

Так как TM-блокировка – это блокировка таблицы,  а не строк,  секция Rows waited on в файле трассировки всегда будет пустой, и поэтому на неё не стоит даже обращать внимания:

Rows waited on:
Session 21: no row
Session 27: no row

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

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

Вставка методом прямой загрузки

Эта возможность образования взаимного блокирования встречается в Oracle довольно редко в виду специфики режима команд, применяемых при её возникновении. Если  два или большее число сеансов пытаются осуществить прямые вставки в таблицы, то они могут создать такую ситуацию, при которой они будут бесконечно ожидать друг друга. Отличие этого сценария взаимного блокирования от сценариев с транзакционной блокировкой заключается в особом режиме применения команды вставки INSERT. При указании подсказки /*+ APPEND */ и использовании подзапроса команда начинает вставлять строки непосредственно в файлы данных, минуя кеш. При этом выполняется монопольная блокировка таблицы, которая иногда и приводит к взаимной блокировке сеансов.

Попробуем смоделировать этот сценарий взаимного блокирования. Для начала создадим две таблицы t8 и t9:

ZH@XE> CREATE TABLE t8 (c1 NUMBER PRIMARY KEY, c2 VARCHAR2(50));
 
Таблица создана

ZH@XE> CREATE TABLE t9 (c1 NUMBER PRIMARY KEY, c2 VARCHAR2(50));
 
Таблица создана

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

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

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

ZH@XE(24)> INSERT /*+ APPEND */ INTO t8 SELECT 1, 'Строка1' FROM dual;
 
Вставлено: 1 строка

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

ZH@XE(23)> INSERT /*+ APPEND */ INTO t9 SELECT 1, 'Строка1' FROM dual;
 
Вставлено: 1 строка

Как было сказано выше, выполнение команд прямой вставки должно приводить к монопольному блокированию таблицы.  Следовательно, сейчас у нас должно быть в каждом из сеансов выставлено по одной TM-блокировке в монопольном режиме. Проверим, так ли это на самом деле, обратившись к представлению V$lock:

SYSTEM@XE> SELECT * FROM v$lock WHERE sid IN (24, 23) AND type = 'TM'
 
ADDR     KADDR    SID TYPE ID1   ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- ---- ----- --- ----- ------- ----- -----
296DAA90 296DAAA8 24  TM   14016 0   6     0       81    0    
296DAB54 296DAB6C 23  TM   14018 0   6     0       42    0    
 
Выбрано: 2 строки

Действительно, в представлении мы видим две блокировки  TM в монопольном режиме (LMODE = 6 - X).  Таблицы t8 и t9 оказались полностью заблокированы для изменений структуры и данных другими сеансами. Теперь выполнение любой команды, изменяющей данные в этих таблицах, должно привести к ожиданию. Попробуем снова осуществить прямую загрузку в первом сеансе, только уже для таблицы t9:

ZH@XE(24)> INSERT /*+ APPEND */ INTO t9 SELECT 2, 'Строка2' FROM dual;
 

Так и есть, возникло ожидание. Заглянем в трассировочный файл:

WAIT #2: nam='enq: TM - contention' ela= 2999757 name|mode=1414332422 object #=14018 table/partition=0 obj#=14017 tim=11169951762

В первом сеансе постоянно возникает ожидание «Конкуренция TM блокировки». Сеанс ждёт освобождения блокировки.  Чтобы убедиться в этом, заглянем в представление v$lock:

SYSTEM@XE(35)> SELECT * FROM v$lock WHERE sid IN (24, 23) AND type = 'TM'
 
ADDR     KADDR    SID TYPE ID1   ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- ---- ----- --- ----- ------- ----- -----
296DAA90 296DAAA8 24  TM   14016 0   6     0       132   0    
296DAB54 296DAB6C 23  TM   14018 0   6     0       93    1    
296DAC18 296DAC30 24  TM   14018 0   0     6       15    0    
 
Выбрано: 3 строки

Из содержимого представления видно, что первый сеанс пытается установить TM-блокировку в монопольном режиме (REQUEST = 6) на таблицу t9. Но так как данная таблица была уже заблокирована ранее вторым сеансом, это привело  к ожиданию. Если теперь второй сеанс попытается осуществить прямую загрузку в таблицу t8, то  возникнет бесконечное ожидание, так как таблица t8 уже заблокирована  ожидающим первым сеансом:

ZH@XE(23)> INSERT /*+ APPEND */ INTO t8 SELECT 2, 'Строка2' FROM dual;

Так и есть, возникло  взаимное блокирование, и в первом сеансе происходит исключение:

ZH@XE(24)> INSERT /*+ APPEND */ INTO t9 SELECT 2, 'Строка2' FROM dual;
 
INSERT /*+ APPEND */ INTO t9 SELECT 2, 'Строка2' FROM dual;
                          *
Ошибка в строке 1:
ORA-00060: deadlock detected while waiting for resource

Посмотрим, что нам покажет трассировочный файл взаимной блокировки. В первой секции отменённый оператор вставки первого сеанса:

Current SQL statement for this session:
INSERT /*+ APPEND */ INTO t9 SELECT 2, 'Строка2' FROM dual

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

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-000036c0-00000000        26      24     X             27      23           X
TM-000036c2-00000000        27      23     X             26      24           X

В качестве ресурсов графа выступают TM-блокировки, выставленные на таблицы t8 и t9. Столбцы holds и waits содержат символьное значение X. Этот символ соответствует монопольному режиму установленной или ожидающей TM-блокировки. В остальном  граф читается стандартным образом, поэтому не будем заострять на этом внимание и сразу обратимся к секции ожидающих строк. В отличие от предыдущего сценария с TM-блокировками, эта секция не пуста:

Rows waited on:
Session 23: obj - rowid = 000036C2 - AAADbMAAEAAAAJBAAA
  (dictionary objn - 14018, file - 4, block - 577, slot - 0)
Session 24: obj - rowid = 000036C1 - AAADbKAAEAAAAI8AAA
  (dictionary objn - 14017, file - 4, block - 572, slot - 0)

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

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

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