Ustalenie limitu czasu dla transakcji w MySQL / InnoDB

Wyszło to z tego pokrewnego pytania , w którym chciałem wiedzieć, jak zmusić dwie transakcje do sekwencyjnego wykonywania w trywialnym przypadku (gdzie obie operują tylko w jednym rzędzie). Otrzymałem odpowiedź - użyj SELECT ... FOR UPDATEjako pierwszego wiersza obu transakcji - ale prowadzi to do problemu: jeśli pierwsza transakcja nigdy nie zostanie zatwierdzona lub wycofana, druga transakcja zostanie zablokowana na czas nieokreślony. innodb_lock_wait_timeoutZmienna określa liczbę sekund, po którym klient próbuje zrobić drugą transakcję zostanie powiedziane „Niestety, spróbuj ponownie” ... ale o ile mogę powiedzieć, że będą ponownie próbują aż do następnego uruchomienia serwera. Więc:

1. Z pewnością musi istnieć sposób na wymuszenie, ROLLBACKjeśli transakcja trwa wiecznie? Czy muszę uciekać się do używania demona do zabijania takich transakcji, a jeśli tak, to jak taki demon wyglądałby?
2. Jeśli połączenie zostanie przerwane w trakcie wait_timeoutlub w trakcie interactive_timeouttransakcji, czy transakcja zostanie wycofana? Czy istnieje sposób na przetestowanie tego z poziomu konsoli?

Wyjaśnienie : innodb_lock_wait_timeoutustawia liczbę sekund, przez które transakcja będzie czekać na zwolnienie blokady przed poddaniem się; chcę wymusić zwolnienie blokady.

Aktualizacja 1 : Oto prosty przykład, który pokazuje, dlaczego innodb_lock_wait_timeoutnie wystarczy, aby pierwsza transakcja nie została zablokowana przez pierwszą:

START TRANSACTION;
SELECT SLEEP(55);
COMMIT;

Przy ustawieniu domyślnym innodb_lock_wait_timeout = 50ta transakcja kończy się bez błędów po 55 sekundach. A jeśli dodasz znak UPDATEprzed SLEEPwierszem, a następnie zainicjujesz drugą transakcję od innego klienta, który próbuje przejść do SELECT ... FOR UPDATEtego samego wiersza, nastąpi przekroczenie limitu czasu drugiej transakcji, a nie tej, która zasnęła.

To, czego szukam, to sposób na wymuszenie końca spokojnego snu tej transakcji.

Aktualizacja 2 : W odpowiedzi na obawy hobodave dotyczące tego, jak realistyczny jest powyższy przykład, oto alternatywny scenariusz: DBA łączy się z serwerem na żywo i działa

START TRANSACTION
SELECT ... FOR UPDATE

gdzie druga linia blokuje wiersz, do którego aplikacja często zapisuje. Następnie DBA zostaje przerwane i odchodzi, zapominając o zakończeniu transakcji. Aplikacja zgrzyta, dopóki wiersz nie zostanie odblokowany. Chciałbym zminimalizować czas, w którym aplikacja utknęła w wyniku tego błędu.

Ponad połowa tego wątku wydaje się dotyczyć zadawania pytań w usłudze ServerFault. Myślę, że pytanie ma sens i jest dość proste: Jak automatycznie wycofać wstrzymaną transakcję?

Jednym z rozwiązań, jeśli chcesz zabić całe połączenie, jest ustawienie wait_timeout / interactive_timeout. Zobacz /programming/9936699/mysql-rollback-on-transaction-with-lost-disconnected-connection .

Ponieważ pytanie jest zadawane tutaj w usłudze ServerFault, logiczne jest założenie, że szukasz rozwiązania MySQL dla problemu MySQL, szczególnie w dziedzinie wiedzy, w której administrator systemu i / lub DBA mieliby wiedzę specjalistyczną. sekcja dotyczy twoich pytań:

Jeśli pierwsza transakcja nigdy nie zostanie zatwierdzona ani wycofana, druga transakcja zostanie zablokowana na czas nieokreślony

Nie zrobi tego. Myślę, że nie rozumiesz innodb_lock_wait_timeout. Robi dokładnie to, czego potrzebujesz.

Wróci z błędem, jak podano w instrukcji:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

• Z definicji nie jest to nieograniczone . Jeśli aplikacja łączy się i blokuje wielokrotnie , oznacza to, że „blokuje się na czas nieokreślony”, a nie transakcja. Druga transakcja blokuje się zdecydowanie na innodb_lock_wait_timeoutkilka sekund.

Domyślnie transakcja nie zostanie wycofana. Twój kod aplikacji jest odpowiedzialny za podjęcie decyzji o tym, jak poradzić sobie z tym błędem, bez względu na to, czy spróbujesz ponownie, czy wycofać.

Jeśli chcesz automatycznie wycofać, wyjaśniono to również w instrukcji:

Bieżąca transakcja nie jest wycofywana. (Aby cofnąć całą transakcję, uruchom serwer z --innodb_rollback_on_timeoutopcją.

RE: Twoje liczne aktualizacje i komentarze

Po pierwsze, w swoich komentarzach stwierdziłeś, że „chciałeś” powiedzieć, że chcesz przekroczyć limit czasu pierwszej transakcji, która blokuje się na czas nieokreślony. Nie wynika to z pierwotnego pytania i jest sprzeczne z „Jeśli pierwsza transakcja nigdy nie zostanie zatwierdzona lub wycofana, wówczas druga transakcja zostanie zablokowana na czas nieokreślony”.

Niemniej jednak mogę również odpowiedzieć na to pytanie. Protokół MySQL nie ma „limitu czasu zapytania”. Oznacza to, że nie można przekroczyć limitu czasu pierwszej zablokowanej transakcji. Musisz poczekać, aż się zakończy, lub zabić sesję. Po zakończeniu sesji serwer automatycznie wycofuje transakcję.

Jedyną inną alternatywą byłoby użycie lub napisanie biblioteki mysql, która wykorzystuje nieblokujące wejścia / wyjścia, co pozwoliłoby twojej aplikacji na zabicie wątku / rozwidlenia wykonującego zapytanie po N sekundach. Implementacja i użycie takiej biblioteki wykracza poza zakres ServerFault. To jest właściwe pytanie dla StackOverflow .

Po drugie, w komentarzach podałeś następujące informacje:

Właściwie bardziej byłem zaniepokojony moim pytaniem o scenariusz, w którym aplikacja klienta zawiesza się (powiedzmy, że zostaje złapana w nieskończoną pętlę) podczas transakcji, niż ta, w której transakcja zajmuje dużo czasu po stronie MySQL.

Nie było to wcale widoczne w twoim pierwotnym pytaniu i nadal tak nie jest. Można to rozpoznać dopiero po udostępnieniu tego dość ważnego smakołyka w komentarzu.

Jeśli to jest problem, który próbujesz rozwiązać, obawiam się, że zadałeś go na niewłaściwym forum. Opisałeś problem programowania na poziomie aplikacji, który wymaga rozwiązania programistycznego, którego MySQL nie jest w stanie zapewnić i jest poza zakresem tej społeczności. Twoja ostatnia odpowiedź odpowiada na pytanie „Jak zapobiec nieskończonemu zapętleniu się programu Ruby?”. To pytanie jest nie na temat dla tej społeczności i powinno być zadawane na StackOverflow .

W podobnej sytuacji mój zespół zdecydował się na użycie pt-kill ( https://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html ). Może raportować lub zabijać zapytania, które (między innymi) działają zbyt długo. Można wtedy uruchomić go w cronie co X minut.

Problem polegał na tym, że zapytanie, które miało nieoczekiwanie długi (np. Nieokreślony) czas wykonania, zablokowało procedurę tworzenia kopii zapasowej, która próbowała opróżnić tabele blokadą odczytu, co z kolei zablokowało wszystkie inne zapytania „oczekiwania na opróżnienie tabel”, które nigdy nie przekroczyły limitu czasu ponieważ nie czekają na blokadę, co spowodowało brak błędów w aplikacji, co ostatecznie spowodowało brak alertów dla administratorów i zatrzymanie aplikacji.

Poprawka polegała oczywiście na naprawieniu początkowego zapytania, ale aby uniknąć sytuacji, która przypadkowo zdarzy się w przyszłości, byłoby świetnie, gdyby możliwe było automatyczne zabijanie (lub zgłaszanie) transakcji / zapytań trwających dłużej niż określony czas, który autor pytania wydaje się zadawać. Jest to możliwe w przypadku pt-kill.

Prostym rozwiązaniem będzie posiadanie procedury składowanej w celu zabicia zapytań, które zajmują więcej czasu niż wymagany timeoutczas i użycie innodb_rollback_on_timeoutopcji wraz z nim.

Po Googlingu przez jakiś czas wygląda na to, że nie ma bezpośredniego sposobu na ustawienie limitu czasu na transakcję. Oto najlepsze rozwiązanie, jakie udało mi się znaleźć:

Komenda

SHOW PROCESSLIST;

daje tabelę z wszystkimi aktualnie wykonywanymi poleceniami i czasem (w sekundach) od ich uruchomienia. Gdy klient przestaje wydawać polecenia, opisuje się je jako wydawanie Sleeppolecenia, przy czym Timekolumna jest liczbą sekund od zakończenia ostatniego polecenia. Możesz więc ręcznie wejść i KILLwszystko, co ma Timewartość powyżej, powiedzmy, 5 sekund, jeśli masz pewność, że żadne zapytanie nie powinno zająć więcej niż 5 sekund w bazie danych. Na przykład, jeśli proces o id 3 ma w Timekolumnie wartość 12 , możesz to zrobić

KILL 3;

Dokumentacja składni KILL sugeruje, że transakcja przeprowadzana przez wątek o tym identyfikatorze zostałaby wycofana (chociaż tego nie testowałem, więc bądź ostrożny).

Ale jak to zautomatyzować i zabijać wszystkie skrypty w godzinach nadliczbowych co 5 sekund? Pierwszy komentarz na tej samej stronie daje nam podpowiedź, ale kod jest w PHP; wydaje się, że nie możemy zrobić SELECTna SHOW PROCESSLISTz poziomu klienta MySQL. Załóżmy jednak, że mamy demona PHP, który uruchamiamy co $MAX_TIMEsekundę, może wyglądać mniej więcej tak:

$result = mysql_query("SHOW FULL PROCESSLIST");
while ($row=mysql_fetch_array($result)) {
  $process_id=$row["Id"];
    if ($row["Time"] > $MAX_TIME) {
      $sql="KILL $process_id";
      mysql_query($sql);
  }
}

Zauważ, że spowodowałoby to efekt uboczny, zmuszając wszystkie bezczynne połączenia klientów do ponownego połączenia, nie tylko te, które źle się zachowują.

Jeśli ktoś ma lepszą odpowiedź, chciałbym ją usłyszeć.

Edycja: Istnieje co najmniej jedno poważne ryzyko związane z użyciem KILLw ten sposób. Załóżmy, że używasz powyższego skryptu do KILLkażdego połączenia, które jest bezczynne przez 10 sekund. Po 10 sekundach bezczynności połączenia, ale przed jego KILLwydaniem użytkownik, którego połączenie jest zabijane, wydaje START TRANSACTIONpolecenie. Następnie są KILLedytowane. Wysyłają, UPDATEa następnie, zastanawiając się dwa razy, wydają ROLLBACK. Ponieważ jednak KILLwycofano pierwotną transakcję, aktualizacja przeszła natychmiast i nie można jej przywrócić! Zobacz ten post dla przypadku testowego.

Jak sugeruje hobodave w komentarzu, u niektórych klientów (choć najwyraźniej nie jest to mysqlnarzędzie wiersza poleceń) możliwe jest ustawienie limitu czasu dla transakcji. Oto demonstracja użycia ActiveRecord w Ruby:

require 'rubygems'
require 'timeout'
require 'active_record'

Timeout::timeout(5) {
  Foo.transaction do
    Foo.create(:name => 'Bar')
    sleep 10
  end
}

W tym przykładzie transakcja wygasa po 5 sekundach i zostaje automatycznie wycofana . ( Aktualizacja w odpowiedzi na komentarz hobodave'a: jeśli odpowiedź bazy danych zajmie więcej niż 5 sekund, transakcja zostanie wycofana tak szybko, jak to nastąpi - nie wcześniej). Jeśli chcesz mieć pewność, że wszystkie transakcje przekroczą limit czasu po nsekundach, możesz zbudować opakowanie wokół ActiveRecord. Zgaduję, że dotyczy to również najpopularniejszych bibliotek Java, .NET, Python itp., Ale jeszcze ich nie testowałem. (Jeśli tak, napisz komentarz do tej odpowiedzi.)

Transakcje w ActiveRecord mają również tę zaletę, że są bezpieczne, jeśli KILLzostanie wydane, w przeciwieństwie do transakcji wykonanych z wiersza poleceń. Zobacz /dba/1561/mysql-client-believes-theyre-in-a-transaction-gets-killed-wreaks-havoc .

Wydaje się, że nie jest możliwe wymuszenie maksymalnego czasu transakcji po stronie serwera, z wyjątkiem skryptu takiego jak ten, który zamieściłem w drugiej odpowiedzi.