Vamos dizer que nós executamos ...

SELECT * FROM MY_TABLE FOR UPDATE

... e há mais de uma linha em MY_TABLE.

Teoricamente, se doisconcorrente transações executam esta declaração, mas acontece de atravessar (e, portanto, bloquear) as linhas em ordem diferente, um deadlock pode ocorrer. Por exemplo:

Transação 1: bloqueia a linha A.Transação 2: bloqueia a linha B.Transação 1: tenta bloquear a linha B e bloqueia.Transação 2: tenta bloquear a linha A e os deadlocks.

A maneira de resolver isso é usar ORDER BY para garantir que as linhas estejam sempre bloqueadas na mesma ordem.

Então, minha pergunta é: esse impasse teórico ocorrerá na prática? Eu sei que existem maneiras deinduzir artificialmente, mas poderia ocorrer na operação normal? Deveríamos usar o ORDER BY, ou é seguro omiti-lo?

Estou principalmente interessado no comportamento do Oracle e do MySQL / InnoDB, mas comentários em outros DBMSs também seriam úteis.

--- EDIT ---

Aqui está como reproduzir um impasse sob o Oracle quando a ordem de bloqueio não é a mesma:

Crie a tabela de teste e preencha com alguns dados de teste ...

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

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

COMMIT;

... de uma sessão do cliente (usei o SQL Developer), execute o seguinte bloco:

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;
/

A partir de umdiferente sessão do cliente (eu simplesmente iniciei mais uma instância do SQL Developer), executei esse mesmo bloco, mas comDESC noORDER BY. Depois de alguns segundos, você receberá:

ORA-00060: deadlock detected while waiting for resource

BTW, você provavelmente vai conseguir o mesmo resultado, removendo completamente oORDER BY (assim ambos os blocos são idênticos), e adicionando o ...

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 1;

... na frente de um bloco, mas ...

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10000;

... na frente do outro (assim, o Oracle escolhe diferentes planos de execução e provavelmente busca as linhas em ordem diferente).

Isso ilustra que o bloqueio é realmente feito quando as linhas são buscadas no cursor (e não para o conjunto de resultados inteiro de uma vez quando o cursor é aberto).