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

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

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

Сценарии возникновения

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

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

Рассмотрим первый  сценарий взаимного блокирования. Необходимым условием  для его возникновения является наличие в двух разных сеансах установленной  и ожидающей блокировок транзакций(TX) в исключительном режиме. Моделировать ситуацию будем с использованием редакции Oracle Express Edition и инструмента администратора DBASQL for Oracle.

Механизм взаимоблокировки

Вначале создадим тестового пользователя zh и выдадим ему все необходимые привилегии:

 Подключение к:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production

SYSTEM@XE> CREATE USER zh IDENTIFIED BY test DEFAULT TABLESPACE users;
 
Пользователь создан

SYSTEM@XE> GRANT connect, resource, alter session TO zh;
 
Grant succeeded

Далее создадим простую таблицу  и вставим в неё две строки:

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

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

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

ZH@XE> COMMIT;
 
Commit complete

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

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

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

ZH@XE(28)> UPDATE t1 SET c2 = 'Строка1' WHERE c1 = 1;
 
Изменено: 1 строка

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

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

В результате выполненных нами действий в существующих сеансах были открыты две транзакции. В первом сеансе была выставлена блокировка транзакции (TX) на первую строку в исключительном режиме. Такая же блокировка выставлена и во втором сеансе, но на вторую строку.  Убедиться в этом мы можем, сделав небольшой запрос к системному представлению v$lock:

SYSTEM@XE> SELECT * FROM v$lock WHERE sid IN (24, 28) AND type = 'TX';
 
ADDR     KADDR    SID TYPE ID1    ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- ---- ------ --- ----- ------- ----- -----
296EE5D8 296EE6F4 28  TX   262166 169 6     0       1110  0   
296FA680 296FA79C 24  TX   327718 163 6     0       879   0   

Рассмотрим более подробно содержимое этого запроса. Столбец SID здесь содержит идентификаторы первого и второго сеансов (28 и 24). Содержимое столбца TYPE указывает на тип блокировки, в нашем случае блокировки транзакции (TX). Столбец LMODE хранит значение 6, что соответствует установившемуся исключительному режиму блокировки.

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

SYSTEM@XE> SELECT sid, TRUNC(id1/POWER(2,16)) rbs, BITAND(id1, POWER(2,16)-1)+ 0 slot, id2 seq FROM v$lock WHERE sid IN (24, 28) AND type = 'TX';
 
SID RBS SLOT SEQ
--- --- ---- ---
28  4   22   169
24  5   38   163
 
Выбрано: 2 строки

Расшифровав значения столбцов ID1 и ID2, мы получили номер сегмента отката, слот и  номер изменения транзакции. Эти значения полностью совпадают со значениями из представления v$transaction и все вместе представляют собой идентификатор транзакции в шестнадцатеричном виде:

SYSTEM@XE> SELECT s.sid, t.xidusn, xidslot, xidsqn FROM v$transaction t, v$session s  WHERE t.addr = s.taddr;
 
SID XIDUSN XIDSLOT XIDSQN
--- ------ ------- ------
24  5      38      163  
28  4      22      169  
 
Выбрано: 2 строки

Данный идентификатор нам ещё встретится в дальнейшем, когда мы будем разбирать содержимое трассировочного файла взаимной блокировки, а пока продолжим последовательность начатых нами действий и изменим в первом сеансе содержимое второй строки:

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

Ожидание …

Сеанс находиться в ожидании. Оно возникло от того, что первый сеанс пытается установить TX-блокировку в исключительном режиме второй строки, которая уже захвачена TX-блокировкой второго сеанса. Если в это время заглянуть в файл трассировки первого сеанса, то мы увидим там следующие строки:

WAIT #1: nam='enq: TX - row lock contention' ela= 3000022 name|mode=1415053318 usn<<16 | slot=327718 sequence=163 obj#=13766 tim=14923101611

В сеансе постоянно возникает ожидание “конкуренция блокировки строки”. В параметрах этого ожидания  мы видим  уже знакомые нам значения идентификатора транзакции второго сеанса (slot, sequence). Именно эта транзакция установила ранее TX-блокировку второй строки в исключительном режиме и привела к ожиданию. Более детально это можно просмотреть в содержимом представления v$lock:

SYSTEM@XE> SELECT * FROM v$lock WHERE sid IN (24, 28) AND type = 'TX';
 
ADDR     KADDR    SID TYPE ID1    ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- ---- ------ --- ----- ------- ----- -----
2A201720 2A201734 28  TX   327718 163 0     6       1074  0   
296EE5D8 296EE6F4 28  TX   262166 169 6     0       7048  0   
296FA680 296FA79C 24  TX   327718 163 6     0       6817  1   
 
Выбрано: 3 строки

Как мы видим, в представлении появилась новая строка о TX-блокировке со значением 6 в поле REQUEST.  Данное значение означает, что сеанс 28 запросил установку TX-блокировки строки  в исключительном режиме. При этом значения столбцов ID1 и ID2 этого запроса содержат идентификатор транзакции сеанса 24. Это свидетельствует о том, что первый сеанс ожидает освобождения строки, захваченной транзакцией именно второго сеанса.

Итак, на данный момент мы имеем классическую картину ожидания. Но что произойдет, если мы изменим во втором сеансе первую строку? Ведь она уже захвачена первым сеансом:

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

Ожидание…

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

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

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

SYSTEM@XE> SELECT s.sid, t.status, t.start_time, t.xid  FROM v$transaction t, v$session s  WHERE t.addr = s.taddr;
 
SID STATUS START_TIME        XID            
--- ------ ----------------- ----------------
24  ACTIVE 01/21/10 23:14:40 05002600A3000000
28  ACTIVE 01/21/10 23:10:49 04001600A9000000

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

Как видно из результатов запроса, транзакции по-прежнему активны. Отменять их полностью у Oracle нет необходимости, достаточно лишь просто вернуться в одном из сеансов к неявной точке сохранения, которая делается перед каждым DML-оператором.

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

Что же касается самой Oracle, то в результате всех перечисленных выше действий, приведших к возникновению взаимной блокировки, в файл журнала alert_xe.log будет занесена запись следующего вида:

Fri Jan 22 01:09:58 2010
ORA-00060: Deadlock detected. More info in file c:\oraclexe\app\oracle\admin\xe\udump\xe_ora_2480.trc.

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

Трассировочный файл взаимной блокировки

Сам файл трассировки содержит множество данных; в том числе, здесь находится и полный дамп состояния процессов Oracle на момент возникновения взаимной блокировки. Но нам важны только несколько секций файла. Первая из них – это текущий SQL-оператор сеанса, который столкнулся с ошибкой взаимной блокировки и был отменён. Для этого находим в файле строку DEADLOCK DETECTED. Чуть ниже неё, после ключевых слов «Current SQL statement for this session»  будет находиться необходимая нам секция:

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

Вторая секция, которая нас заинтересует – это граф взаимной блокировки. Он находится после ключевой строки Deadlock graph и отображает цепочку захватов и ожиданий блокировок между сеансами:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00040016-000000a9        20      28     X             21      24           X
TX-00050026-000000a3        21      24     X             20      28           X

Вспомним, как Oracle обнаруживает блокировки. Для этого он постоянно строит граф ожидания транзакций. Если в этом графе обнаружен цикл, то это означает, что возникла взаимная блокировка. Так вот именно этот цикл и отображается в секции Deadlock graph, правда в очень специфическом виде.

Визуально граф представлен в виде таблицы, которая разделена на три логических блока. Первый блок обозначает ресурсы, участвующие во взаимной блокировке. В нашем случае таким ресурсом является блокировка транзакции. Ресурс  обозначается символьным кодом TX, после которого идет идентификатор транзакции в шестнадцатеричном виде. Именно этот идентификатор, но только в десятичном виде мы получили, когда расшифровывали значения столбцов ID1 и ID2 представления v$lock. Второй блок состоит из столбцов, содержащих информацию о сеансах, удерживающих данную блокировку, а также режиме, в котором эта блокировка установлена. И наконец, третий блок аналогичен второму, но противоположен по содержанию. Он хранит информацию о сеансах,  которые сделали запрос на установление блокировки, но вынуждены ожидать освобождения блокировки из второго блока.

Расшифровка графа не представляет сложности. Для этого нам надо проанализировать содержимое таблицы построчно, слева направо. К примеру, для нашего случая это будет выглядеть следующим образом. Транзакционная блокировка TX-00040016-000000a9 на строку удерживается сеансом 28 (поле session) в исключительном режиме (символ X в поле holds).  Сеанс 24 одновременно ждёт освобождения этого ресурса, чтобы установить свою TX-блокировку в исключительном режиме (символ X в поле waits).

Пока это нормальное ожидание необходимого ресурса. Поэтому далее мы обратимся ко второй строке графа. Здесь транзакционная блокировка TX-00050026-000000a3 на строку удерживается сеансом 24 в исключительном режиме, а сеанс 28 ждёт освобождение строки, чтобы установить свою TX-блокировку в исключительном режиме. В то же время в первой строке графа сеанс 24 уже ожидает освобождения ресурса, в результате чего получается, что сеансы находятся в состоянии бесконечного ожидания. Единственным логичным действием в этом  случае явилась бы отмена ожидания установки блокировки в сеансе 28, что собственно и было сделано Oracle. В графе такое отменённое ожидание всегда отображается последним в блоке Waiter(s).

Итак, граф расшифрован. Он дал нам описание цепочки захватов и ожиданий TX блокировок в сеансах. Но по этой цепочке мы можем судить только об общей картине возникновения взаимной блокировки. Если же нам потребуется найти конкретные ресурсы, из-за которых возникают ожидания, сделать нам это будет затруднительно. К счастью Oracle сам позаботился об этом, записав в файл трассировки информацию о строках, освобождения которых от TX-блокировок ожидают сеансы. Рассмотрим более подробно эту секцию. Найти её можно сразу после графа, по ключевой строке Rows waited on:

Rows waited on:
Session 24: obj - rowid = 000035C6 - AAADXGAAEAAAAFkAAA
  (dictionary objn - 13766, file - 4, block - 356, slot - 0)
Session 28: obj - rowid = 000035C6 - AAADXGAAEAAAAFkAAB
  (dictionary objn - 13766, file - 4, block - 356, slot - 1)

В этой секции для каждого ожидающего сеанса, который перечислен в графе, указана строка, TX-блокировку которой пытается получить этот сеанс. Строка идентифицируется номером объекта, которому она принадлежит, и идентификатором ROWID. Чуть ниже дана их полная расшифровка в десятичном виде. Это позволяет с лёгкостью, обратившись, например, к системному представлению dba_objects, идентифицировать объект, которому принадлежит данная строка:

SYS@XE> SELECT owner, object_name FROM dba_objects WHERE object_id = 13766;
 
OWNER OBJECT_NAME
----- -----------
ZH    T1 

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

Information on the OTHER waiting sessions:
Session 24:
  pid=21 serial=48 audsid=141 user: 39/ZH
  O/S info: user: ALFA\Сергей, term: ALFA, ospid: 1984:2524, machine:
            program: DBASQL.exe
  client info: DBASQL
  application name: DBASQL.exe, hash value=0
  Current SQL Statement:
  UPDATE t1 SET c2 = 'Строка1' WHERE c1 = 1
End of information on OTHER waiting sessions.

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

Как определить по содержимому трассировочного файла, что возник первый сценарий взаимного блокирования? Для ответа на этот вопрос обратимся в первую очередь к графу взаимной блокировки. Для начала мы должны определить, с какого идентификатора начинаются имена ресурсов графа в столбце «Resource Name». В нашем случае это всегда будет идентификатор TX, то есть блокировка транзакции. Далее нам следует проверить значения режимов блокировок, отображаемые в столбцах holds и waits. Они должны иметь одинаковое значение, равное символу X. Не следует также забывать, что данный сценарий взаимного блокирования возникает на уровне строк, и, следовательно, в секции «Rows waited» всегда будут присутствовать данные об ожидающих строках. Отсюда следует непреложное правило о том, что в первой секции «Current SQL statement for this session» при данном сценарии вы никогда не встретите оператора INSERT, так как строки, вставленные в одном из сеансов, никогда не будут доступны для другого сеанса до фиксации транзакции.

Выводы

Пришло время обобщить информацию, которую мы получили, изучая взаимную блокировку, образующуюся по первому сценарию. Обычно такая взаимная блокировка возникает из-за неодинаковой последовательности обработки строк в пределах транзакций разных сеансов. Если бы мы, к примеру, в нашем случае проводили бы обновление строк таблицы в каждом из сеансов последовательно в соответствии со значением ключевого столбца «c1», то мы могли бы избежать взаимных блокировок. Образовалась бы простая очередь ожидания ресурса. Правда, бывают ситуации, когда одинаковую последовательность обработки строк в разных сеансах сделать трудно или даже невозможно. Как тогда выходить из ситуации?

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

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