Fixarea „Lock wait timeout exceeded; încercați să reporniți tranzacția” pentru un tabel Mysql „blocat”? (Programare, Mysql, Tranzacții)

Tom a intrebat.

Dintr-un script am trimis o interogare ca aceasta de mii de ori la baza mea de date locală:

update some_table set some_column = some_value

Am uitat să adaug partea where, astfel încât aceeași coloană a fost setată la aceeași valoare a pentru toate rândurile din tabel și acest lucru a fost făcut de mii de ori, iar coloana a fost indexată, astfel încât indexul corespunzător a fost probabil actualizat și el de multe ori.

Am observat că ceva nu era în regulă, pentru că dura prea mult, așa că am oprit scriptul. De atunci am și repornit computerul, dar ceva s-a blocat în tabel, pentru că interogările simple durează foarte mult timp, iar când încerc să renunț la indexul corespunzător, acesta eșuează cu acest mesaj:

Lock wait timeout exceeded; try restarting transaction

Este o tabelă innodb, deci blocată tranzacția este probabil implicită. Cum pot repara acest tabel și să elimin tranzacția blocată din el?

Comentarii

  • Care este rezultatul din SHOW FULL PROCESSLIST? –  > Por Wolph.
  • Se afișează doar comanda SHOW FULL PROCESSLIST, nimic altceva. Este o bază de date de dezvoltare locală. Nu rulează nimic pe ea. Am primit mesajul de eroare „lock wait…” pe linia de comandă când am încercat să scot indexul de acolo. –  > Por Tom.
  • În acest caz, probabil că creați 2 conexiuni separate în tranzacții diferite care trebuie să se aștepte reciproc. –  > Por Wolph.
  • Nu am creat nicio tranzacție după aceea. Am omorât scriptul, am repornit mașina și m-am logat din linia de comandă pentru a mă uita în jur. Nimic altceva nu folosea baza de date în afară de clientul de linie de comandă mysql, deci ceva trebuie să fi fost blocat în tabel. –  > Por Tom.
  • Întrebare conexă: Cum să depanăm Blocare așteptare timp de așteptare depășit? –  > Por Amir Ali Akbari.
14 răspunsuri
Mihai Crăiță

Am avut o problemă similară și am rezolvat-o verificând firele de execuție care rulează. pentru a vedea firele de execuție folosește următoarea comandă în interfața de linie de comandă mysql:

SHOW PROCESSLIST;

Se poate trimite și din phpMyAdmin dacă nu ai acces la interfața de linie de comandă mysql.
Aceasta va afișa o listă de fire de execuție cu ID-urile corespunzătoare și timpul de execuție, astfel încât să puteți KILL firele de execuție care durează prea mult timp.În phpMyAdmin veți avea un buton pentru oprirea firelor de execuție folosind KILL, dacă folosiți interfața de linie de comandă, utilizați doar comanda KILL urmată de ID-ul firului de execuție, ca în exemplul următor:

KILL 115;

Acest lucru va încheia conexiunea pentru firul corespunzător.

Comentarii

    40

  • ATENȚIE! Acest lucru a fost menționat de cineva pe unul dintre numeroasele topicuri SO referitoare la această problemă: Uneori, procesul care a blocat tabelul apare ca fiind în așteptare în lista de procese! Eu mi-am smuls părul din cap până când am omorât toate firele care erau deschise în baza de date în cauză, adormite sau nu. Asta a deblocat în cele din urmă tabelul și a permis ca interogarea de actualizare să ruleze. Autorul comentariului a menționat ceva de genul: „Uneori, un fir MySQL blochează un tabel, apoi doarme în timp ce așteaptă să se întâmple ceva care nu are legătură cu MySQL”. –  > Por Eric L..
  • (Am adăugat propriul meu răspuns pentru a completa acel fragment de gândire aici, la o întrebare conexă) – –  > Por Eric L..
  • Acest lucru m-a ajutat. Am avut câteva somn fire create de funcția de gestionare a bazei de date/interogare a bazei de date PHPStorm. –  > Por Ejaz.
  • Minunat! Am avut un proces ascuns de 5 ore pe care l-am putut identifica și ucide. –  > Por Aldo Paradiso.
  • aceasta nu este o soluție, așa cum nu este o soluție nici să pui bandă adezivă peste o rană infectată. nu abordezi problema de fond. –  > Por user2914191.
nlsrchtr

Puteți verifica tranzacțiile în curs de execuție cu

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

Tranzacția dvs. ar trebui să fie una dintre primele, deoarece este cea mai veche din listă. Acum, luați valoarea din trx_mysql_thread_id și trimiteți-o la KILL comanda:

KILL 1234;

Dacă nu sunteți sigur care este tranzacția dvs., repetați prima interogare foarte des și vedeți ce tranzacții persistă.

Comentarii

  • S-ar putea să fie nevoie să folosiți contul de root pentru a rula acel SQL pentru a vedea care tranzacție blochează de fapt accesul altora la tabel –  > Por tom10271.
martoncsukas

Verificați starea InnoDB pentru blocaje

SHOW ENGINE InnoDB STATUS;

Verificați tabelele deschise MySQL

SHOW OPEN TABLES WHERE In_use > 0;

Verificați tranzacțiile InnoDB în așteptare

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

Verificați dependența de blocare – ce blochează ce

SELECT * FROM `information_schema`.`innodb_locks`;

După ce ați investigat rezultatele de mai sus, ar trebui să puteți vedea ce blochează ce.

Cauza principală a problemei ar putea fi și în codul dumneavoastră – vă rugăm să verificați funcțiile aferente, în special pentru adnotări, dacă utilizați JPA ca Hibernate.

De exemplu, așa cum este descris aici, utilizarea greșită a următoarei adnotări ar putea cauza blocaje în baza de date:

@Transactional(propagation = Propagation.REQUIRES_NEW) 

Comentarii

  • Vă mulțumim foarte mult! Rularea SELECT * FROM information_schema.innodb_trx t JOIN information_schema.processlist p ON t.trx_mysql_thread_id = p.id a dezvăluit vinovatul: firul de blocare a venit de la adresa mea de IP pe … Uitasem să închid o consolă de depanare pe care o lăsasem în mijlocul unei tranzacții… –  > Por Elias Strehle.
Max D

Acest lucru a început să mi se întâmple când dimensiunea bazei mele de date a crescut și făceam multe tranzacții pe ea.

Adevărul este că probabil există o modalitate de a optimiza fie interogările, fie BD-ul, dar încercați aceste 2 interogări pentru o soluție de rezolvare.

Fă asta:

SET GLOBAL innodb_lock_wait_timeout = 5000; 

Și apoi asta:

SET innodb_lock_wait_timeout = 5000; 

Comentarii

  • Link de referință: innodb_lock_wait_timeout –  > Por culix.
  • Rulez o bază de date de 11GB foarte ocupată. Acesta este singurul lucru care a funcționat pentru mine, vă mulțumesc! –  > Por dongemus.
  • Amintiți-vă doar să schimbați valorile la valorile sale anterioare dacă nu doriți să le păstrați acolo pentru totdeauna. –  > Por Ricardo Martins.
  • Acest lucru înseamnă că unii dintre utilizatorii mei vor avea o experiență mai lentă, corect? –  > Por Arnold Roa.
utilizator3388324

Atunci când stabiliți o conexiune pentru o tranzacție, dobândiți un blocaj înainte de a efectua tranzacția. Dacă nu reușești să dobândești blocajul, atunci încerci pentru o perioadă de timp. În cazul în care blocajul tot nu poate fi obținut, atunci se aruncă o eroare de blocare a timpului de așteptare depășit. Motivul pentru care nu se poate obține un blocaj este că nu se închide conexiunea. Astfel, atunci când încercați să obțineți un blocaj a doua oară, nu veți putea obține blocajul deoarece conexiunea anterioară este încă neînchisă și păstrează blocajul.

Soluție: închideți conexiunea sau setAutoCommit(true) (în funcție de proiectul dumneavoastră) pentru a elibera blocajul.

Benj

Reporniți MySQL, funcționează bine.

DAR aveți grijă că dacă o astfel de interogare este blocată, există o problemă undeva :

  • în interogarea dvs. (char greșit plasat, produs cartezian, …)
  • foarte multe înregistrări de editat
  • îmbinări sau teste complexe (MD5, substringuri, LIKE %...%, , etc.)
  • problemă de structură a datelor
  • modelul de chei străine (blocare în lanț/în buclă)
  • date indexate greșit

După cum a spus @syedrakib, funcționează, dar aceasta nu este o soluție de lungă durată pentru producție.

Atenție : efectuarea repornirii vă poate afecta datele cu o stare inconsistentă.

De asemenea, puteți verifica modul în care MySQL gestionează interogarea dvs. cu ajutorul cuvântului cheie EXPLAIN și puteți vedea dacă este posibil să faceți ceva acolo pentru a accelera interogarea (indici, teste complexe,…).

Comentarii

  • MULȚUMESC. nu mi-ați rezolvat direct problema, dar am suferit din cauza blocajelor de masă și a fost atât de rău. Aceasta este singura postare ob tot internetul, care mă duce la ideea de a verifica cheile străine. Așa că am aflat că cheia cheii primare era 11 și cheile străine 10. Nu știu cum s-a putut întâmpla așa ceva și de ce a funcționat totul înainte. –  > Por EscapeNetscape.
  • @EscapeNetscape ești binevenit, mă bucur că acest mic răspuns te-a ajutat 🙂 –  > Por Benj.
Shemeer M Ali

Mergeți la procese în mysql.

Deci, poate vedea că există o sarcină care încă funcționează.

Opriți procesul respectiv sau așteptați până când procesul se termină.

Comentarii

  • Se rezolvă problema. Dar aceasta nu poate fi o soluție pentru un server de producție LIVE…… Cum putem ieși din această manipulare deadlock? Sau cum putem EVITA ca acest blocaj să se întâmple? –  > Por Rakib.
  • ei bine, se întâmplă că, chiar și cu interogări mysql PERFECT bine formate și PERFECT scăpate, care nici măcar nu sunt scrise de către dezvoltator, ci de către interfața active-records a framework-ului, problemele de lock wait timeout pot apărea ÎNCĂ. Așadar, nu cred că scrierea unei interogări mysql corespunzătoare este un factor în acest caz. –  > Por Rakib.
The Science Boy

M-am lovit de aceeași problemă cu o declarație „update”. Soluția mea a fost pur și simplu să parcurg operațiunile disponibile în phpMyAdmin pentru tabel. Am optimizat, am făcut flush și am defragmentat tabelul (nu în această ordine). Pentru mine nu a fost nevoie să renunț la tabel și să îl restaurez din copie de rezervă. 🙂

Comentarii

  • S-ar putea să rezolve problema. Dar faptul că trebuie să faceți acest lucru de fiecare dată când apare o astfel de eroare nu poate fi o soluție pentru un server de producție LIVE…… Cum putem scoate această gestionare a blocajului? Sau cum putem EVITA ca acest deadlock să se întâmple? –  > Por Rakib.
kriver

Am avut aceeași problemă. Cred că a fost o problemă de deadlock cu SQL. Puteți forța închiderea procesului SQL din Task Manager. Dacă asta nu a rezolvat problema, repornește calculatorul. Nu este nevoie să renunțați la tabel și să reîncărcați datele.

Comentarii

  • Aceasta rezolvă problema. Dar aceasta nu poate fi o soluție pentru un server de producție LIVE…… Cum putem ieși din această gestionare a blocajului? Sau cum putem EVITA ca acest deadlock să se întâmple? –  > Por Rakib.
  • reporniți Apache și serviciile sale (sau cel puțin MySQL), nu este nevoie de repornire –  > Por Amjo.
Mike Lane

Am avut această problemă atunci când am încercat să șterg un anumit grup de înregistrări (folosind MS Access 2007 cu o conexiune ODBC la MySQL pe un server web). În mod obișnuit, aș șterge anumite înregistrări din MySQL, apoi le-aș înlocui cu înregistrări actualizate (ștergerea în cascadă a mai multor înregistrări conexe, acest lucru eficientizează ștergerea tuturor înregistrărilor conexe pentru o singură ștergere de înregistrare).

Am încercat să parcurg operațiunile disponibile în phpMyAdmin pentru tabel (optimizare, curățare, etc.), dar primeam o eroare de tipul „need permission to RELOAD” când încercam să curăț. Deoarece baza mea de date se află pe un server web, nu am putut reporni baza de date. Restaurarea de la o copie de rezervă nu era o opțiune.

Am încercat să execut o interogare de ștergere pentru acest grup de înregistrări pe accesul mySQL cPanel pe web. Am primit același mesaj de eroare.

Soluția mea: Am folosit browserul gratuit MySQL Query Browser al Sun (Oracle) (pe care l-am instalat anterior pe computerul meu) și am rulat interogarea de ștergere acolo. A funcționat imediat, problemă rezolvată. Am putut apoi să efectuez din nou funcția cu ajutorul scriptului Access, folosind conexiunea ODBC Access to MySQL.

Anushri HV

Problema în cazul meu: Unele actualizări au fost făcute la unele rânduri în cadrul unei tranzacții și, înainte ca tranzacția să fie confirmată, în alt loc, aceleași rânduri au fost actualizate în afara acestei tranzacții. Ensuring that all the updates to the rows are made within the same transaction resolved my issue.

Comentarii

  • Acest lucru nu răspunde la OP, deoarece inițial este clar că în cazul de utilizare se execută, în acest caz din greșeală, mai multe actualizări în mai multe tranzacții fără legătură între ele. –  > Por GullerYA.
arjit shukla

problemă rezolvată în cazul meu prin schimbarea delete în truncate

issue-query:

delete from Survey1.sr_survey_generic_details
mycursor.execute(query)

fix-query:

truncate table Survey1.sr_survey_generic_details
mycursor.execute(query)

Hiren Raj

L-am rezolvat.

Asigurați-vă că nu aveți un tip de date necorespunzător inserat în interogare.Am avut o problemă în care am încercat „user browser agent data” în VARCHAR(255) și aveam o problemă cu această blocare, însă când am schimbat-o în TEXT(255) a rezolvat-o.

Deci, cel mai probabil, este o nepotrivire a tipului de date.

Tom

Am rezolvat problema renunțând la tabel și restaurându-l din copia de rezervă.

Comentarii

  • Am ajuns la concluzia că tranzacția blocată s-a terminat în fundal în timp ce aștepta un răspuns și astfel, până când a primit un răspuns, nu a mai funcționat nimic altceva. –  > Por Joshua.