Ik voer de volgende MySQL UPDATE-instructie uit:
mysql> update customer set account_import_id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Ik gebruik geen transactie, dus waarom zou ik deze foutmelding krijgen? Ik heb zelfs geprobeerd mijn MySQL-server opnieuw op te starten, maar het hielp niet.
De tabel heeft 406.733 rijen.
Antwoord 1, autoriteit 100%
U gebruikt een transactie; autocommit schakelt transacties niet uit, het zorgt er alleen voor dat ze automatisch worden vastgelegd aan het einde van het overzicht.
Wat er aan de hand is, is dat een andere thread een recordvergrendeling op een record vasthoudt (u werkt elke record in de tabel bij!) te lang, en uw thread heeft een time-out.
Je kunt meer details van het evenement zien door een
. te geven
SHOW ENGINE INNODB STATUS
na de gebeurtenis (in SQL-editor). Doe dit bij voorkeur op een stille testmachine.
Antwoord 2, autoriteit 92%
HOE ONTGRENDELEN voor vergrendelde tafels in MySQL:
Door dergelijke vergrendelingen te verbreken, kan atomiciteitin de database niet worden afgedwongen op de sql-instructies die de vergrendeling hebben veroorzaakt.
Dit is hackachtig, en de juiste oplossing is om je applicatie te repareren die de vergrendelingen heeft veroorzaakt. Als er echter dollars op het spel staan, zal een snelle schop de zaken weer in beweging krijgen.
1) Voer MySQL in
mysql -u your_user -p
2) Laten we eens kijken naar de lijst met vergrendelde tafels
mysql> show open tables where in_use>0;
3) Laten we eens kijken naar de lijst van de huidige processen, een daarvan is het vergrendelen van uw tafel(s)
mysql> show processlist;
4) Stop een van deze processen
mysql> kill <put_process_id_here>;
Antwoord 3, autoriteit 48%
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 |
+--------------------------+-------+
Activeer nu de vergrendeling opnieuw. Je hebt 100 seconden de tijd om een SHOW ENGINE INNODB STATUS\Gnaar de database te sturen en te zien welke andere transactie de jouwe blokkeert.
Antwoord 4, autoriteit 33%
Kijk eens of je database goed is afgesteld. Vooral de transacties isolatie. Is het geen goed idee om de variabele innodb_lock_wait_timeout te vergroten.
Controleer het isolatieniveau van uw databasetransactie in de mysql-cli:
mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation, @@session.tx_isolation;
+-----------------------+-----------------+------------------------+
| @@GLOBAL.tx_isolation | @@tx_isolation | @@session.tx_isolation |
+-----------------------+-----------------+------------------------+
| REPEATABLE-READ | REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+------------------------+
1 row in set (0.00 sec)
Je zou verbeteringen kunnen krijgen door het isolatieniveau te veranderen, gebruik het orakel zoals READ COMMITTED in plaats van HERHAALBAAR LEZEN (InnoDB Defaults)
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>
Probeer ook SELECT FOR UPDATE alleen te gebruiken in indien nodig.
Antwoord 5, autoriteit 13%
Geen van de voorgestelde oplossingen werkte voor mij, maar dit wel.
Iets blokkeert de uitvoering van de query. Hoogstwaarschijnlijk een andere query die een van de tabellen in uw query bijwerkt, invoegt of verwijdert. Je moet uitzoeken wat dat is:
SHOW PROCESSLIST;
Zodra u het blokkeringsproces heeft gevonden, zoekt u de idop en voert u :
uit
KILL {id};
Voer uw eerste zoekopdracht opnieuw uit.
Antwoord 6, autoriteit 5%
100% met wat MarkR zei. autocommit maakt van elke afschrift een transactie met één afschrift.
SHOW ENGINE INNODB STATUSzou je wat aanwijzingen moeten geven over de reden van de impasse. Kijk ook eens goed naar je log met trage zoekopdrachten om te zien wat er nog meer naar de tabel vraagt en probeer alles te verwijderen dat een volledige tabelscan doet. Vergrendeling op rijniveau werkt goed, maar niet wanneer u alle rijen probeert te vergrendelen!
Antwoord 7, autoriteit 2%
mysql->SHOW PROCESSLIST;
kill xxxx;
en dood dan welke in de slaap. In mijn geval is dat 2456.
Antwoord 8, autoriteit 2%
Kunt u een ander record in deze tabel bijwerken of wordt deze tabel veel gebruikt? Wat ik denk is dat terwijl het probeert om een slot te verwerven dat het nodig heeft om dit record bij te werken, de ingestelde time-out is verlopen. Mogelijk kunt u de tijd verlengen, wat kan helpen.
Antwoord 9
Het aantal rijen is niet enorm… Maak een index op account_import_id als dit niet de primaire sleutel is.
CREATE INDEX idx_customer_account_import_id ON customer (account_import_id);
Antwoord 10
Als je net een grote zoekopdracht hebt beëindigd, duurt het even om rollback. Als u nog een query uitvoert voordat de gedode query is teruggedraaid, krijgt u mogelijk een time-outfout bij het vergrendelen. Dat is wat er met mij is gebeurd. De oplossing was gewoon even wachten.
Details:
Ik had een DELETE-verzoek ingediend om ongeveer 900.000 van de ongeveer 1 miljoen rijen te verwijderen.
Ik heb dit per ongeluk uitgevoerd (verwijdert slechts 10% van de rijen):
DELETE FROM table WHERE MOD(id,10) = 0
In plaats hiervan (verwijdert 90% van de rijen):
DELETE FROM table WHERE MOD(id,10) != 0
Ik wilde 90% van de rijen verwijderen, niet 10%. Dus stopte ik het proces in de MySQL-opdrachtregel, wetende dat het alle rijen zou terugdraaien die het tot nu toe had verwijderd.
Vervolgens heb ik onmiddellijk de juiste opdracht uitgevoerd en kort daarna kreeg ik een lock timeout exceeded-fout. Ik realiseerde me dat de vergrendeling eigenlijk de rollbackvan de gedode query kan zijn die nog steeds op de achtergrond plaatsvindt. Dus ik wachtte een paar seconden en voerde de query opnieuw uit.
Antwoord 11
Zorg ervoor dat de databasetabellen de InnoDB-opslagengine en het READ-COMMITTED-transactie-isolatieniveau gebruiken.
U kunt het controleren door SELECT @@GLOBAL.tx_isolation, @@tx_isolation; op mysql-console.
Als het niet is ingesteld op READ-COMMITTED, moet u het instellen. Zorg ervoor dat u, voordat u het instelt, SUPER-rechten heeft in mysql.
Je kunt hulp krijgen van http://dev.mysql .com/doc/refman/5.0/en/set-transaction.html.
Als u dit instelt, denk ik dat uw probleem wordt opgelost.
Misschien wil je ook controleren of je dit niet in twee processen tegelijk probeert bij te werken. Gebruikers ( @tala ) zijn in deze context soortgelijke foutmeldingen tegengekomen, controleer dat misschien nog eens…
Antwoord 12
Ik kom van Google en ik wilde gewoon de oplossing toevoegen die voor mij werkte. Mijn probleem was dat ik records probeerde te verwijderen van een enorme tabel met veel FK in cascade, dus ik kreeg dezelfde fout als de OP.
Ik heb de autocommituitgeschakeld en toen werkte het gewoon door COMMITtoe te voegen aan het einde van de SQL-zin. Voor zover ik heb begrepen, wordt hiermee de buffer beetje bij beetje vrijgegeven in plaats van te wachten aan het einde van de opdracht.
Om bij het voorbeeld van de OP te blijven, had dit moeten werken:
mysql> set autocommit=0;
mysql> update customer set account_import_id = 1; commit;
Vergeet niet om de autocommitopnieuw te activeren als je de MySQL-configuratie wilt laten zoals voorheen.
mysql> set autocommit=1;
Antwoord 13
Probeer de onderstaande twee parameters bij te werken, aangezien ze standaardwaarden moeten hebben.
innodb_lock_wait_timeout = 50
innodb_rollback_on_timeout = AAN
Voor het controleren van de parameterwaarde kunt u de onderstaande SQL gebruiken.
WEER WERELDWIJDE VARIABELEN ZOALS ‘innodb_rollback_on_timeout’;
Antwoord 14
Te laat op het feest (zoals gewoonlijk), maar mijn probleem was het feit dat ik een slechte SQL schreef (als beginner) en dat verschillende processen een blokkering hadden op de record(s) <– niet zeker van de juiste woordenstroom. Uiteindelijk moest ik gewoon: SHOW PROCESSLISTen vervolgens de ID’s doden met KILL <id>
Antwoord 15
Dit soort dingen is mij overkomen toen ik php gebruikte
taalconstructie exit; midden in de transactie. Dan dit
transactie “hangt” en je moet het mysql-proces beëindigen (hierboven beschreven met proceslijst;)
Antwoord 16
In mijn geval voerde ik een abnormale zoekopdracht uit om gegevens te herstellen. Als u de tabellen in uw zoekopdracht vergrendelt, hoeft u zich niet bezig te houden met de vergrendelingstime-out:
LOCK TABLES `customer` WRITE;
update customer set account_import_id = 1;
UNLOCK TABLES;
Dit is waarschijnlijk geen goed idee voor normaal gebruik.
Zie voor meer informatie: MySQL 8.0 Reference Manual
Antwoord 17
Ik kwam dit tegen met 2 Doctrine DBAL-verbindingen, waarvan één als niet-transactioneel (voor belangrijke logbestanden), ze zijn bedoeld om parallel te lopen en niet van elkaar afhankelijk te zijn.
CodeExecution(
TransactionConnectionQuery()
TransactionlessConnectionQuery()
)
Mijn integratietests werden na de test verpakt in transacties voor het terugdraaien van gegevens.
beginTransaction()
CodeExecution(
TransactionConnectionQuery()
TransactionlessConnectionQuery() // CONFLICT
)
rollBack()
Mijn oplossing was om de inpaktransactie in die tests uit te schakelen en de db-gegevens op een andere manier opnieuw in te stellen.
Antwoord 18
We zijn gisteren dit probleem tegengekomen en nadat we zowat elke voorgestelde oplossing hier hadden doorgenomen, en verschillende andere van andere antwoorden/forums, hebben we het uiteindelijk opgelost toen we ons het eigenlijke probleem realiseerden.
Door een slechte planning is onze database opgeslagen op een gekoppeld volume dat ook onze reguliere geautomatiseerde back-ups ontving. Dat volume had de maximale capaciteit bereikt.
Toen we wat ruimte hadden vrijgemaakt en opnieuw waren opgestart, was deze fout opgelost.
Merk op dat we ook een aantal processen handmatig hebben afgebroken: kill <process_id>;dus dat kan nog steeds nodig zijn.
Al met al was onze conclusie dat het ongelooflijk frustrerend was dat geen van onze logs of waarschuwingen direct een gebrek aan schijfruimte vermeldde, maar dat leek de hoofdoorzaak te zijn.
Antwoord 19
Ik had dezelfde fout, ook al was ik maar één tabel met één item aan het bijwerken, maar na het herstarten van mysql was het opgelost.