Powiedzmy, że wykonamy ...

SELECT * FROM MY_TABLE FOR UPDATE

... i jest więcej niż jeden wiersz w MY_TABLE.

Teoretycznie, jeśli dwarównoległy transakcje wykonują tę instrukcję, ale zdarza się, że przechodzą (i dlatego blokują) wiersze w innej kolejności, może wystąpić zakleszczenie. Na przykład:

Transakcja 1: Blokuje wiersz A.Transakcja 2: Blokuje wiersz B.Transakcja 1: Próbuje zablokować wiersz B i bloki.Transakcja 2: Próby zablokowania wiersza A i zakleszczeń.

Aby rozwiązać ten problem, użyj ORDER BY, aby upewnić się, że wiersze są zawsze zablokowane w tej samej kolejności.

Więc moje pytanie brzmi: czy ten teoretyczny impas kiedykolwiek wystąpi w praktyce? Wiem, że są sposobysztucznie go wywołać, ale czy kiedykolwiek zdarzyło się to podczas normalnej pracy? Czy powinniśmy zawsze używać ORDER BY, czy też można bezpiecznie to pominąć?

Interesuje mnie przede wszystkim zachowanie Oracle i MySQL / InnoDB, ale pomocne będą również komentarze do innych DBMS.

--- EDYTOWAĆ ---

Oto jak odtworzyć zakleszczenie w Oracle, gdy kolejność blokowania nie jest taka sama:

Utwórz tabelę testową i wypełnij ją danymi testowymi ...

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

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

COMMIT;

... z jednej sesji klienta (użyłem SQL Developer), uruchom następujący blok:

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

Odróżne sesja klienta (po prostu uruchomiłem jeszcze jedną instancję SQL Developer), uruchom ten sam blok, ale zDESC wORDER BY. Po kilku sekundach otrzymasz:

ORA-00060: deadlock detected while waiting for resource

BTW, prawdopodobnie osiągniesz ten sam wynik, całkowicie usuwającORDER BY (więc oba bloki są identyczne) i dodanie ...

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 1;

... przed jednym blokiem, ale ...

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10000;

... przed drugim (więc Oracle wybiera różne plany wykonania i prawdopodobnie pobiera wiersze w innej kolejności).

Pokazuje to, że blokowanie jest rzeczywiście wykonywane, ponieważ wiersze są pobierane z kursora (a nie dla całego zestawu wyników na raz, gdy kursor jest otwarty).