Может ли возникнуть тупик с тем же методом доступа?

Возможно ли возникновение взаимоблокировки, если два параллельных оператора DML изменяют одни и те же данные и используют один и тот же метод доступа?

Основываясь на моих тестах и ​​моих догадках о том, как работает Oracle, ответ - нет.

Но я хочу быть на 100% уверенным. Я ищу официальный источник, который говорит, что взаимоблокировки не могут произойти таким образом, или тестовый пример, демонстрирующий, что взаимоблокировки могут произойти таким образом.

Другой способ задать этот вопрос: будет ли Oracle всегда возвращать результаты в том же порядке, если используется тот же метод доступа? (И никаких изменений данных между прогонами.)

Например, если запрос использует полное сканирование таблицы и возвращает строки в порядке 4/3/2/1, он всегда будет возвращать строки в этом порядке? И если сканирование диапазона индексов возвращает строки в порядке 1/2/3/4, всегда ли они возвращают строки в этом порядке? Не имеет значения, каков фактический порядок, просто порядок детерминирован.

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

UPDATE

Мой оригинальный вопрос был несколько общим. Меня больше всего интересует, возможно ли запускать что-то вроде update table_without_index set a = -1 в двух разных сеансах одновременно и получать блокировку? (Я спрашиваю об одном обновлении, а не о серии обновлений.)

Во-первых, позвольте мне показать, что то же самое выражение может вызвать тупик.

Создать таблицу, индекс и некоторые данные:

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

Обратите внимание, что я создаю таблицу с pctfree 0, а обновленные значения занимают значительно больше места, поэтому будет много миграции строк. (Это ответ на ответ @Tony Andrew, хотя я боюсь, что мой тест может быть слишком упрощенным.Кроме того, я не думаю, что нам нужно беспокоиться о вставке строк между обновлениями, только одно из обновлений увидит новую строку так, это не вызовет тупик. Если новая строка не сдвинет кучу других вещей.)

drop table deadlock_test purge;
create table deadlock_test(a number) pctfree 0;
create index deadlock_test_index on deadlock_test(a);
insert into deadlock_test select 2 from dual connect by level <= 10000;
insert into deadlock_test select 1 from dual connect by level <= 10000;
commit;

Запустить этот блок в сеансе 1:

begin
 while true loop
 update deadlock_test set a = -99999999999999999999 where a > 0;
 rollback;
 end loop;
end;
/

Запустить этот блок в сеансе 2:

--First, influence the optimizer so it will choose an index range scan.
--This is not gaurenteed to work for every environment. You may need to 
--change other settings for Oracle to choose the index over the table scan.
alter session set optimizer_index_cost_adj = 1;
begin
 while true loop
 update deadlock_test set a = -99999999999999999999 where a > 0;
 rollback;
 end loop;
end;
/

Через несколько секунд один из этих сеансов выдает ORA-00060: deadlock detected while waiting for resource. Это связано с тем, что один и тот же запрос блокирует строки в другом порядке в каждом сеансе.

Исключая приведенный выше сценарий, может возникнуть тупик?

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

Насколько я могу судить, если вы удалите optimizer_index_cost_adj или что-нибудь еще, что изменит план, код никогда не вызовет тупик. (Я запускаю код некоторое время, без ошибок.)

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

Может ли кто-нибудь создать тестовый пример, когда один оператор обновления, выполняющийся одновременно и используя тот же план, вызывает тупик?

2 ответа

"Порядок" детерминирован с вашей точки зрения, только если вы включите ORDER BY в свой запрос. Независимо от того, является ли он детерминированным с точки зрения сервера, это деталь реализации, на которую нельзя полагаться.

Что касается блокировки, два идентичных оператора DML могут блокировать (но не взаимоблокировать) друг друга. Например:

CREATE TABLE THE_TABLE (
 ID INT PRIMARY KEY
);

Транзакция A:

INSERT INTO THE_TABLE VALUES(1);

Транзакция B:

INSERT INTO THE_TABLE VALUES(1);

В этот момент транзакция B остановлена ​​, пока транзакция А не совершит или не вернется. Если A совершает ошибку, B не работает из-за нарушения PRIMARY KEY. Если A откат, B преуспевает.

Аналогичные примеры могут быть созданы для UPDATE и DELETE.

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

Что касается dead -locks, их можно достичь с помощью нескольких операторов. Например:

A: INSERT INTO THE_TABLE VALUES(1);
B: INSERT INTO THE_TABLE VALUES(2);
A: INSERT INTO THE_TABLE VALUES(2);
B: INSERT INTO THE_TABLE VALUES(1); -- SQL Error: ORA-00060: deadlock detected while waiting for resource

Или, возможно, с операторами, которые изменяют несколько строк в другом порядке и некоторые очень неудачные сроки (может ли кто-нибудь подтвердить это?).

--- UPDATE ---

В ответ на обновление вашего вопроса позвольте мне сделать общее замечание: если одновременные потоки выполнения блокируют объекты в последовательном порядке, взаимоблокировки невозможны. Это верно для любого вида блокировки, будь то мьютексы в вашей средней многопоточной программе (например, см. мысли Herb Sutter об иерархиях блокировок) или это базы данных. После того, как вы измените порядок таким образом, что любые две блокировки "перевернуты", будет введен потенциал тупиков.

Без сканирования индекса вы обновляете строки (и блокировки) в одном порядке, а индекс - в другом. Итак, это, вероятно, происходит в вашем случае:

  • Если вы отключите сканирование индексов для обеих одновременных транзакций, они оба фиксируют строки в том же порядке [X], поэтому не существует взаимоблокировки.
  • Если вы включите сканирование индексов только для одной транзакции, они больше не блокируют строки в одном порядке, следовательно, могут возникнуть взаимоблокировки.
  • Если вы включаете сканирование индексов для обеих транзакций, то снова оба они блокируют строки в одном порядке, а тупик невозможен (попробуйте alter session set optimizer_index_cost_adj = 1; в обоих сеансах, и вы увидите).

[X] Хотя я бы не стал полагаться на полное сканирование таблицы с гарантированным заказом - возможно, именно так работает текущий Oracle в этих конкретных обстоятельствах, а некоторые будущие Oracle или разные обстоятельства могут приводить к другому поведению.

Таким образом, наличие индекса является случайным - реальная проблема заключается в упорядочении. Как раз так бывает, что на заказ в UPDATE может влиять индекс, но если бы мы могли повлиять на упорядочение по-другому, мы получим аналогичные результаты.

Так как UPDATE не имеет ORDER BY, вы не можете гарантировать порядок блокировки только UPDATE. Однако, если вы отделите блокировку от обновления, вы можете гарантировать порядок блокировки:

SELECT ... ORDER BY ... FOR UPDATE;

Хотя ваш исходный код вызвал взаимоблокировки в моей среде Oracle 10, следующий код не делает:

Сессия 1:

declare
 cursor cur is select * from deadlock_test where a > 0 order by a for update;
begin
 while true loop
 for locked_row in cur loop
 update deadlock_test set a = -99999999999999999999 where current of cur;
 end loop;
 rollback;
 end loop;
end;
/

Сессия 2:

alter session set optimizer_index_cost_adj = 1;
declare
 cursor cur is select * from deadlock_test where a > 0 order by a for update;
begin
 while true loop
 for locked_row in cur loop
 update deadlock_test set a = -99999999999999999999 where current of cur;
 end loop;
 rollback;
 end loop;
end;
/


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

licensed under cc by-sa 3.0 with attribution.