Row Locks, Gap Locks und InnoDB-Sperren verständlich erklärt

1. Warum InnoDB-Sperren verstehen so wichtig ist

InnoDB Sperren sind in vielen Teams ein blinder Fleck. Man arbeitet mit Transaktionen, SELECT FOR UPDATE und Isolationsleveln, aber das genaue Verhalten — welche Zeilen gesperrt werden, ob der Lock auch die Lücke daneben einschließt, wie lange er gehalten wird — bleibt oft unklar. Das Ergebnis sind schwer reproduzierbare Deadlocks, Wartezeiten unter Last und Fehler, die nur in Produktion auftreten.

InnoDB unterscheidet mehrere Sperr-Typen, die auf verschiedenen Granularitätsebenen arbeiten: von der einzelnen Zeile bis zum gesamten Index-Gap. Der Standard-Isolationslevel REPEATABLE READ aktiviert dabei automatisch einen besonders breiten Sperrmechanismus — den Next-Key Lock — der verhindert, dass andere Transaktionen neue Zeilen in einen gesperrten Bereich einfügen. Das ist kein Bug, sondern gewolltes Verhalten zum Schutz vor Phantom Reads. Nur wer diesen Mechanismus versteht, kann den Lock-Scope gezielt reduzieren und Deadlocks vermeiden, statt nur zu reagieren.

2. Shared (S) vs. Exclusive (X) Locks

Der fundamentalste Unterschied bei InnoDB Sperren liegt zwischen lesenden und schreibenden Absichten. Ein Shared Lock (S) erlaubt mehreren Transaktionen gleichzeitig das Lesen einer Zeile. Ein Exclusive Lock (X) reserviert die Zeile exklusiv für eine Transaktion — niemand sonst kann gleichzeitig lesen oder schreiben. Beide Sperr-Typen wirken auf Row-Ebene, also auf einzelne Index-Records.

-- Shared lock: multiple transactions can hold this simultaneously
SELECT * FROM sales_order WHERE entity_id = 100 LOCK IN SHARE MODE;

-- Exclusive lock: only one transaction can hold this at a time
SELECT * FROM sales_order WHERE entity_id = 100 FOR UPDATE;

-- Compatibility matrix (rows = held lock, cols = requested):
--         S      X
--   S  |  ok  | wait
--   X  | wait | wait

-- Two sessions can both hold S-lock on same row at the same time.
-- Any session requesting X-lock must wait for all S-locks to be released.

Die Kompatibilitätsmatrix ist entscheidend: Shared Locks blockieren sich gegenseitig nicht, aber sie blockieren jeden Exclusive Lock. Wer daher LOCK IN SHARE MODE für eine lesende Prüfung nutzt, blockiert alle parallelen Schreibversuche auf dieselbe Zeile — manchmal gewollt, oft unbeabsichtigt.

3. Intention Locks auf Tabellenebene

Damit InnoDB nicht für jede Zeilen-Lock-Anfrage die gesamte Tabelle scannen muss, gibt es Intention Locks auf Tabellenebene. Ein Intention Shared Lock (IS) signalisiert, dass eine Transaktion plant, S-Locks auf einzelne Zeilen zu setzen. Ein Intention Exclusive Lock (IX) signalisiert geplante X-Locks. Diese Intention Locks werden automatisch vor jedem Row-Lock gesetzt.

Dieser Mechanismus ermöglicht es InnoDB, bei einer LOCK TABLES ... WRITE-Anfrage sofort zu prüfen, ob die Tabelle frei ist, ohne alle existierenden Zeilen-Locks zu inspizieren. IS- und IX-Locks sind untereinander und mit Row-Locks kompatibel. Nur wenn ein vollständiger Tabellen-Lock angefordert wird, blockieren IS/IX-Locks ihn.

4. Record Lock: Eine einzelne Zeile sperren

Ein Record Lock sperrt genau einen einzelnen Index-Record. Wichtig: InnoDB sperrt keine physischen Zeilen, sondern Index-Einträge. Wenn eine Tabelle keinen passenden Index für die WHERE-Bedingung hat, sperrt InnoDB über den Clustered Index (Primärschlüssel) alle gescannten Zeilen — bei einem Full Table Scan faktisch die gesamte Tabelle.

-- Record lock on a single row via primary key — minimal lock scope
BEGIN;
SELECT entity_id, status, grand_total
FROM sales_order
WHERE entity_id = 12345
FOR UPDATE;
-- Only the row with entity_id = 12345 is locked (record lock, no gap)
-- All other rows in the table remain freely accessible
COMMIT;

Record Locks entstehen, wenn die WHERE-Bedingung einen Unique-Index oder den Primärschlüssel exakt trifft — also kein Bereich, sondern ein einzelner, vorhandener Wert selektiert wird. Das ist die engstmögliche InnoDB Sperre und daher das Ideal für hochnebenläufige Szenarien.

5. Gap Lock: Die Lücke zwischen Index-Records

Ein Gap Lock sperrt nicht eine Zeile, sondern die Lücke zwischen zwei Index-Records — oder zwischen dem letzten Record und dem Ende des Index. Sein einziger Zweck ist es, zu verhindern, dass andere Transaktionen neue Zeilen per INSERT in diesen Bereich einfügen. Gap Locks sind untereinander kompatibel: Zwei Transaktionen können denselben Gap gleichzeitig sperren, weil beide nur INSERTs verhindern wollen.

-- Range query triggers gap locks in REPEATABLE READ
BEGIN;
SELECT entity_id, status
FROM sales_order
WHERE entity_id BETWEEN 100 AND 200
FOR UPDATE;
-- InnoDB acquires:
--   X record locks on all existing rows with entity_id IN [100, 200]
--   Gap locks on all gaps between those rows (including before 100 and after 200)
--   No other transaction can INSERT a row with entity_id in [100, 200]
COMMIT;

-- Gap lock also triggers when the searched value does NOT exist:
BEGIN;
SELECT * FROM sales_order WHERE entity_id = 999 FOR UPDATE;
-- If entity_id = 999 does not exist, a gap lock is placed on
-- the gap surrounding where 999 would be in the index
COMMIT;

6. Next-Key Lock: InnoDB-Standard in REPEATABLE READ

Der Next-Key Lock ist die Kombination aus Record Lock und Gap Lock auf den Gap vor diesem Record. Er ist der Standard-Sperrmechanismus von InnoDB im Isolationslevel REPEATABLE READ. Der Grund liegt im Phantom-Read-Schutz: Ohne Next-Key Locks könnte eine Transaktion beim erneuten Ausführen derselben Bereichsabfrage plötzlich neue Zeilen sehen, die eine andere Transaktion in der Zwischenzeit eingefügt hat.

-- Index values in table: (10, 20, 30, 40)
-- Query: SELECT ... WHERE col > 15 AND col < 35 FOR UPDATE

-- Next-key locks created (each covers gap + record):
--   (10, 20]   -- gap lock on (10,20) + record lock on 20
--   (20, 30]   -- gap lock on (20,30) + record lock on 30
--   (30, +inf) -- gap lock after 30 (supremum pseudo-record)

-- Result: no other transaction can INSERT col = 18, 25, 31, or any
-- value that would fall into the locked range

-- To get only a record lock (no gap), use an exact unique key lookup:
SELECT id, col FROM t WHERE id = 20 FOR UPDATE;
-- LOCK_MODE in data_locks will show: X,REC_NOT_GAP (record only)

Next-Key Locks sind die häufigste Ursache für unerwartete Lock-Wartezeiten. Besonders bei schlecht selektiven Indizes oder Bereichsabfragen auf großen Tabellen kann ein einziger SELECT FOR UPDATE Hunderte von Zeilen und alle Gaps dazwischen sperren — ein Muster, das unter Einzellast harmlos aussieht und unter Nebenläufigkeit sofort zu Deadlocks führt.

7. Lock-Scope reduzieren

Es gibt mehrere erprobte Strategien, um InnoDB Sperren auf ein Minimum zu reduzieren und damit Deadlocks und Wartezeiten zu verringern.

Strategie 1: Enge WHERE-Klauseln mit hoch-selektiven Indizes. Je präziser der Index-Scan, desto weniger Zeilen und Gaps werden gesperrt. Ein Primärschlüssel-Lookup sperrt genau eine Zeile, kein Gap.

Strategie 2: Isolationslevel auf READ COMMITTED senken. In READ COMMITTED verwendet InnoDB keine Gap Locks und gibt Record Locks auf Zeilen frei, die von der WHERE-Bedingung nicht zurückgegeben werden. Das reduziert den Lock-Scope erheblich, eliminiert aber den Phantom-Read-Schutz.

-- Switch isolation level for a session to reduce gap locks
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Range queries now use only record locks on matching rows
BEGIN;
SELECT entity_id FROM sales_order WHERE status = 'processing' FOR UPDATE;
-- In READ COMMITTED: only rows actually returned are locked
-- In REPEATABLE READ: would also lock all gaps in the index scan range
COMMIT;

-- Reset to server default
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Strategie 3: Transaktionen kurz halten. Jede Zeile bleibt gesperrt, solange die Transaktion offen ist. Lange Transaktionen akkumulieren Locks. Wer teure Berechnungen oder externe API-Calls innerhalb einer Transaktion macht, riskiert unnötig lange Lock-Haltezeiten.

Strategie 4: SELECT FOR UPDATE nur wo nötig. Wenn kein Schreiben geplant ist, aber eine konsistente Lesesperre benötigt wird, reicht LOCK IN SHARE MODE. Das erlaubt anderen Transaktionen weiterhin lesend zuzugreifen.

8. Aktive Sperren mit performance_schema diagnostizieren

In MySQL 8 ist performance_schema.data_locks die zentrale Tabelle, um aktive InnoDB Sperren zu inspizieren. Das Feld LOCK_TYPE zeigt, ob es sich um einen TABLE- oder RECORD-Lock handelt. Das Feld LOCK_MODE gibt den genauen Modus aus — inklusive Gap-Lock-Information, die in früheren MySQL-Versionen nur aus dem INNODB STATUS extrahiert werden konnte.

-- Show all active InnoDB locks (MySQL 8+)
SELECT
    dl.ENGINE_LOCK_ID,
    dl.THREAD_ID,
    dl.OBJECT_SCHEMA,
    dl.OBJECT_NAME,
    dl.LOCK_TYPE,    -- TABLE or RECORD
    dl.LOCK_MODE,    -- X | X,GAP | X,REC_NOT_GAP | S | S,GAP | IX | IS
    dl.LOCK_STATUS,  -- GRANTED or WAITING
    dl.LOCK_DATA     -- index value or 'supremum pseudo-record' for end-of-index gap
FROM performance_schema.data_locks dl
ORDER BY dl.THREAD_ID, dl.LOCK_TYPE;

-- Find lock waits: which thread blocks which, and on which key?
SELECT
    r.THREAD_ID        AS waiting_thread,
    r.OBJECT_NAME      AS table_name,
    r.LOCK_MODE        AS waiting_for,
    b.THREAD_ID        AS blocking_thread,
    b.LOCK_MODE        AS blocking_with,
    b.LOCK_DATA        AS blocked_on_key
FROM performance_schema.data_lock_waits w
INNER JOIN performance_schema.data_locks r
    ON r.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
INNER JOIN performance_schema.data_locks b
    ON b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID;

Die LOCK_MODE-Werte im Überblick: X,GAP ist ein reiner Gap Lock (keine Zeile gesperrt). X,REC_NOT_GAP ist ein Record Lock ohne Gap-Anteil. X allein ist ein Next-Key Lock. IX ist ein Intention Exclusive Lock auf Tabellenebene.

9. NOWAIT und SKIP LOCKED

Seit MySQL 8.0 können Anwendungen mit NOWAIT und SKIP LOCKED nicht-blockierend auf gesperrte Zeilen reagieren, anstatt auf Lock-Freigabe zu warten.

-- NOWAIT: return an error immediately if the row is locked
SELECT entity_id, status
FROM sales_order
WHERE entity_id = 100
FOR UPDATE NOWAIT;
-- If entity_id = 100 is locked: ERROR 3572 (ER_LOCK_NOWAIT)
-- Application can catch this and retry or show a conflict message

-- SKIP LOCKED: return only rows that are NOT currently locked
-- Ideal pattern for queue workers processing rows in parallel
SELECT entity_id, increment_id
FROM sales_order
WHERE status = 'pending'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- Worker A gets rows 1-10, Worker B (running concurrently) gets rows 11-20
-- No deadlock possible because locked rows are simply skipped

SKIP LOCKED ist das Schlüsselmuster für Queue-basierte Verarbeitung. In Magento-Setups, wo mehrere Cron-Prozesse gleichzeitig Bestellungen oder Nachrichten verarbeiten, verhindert es, dass alle Worker auf denselben Lock warten. Jeder Worker erhält exklusiv einen nicht gesperrten Batch und bearbeitet ihn ohne Interferenz.

11. Zusammenfassung

InnoDB Sperren sind kein akademisches Detail, sondern ein praktisches Produktionsthema. Wer nicht unterscheidet, ob InnoDB einen Record Lock, einen Gap Lock oder einen Next-Key Lock setzt, wird Deadlocks und Lock-Timeouts nicht dauerhaft lösen — nur symptomatisch behandeln. Die Schlüsselerkenntnisse: REPEATABLE READ erzeugt standardmäßig Next-Key Locks, um Phantom Reads zu verhindern. READ COMMITTED reduziert Gap Locks, verliert aber Phantom-Protection. Mit performance_schema.data_locks in MySQL 8 lässt sich jeder aktive Lock präzise inspizieren.

Für Queue-artige Verarbeitung bieten NOWAIT und SKIP LOCKED elegante Wege, Lock-Wartezeiten komplett zu vermeiden. Die wichtigste Praxisregel: Transaktionen kurz halten, WHERE-Bedingungen so präzise wie möglich formulieren und Gap Locks nur dann in Kauf nehmen, wenn Phantom-Read-Schutz wirklich benötigt wird.

12. FAQ: InnoDB Sperren