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

SELECT * FROM MY_TABLE FOR UPDATE

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

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

Transaction 1: Locks row A. Transaction 2: Locks row B. Transaction 1: Attempts to lock row B and blocks. Transaction 2: Attempts to lock row A and deadlocks.

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

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

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

--- EDIT ---

Вот как воспроизвести тупик под 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;
/

Изdifferent сеанс клиента (я просто запустил еще один экземпляр 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 выбирает разные планы выполнения и, вероятно, выбирает строки в другом порядке).

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