Otrzymanie „Przekroczono limit czasu oczekiwania na zablokowanie; spróbuj zrestartować transakcję ”, nawet jeśli nie korzystam z transakcji

Korzystam z następującej UPDATEinstrukcji MySQL :

mysql> update customer set account_import_id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Nie używam transakcji, więc dlaczego miałbym otrzymywać ten błąd? Próbowałem nawet zrestartować mój serwer MySQL i to nie pomogło.

Tabela ma 406,733 wierszy.

Korzystasz z transakcji; autocommit nie wyłącza transakcji, po prostu sprawia, że ​​są one automatycznie zatwierdzane na końcu wyciągu.

Co się dzieje, jakiś inny wątek trzyma blokadę rekordu na jakimś rekordzie (aktualizujesz każdy rekord w tabeli!) Zbyt długo, a twój wątek wygasa.

Możesz zobaczyć więcej szczegółów wydarzenia, wydając

SHOW ENGINE INNODB STATUS

po zdarzeniu (w sqledytorze). Najlepiej zrób to na cichej maszynie testowej.

JAK WYMUSZYĆ ODBLOKOWANIE zablokowanych tabel w MySQL:

Złamanie takich blokad może spowodować, że atomowość w bazie danych nie będzie wymuszana w instrukcjach SQL, które spowodowały blokadę.

Jest to hackerskie, a właściwym rozwiązaniem jest naprawienie aplikacji, która spowodowała blokady. Jednak gdy dolary staną na linii, szybkie kopnięcie sprawi, że wszystko znów się zacznie.

1) Wpisz MySQL

mysql -u your_user -p

2) Zobaczmy listę zablokowanych tabel

mysql> show open tables where in_use>0;

3) Zobaczmy listę bieżących procesów, jednym z nich jest blokowanie twoich tabel

mysql> show processlist;

4) Zabij jeden z tych procesów

mysql> kill <put_process_id_here>;

mysql> set innodb_lock_wait_timeout=100

Query OK, 0 rows affected (0.02 sec)

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 100   |
+--------------------------+-------+

Teraz ponownie uruchom blokadę. Masz 100 sekund na wydanie SHOW ENGINE INNODB STATUS\Gbazy danych i sprawdzenie, która inna transakcja blokuje twoją.

Sprawdź, czy baza danych jest dobrze dostrojona. Zwłaszcza izolacja transakcji. Nie jest dobrym pomysłem zwiększenie zmiennej innodb_lock_wait_timeout.

Sprawdź poziom izolacji transakcji w bazie danych w mliql cli:

mysql> SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation, @@session.transaction_isolation;
+-----------------------+-----------------+------------------------+
| @@GLOBAL.tx_isolation | @@tx_isolation  | @@session.tx_isolation |
+-----------------------+-----------------+------------------------+
| REPEATABLE-READ       | REPEATABLE-READ | REPEATABLE-READ        |
+-----------------------+-----------------+------------------------+
1 row in set (0.00 sec)

Możesz uzyskać ulepszenia zmieniające poziom izolacji, użyj wyroczni jak READ COMMITTED zamiast REPEATABLE READ (Domyślnie InnoDB)

mysql> SET tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> 

Spróbuj także użyć WYBIERZ NA AKTUALIZACJĘ tylko w razie potrzeby.

Żadne z sugerowanych rozwiązań nie działało dla mnie, ale tak się stało.

Coś blokuje wykonanie zapytania. Najprawdopodobniej kolejne zapytanie aktualizujące, wstawiające lub usuwające jedną z tabel w zapytaniu. Musisz dowiedzieć się, co to jest:

SHOW PROCESSLIST;

Po zlokalizowaniu procesu blokowania znajdź go idi uruchom:

KILL {id};

Ponownie uruchom pierwsze zapytanie.

100% z tym, co powiedział MarkR. autocommit sprawia, że ​​każde wyciągi są transakcjami jednoświadczeniowymi.

SHOW ENGINE INNODB STATUSpowinien dać ci wskazówki co do przyczyny impasu. Przyjrzyj się również swojemu dziennikowi powolnych zapytań, aby zobaczyć, co jeszcze odpytuje tabelę i spróbuj usunąć wszystko, co robi pełny skan tabeli. Blokowanie na poziomie wierszy działa dobrze, ale nie wtedy, gdy próbujesz zablokować wszystkie rzędy!

Czy możesz zaktualizować dowolny inny rekord w tej tabeli, czy też ta tabela jest intensywnie używana? Myślę, że podczas próby uzyskania blokady potrzebnej do aktualizacji tego rekordu upłynął limit czasu. Możesz zwiększyć czas, który może pomóc.

Liczba wierszy nie jest ogromna ... Utwórz indeks na account_import_id, jeśli nie jest to klucz podstawowy.

CREATE INDEX idx_customer_account_import_id ON customer (account_import_id);

Jeśli właśnie zabiłeś duże zapytanie, zajmie to trochę czasu rollback . Jeśli wydasz inne zapytanie przed zakończeniem wycofanego zapytania, może pojawić się błąd przekroczenia limitu czasu blokady. Tak mi się stało. Rozwiązaniem było tylko chwilę poczekać.

Detale:

Wysłałem zapytanie DELETE, aby usunąć około 900 000 z około 1 miliona wierszy.

Uruchomiłem to przez pomyłkę (usuwa tylko 10% wierszy): DELETE FROM table WHERE MOD(id,10) = 0

Zamiast tego (usuwa 90% wierszy): DELETE FROM table WHERE MOD(id,10) != 0

Chciałem usunąć 90% wierszy, a nie 10%. Zabiłem więc proces w wierszu poleceń MySQL, wiedząc, że przywróci on wszystkie wiersze, które dotychczas usunął.

Potem natychmiast uruchomiłem prawidłowe polecenie i lock timeout exceededwkrótce potem wystąpił błąd. Uświadomiłem sobie, że blokada może być tym, rollbackco zostało zabitego zapytania, które wciąż dzieje się w tle. Poczekałem kilka sekund i ponownie uruchomiłem zapytanie.

Upewnij się, że tabele bazy danych używają silnika pamięci masowej InnoDB i poziomu izolacji transakcji ODCZYTOWO ZGODZONEGO.

Możesz to sprawdzić przez SELECT @@ GLOBAL.tx_isolation, @@ tx_isolation; na konsoli mysql.

Jeśli nie jest ustawiony na READ-COMMITTED, musisz go ustawić. Przed ustawieniem upewnij się, że masz uprawnienia SUPER w mysql.

Możesz uzyskać pomoc na stronie http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html .

Ustawiając to, myślę, że twój problem zostanie rozwiązany.

Możesz także sprawdzić, czy nie próbujesz zaktualizować tego w dwóch procesach jednocześnie. Użytkownicy (@tala) napotkali podobne komunikaty o błędach w tym kontekście, może dwukrotnie sprawdź, czy ...

Pochodzę z Google i chciałem tylko dodać rozwiązanie, które działało dla mnie. Mój problem polegał na tym, że próbowałem usunąć rekordy ogromnego stołu, który miał kaskadowo dużo FK, więc dostałem ten sam błąd co OP.

Wyłączyłem, autocommita potem działało tylko dodawanieCOMMIT na końcu zdania SQL. O ile rozumiem, zwalnia bufor stopniowo, zamiast czekać na końcu polecenia.

Aby pozostać na przykładzie PO, powinno to zadziałać:

mysql> set autocommit=0;

mysql> update customer set account_import_id = 1; commit;

Nie zapomnij ponownie aktywować autocommitponownie, jeśli chcesz opuścić konfigurację MySQL jak poprzednio.

mysql> set autocommit=1;

Późno na imprezę (jak zwykle) mój problem polegał jednak na tym, że napisałem trochę złego SQL (będąc nowicjuszem) i kilka procesów miało blokadę rekordu (ów) <- nie jestem pewien, czy jest odpowiednia wersja. Skończyło się na: SHOW PROCESSLISTa następnie zabiciu identyfikatorów za pomocąKILL <id>

Takie rzeczy przydarzyły mi się, gdy korzystałem z wyjścia konstruktora języka PHP; w trakcie transakcji. Następnie transakcja „zawiesza się” i musisz zabić proces mysql (opisany powyżej z listą procesów;)

W moim przypadku uruchomiłem nieprawidłowe zapytanie, aby naprawić dane. Jeśli zablokujesz tabele w zapytaniu, nie będziesz musiał radzić sobie z limitem czasu blokady:

LOCK TABLES `customer` WRITE;
update customer set account_import_id = 1;
UNLOCK TABLES;

Prawdopodobnie nie jest to dobry pomysł do normalnego użytkowania.

Aby uzyskać więcej informacji, zobacz: MySQL 8.0 Reference Manual

Natknąłem się na to, mając 2 połączenia Doctrine DBAL, jedno z nich jako nietransakcyjne (dla ważnych logów), mają one działać równolegle, nie zależąc od siebie.

CodeExecution(
    TransactionConnectionQuery()
    TransactionlessConnectionQuery()
)

Moje testy integracyjne zostały opakowane w transakcje wycofania danych po samym teście.

beginTransaction()
CodeExecution(
    TransactionConnectionQuery()
    TransactionlessConnectionQuery() // CONFLICT
)
rollBack()

Moim rozwiązaniem było wyłączenie transakcji owijania w tych testach i zresetowanie danych db w inny sposób.

Miałem ten sam błąd, chociaż aktualizowałem tylko jedną tabelę z jednym wpisem, ale po ponownym uruchomieniu mysql został rozwiązany.