Naprawianie „Przekroczono limit czasu oczekiwania na blokadę; spróbować ponownie uruchomić transakcję ”dla„ zablokowanej ”tabeli MySQL?


134

Ze skryptu wysłałem zapytanie takie jak to tysiące razy do mojej lokalnej bazy danych:

update some_table set some_column = some_value

Zapomniałem dodać część gdzie, więc ta sama kolumna została ustawiona na tę samą wartość dla wszystkich wierszy w tabeli i zostało to zrobione tysiące razy, a kolumna została zindeksowana, więc odpowiedni indeks był prawdopodobnie aktualizowany zbyt wiele razy .

Zauważyłem, że coś jest nie tak, ponieważ trwało to zbyt długo, więc zabiłem scenariusz. Od tamtej pory nawet zrestartowałem komputer, ale coś utknęło w tabeli, ponieważ proste zapytania trwają bardzo długo, a kiedy próbuję porzucić odpowiedni indeks, kończy się to z następującym komunikatem:

Lock wait timeout exceeded; try restarting transaction

To tabela innodb, więc zablokowana transakcja jest prawdopodobnie niejawna. Jak mogę naprawić tę tabelę i usunąć z niej zablokowaną transakcję?


3
Jaki jest wynik SHOW FULL PROCESSLIST?
Wolph

Pokazuje tylko polecenie SHOW FULL PROCESSLIST, nic więcej. To lokalna baza danych deweloperskich. Nic na nim nie działa. Otrzymałem komunikat o błędzie „lock wait ..” w wierszu poleceń, gdy próbowałem porzucić stamtąd indeks.
Tom

W takim przypadku prawdopodobnie tworzysz 2 oddzielne połączenia w różnych transakcjach, które muszą na siebie czekać.
Wolph

Nie utworzyłem później żadnych transakcji. Zabiłem skrypt, zrestartowałem komputer i zalogowałem się z wiersza poleceń, aby się rozejrzeć. Nic innego nie korzystało z bazy danych poza klientem wiersza poleceń mysql, więc coś musiało utknąć w tabeli.
Tom

Odpowiedzi:


145

Miałem podobny problem i rozwiązałem go sprawdzając uruchomione wątki. Aby zobaczyć uruchomione wątki, użyj następującego polecenia w interfejsie wiersza poleceń mysql:

SHOW PROCESSLIST;

Może być również wysłany z phpMyAdmin, jeśli nie masz dostępu do interfejsu wiersza poleceń mysql.
Spowoduje to wyświetlenie listy wątków z odpowiednimi identyfikatorami i czasem wykonania, dzięki czemu możesz ZABIĆ wątki, których wykonanie zajmuje zbyt dużo czasu. W phpMyAdmin będziesz miał przycisk do zatrzymywania wątków za pomocą KILL, jeśli używasz interfejsu wiersza poleceń, po prostu użyj polecenia KILL, po którym następuje identyfikator wątku, jak w poniższym przykładzie:

KILL 115;

Spowoduje to zakończenie połączenia dla odpowiedniego wątku.


36
PAMIĘTAJ! Wspomniał o tym ktoś w jednym z wielu wątków SO dotyczących tego problemu: Czasami proces, który zablokował tabelę, pojawia się jako uśpiony na liście procesów! Wyrywałem sobie włosy z głowy, aż zabiłem wszystkie wątki, które były otwarte w danej bazie danych, śpiąc lub nie. To ostatecznie odblokowało tabelę i uruchomiło zapytanie aktualizujące. Komentator wspomniał o czymś w rodzaju: „Czasami wątek MySQL blokuje tabelę, a następnie śpi, czekając na coś, co nie jest związane z MySQL”.
Eric L.

(Dodałem własną odpowiedź, aby rozwinąć ten fragment myśli tutaj , w powiązanym pytaniu)
Eric L.,

To mi pomogło. Miałem kilka uśpionych wątków utworzonych przez funkcję zarządzania bazą danych / zapytań PHPStorm.
Ejaz

Świetny! Od 5 godzin miałem ukryty proces, który mogłem zidentyfikować i zabić.
Aldo Paradiso

2
to nie jest rozwiązanie, podobnie jak przyklejenie przewodów taśmą do zakażonej rany jest rozwiązaniem. nie rozwiązujesz podstawowego problemu głównego.
user2914191

57

Możesz sprawdzić aktualnie realizowane transakcje za pomocą

SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`

Twoja transakcja powinna być jedną z pierwszych, ponieważ jest najstarszą na liście. Teraz po prostu weź wartość z trx_mysql_thread_idi wyślij KILLpolecenie:

KILL 1234;

Jeśli nie masz pewności, która transakcja jest Twoja, powtarzaj bardzo często pierwsze zapytanie i zobacz, które transakcje trwają.


Może być konieczne użycie konta roota, aby uruchomić ten SQL, aby zobaczyć, która transakcja faktycznie blokuje innym dostęp do tabeli
tom10271

41

Sprawdź status InnoDB dla zamków

SHOW ENGINE InnoDB STATUS;

Sprawdź otwarte tabele MySQL

SHOW OPEN TABLES WHERE In_use > 0;

Sprawdź oczekujące transakcje InnoDB

SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`; 

Sprawdź zależność zamka - co blokuje co

SELECT * FROM `information_schema`.`innodb_locks`;

Po zbadaniu powyższych wyników powinieneś być w stanie zobaczyć, co blokuje co.

Główna przyczyna problemu może również znajdować się w Twoim kodzie - sprawdź powiązane funkcje, szczególnie w przypadku adnotacji, jeśli używasz JPA, takiego jak Hibernate.

Na przykład, jak opisano tutaj , niewłaściwe użycie poniższej adnotacji może spowodować blokady w bazie danych:

@Transactional(propagation = Propagation.REQUIRES_NEW) 

4
Dziękuję bardzo! Uruchomienie SELECT * FROM information_schema.innodb_trx t JOIN information_schema.processlist p ON t.trx_mysql_thread_id = p.idujawniło winowajcę: Wątek blokujący pochodzi z mojego adresu IP ... Zapomniałem zamknąć konsolę debugowania, którą zostawiłem w trakcie transakcji ...
Elias Strehle

40

Zaczęło się to przydarzać, gdy wzrosła wielkość mojej bazy danych i wykonywałem na niej wiele transakcji.

Prawda jest taka, że ​​prawdopodobnie istnieje sposób na zoptymalizowanie zapytań lub bazy danych, ale wypróbuj te 2 zapytania, aby obejść problem.

Uruchomić to:

SET GLOBAL innodb_lock_wait_timeout = 5000; 

A potem to:

SET innodb_lock_wait_timeout = 5000; 

2
Link referencyjny: innodb_lock_wait_timeout
culix

1
Mam bardzo zajętą ​​bazę danych 11 GB. To jedyna rzecz, która mi pomogła, dziękuję!
dongemus

Pamiętaj tylko, aby zmienić wartości na poprzednie, jeśli nie chcesz, aby były tam na zawsze.
Ricardo Martins

Oznacza to, że niektórzy z moich użytkowników będą działać wolniej, prawda?
Arnold Roa

9

Kiedy ustanawiasz połączenie dla transakcji, uzyskujesz blokadę przed wykonaniem transakcji. Jeśli nie możesz zdobyć zamka, spróbuj przez jakiś czas. Jeśli nadal nie można uzyskać blokady, zgłaszany jest błąd przekroczenia czasu oczekiwania na blokadę. Dlaczego nie możesz uzyskać blokady, to fakt, że nie zamykasz połączenia. Tak więc, gdy próbujesz uzyskać blokadę po raz drugi, nie będziesz w stanie uzyskać blokady, ponieważ poprzednie połączenie jest nadal niezamknięte i utrzymuje blokadę.

Rozwiązanie: zamknij połączenie lub setAutoCommit(true)(zgodnie z projektem) zwolnij blokadę.


7

Uruchom ponownie MySQL, działa dobrze.

ALE uważaj, jeśli takie zapytanie utknie, gdzieś jest problem:

  • w zapytaniu (niewłaściwy znak, iloczyn kartezjański, ...)
  • bardzo liczne rekordy do edycji
  • złożone połączenia lub testy (MD5, podciągi LIKE %...%itp.)
  • problem struktury danych
  • model klucza obcego (blokada łańcucha / pętli)
  • błędnie zindeksowane dane

Jak powiedział @syedrakib, działa, ale nie jest to długotrwałe rozwiązanie do produkcji.

Uwaga: ponowne uruchomienie może wpłynąć na dane o niespójnym stanie.

Możesz również sprawdzić, jak MySQL obsługuje twoje zapytanie za pomocą słowa kluczowego EXPLAIN i zobaczyć, czy jest tam coś, co przyspieszyło zapytanie (indeksy, złożone testy, ...).


DZIĘKUJĘ CI. nie rozwiązałeś mojego problemu bezpośrednio, ale cierpiałem na blokady stołu i było tak źle. To jedyny post w całym internecie, który nasuwa mi pomysł sprawdzenia kluczy obcych. Dowiedziałem się więc, że kluczem podstawowym jest 11, a obcych 10. Nie wiem, jak to się mogło stać i dlaczego wszystko wcześniej działało.
EscapeNetscape

@EscapeNetscape, nie ma za co, cieszę się, że ta mała odpowiedź ci pomogła :)
Benj

3

Przejdź do procesów w mysql.

Więc widzę, że zadanie nadal działa.

Zabij określony proces lub poczekaj, aż proces się zakończy.


7
Rozwiązuje problem. Ale to nie może być rozwiązanie dla serwera produkcyjnego LIVE ...... Jak możemy wyświetlić tę obsługę zakleszczenia? Albo jak możemy UNIKAĆ tego impasu?
Rakib

1
cóż, tak się składa, że ​​nawet przy PERFEKCYJNIE dobrze sformułowanych i PERFEKCYJNIE unikniętych zapytaniach mysql, które nie są nawet napisane przez programistę, ale przez interfejs aktywnych rekordów frameworka, nadal mogą wystąpić problemy z przekroczeniem czasu oczekiwania na blokadę. Więc nie pisanie prawidłowego zapytania mysql jest tutaj czynnikiem.
Rakib

1

Napotkałem ten sam problem z komunikatem „aktualizacja”. Moim rozwiązaniem było po prostu wykonanie operacji dostępnych w phpMyAdmin dla tabeli. Zoptymalizowałem, opróżniłem i zdefragmentowałem tabelę (nie w tej kolejności). Nie ma potrzeby upuszczania tabeli i przywracania jej z kopii zapasowej. :)


1
To może rozwiązać problem. Ale konieczność robienia tego za każdym razem, gdy wystąpi taki błąd, nie może być rozwiązaniem dla serwera produkcyjnego LIVE ...... Jak możemy wyświetlić tę obsługę zakleszczenia? Albo jak możemy UNIKAĆ tego impasu?
Rakib

1

Miałem ten sam problem. Myślę, że był to problem z zakleszczeniem w SQL. Możesz po prostu wymusić zamknięcie procesu SQL z Menedżera zadań. Jeśli to nie rozwiązało problemu, po prostu uruchom ponownie komputer. Nie musisz usuwać tabeli i ponownie ładować danych.


Rozwiązuje problem. Ale to nie może być rozwiązanie dla serwera produkcyjnego LIVE ...... Jak możemy wyświetlić tę obsługę zakleszczenia? Albo jak możemy UNIKAĆ tego impasu?
Rakib

zrestartuj Apache i jego usługi (lub przynajmniej MySQL), nie ma potrzeby ponownego uruchamiania
Amjo

1

Miałem ten problem podczas próby usunięcia pewnej grupy rekordów (używając MS Access 2007 z połączeniem ODBC do MySQL na serwerze WWW). Zwykle usuwałbym pewne rekordy z MySQL, a następnie zastępowałem je zaktualizowanymi (kaskadowe usuwanie kilku powiązanych rekordów, co usprawnia usuwanie wszystkich powiązanych rekordów w celu usunięcia pojedynczego rekordu).

Próbowałem wykonać operacje dostępne w phpMyAdmin dla tabeli (optymalizacja, opróżnianie itp.), Ale otrzymywałem pozwolenie na błąd RELOAD, gdy próbowałem opróżnić. Ponieważ moja baza danych znajduje się na serwerze WWW, nie mogę ponownie uruchomić bazy danych. Przywracanie z kopii zapasowej nie wchodziło w grę.

Próbowałem uruchomić kwerendę usuwającą tę grupę rekordów na dostępie do mySQL cPanel w sieci. Otrzymałem ten sam komunikat o błędzie.

Moje rozwiązanie: Użyłem bezpłatnej przeglądarki MySQL Query Browser firmy Sun (Oracle) (którą wcześniej zainstalowałem na moim komputerze) i uruchomiłem tam zapytanie usuwające. Zadziałało od razu, problem rozwiązany. Następnie mogłem ponownie wykonać tę funkcję za pomocą skryptu Access przy użyciu połączenia ODBC Access to MySQL.


0

Problem w moim przypadku: wprowadzono pewne aktualizacje niektórych wierszy w ramach transakcji i przed zatwierdzeniem transakcji w innym miejscu te same wiersze były aktualizowane poza tą transakcją. Ensuring that all the updates to the rows are made within the same transaction resolved my issue.


To nie jest odpowiedzią na PO, ponieważ początkowo jest jasne, że przypadek użycia działa, w tym przypadku omyłkowo, wiele aktualizacji w wielu niepowiązanych transakcjach.
GullerYA

-1

problem został rozwiązany w moim przypadku, zmieniając deletenatruncate

problem- zapytanie:

delete from Survey1.sr_survey_generic_details
mycursor.execute(query)

napraw- zapytanie:

truncate table Survey1.sr_survey_generic_details
mycursor.execute(query)

-2

Naprawione.

Upewnij się, że w zapytaniu nie ma niezgodnego typu danych. Wystąpił problem polegający na tym, że próbowałem „danych agenta przeglądarki użytkownika” VARCHAR(255)i miałem problem z tą blokadą, jednak gdy ją zmieniłem TEXT(255), naprawiłem go.

Najprawdopodobniej jest to niezgodność typu danych.


-161

Rozwiązałem problem, upuszczając stół i przywracając go z kopii zapasowej.


34
Nawiasem mówiąc, ta odpowiedź zasługuje na zaszczyt bycia „zaakceptowaną” odpowiedzią wszech czasów SO z najniższymi punktami! 🏆
ashleedawg

6
Jest to również generalnie najniższa odpowiedź
Bob Kerman
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.