Gap Locks und Next-Key Locks in InnoDB erklärt

1. Was Gap Locks sind und warum InnoDB sie braucht

Ein Gap Lock sperrt nicht eine konkrete Zeile, sondern die Lücke zwischen zwei benachbarten Indexeinträgen, oder den Bereich vor dem ersten beziehungsweise nach dem letzten Eintrag eines Index. Diese Sperre verhindert, dass andere Transaktionen innerhalb dieser Lücke neue Zeilen einfügen, solange die haltende Transaktion aktiv ist. Anders als ein Record Lock, der eine existierende Zeile schützt, schützt ein Gap Lock einen leeren Bereich, in dem noch gar keine Zeile existiert.

Der Grund für diesen ungewöhnlichen Mechanismus liegt in InnoDBs Standard-Isolation-Level REPEATABLE READ. Ohne Gap Locks könnte eine Transaktion eine Bereichsabfrage mit SELECT ... FOR UPDATE ausführen, eine andere Transaktion könnte parallel eine neue Zeile in genau diesen Bereich einfügen und committen, und ein zweiter identischer Lesevorgang der ersten Transaktion würde plötzlich eine zusätzliche Zeile sehen. Dieses Phänomen heißt Phantom Read, und Gap Locks sind InnoDBs Mechanismus, um es gezielt zu verhindern.

Wichtig für das Verständnis: Gap Locks sind reine Sperren gegen das Einfügen, sie verhindern nicht das Lesen. Zwei Transaktionen können problemlos denselben Gap gleichzeitig mit einem Gap Lock belegen, da Gap Locks sich untereinander nicht ausschließen, egal ob im Shared- oder Exclusive-Modus. Erst der Versuch, eine neue Zeile in den gesperrten Bereich einzufügen, führt zu einem Konflikt und damit potenziell zu einem Lock Wait.

Dieser Artikel geht Schritt für Schritt durch die praktische Seite von Gap Locks und Next-Key Locks: von der grundlegenden Funktionsweise über konkrete Beispiele mit Indizes bis zu den Strategien, mit denen sich unerwünschte Lock Waits gezielt reduzieren lassen, ohne auf die Konsistenzgarantien von InnoDB verzichten zu müssen.

2. Next-Key Locks: Record Lock plus Gap Lock

Ein Next-Key Lock ist die Standard-Sperrform, die InnoDB unter REPEATABLE READ für die meisten Zeilenzugriffe verwendet. Er kombiniert einen Record Lock auf einer konkreten Indexzeile mit einem Gap Lock auf der Lücke unmittelbar davor. Praktisch bedeutet das: Wird eine Zeile mit dem Wert 50 in einem Index gesperrt, sperrt InnoDB nicht nur diese Zeile, sondern auch den gesamten Bereich zwischen dem vorherigen Indexwert, etwa 40, und 50 selbst.

Diese Kombination erklärt, warum InnoDB Phantom Reads bei Bereichsabfragen unter REPEATABLE READ zuverlässig verhindert: Eine Abfrage wie SELECT * FROM orders WHERE amount BETWEEN 40 AND 50 FOR UPDATE setzt Next-Key Locks über die gesamte betroffene Spanne, sodass weder existierende Zeilen verändert noch neue Zeilen in den Bereich eingefügt werden können, bis die Transaktion abgeschlossen ist. Am oberen Ende der Indexstruktur, hinter dem letzten Eintrag, verwendet InnoDB einen speziellen Supremum-Pseudo-Datensatz, damit auch der Bereich nach der letzten existierenden Zeile durch einen Next-Key Lock abgedeckt werden kann.

Am unteren Ende der Indexstruktur, vor dem allerersten Eintrag, gilt dieselbe Logik in umgekehrter Richtung: InnoDB sperrt den Bereich vor dem kleinsten existierenden Indexwert, sobald eine Abfrage diesen Wert einschließt. Diese Symmetrie stellt sicher, dass Next-Key Locks lückenlos über die gesamte Wertespanne einer Spalte wirken, unabhängig davon, ob der abgefragte Bereich am Anfang, in der Mitte oder am Ende der vorhandenen Werte liegt.

-- Next-key lock: record lock on the row plus gap lock before it
START TRANSACTION;

SELECT * FROM orders WHERE amount BETWEEN 40 AND 50 FOR UPDATE;
-- InnoDB locks the matching rows AND the gaps between them,
-- preventing inserts of new rows with amount in that range

-- This insert from another session would have to wait:
-- INSERT INTO orders (amount) VALUES (45);

COMMIT;

3. Phantom Reads und warum REPEATABLE READ sie verhindern muss

Ein Phantom Read entsteht, wenn eine Transaktion eine Bereichsabfrage zweimal ausführt und beim zweiten Mal zusätzliche Zeilen sieht, die eine andere Transaktion inzwischen eingefügt und committet hat. Der SQL-Standard erlaubt Phantom Reads offiziell unter REPEATABLE READ, InnoDBs Implementierung geht jedoch bewusst weiter und verhindert sie in den meisten Fällen zusätzlich, was einer der Gründe ist, warum InnoDBs REPEATABLE READ strenger ist als die Minimalanforderung des Standards.

Für einfache SELECT-Abfragen ohne Sperrklausel übernimmt bereits der MVCC-Snapshot diese Aufgabe, da neu eingefügte Zeilen anderer Transaktionen im Snapshot schlicht nicht sichtbar sind. Für schreibende Zugriffe wie UPDATE, DELETE und SELECT ... FOR UPDATE reicht der Snapshot allein nicht aus, da diese Operationen immer mit dem aktuellsten Datenbestand arbeiten müssen. Hier übernehmen Gap Locks und Next-Key Locks die Aufgabe, Phantom Reads durch aktives Blockieren neuer Einfügungen zu verhindern, statt nur eine ältere Sicht auf bestehende Daten zu liefern.

Ein oft übersehener Sonderfall betrifft DELETE-Anweisungen mit einer Bereichsbedingung: Auch wenn im gesperrten Bereich aktuell keine einzige Zeile mehr existiert, weil sie bereits gelöscht wurde, bleibt der Next-Key Lock auf der Lücke bestehen, bis die Transaktion committet. Andere Transaktionen können in diesen scheinbar leeren Bereich trotzdem keine neuen Zeilen einfügen, ein Verhalten, das viele Entwickler beim ersten Kontakt mit InnoDBs Locking-Modell überrascht.

4. Gap Locks in der Praxis: Beispiele mit Indizes

Ein besonders anschauliches Beispiel für Gap Locks ist eine Tabelle mit einer Spalte status, die die Werte 10, 20 und 30 enthält, versehen mit einem sekundären Index auf dieser Spalte. Sperrt eine Transaktion die Zeile mit Status 20 mittels SELECT ... FOR UPDATE, setzt InnoDB einen Next-Key Lock, der sowohl die Zeile mit Status 20 als auch den Bereich zwischen 10 und 20 abdeckt. Der Versuch einer anderen Transaktion, eine neue Zeile mit Status 15 einzufügen, wird dadurch blockiert, obwohl Status 15 in der Tabelle noch gar nicht existiert und mit der ursprünglichen WHERE-Bedingung auf den ersten Blick nichts zu tun hat.

Dieses Verhalten überrascht viele Entwickler, die erwarten, dass eine Sperre auf Status 20 nur diese eine Zeile betrifft. In der Praxis führt das häufig zu scheinbar grundlosen Lock Waits bei parallelen Inserts in dieselbe Wertespanne einer indizierten Spalte, etwa bei fortlaufend vergebenen Bestellnummern, Zeitstempeln oder sequenziell steigenden IDs, die von mehreren Sessions gleichzeitig eingefügt werden.

-- Table: orders(id PK, status INT, INDEX idx_status(status))
-- Existing status values: 10, 20, 30

-- Session A
START TRANSACTION;
SELECT * FROM orders WHERE status = 20 FOR UPDATE;
-- Next-key lock covers status = 20 AND the gap (10, 20]

-- Session B, concurrently
START TRANSACTION;
INSERT INTO orders (status) VALUES (15);
-- BLOCKS: 15 falls inside the gap locked by session A,
-- even though session A never touched a row with status 15

5. Unerwartete Lock Waits durch Gap Locks

Die häufigste praktische Konsequenz von Gap Locks sind Lock Waits, die auf den ersten Blick unerklärlich wirken, weil die betroffenen Transaktionen scheinbar unterschiedliche Zeilen bearbeiten. Ein typisches Szenario: Mehrere parallele Prozesse fügen Bestellungen mit fortlaufenden Bestellnummern in eine Tabelle mit sekundärem Index auf dieser Nummer ein. Jeder Insert setzt kurzzeitig einen Insert Intention Lock, der mit den Gap Locks anderer, noch offener Transaktionen kollidieren kann, insbesondere wenn eine dieser Transaktionen ungewöhnlich lange offen bleibt.

Ein zweites häufiges Symptom sind Deadlocks, die auf Gap Locks zurückzuführen sind, wie im separaten Artikel dieser Serie zu MySQL-Deadlocks im Detail beschrieben. Da mehrere Transaktionen gleichzeitig kompatible Gap Locks im Shared-Modus auf demselben Bereich halten können, aber beim Versuch, diese Gap Locks in echte Insert-Sperren umzuwandeln, in Konflikt geraten, entstehen Wartezyklen, die auf den ersten Blick nicht wie klassische Zeilenkonflikte aussehen. Die Diagnose über SHOW ENGINE INNODB STATUS zeigt in solchen Fällen häufig lock_mode X locks gap before rec als Hinweis auf einen reinen Gap-Konflikt statt eines Record-Konflikts.

Praktisch beobachtet man dieses Muster häufig bei Magento-Bestandsverwaltung oder ähnlichen Systemen, bei denen mehrere parallele Worker-Prozesse Datensätze mit steigenden IDs in dieselbe indizierte Spalte einfügen. Solange alle Worker in derselben Reihenfolge und mit möglichst kurzen Transaktionen arbeiten, bleibt das Risiko gering, aber sobald ein einzelner Worker durch einen externen API-Aufruf länger blockiert, staut sich die Warteschlange der übrigen Worker an genau diesem Gap.

6. READ COMMITTED reduziert Gap Locks deutlich

Unter dem Isolation Level READ COMMITTED reduziert InnoDB die Verwendung von Gap Locks erheblich. Der Grund liegt darin, dass READ COMMITTED keinen konsistenten Snapshot über die gesamte Transaktionsdauer garantiert und daher auch nicht denselben strengen Phantom-Read-Schutz wie REPEATABLE READ benötigt. Für Bereichsabfragen mit SELECT ... FOR UPDATE setzt InnoDB unter READ COMMITTED lediglich Record Locks auf tatsächlich existierende, passende Zeilen, nicht aber Gap Locks auf die Lücken dazwischen.

Diese Reduktion macht READ COMMITTED zu einer praktikablen Option für Anwendungsfälle mit hoher paralleler Insert-Last in dieselbe Wertespanne einer indizierten Spalte, etwa bei Systemen mit sehr vielen gleichzeitigen Bestellungen oder Log-Einträgen. Der Kompromiss: Man verzichtet auf den zusätzlichen Phantom-Read-Schutz von REPEATABLE READ, gewinnt dafür aber spürbar reduzierte Lock-Wait-Häufigkeit. Diese Abwägung sollte pro Anwendungsfall bewusst getroffen werden, nicht global für die gesamte Datenbank.

Wichtig ist dabei, dass die Umstellung auf READ COMMITTED nicht rückwirkend für bereits laufende Transaktionen gilt, sondern erst für Transaktionen, die nach der Umstellung neu gestartet werden. Wer den Isolation Level nur für einzelne insert-lastige Codepfade ändern möchte, etwa einen Bulk-Import-Endpunkt, sollte das gezielt über SET TRANSACTION ISOLATION LEVEL direkt vor der jeweiligen Transaktion tun, statt den Session- oder gar globalen Default zu verändern.

-- Under READ COMMITTED, gap locks are largely avoided
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

SELECT * FROM orders WHERE status = 20 FOR UPDATE;
-- Only a record lock on the matching row(s), no gap lock

-- This insert from another session now succeeds immediately
-- INSERT INTO orders (status) VALUES (15);

COMMIT;

7. Insert Intention Locks

Ein Insert Intention Lock ist eine spezielle Variante des Gap Locks, die InnoDB automatisch vor jedem INSERT in eine gesperrte Lücke setzt. Anders als ein regulärer Gap Lock, der andere Gap Locks im selben Bereich nicht blockiert, signalisiert ein Insert Intention Lock die konkrete Absicht, eine neue Zeile in genau diesen Bereich einzufügen. Zwei Insert Intention Locks auf unterschiedliche Positionen innerhalb derselben Lücke blockieren sich dabei nicht gegenseitig, solange die exakten Einfügepositionen unterschiedlich sind.

Diese Feinheit ist wichtig für das Verständnis paralleler Inserts: Fügen zwei Transaktionen gleichzeitig unterschiedliche neue Werte in dieselbe Lücke ein, etwa 12 und 17 zwischen den existierenden Werten 10 und 20, können beide Inserts parallel erfolgreich abgeschlossen werden, da ihre Insert Intention Locks unterschiedliche Positionen innerhalb der Lücke markieren. Erst wenn ein regulärer Gap Lock oder Next-Key Lock einer anderen, noch offenen Transaktion diesen Bereich bereits exklusiv beansprucht, muss der Insert Intention Lock warten.

In SHOW ENGINE INNODB STATUS erscheint ein Insert Intention Lock mit dem Hinweis lock_mode X locks gap before rec insert intention, gut unterscheidbar vom regulären Gap Lock ohne diesen Zusatz. Diese textuelle Markierung hilft bei der Diagnose erheblich, weil sie sofort zeigt, ob eine wartende Transaktion tatsächlich einfügen möchte oder nur eine bereits bestehende Zeile lesend oder schreibend sperren will.

8. Unique Index vs. Secondary Index im Locking

Ein entscheidender Unterschied im Gap-Lock-Verhalten besteht zwischen exakten Zugriffen über einen eindeutigen Index und Zugriffen über einen sekundären, nicht eindeutigen Index. Sucht eine Transaktion über einen Unique Index, etwa den Primary Key, nach genau einem Wert mit einer Gleichheitsbedingung, setzt InnoDB ausschließlich einen Record Lock auf diese eine Zeile, ohne zusätzlichen Gap Lock. Der Grund: Da der Index eindeutig ist, kann keine weitere Zeile mit demselben Wert existieren, ein Phantom Read in diesem engen Sinn ist logisch ausgeschlossen.

Anders bei einem sekundären, nicht eindeutigen Index oder bei einer Bereichsabfrage über einen Unique Index: Hier setzt InnoDB grundsätzlich Next-Key Locks, die den gesamten betroffenen Bereich inklusive der Lücken abdecken. Diese Unterscheidung erklärt, warum ein exakter Zugriff über den Primary Key deutlich seltener zu unerwarteten Lock Waits führt als ein Zugriff über einen sekundären Index mit denselben WHERE-Bedingungen, selbst wenn beide Abfragen scheinbar dieselbe einzelne Zeile betreffen sollen.

Diese Erkenntnis lässt sich gezielt für das Schema-Design nutzen: Wo immer möglich, sollten häufig für SELECT ... FOR UPDATE genutzte Spalten als Unique Index modelliert werden, etwa durch einen zusammengesetzten Unique Index aus mehreren fachlich eindeutigen Spalten, statt sich auf einen einfachen, nicht eindeutigen sekundären Index zu verlassen. Der zusätzliche Aufwand bei der Schemaplanung zahlt sich in Form spürbar reduzierter Gap-Lock-Konflikte unter hoher Parallelität aus.

-- Unique index equality lookup: record lock only, no gap lock
START TRANSACTION;
SELECT * FROM orders WHERE id = 4711 FOR UPDATE;  -- id is the PRIMARY KEY
-- other inserts near id=4711 are NOT blocked

-- Secondary, non-unique index equality lookup: next-key lock
START TRANSACTION;
SELECT * FROM orders WHERE status = 20 FOR UPDATE;  -- status has a secondary index
-- gap before and around status=20 IS locked, inserts nearby may block

9. Gap Locks bei Auto-Increment und Range-Queries

Spalten mit AUTO_INCREMENT nutzen einen eigenen, speziellen Locking-Mechanismus, der von den regulären Gap Locks zu unterscheiden ist. Der klassische AUTO_INCREMENT-Lock, gesteuert über den Parameter innodb_autoinc_lock_mode, sorgt dafür, dass fortlaufende Werte auch bei parallelen Inserts ohne Lücken oder Duplikate vergeben werden, ohne dass dafür klassische Gap Locks über den gesamten Wertebereich nötig sind. Im Standardmodus (interleaved) wird dieser spezielle Lock nur kurzzeitig für die Wertevergabe selbst gehalten, nicht für die gesamte Transaktionsdauer.

Dieser Mechanismus zeigt exemplarisch, dass nicht jede Sperre in InnoDB ein Gap Lock im engeren Sinne ist, auch wenn beide Konzepte oft in einem Atemzug genannt werden, weil beide primär beim Einfügen relevant werden.

Bei Range-Queries mit BETWEEN oder Vergleichsoperatoren wie < und > auf einer indizierten Spalte setzt InnoDB dagegen reguläre Next-Key Locks über den gesamten abgefragten Bereich. Je größer der abgefragte Bereich, desto mehr Zeilen und Lücken werden gesperrt, was bei breiten Bereichsabfragen unter REPEATABLE READ zu erheblichen Lock-Wait-Häufungen führen kann. Eine bewusste Einschränkung des abgefragten Bereichs, etwa durch das Aufteilen einer großen Bereichsabfrage in mehrere kleinere Transaktionen, reduziert die Anzahl gleichzeitig gehaltener Gap Locks spürbar.

Der Parameter innodb_autoinc_lock_mode kennt drei Werte: 0 (traditional) hält den Lock über die gesamte INSERT-Anweisung, 1 (consecutive, Standard) nur für die eigentliche Wertevergabe, und 2 (interleaved) erlaubt die höchste Parallelität, kann bei gemischten INSERT-Typen aber zu Lücken in der Wertefolge führen. Für statement-basierte Replikation ist Modus 2 ungeeignet, da die Reihenfolge der vergebenen Werte dort nicht mehr deterministisch reproduzierbar ist.

10. Zusammenfassung

Ein Gap Lock sperrt die Lücke zwischen zwei Indexeinträgen, nicht eine konkrete Zeile, und existiert ausschließlich, um Phantom Reads unter InnoDBs Standard-Isolation-Level REPEATABLE READ zu verhindern. Ein Next-Key Lock kombiniert diesen Gap Lock mit einem Record Lock auf der zugehörigen Zeile und ist die Standard-Sperrform für die meisten schreibenden Zugriffe. In der Praxis führt dieser Mechanismus regelmäßig zu Lock Waits, die auf den ersten Blick unerklärlich wirken, weil parallele Transaktionen scheinbar unterschiedliche Zeilen betreffen, tatsächlich aber in denselben gesperrten Bereich einfügen wollen.

Wer Gap Locks gezielt umgehen will, hat mehrere Werkzeuge: Zugriffe über einen Unique Index statt einen sekundären Index minimieren, READ COMMITTED für insert-lastige Anwendungsfälle in Betracht ziehen, wo der zusätzliche Phantom-Read-Schutz nicht zwingend nötig ist, und Bereichsabfragen so eng wie möglich gestalten. Wer diese Mechanismen versteht, statt Lock Waits als undurchsichtige Blackbox hinzunehmen, kann InnoDBs Locking-Verhalten gezielt für die eigene Anwendung optimieren.

11. FAQ: Gap Locks und Next-Key Locks in InnoDB