Базы данных Oracle - статьи

         

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


Для начала образуем два сеанса. Очистим таблицу 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, как указано у Тома Кайта.   


Содержание раздела