InnoDB-Transaktionssperren und Isolationsstufen in MySQL

    Hallo habr
    Ich empfehle jedem, sich an das Wesentliche der InnoDB-Engine-Sperren in MySQL zu erinnern oder es zu verstehen.


    KDPV: Deadlock der tropischen Fauna



    Grundlegende Konzepte


    Jeder, denke ich, weiß bereits, dass InnoDB Sperren auf Zeilenebene verwendet. In Abhängigkeit von der Isolationsstufe können Transaktionen sowohl Zeilen blockieren, die in die resultierende Auswahl fallen, als auch alle Zeilen, die während der Suche angezeigt wurden. In REPEATABLE READ muss zum Beispiel eine blockierende Abfrage ohne Verwendung eines Index über die gesamte Tabelle iteriert und daher alle Datensätze gesperrt werden. Denken Sie daher daran,% username%, die richtige Auswahl der Indizes wirkt sich direkt auf die Geschwindigkeit der Sperren aus.

    Es gibt zwei grundlegende Arten von Sperren:
    • Shared Lock - Eine Shared Lock-Sperre, mit der andere Transaktionen die Zeile lesen und dieselbe Shared Lock-Sperre aktivieren können. Das Ändern der Zeile oder das Setzen einer exklusiven Sperre ist jedoch nicht möglich.
    • Exklusive Sperre - Die exklusive Sperre verhindert, dass andere Transaktionen die Leitung sperren, und kann die Leitung in Abhängigkeit von der aktuellen Isolationsstufe (über welche unten) sowohl zum Schreiben als auch zum Lesen sperren.

    Wenn Sie tiefer graben, stellt sich heraus, dass es zwei weitere Arten von Sperren gibt. Nennen wir sie "Absichts" -Sperren. Sie können einen Eintrag in InnoDB nicht einfach übernehmen und blockieren. Die gemeinsamen Intent- und Intention-Exclusive- Sperren sind Sperren auf Tabellenebene und blockieren nur die Erstellung anderer Sperren und Operationen für die gesamte LOCK TABLE-Tabelle. Das Auferlegen einer solchen Sperre durch eine Transaktion zeigt nur die Absicht der Transaktion an, die entsprechende gemeinsame oder ausschließliche Zeilensperre zu erhalten.

    Wenn die für die Zeile auferlegte Sperre die Ausführung der Operation nicht ermöglicht, wartet die Transaktion einfach darauf, dass die störende Sperre aufgehoben wird. Bei Cross-Locks kann man lange warten, das ist Deadlock. In der MySQL-Dokumentation finden Sie einige Tipps .wie man Deadlocks vermeidet.

    InnoDB legt Sperren nicht für die Datenzeilen selbst fest, sondern für Indexdatensätze. Die eine oder andere Sperre kann auferlegt werden:
    • Datensatzsperre - Datensatzindex sperren
    • Lückensperre - sperrt die Lücke zwischen, vor oder nach der Indexaufnahme
    • next-key lock - sperrt den Indexeintrag und die Lücke davor

    Eine Lückenblockierung ist erforderlich, um das Auftreten von Phantomdatensätzen zu vermeiden, wenn beispielsweise zwischen zwei identischen Ablesungen eines Bereichs eine benachbarte Transaktion es schafft, einen Datensatz in diesen Bereich einzufügen.

    Alles, was oben beschrieben wurde, wird implizit von InnoDB definiert. Sie müssen sich nur vorstellen, was „unter der Haube“ passiert.
    Welche Anfragen genau Sperren auferlegen, können Sie nochmals in der Dokumentation nachlesen .

    Bevor Sie zu den Isolationsstufen übergehen, sollten Sie das Konzept des konsistenten Lesens ( konsistenten Lesens ) berücksichtigen .
    Zum Zeitpunkt der ersten Anforderung wird in der Transaktion (der sogenannten Leseansicht) ein Datenbankdaten-Snapshot erstellt, der nicht von Änderungen bei parallelen Transaktionen betroffen ist, sondern sich auf die Änderungen in der aktuellen auswirkt. Das Lesen aus einem solchen Schnappschuss wird als nicht blockierendes konsistentes Lesen bezeichnet. Nicht blockieren - da für das Erstellen eines Schnappschusses keine hängenden Sperren erforderlich sind -, da keine Katastrophen in der Außenwelt (mit Ausnahme von DROP TABLE und ALTER TABLE) die gemütliche Welt des Schnappschusses beeinträchtigen. InnoDB kann aufgefordert werden, vor der ersten Anforderung in der Transaktion einen Snapshot zu erstellen. Dies muss zu Beginn der Transaktion angegeben werden - START TRANSACTION WITH CONSISTENT SNAPSHOT.

    InnoDB-Transaktionsisolationsstufen


    Die Isolationsstufe kann mit der Abfrage SET [GLOBAL | geändert werden SESSION] TRANSACTION ISOLATION LEVEL.

    REPEATABLE READ (Standardwert)

    • Konsistentes Lesen (SELECT) blockiert nichts, es liest Zeilen aus einem Snapshot, der beim ersten Lesen einer Transaktion erstellt wurde. Dieselben Abfragen geben immer dasselbe Ergebnis zurück.
    • Bei Blocklesevorgängen (SELECT ... FOR UPDATE / LOCK IN SHARE MODE), UPDATE und DELETE hängt die Sperre von der Art der Bedingung ab. Wenn die Bedingung eindeutig ist (WHERE id = 42), wird nur der gefundene Indexdatensatz gesperrt (Datensatzsperre). Liegt die Bedingung in einem Bereich (WHERE id> 42), ist der gesamte Bereich gesperrt (Lückensperre oder Next-Key-Sperre).

    LESEN SIE ENGAGIERT

    • Konsistentes Lesen blockiert nichts, aber jedes Mal wird ein neuer Schnappschuss erstellt.
    • Durch das Blockieren des Lesens (SELECT ... FOR UPDATE / LOCK IN SHARE MODE), UPDATE und DELETE werden nur die gewünschten Indexdatensätze gesperrt (Datensatzsperre). Somit ist es möglich, Datensätze in einem parallelen Fluss zwischen den Indizes einzufügen. Lücken werden nur beim Prüfen von Fremdschlüsseln und Doppelschlüsseln gesperrt (Gap Lock). Außerdem werden Sperren gescannter Zeilen (Datensatzsperren), die WHERE nicht erfüllen, unmittelbar nach der WHERE-Verarbeitung freigegeben.

    READ UNCOMMITED (der schwächste Level)

    • Alle SELECT-Abfragen werden nicht blockierend gelesen. Änderungen an einer laufenden Transaktion können in anderen Transaktionen gelesen und anschließend rückgängig gemacht werden. Dies ist das sogenannte "Dirty Reading" (inkonsistent).
    • Ansonsten ist alles wie bei READ COMMITED.

    SERIALIZABLE (strengste Stufe)

    • Ähnlich wie REPEATABLE READ, mit Ausnahme eines Punktes. Wenn Autocommit deaktiviert ist (und wenn die Transaktion explizit startet, ist sie deaktiviert), werden alle einfachen SELECT-Abfragen implizit in SELECT ... LOCK IN SHARE MODE umgewandelt. Wenn diese Option aktiviert ist, wird jede SELECT-Anweisung in eine separate Transaktion übernommen. Es wird in der Regel verwendet, um alle Leseanforderungen in SELECT ... LOCK IN SHARE MODE umzuwandeln, wenn dies im Anwendungscode nicht möglich ist.


    Noch eine sache ...


    Und zum Schluss noch ein paar Stücke, über die Sie Bescheid wissen sollten.

    SELECT ... LOCK IN SHARE MODE - sperrt gelesene Zeilen zum Schreiben.
    Andere Sitzungen können lesen, warten jedoch, bis die Transaktion abgeschlossen ist, um die betroffenen Zeilen zu ändern. Wenn zum Zeitpunkt eines solchen SELECT die Zeile bereits von einer anderen Transaktion geändert, aber noch nicht festgeschrieben wurde, wartet die Anforderung auf den Abschluss der Transaktion und liest dann die neuesten Daten. Diese Konstruktion wird in der Regel benötigt, um die neuesten Daten (unabhängig von der Laufzeit der Transaktion) zu erhalten und gleichzeitig sicherzustellen, dass diese von niemandem geändert werden.

    SELECT ... FOR UPDATE - blockiert gelesene Zeilen zum Lesen. Genau die gleiche Sperre setzt ein normales UPDATE, wenn es Daten zum Aktualisieren liest.

    Bitte beachten Sie, dass solche Abfragen nicht mehr aus einem Snapshot als einfaches SELECT gelesen werden, d. H. Sie sehen die Änderungen, die von einer anderen Transaktion nach dem Start der aktuellen Transaktion übernommen wurden. Dies liegt daran, dass InnoDB nur die neueste Version einer Zeile blockieren kann und in einem Snapshot nicht unbedingt die letzte sein muss.

    Beispielsweise kann in einem Skript durch Lesen → Ändern → Zurückschreiben zwischen Lesen und Schreiben einer parallelen Transaktion Daten geändert werden. Diese Änderung wird jedoch von der aktuellen Transaktion beim Zurückschreiben sofort gelöscht. LOCK IN SHARE MODE in diesem Beispiel lässt eine benachbarte Transaktion nicht zu, sondern muss warten. Beachten Sie, dass in diesem Fall die Sperre zweimal gesetzt wird, zuerst beim Lesen und dann beim Schreiben exklusiv. Da es zwei Sperren gibt, besteht theoretisch die Möglichkeit, dass eine dritte zwischen ihnen verschoben wird und ein Deadlock verursacht wird.

    Der Unterschied zwischen FOR UPDATE besteht darin, dass sofort eine exklusive Sperre gesetzt wird, genau wie bei einem regulären UPDATE. Somit wird für das Skript, Lesen → Ändern → Zurückschreiben, die Sperre zum Zeitpunkt des Lesens nur einmal gesetzt. Diese Option verringert die Wahrscheinlichkeit von Deadlocks.

    Jetzt auch beliebt: