Row-Level-Locking vs. Table-Locking in MySQL

1. Locking-Granularität: warum sie zählt

Die Granularität eines Locks bestimmt, wie viel von einer Tabelle für andere Transaktionen blockiert wird, während eine Transaktion auf ihr arbeitet. Ein Table Lock sperrt die gesamte Tabelle, unabhängig davon, welche einzelnen Zeilen tatsächlich betroffen sind. Ein Row Lock sperrt ausschließlich die konkret gelesenen oder geänderten Zeilen und lässt parallele Transaktionen ungehindert auf anderen Zeilen derselben Tabelle arbeiten. Der Unterschied zwischen diesen beiden Granularitätsstufen entscheidet maßgeblich über die erreichbare Parallelität eines Systems unter Last.

InnoDB wurde von Grund auf als Row-Level-Locking-Engine entworfen, im deutlichen Gegensatz zur älteren MyISAM-Engine, die ausschließlich Table Locks kennt. Dieser Unterschied ist einer der Hauptgründe, warum InnoDB heute praktisch überall als Standard-Engine eingesetzt wird: In einem Shop-System mit tausenden gleichzeitigen Bestellungen kann InnoDB parallel auf unterschiedlichen Bestellzeilen arbeiten, während MyISAM die gesamte Bestelltabelle für jede einzelne Schreiboperation blockieren würde.

Trotzdem ist Table-Locking in InnoDB nicht komplett verschwunden. Bestimmte Operationen, insbesondere DDL-Anweisungen und explizite LOCK TABLES-Befehle, greifen weiterhin auf tabellenweite Sperrmechanismen zurück. Dieser Artikel zeigt im Detail, wann Row-Level-Locking greift, wann Table-Locking unvermeidlich ist, und wie die zugrunde liegenden Lock-Modi technisch zusammenarbeiten.

Für Magento-Betreiber und andere Anwendungen mit hohem Schreibaufkommen auf zentralen Tabellen, etwa Bestand oder Preise, ist dieses Verständnis besonders praxisrelevant: Ein unbedacht platziertes ALTER TABLE während der Geschäftszeiten kann durch ein einziges Table Lock den gesamten Checkout-Prozess für Minuten lahmlegen, während ein sauber geplantes Online-DDL-Fenster denselben Schema-Change ohne spürbare Downtime durchführt.

2. InnoDB Row Locks im Detail

Ein Row Lock in InnoDB wird nicht auf die Zeile selbst gesetzt, sondern auf den zugrunde liegenden Index-Eintrag. Diese technische Feinheit hat weitreichende Konsequenzen: Existiert kein passender Index für eine WHERE-Bedingung, muss InnoDB einen vollständigen Tabellenscan durchführen und sperrt dabei potenziell jede einzelne Zeile der Tabelle, obwohl semantisch nur wenige Zeilen tatsächlich verändert werden. Ein fehlender Index kann so ein Row-Level-Locking faktisch in ein Table-Level-Locking verwandeln, ohne dass ein expliziter Table Lock im eigentlichen Sinn beteiligt ist.

Row Locks werden für UPDATE, DELETE und SELECT ... FOR UPDATE gesetzt, während einfache SELECT-Abfragen ohne explizite Sperrklausel unter dem Standard-Isolation-Level REPEATABLE READ komplett ohne Locking über den MVCC-Snapshot laufen. Das bedeutet: Ein lesender Zugriff blockiert einen schreibenden Zugriff auf dieselbe Zeile in InnoDB grundsätzlich nicht, ein fundamentaler Unterschied zu klassischen Table-Locking-Systemen, bei denen selbst lesende Zugriffe schreibende Operationen blockieren können.

Ein Row Lock wird technisch als Eintrag in einer internen Sperrtabelle im Speicher verwaltet, die pro Transaktion referenziert, welche Indexeinträge sie hält. Diese Struktur erlaubt es InnoDB, bei jeder neuen Sperranfrage sehr schnell zu prüfen, ob bereits eine konkurrierende Sperre existiert, ohne die eigentlichen Datenseiten durchsuchen zu müssen. Bei sehr vielen gleichzeitig gehaltenen Row Locks wächst dieser Speicherverbrauch spürbar, was bei extrem großen Batch-Transaktionen zu einem eigenen Skalierungsproblem werden kann.

Die Größe dieser internen Sperrstruktur lässt sich indirekt über SHOW ENGINE INNODB STATUS im Abschnitt TRANSACTIONS beobachten, wo InnoDB die Anzahl der Lock-Structs pro Transaktion ausgibt. Eine Transaktion mit auffällig vielen Lock-Structs, etwa mehreren Zehntausend, ist ein deutliches Signal dafür, dass die Batch-Größe reduziert oder die Transaktion in mehrere kleinere aufgeteilt werden sollte, bevor sie den Buffer Pool unnötig belastet.

-- Row lock via primary key index, only this one row is locked
START TRANSACTION;
SELECT * FROM orders WHERE id = 4711 FOR UPDATE;
-- other transactions can freely update orders with a different id

-- Without a suitable index, InnoDB must scan and lock far more
SELECT * FROM orders WHERE customer_note LIKE '%urgent%' FOR UPDATE;
-- no index on customer_note: full scan, many rows examined and locked

-- Adding an index restores true row-level locking
CREATE INDEX idx_customer_note ON orders (customer_note(20));

3. Table Locks: wann sie trotzdem entstehen

Der offensichtlichste Fall eines Table Locks in MySQL ist der explizite Befehl LOCK TABLES tabelle WRITE, der die gesamte Tabelle für alle anderen Sessions sperrt, bis UNLOCK TABLES aufgerufen wird. Dieser Befehl stammt historisch aus der MyISAM-Ära und wird in modernen InnoDB-Anwendungen kaum noch benötigt, da Row Locks und Transaktionen die meisten Anwendungsfälle abdecken, für die früher explizite Table Locks nötig waren.

Der praktisch relevantere Fall ist DDL: Klassische ALTER TABLE-Operationen, die eine vollständige Tabellenkopie erfordern, etwa das Ändern eines Spaltentyps, blockieren während der gesamten Operationsdauer nahezu alle Zugriffe auf die betroffene Tabelle. Auch TRUNCATE TABLE arbeitet mit tabellenweiten Sperren, da es intern nicht wie DELETE zeilenweise, sondern durch Neuanlage der Datendatei funktioniert. Bei großen Produktionstabellen kann ein solches ALTER TABLE mehrere Minuten oder sogar Stunden dauern, während derer die Anwendung auf diese Tabelle effektiv nicht zugreifen kann.

-- Explicit table lock, historically relevant, rarely needed with InnoDB
LOCK TABLES orders WRITE;
-- all other sessions are blocked from reading or writing orders
UPDATE orders SET status = 'archived' WHERE created_at < '2020-01-01';
UNLOCK TABLES;

-- Classic ALTER TABLE that requires a full table copy
-- blocks nearly all access to the table for its entire duration
ALTER TABLE orders MODIFY COLUMN customer_note TEXT;

-- TRUNCATE also uses table-level locking internally
TRUNCATE TABLE session_log;

-- COPY algorithm forces a full table lock for the whole duration
ALTER TABLE orders ALGORITHM=COPY, ADD COLUMN priority TINYINT DEFAULT 0;

4. Lock-Modi: S, X, IS und IX erklärt

InnoDB unterscheidet auf Zeilenebene zwischen zwei grundlegenden Lock-Modi: Shared Locks (S) und Exclusive Locks (X). Ein Shared Lock erlaubt anderen Transaktionen, ebenfalls einen Shared Lock auf dieselbe Zeile zu halten, verhindert aber jeden Exclusive Lock. Mehrere Transaktionen können also gleichzeitig lesend auf eine Zeile zugreifen, aber keine kann sie ändern, solange ein Shared Lock aktiv ist. Ein Exclusive Lock dagegen verhindert sowohl weitere Shared als auch weitere Exclusive Locks auf derselben Zeile, komplett exklusiver Zugriff für genau eine Transaktion.

Auf Tabellenebene ergänzt InnoDB diese beiden Modi um Intention Locks: Intention Shared (IS) und Intention Exclusive (IX). Diese Locks signalisieren, dass eine Transaktion beabsichtigt, weiter unten in der Hierarchie, also auf einzelnen Zeilen, Shared oder Exclusive Locks zu setzen. Ein IS-Lock auf Tabellenebene wird gesetzt, bevor ein S-Lock auf einer Zeile erfolgt, ein IX-Lock entsprechend vor einem X-Lock. Diese Kombination erlaubt InnoDB, sehr effizient zu prüfen, ob eine tabellenweite Operation, etwa ein LOCK TABLES oder ALTER TABLE, mit bereits laufenden Row-Level-Transaktionen kollidiert, ohne dafür jede einzelne Zeile durchsuchen zu müssen.

Zusätzlich zu S und X kennt InnoDB feinere Sperrformen wie den Update Lock, eine Zwischenform, die beim Auswerten der WHERE-Bedingung eines UPDATE zunächst gesetzt und erst bei der tatsächlichen Änderung der Zeile in einen vollen Exclusive Lock umgewandelt wird. Diese Zwischenstufe reduziert das Risiko bestimmter Deadlock-Muster, bei denen mehrere Transaktionen gleichzeitig dieselben Zeilen für ein potenzielles Update lesen, ohne sofort exklusiv zu sperren.

5. Intention Locks und das Zusammenspiel mit Row Locks

Ohne Intention Locks müsste InnoDB bei jeder tabellenweiten Operation, etwa einem geplanten LOCK TABLES ... WRITE, sämtliche Zeilen der Tabelle einzeln auf bestehende Sperren prüfen, ein Vorgang, der bei großen Tabellen inakzeptabel langsam wäre. Stattdessen genügt ein einziger Blick auf die Intention Locks der Tabelle: Findet InnoDB dort einen aktiven IX-Lock einer anderen Transaktion, weiß es sofort, dass irgendwo in der Tabelle mindestens ein X-Lock aktiv ist, ohne jede einzelne Zeile durchsuchen zu müssen.

Dieses hierarchische Lock-System aus Zeilen- und Tabellen-Locks ist der Grund, warum InnoDB Row-Level-Locking und gelegentliches Table-Locking konsistent miteinander kombinieren kann. Ein ALTER TABLE, das einen exklusiven Table Lock benötigt, muss zunächst warten, bis alle aktiven Intention Locks anderer Transaktionen aufgelöst sind, also bis alle laufenden Row-Level-Transaktionen auf dieser Tabelle abgeschlossen sind. Genau das erklärt, warum eine einzelne lang laufende Transaktion ein an sich schnelles ALTER TABLE über Stunden blockieren kann, obwohl auf den ersten Blick keine offensichtliche Sperre erkennbar ist.

Die Kompatibilitätsmatrix zwischen den vier Lock-Modi folgt einer einfachen Regel: IS ist mit IS und IX kompatibel, IX ist mit IS und IX kompatibel, aber weder S noch X sind mit IX kompatibel, und S ist nur mit IS und S kompatibel. Diese Matrix wird von InnoDB intern für jede Sperranfrage geprüft, bevor eine Transaktion überhaupt in den Wartezustand für ein tatsächliches Row Lock übergeht, was die Konfliktprüfung auf Tabellenebene auf eine einzige, sehr schnelle Tabellen-Lookup-Operation reduziert.

6. Metadata Locks bei DDL-Operationen

Neben den InnoDB-internen Row- und Table-Locks existiert eine weitere, MySQL-Server-weite Sperrschicht: Metadata Locks (MDL). Jede Transaktion, die auf eine Tabelle zugreift, hält implizit einen Shared Metadata Lock auf diese Tabelle, solange die Transaktion offen ist, unabhängig davon, ob es sich um eine lesende oder schreibende Operation handelt. DDL-Anweisungen wie ALTER TABLE benötigen dagegen einen Exclusive Metadata Lock, der erst erteilt wird, wenn alle Shared Metadata Locks anderer Sessions freigegeben wurden.

Diese Regel erklärt ein häufig beobachtetes Phänomen in der Praxis: Ein ALTER TABLE-Befehl scheint einzufrieren, obwohl die Tabelle scheinbar nicht aktiv genutzt wird. Die Ursache ist fast immer eine vergessene, offen gebliebene Transaktion in einer anderen Session, die zwar keine aktiven Row Locks mehr hält, aber durch das bloße Offenbleiben weiterhin einen Shared Metadata Lock besitzt. Der ALTER-Befehl wartet dann, bis diese Transaktion committet oder zurückgerollt wird, und blockiert in der Zwischenzeit selbst neue, an sich unabhängige Anfragen an dieselbe Tabelle, da diese wiederum hinter dem wartenden Exclusive Lock in der Warteschlange stehen.

-- Find sessions holding metadata locks that block a pending ALTER TABLE
SELECT
  waiting_pid, waiting_query,
  blocking_pid, blocking_query
FROM sys.innodb_lock_waits;

-- Alternative: inspect metadata lock waits directly
SELECT * FROM performance_schema.metadata_locks
WHERE OBJECT_NAME = 'orders' AND LOCK_STATUS = 'PENDING';

-- Kill the offending idle-in-transaction session if confirmed safe
-- KILL <blocking_pid>;

7. Online DDL vs. klassisches ALTER TABLE

Seit MySQL 5.6 unterstützt InnoDB für viele DDL-Operationen Online DDL, das den notwendigen Exclusive Metadata Lock auf einen sehr kurzen Moment zu Beginn und Ende der Operation begrenzt. Während der eigentlichen Umstrukturierung, etwa beim Hinzufügen eines Index, laufen lesende und schreibende Zugriffe auf die Tabelle nahezu ungestört weiter, InnoDB protokolliert parallele Änderungen in einem Row Log und wendet sie am Ende der Operation nach.

Nicht jede DDL-Operation unterstützt Online DDL im vollen Umfang. Das Ändern eines Spaltentyps auf einen inkompatiblen Typ, etwa von VARCHAR auf INT, erfordert weiterhin eine vollständige Tabellenkopie mit entsprechend langem Table Lock. Der Befehl ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE lässt sich explizit anfordern und schlägt mit einer klaren Fehlermeldung fehl, falls die konkrete Operation dieses Verfahren nicht unterstützt, was vor unerwarteten langen Sperren in Produktionsumgebungen schützt.

Für besonders große Tabellen, bei denen selbst Online DDL durch die Größe des Row Logs an Grenzen stößt, bieten externe Tools wie pt-online-schema-change oder gh-ost eine Alternative, die über Trigger und schrittweise Kopie noch feingranularer steuern, wie viel Last die Migration auf dem Produktivsystem erzeugt. Diese Werkzeuge sind besonders bei Tabellen im dreistelligen Gigabyte-Bereich relevant, wo native Online DDL trotz kurzer Metadata-Lock-Phase spürbaren I/O-Druck erzeugen kann.

-- Explicitly require online DDL, fail fast if not supported
ALTER TABLE orders
  ADD INDEX idx_status_created (status, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

-- This type change is not compatible with online DDL and will
-- error out immediately instead of silently locking the table
-- ALTER TABLE orders MODIFY COLUMN quantity VARCHAR(10)
--   ALGORITHM=INPLACE, LOCK=NONE;  -- fails: incompatible change

8. MyISAM vs. InnoDB im Locking-Vergleich

Ein kurzer Blick auf MyISAM verdeutlicht, warum Row-Level-Locking überhaupt zum Standard wurde. MyISAM kennt ausschließlich Table Locks: Jede schreibende Operation sperrt die gesamte Tabelle, jede lesende Operation setzt einen Shared Table Lock, der jede parallele Schreiboperation blockiert. In einem System mit hoher Schreibfrequenz führt das zu einer seriellen Verarbeitung aller Schreiboperationen, unabhängig davon, wie viele Zeilen tatsächlich betroffen sind.

InnoDBs Row-Level-Locking ermöglicht dagegen, dass tausende Transaktionen gleichzeitig auf unterschiedlichen Zeilen derselben Tabelle arbeiten, solange sie sich nicht gegenseitig überschneiden. Dieser Unterschied ist der wesentliche Grund, warum MyISAM heute praktisch nur noch für sehr spezielle Anwendungsfälle wie reine Lesetabellen ohne gleichzeitige Schreiblast infrage kommt, während InnoDB der De-facto-Standard für nahezu jede transaktionale MySQL-Anwendung ist.

Ein weiterer, oft übersehener Unterschied betrifft die Absturzsicherheit: MyISAM führt keine Transaktionsprotokolle, sodass ein Server-Crash mitten in einer Schreiboperation zu beschädigten Tabellen führen kann, die manuell mit REPAIR TABLE repariert werden müssen. InnoDB dagegen stellt über sein Redo Log und den Crash-Recovery-Mechanismus sicher, dass die Tabelle nach einem Neustart automatisch in einen konsistenten Zustand zurückversetzt wird, ohne manuellen Eingriff.

9. Lock-Konflikte in der Praxis diagnostizieren

Wenn eine Anwendung unerwartete Wartezeiten meldet, ist die erste Diagnose, ob es sich um einen Row-Level- oder einen Table-Level-Konflikt handelt. SHOW ENGINE INNODB STATUS zeigt im Abschnitt TRANSACTIONS aktive Sperren und wartende Transaktionen. performance_schema.data_locks liefert eine strukturierte, abfragbare Sicht auf alle aktuell gehaltenen Sperren inklusive Lock-Modus, betroffenem Index und gesperrtem Datenbereich, was die manuelle Suche im Textformat von SHOW ENGINE INNODB STATUS überflüssig macht.

Ein typisches Symptom für ein fehlendes Row-Level-Locking durch einen fehlenden Index ist eine Transaktion, die viel mehr Zeilen sperrt, als die Anwendungslogik erwarten würde. Die Spalte lock_data in performance_schema.data_locks zeigt dann eine ungewöhnlich hohe Anzahl gesperrter Zeilen für eine eigentlich sehr spezifische WHERE-Bedingung, ein klares Signal dafür, dass ein Index fehlt und InnoDB stattdessen einen breiten Tabellenscan mit entsprechend vielen Sperren durchführt.

Für Metadata Locks liefert performance_schema.metadata_locks die passende Diagnosetabelle, ergänzt durch die komfortablere Sicht sys.schema_table_lock_waits, die blockierende und blockierte Sessions direkt gegenüberstellt, inklusive der jeweiligen Thread-ID und der wartenden Query. Diese Kombination aus drei Diagnosequellen, Row Locks über data_locks, Metadata Locks über metadata_locks und dem klassischen SHOW ENGINE INNODB STATUS, deckt praktisch jede Form von Lock-Konflikt in InnoDB ab.

10. Zusammenfassung

InnoDB ist grundlegend als Row-Level-Locking-Engine konzipiert, sperrt also standardmäßig nur die tatsächlich betroffenen Zeilen über den zugehörigen Index-Eintrag. Table-Locking bleibt trotzdem relevant, insbesondere bei DDL-Operationen ohne Online-DDL-Unterstützung, bei explizitem LOCK TABLES und implizit über Metadata Locks, die selbst kurze, an sich unkritische ALTER-TABLE-Befehle blockieren können, wenn eine andere Session eine Transaktion offen gelassen hat. Die Lock-Modi S, X, IS und IX bilden ein effizientes hierarchisches System, das InnoDB erlaubt, Zeilen- und Tabellensperren ohne teure vollständige Tabellenscans zu koordinieren.

Wer Row-Level-Locking wirklich ausnutzen will, muss sicherstellen, dass jede WHERE-Bedingung durch einen passenden Index abgedeckt ist, DDL-Operationen wo möglich mit ALGORITHM=INPLACE, LOCK=NONE durchführt und offen gebliebene Transaktionen konsequent vermeidet, die sonst über Metadata Locks selbst harmlose Schemaänderungen blockieren.

11. FAQ: Row-Level-Locking vs. Table-Locking