Jak uniknąć "impasu mysql znalezionego podczas próby uzyskania blokady; spróbuj ponownie uruchomić transakcję"

Impas ma miejsce, gdy dwie transakcje czekają na siebie, aby uzyskać blokadę. Przykład:

• Tx 1: lock a, then B
• Tx 2: lock B, then a

Istnieje wiele pytań i odpowiedzi na temat impas. Za każdym razem, gdy wstawiasz/aktualizujesz / lub usuwasz wiersz, uzyskuje się blokadę. Aby uniknąć impasu, należy upewnić się, że równoległe transakcje nie aktualizują wiersza w kolejności, która może spowodować impas. Ogólnie rzecz biorąc, spróbuj zdobyć lock zawsze w tym samym order nawet w różnych transakcjach (np. zawsze najpierw tabela a, potem tabela B).

Innym powodem blokady w bazie danych mogą być brakujące indeksy . Gdy wiersz jest wstawiany/aktualizowany/usuwany, baza danych musi sprawdzić ograniczenia relacyjne, to znaczy upewnić się, że relacje są spójne. W tym celu baza danych musi sprawdzić klucze obce w powiązanych tabelach. To Może spowodować uzyskanie innego Locka niż wiersz, który jest modyfikowany. Upewnij się, że zawsze mieć indeks na kluczach obcych (i oczywiście klucze podstawowe), w przeciwnym razie może to spowodować blokadę tabeli zamiast blokadę wiersza. Jeśli dojdzie do zablokowania stołu, wartość blokady jest wyższa, a prawdopodobieństwo zablokowania wzrasta.

Jest prawdopodobne, że polecenie delete wpłynie na dużą część wszystkich wierszy w tabeli. W końcu może to doprowadzić do uzyskania blokady tabeli podczas usuwania. Trzymanie się blokady (w tym przypadku blokada wiersza lub strony) i uzyskanie większej liczby blokad jest zawsze ryzykiem impasu. Jednak nie mogę wyjaśnić, dlaczego instrukcja insert prowadzi do eskalacji blokady - może to mieć związek z dzieleniem/dodawaniem stron, ale ktoś znający lepiej MySQL będzie musiał tam wypełnić.

Na początek warto spróbować od razu uzyskać blokadę tabeli dla instrukcji delete. Zobacz blokowanie tabeli problemy z blokowaniem tabel .

Na wypadek, gdyby ktoś nadal zmagał się z tym problemem:

Napotkałem podobny problem, w którym 2 żądania trafiały na serwer w tym samym czasie. Nie było takiej sytuacji jak poniżej:

T1:
    BEGIN TRANSACTION
    INSERT TABLE A
    INSERT TABLE B
    END TRANSACTION

T2:
    BEGIN TRANSACTION
    INSERT TABLE B
    INSERT TABLE A
    END TRANSACTION

Potem okazało się, że nie było relacji rodzic dziecko statek między 2 tabel z powodu obcego klucza. Kiedy wstawiałem rekord w tabeli potomnej, transakcja uzyskiwała blokadę w wierszu tabeli nadrzędnej. Natychmiast po tym byłem próba aktualizacji nadrzędnego wiersza, który wyzwalał elewację blokady na wyłączną. Ponieważ druga jednoczesna transakcja już posiadała współdzieloną blokadę, powodowała ona impas.

Zobacz: https://blog.tekenlight.com/2019/02/21/database-deadlock-mysql.html

Możesz spróbować, aby to zadanie delete działało, najpierw wstawiając klucz każdego wiersza do usunięcia do tabeli tymczasowej, takiej jak ten pseudokod

create temporary table deletetemp (userid int);

insert into deletetemp (userid)
  select userid from onlineusers where datetime <= now - interval 900 second;

delete from onlineusers where userid in (select userid from deletetemp);

Łamanie go w ten sposób jest mniej efektywne, ale pozwala uniknąć konieczności trzymania zamka na klucz podczas delete.

Również zmodyfikuj swoje zapytania select, aby dodać klauzulę where z wyłączeniem wierszy starszych niż 900 sekund. Pozwala to uniknąć zależności od zadania cron i pozwala przełożyć go na uruchamianie rzadziej.

Teoria co do impasów: nie mam zbyt wiele tła w MySQL, ale tutaj idzie... delete będzie trzymać blokadę zakresu klawiszy dla datetime, aby zapobiec dodaniu wierszy pasujących do klauzuli where w środku transakcji, a gdy znajdzie wiersze do usunięcia, spróbuje uzyskać blokadę na każdej stronie, którą modyfikuje. insert uzyska blokadę na stronie, do której się wstawia, a następnie spróbuje uzyskać blokadę klucza. Normalnie {[7] } będzie cierpliwie czekać na blokada klawisza zostanie zablokowana, jeśli delete spróbuje zablokować tę samą stronę, której używa insert, ponieważdelete potrzebuje tej blokady, a insert tej blokady klawisza. Nie wydaje się to jednak właściwe dla wstawek, delete i insert używają zakresów datetime, które nie nakładają się na siebie, więc może dzieje się coś innego.

Http://dev.mysql.com/doc/refman/5.1/en/innodb-next-key-locking.html

Dla programistów Java używających Springa uniknąłem tego problemu, używając aspektu AOP, który automatycznie powtarza transakcje, które napotykają przejściowe impasy.

Zobacz @RetryTransaction Javadoc po więcej informacji.

Mam metodę, której wewnętrzne elementy są zawinięte w MySqlTransaction.

Problem impasu pojawił się dla mnie, gdy uruchomiłem tę samą metodę równolegle z sobą.

Nie wystąpił problem z uruchomieniem pojedynczej instancji metody.

Kiedy usunąłem MySqlTransaction, byłem w stanie uruchomić metodę równolegle z samym sobą bez żadnych problemów.

Dzieląc się swoim doświadczeniem, niczego nie popieram.

cron to niebezpieczne. Jeśli jedna instancja cron nie zakończy się przed następną, prawdopodobnie będą walczyć ze sobą.

Lepiej byłoby mieć stale uruchomione zadanie, które usuwałoby niektóre wiersze, usypiało niektóre, a następnie powtarzało.

Również, INDEX(datetime) jest bardzo ważne, aby uniknąć impasu.

Ale jeśli test datetime zawiera więcej niż, powiedzmy, 20% tabeli, DELETE wykona skanowanie tabeli. Mniejsze kawałki usuwane częściej jest obejściem.

Inny powodem przechodzenia z mniejszymi kawałkami jest blokowanie mniejszej liczby rzędów.

Dolna linia:

• INDEX(datetime)
• ciągłe uruchamianie zadania -- delete, sleep a minute, repeat.
• aby upewnić się, że powyższe zadanie nie umarło, wykonaj zadanie cron, którego jedynym celem jest ponowne uruchomienie go po niepowodzeniu.

Inne techniki usuwania: http://mysql.rjweb.org/doc.php/deletebig