Warum hängt ALTER TABLE, obwohl die Tabelle klein ist?

Eine kleine Tabelle sollte in Sekunden geändert sein, aber ALTER TABLE wartet scheinbar endlos. Dann ist Größe möglicherweise gar nicht der Engpass.

Hintergrund

MariaDB schützt Tabellenstrukturen mit Metadata Locks. Eine offene Transaktion, die eine Tabelle benutzt hat, kann deren Metadaten bis zum Transaktionsende festhalten. Ein DDL wie ALTER TABLE wartet dann auf die Freigabe.

Erster Blick

SHOW FULL PROCESSLIST;

Suche nach dem wartenden ALTER und nach älteren Sessions/Transaktionen.

SELECT
    trx_id,
    trx_state,
    trx_started,
    trx_mysql_thread_id,
    trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

Typische Überraschung

Der Blockierer muss nicht gerade eine sichtbare Query ausführen. Eine Anwendung kann eine Transaktion geöffnet, gelesen und danach vergessen haben zu committen. Das DDL hängt dann hinter diesem Zustand.

Lösung ist nicht automatisch KILL

In Produktion zuerst herausfinden, welchem Request/Worker die Session gehört. Danach Anwendung sauber beenden oder bewusst entscheiden, ob die Session beendet werden darf.

Was du aus dem Fall mitnehmen willst

„ALTER ist langsam“ und „ALTER wartet auf einen Lock“ sind zwei völlig verschiedene Probleme. Erst den Zustand erkennen, bevor du über Indizes, Tabellenkopien oder Serverleistung nachdenkst.

Quelle: https://mariadb.com/docs/server/reference/sql-statements/transactions/metadata-locking