Вопросы Теги

Должен ли SELECT … FOR UPDATE всегда содержать ORDER BY?

Допустим, мы выполняем...

SELECT * FROM MY_TABLE FOR UPDATE

... и в MY _TABLE более одной строки.

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

  • Транзакция 1 :Блокирует строку A.
  • Транзакция 2 :Блокирует строку B.
  • Транзакция 1 :Попытка заблокировать строку B и блоки.
  • Транзакция 2 :Попытки заблокировать строку A и взаимоблокировки.

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

Итак, мой вопрос: :возникнет ли когда-нибудь этот теоретический тупик на практике? Я знаю, что есть способы искусственно вызвать его , но могло ли это когда-нибудь произойти при нормальной работе? Должны ли мы просто всегда использовать ORDER BY, или на самом деле безопасно его опустить?

Меня в первую очередь интересует поведение Oracle и MySQL/InnoDB, но комментарии по другим СУБД также будут полезны.

---РЕДАКТИРОВАТЬ---

Вот как воспроизвести взаимоблокировку в Oracle, когда порядок блокировки не тот же:

Создайте тестовую таблицу и заполните ее тестовыми данными...

CREATE TABLE DEADLOCK_TEST (
    ID INT PRIMARY KEY,
    A INT 
);

INSERT INTO DEADLOCK_TEST SELECT LEVEL, 1 FROM DUAL CONNECT BY LEVEL <= 10000;

COMMIT;

...из одного клиентского сеанса (я использовал SQL Developer ), запустил следующий блок:

DECLARE
    CURSOR CUR IS 
        SELECT * FROM DEADLOCK_TEST
        WHERE ID BETWEEN 1000 AND 2000 
        ORDER BY ID 
        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;
/

Из другого клиентского сеанса (я просто запустил еще один экземпляр SQL Developer ), запустив тот же блок, но с DESCв ORDER BY. Через несколько секунд вы получите:

ORA-00060: deadlock detected while waiting for resource

Кстати, вы, скорее всего, добьетесь того же результата, полностью удаливORDER BY(так что оба блока идентичны ), и добавление...

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 1;

...перед одним кварталом, но...

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10000;

... перед другим (, поэтому Oracle выбирает разные планы выполнения и, вероятно, извлекает строки в другом порядке ).

Это показывает, что блокировка действительно выполняется, когда строки извлекаются из курсора (, а не для всего набора результатов -сразу при открытии курсора ).

15
задан Community 23 May 2017 в 12:14
поделиться