Sperren von Zeilen in SQL: Inside SELECT FOR UPDATE

SELECT FOR UPDATE ist ein Row-Level-Locking-Mechanismus in SQL, der verwendet wird, um die in einer Transaktion abgerufenen Zeilen zu sperren.

Sein Zweck ist es, andere Transaktionen daran zu hindern, diese Zeilen zu modifizieren oder Sperren für sie zu erhalten, und er wird häufig in Szenarien verwendet, in denen die Datenkonsistenz sichergestellt werden muss.

In MySQL ist die spezifische Implementierung von SELECT FOR UPDATE eng mit der Storage Engine (wie InnoDB) verbunden. Im Kern verwendet sie Row Locks, um die Zielzeilen zu sperren.

Kernfunktionen

Art der Sperre:

SELECT FOR UPDATE platziert exklusive Sperren (X Locks) auf den durch die Abfrage abgerufenen Zeilen.
Andere Transaktionen können diese Zeilen nicht modifizieren oder Shared oder Exclusive Locks auf ihnen platzieren.

Anwendungsbereich:

Muss innerhalb einer Transaktion verwendet werden (d. h. zwischen BEGIN und COMMIT).
Nur wirksam bei Verwendung einer Storage Engine, die Transaktionen unterstützt (wie InnoDB).

Verhalten:

Wenn die Zielzeilen bereits durch eine andere Transaktion gesperrt sind, wechselt die aktuelle Transaktion in einen Wartezustand, bis die Sperre freigegeben wird oder ein Timeout auftritt.

Implementierungsprinzip

InnoDB Row Locking Mechanismus

Die InnoDB Storage Engine implementiert Row-Level-Locking über Indizes.
SELECT FOR UPDATE sperrt alle Zeilen, die die Abfragebedingung erfüllen. Wenn die Abfrage keinen Index verwendet, wird sie zu einer Tabellensperre herabgestuft, wodurch die gesamte Tabelle gesperrt wird.

Sperrprozess

Während der Abfrageausführung versucht InnoDB, eine exklusive Sperre auf jede gescannte Zeile zu setzen.
Wenn eine Zeile bereits durch eine andere Transaktion gesperrt ist, wartet die aktuelle Transaktion, bis diese Sperre freigegeben wird.

Arten von Sperren

Row Lock: Indexbasierte Sperre, die nur die abgerufenen Zeilen sperrt.
Gap Lock: Unter der Isolationsstufe REPEATABLE READ wird, wenn eine Bereichsabfrage keine Zeilen findet, eine Sperre auf die Lücke zwischen dem Bereich gesetzt, um zu verhindern, dass neue Zeilen eingefügt werden.

Auswirkung von Transaktionsisolationsstufen

READ COMMITTED: Jede Abfrage liest die neuesten Daten und sperrt nur die von der aktuellen Abfrage zurückgegebenen Zeilen.
REPEATABLE READ: Das Abfrageergebnis basiert auf einem Transaktions-Snapshot, und der gesperrte Bereich kann nicht übereinstimmende Zeilen enthalten (aufgrund von Gap Locking).
SERIALIZABLE: Sperrt alle Daten innerhalb des Abfragebereichs.

Ausführungsprozess

Das Folgende veranschaulicht den Ausführungsprozess von SELECT FOR UPDATE am Beispiel von InnoDB:

Transaktion starten: Beginnen Sie die Transaktion mit BEGIN.
Abfragen und Sperren: Führen Sie SELECT ... FOR UPDATE aus, um eine exklusive Sperre (X Lock) auf Datensätze anzuwenden, die die Bedingung erfüllen.
Datenoperationen: Ändern oder lesen Sie die gesperrten Daten.
Sperre freigeben: Sobald die Transaktion committet wird (COMMIT), wird die Sperre freigegeben. Wenn die Transaktion zurückgerollt wird (ROLLBACK), wird die Sperre ebenfalls freigegeben.

Beispiele

Grundlegende Verwendung

BEGIN;
SELECT * FROM orders WHERE order_id = 1 FOR UPDATE;
/* Sperrt die Zeile mit order_id = 1 */
UPDATE orders SET status = 'processed' WHERE order_id = 1;
COMMIT;

Multi-Transaktions-Konkurrenz-Szenario

Transaktion A:

BEGIN;
SELECT * FROM orders WHERE order_id = 1 FOR UPDATE;
-- Sperrt die Zeile mit order_id = 1

Transaktion B (bevor Transaktion A committet):

BEGIN;
SELECT * FROM orders WHERE order_id = 1 FOR UPDATE;
-- Wartet darauf, dass Transaktion A die Sperre freigibt

Transaktion B kann die Sperre erst erhalten, wenn Transaktion A entweder committet oder ein Rollback durchführt.

Verhalten von Gap Locks

Unter der Isolationsstufe REPEATABLE READ kann eine Bereichsabfrage eine Gap Lock auslösen. Zum Beispiel:

SELECT * FROM orders WHERE order_id BETWEEN 10 AND 20 FOR UPDATE;

Wenn die Abfrage keine Ergebnisse zurückgibt, platziert InnoDB dennoch eine Gap Lock auf den Bereich zwischen 10 und 20, wodurch verhindert wird, dass andere Transaktionen neue Datensätze innerhalb dieses Bereichs einfügen.

Optimierung und Überlegungen

Verwendung von Indizes:

Abfragen, die Indizes verwenden, wenden Row-Level-Locks an und vermeiden eine Herabstufung auf Tabellen-Level-Locks.
Wenn keine Indizes verwendet werden, kann die gesamte Tabelle gesperrt werden, was die Leistung der Parallelität negativ beeinflusst.

Vermeiden Sie lange Transaktionen:

Das Halten von Sperren für zu lange Zeit kann dazu führen, dass andere Transaktionen blockiert werden. Es wird empfohlen, die Transaktionsdauer zu minimieren.

Deadlock-Erkennung:

InnoDB erkennt Deadlocks automatisch und rollt eine der Transaktionen zurück. Es ist ratsam, die Transaktionslogik sorgfältig zu entwerfen, um Deadlocks zu vermeiden.

Verwendung gemäß Geschäftsanforderungen:

Verwenden Sie SELECT FOR UPDATE nur, wenn es erforderlich ist, um Datenmodifikationskonflikte zu verhindern, um unnötige Lock-Konflikte zu vermeiden.

Zusammenfassung

SELECT FOR UPDATE ist eine Sperroperation in MySQL, die den Row-Lock-Mechanismus verwendet, um gleichzeitige Modifikationskonflikte zu verhindern.
Seine Implementierung basiert auf Transaktionen, Isolationsstufen und Indexoptimierung.
Bei angemessener Verwendung kann SELECT FOR UPDATE die Datenkonsistenz effektiv schützen, aber es muss auf den Leistungs-Overhead geachtet werden, um Lock-Konflikte und Deadlocks zu vermeiden.

Wir sind Leapcell, Ihre erste Wahl für das Hosting von Backend-Projekten.

Leapcell ist die Next-Gen Serverless Platform für Webhosting, Async Tasks und Redis:

Multi-Language Support

Entwickeln Sie mit Node.js, Python, Go oder Rust.

Stellen Sie unbegrenzt Projekte kostenlos bereit

zahlen Sie nur für die Nutzung – keine Anfragen, keine Gebühren.

Unschlagbare Kosteneffizienz

Pay-as-you-go ohne Leerlaufgebühren.
Beispiel: 25 $ unterstützen 6,94 Mio. Anfragen bei einer durchschnittlichen Antwortzeit von 60 ms.

Optimierte Entwicklererfahrung

Intuitive Benutzeroberfläche für mühelose Einrichtung.
Vollautomatische CI/CD-Pipelines und GitOps-Integration.
Echtzeitmetriken und -protokollierung für verwertbare Erkenntnisse.

Mühelose Skalierbarkeit und hohe Leistung

Auto-Scaling zur einfachen Bewältigung hoher Parallelität.
Null Betriebsaufwand – konzentrieren Sie sich einfach auf das Bauen.

Erfahren Sie mehr in der Dokumentation!

Folgen Sie uns auf X: @LeapcellHQ