Detaillierte Erklärung der MySQL-Sperren (Tabellensperren, Zeilensperren, gemeinsame Sperren, exklusive Sperren, Lückensperren)

Im wirklichen Leben ist ein Schloss ein Werkzeug, das wir verwenden, wenn wir uns vor der Außenwelt verstecken möchten. Bei Computern handelt es sich dabei um einen Mechanismus zur Koordination mehrerer Prozesse oder Kreise, um gleichzeitig auf eine Ressource zuzugreifen. In einer Datenbank besteht neben der Konkurrenz um herkömmliche Computerressourcen (CPU, RAM, E/A usw.) auch die Gefahr, dass Daten als Ressource von vielen Benutzern gemeinsam genutzt werden und auf die viele Benutzer zugreifen. Die Sicherstellung der Konsistenz und Effektivität gleichzeitiger Datenzugriffe ist ein Problem, das alle Datenbanken lösen müssen. Sperrkonflikte sind ebenfalls ein wichtiger Faktor, der die Leistung gleichzeitiger Zugriffe von Datenbanken beeinträchtigt. Aus dieser Perspektive sind Sperren für Datenbanken besonders wichtig.

MySQL-Sperren

Im Vergleich zu anderen Datenbanken ist der Sperrmechanismus von MySQL relativ einfach. Sein bemerkenswertestes Merkmal ist, dass verschiedene Speicher-Engines unterschiedliche Sperrmechanismen unterstützen. Abhängig von der Speicher-Engine können die Eigenschaften von Sperren in MySQL grob wie folgt zusammengefasst werden:

Overhead, Sperrgeschwindigkeit, Deadlock, Granularität und Parallelitätsleistung

• Tabellensperre: geringer Overhead, schnelle Sperre; kein Deadlock; starke Sperre, hohe Wahrscheinlichkeit eines Sperrkonflikts, geringste Parallelität

• Zeilensperre: hoher Overhead, langsame Sperrung; Deadlocks können auftreten; geringe Sperrgranularität, geringe Wahrscheinlichkeit eines Sperrkonflikts, hohe Parallelität

• Seitensperre: Die Kosten und die Sperrgeschwindigkeit liegen zwischen Tabellensperre und Zeilensperre. Es kann zu Deadlocks kommen. Die Sperrgranularität liegt zwischen Tabellensperre und Zeilensperre und die Parallelität ist durchschnittlich.

Anhand der oben genannten Merkmale lässt sich nur schwer allgemein sagen, welches Schloss das beste ist. Wir können nur anhand der Merkmale der jeweiligen Anwendung sagen, welches Schloss besser geeignet ist. Nur aus der Perspektive des Schlosses:

Tabellensperren eignen sich eher für abfragebasierte Anwendungen, die nur eine kleine Datenmenge entsprechend den Indexbedingungen aktualisieren. Zeilensperren eignen sich eher für Anwendungen, bei denen entsprechend den Indexbedingungen und gleichzeitigen Abfragen eine große Anzahl kleiner Mengen unterschiedlicher Daten gleichzeitig aktualisiert werden. (PS: Da BDB durch InnoDB ersetzt wurde, diskutieren wir nur die Probleme der MyISAM-Tabellensperren und InnoDB-Zeilensperren.)

MyISAM-Tabellensperren

Die MyISAM-Speicher-Engine unterstützt nur Tabellensperren. Dies ist der einzige Sperrtyp, der in den ersten Versionen von MySQL unterstützt wurde. Da die Anforderungen der Anwendungen an Transaktionsintegrität und Parallelität immer weiter stiegen, begann MySQL mit der Entwicklung einer transaktionsbasierten Speicher-Engine. Später erschienen nach und nach die BDB-Speicher-Engine, die Seitensperren unterstützt, und die InnoDB-Speicher-Engine, die Zeilensperren unterstützt (InnoDB ist eigentlich ein eigenständiges Unternehmen und wurde inzwischen von Oracle übernommen). MyISAM-Tabellensperren sind jedoch immer noch der am häufigsten verwendete Sperrtyp. In diesem Abschnitt wird die Verwendung von MyISAM-Tabellensperren ausführlich vorgestellt.

Sperrkonflikte auf Abfragetabellenebene

Tabellensperrenkonflikte auf Ihrem System können durch die Untersuchung der Statusvariablen table_locks_waited und table_locks_immediate analysiert werden:

mysql> Status wie „Tabelle%“ anzeigen;
+--------------------------+----------+
| Variablenname | Wert |
+--------------------------+----------+
| Tabellensperren_sofort | 2979 |
| Auf Tabellensperren gewartet | 0 |
+--------------------------+----------+
2 Zeilen im Satz (0,00 Sek.)

Wenn der Wert von Table_locks_waited relativ hoch ist, bedeutet dies, dass es einen ernsthaften Sperrkonflikt auf Tabellenebene gibt.

MySQL-Sperrmodus auf Tabellenebene

MySQL-Sperren auf Tabellenebene haben zwei Modi: gemeinsame Lesesperre für die Tabelle (Table Read Lock) und exklusive Schreibsperre für die Tabelle (Table Write Lock). Die Kompatibilität der Sperrmodi wird in der folgenden Tabelle angezeigt.

Tabellensperrenkompatibilität in MySQL

Es ist ersichtlich, dass der Lesevorgang auf der MyISAM-Tabelle die Leseanforderungen anderer Benutzer für dieselbe Tabelle nicht blockiert, aber die Schreibanforderungen für dieselbe Tabelle blockiert; der Schreibvorgang auf der MyISAM-Tabelle blockiert die Lese- und Schreibvorgänge anderer Benutzer auf derselben Tabelle; die Lese- und Schreibvorgänge der MyISAM-Tabelle sowie die Schreibvorgänge selbst sind seriell! Aus dem in der folgenden Tabelle gezeigten Beispiel können wir erkennen, dass, wenn ein Thread eine Schreibsperre für eine Tabelle erhält, nur der Thread, der die Sperre hält, die Tabelle aktualisieren kann. Lese- und Schreibvorgänge anderer Threads warten, bis die Sperre aufgehoben wird.

Beispiel für das Blockieren und Lesen von Schreibvorgängen im MyISAM-Speichermodul

So fügen Sie eine Tabellensperre hinzu

Vor der Ausführung einer Abfrageanweisung (SELECT) fügt MyISAM automatisch eine Lesesperre für alle beteiligten Tabellen hinzu. Vor der Ausführung einer Aktualisierungsoperation (UPDATE, DELETE, INSERT usw.) fügt es automatisch eine Schreibsperre für die beteiligten Tabellen hinzu. Dieser Vorgang erfordert kein Eingreifen des Benutzers. Daher müssen Benutzer die MyISAM-Tabelle im Allgemeinen nicht explizit direkt mit dem Befehl LOCK TABLE sperren. In den Beispielen in diesem Buch dient die explizite Sperre hauptsächlich der Bequemlichkeit und ist nicht erforderlich.

Der Zweck des expliziten Sperrens der MyISAM-Tabelle besteht darin, Transaktionsvorgänge bis zu einem gewissen Grad zu simulieren und ein konsistentes Lesen mehrerer Tabellen zu einem bestimmten Zeitpunkt zu erreichen. Beispielsweise gibt es eine Bestelltabelle „orders“, die den Gesamtbetrag jeder Bestellung aufzeichnet, und es gibt auch eine Bestelldetailtabelle „order_detail“, die den Zwischenbetrag jedes Produkts in jeder Bestellung aufzeichnet. Wenn wir überprüfen müssen, ob der Gesamtbetrag dieser beiden Tabellen übereinstimmt, müssen wir möglicherweise die folgenden beiden SQL-Anweisungen ausführen:

Wählen Sie die Summe (Gesamtsumme) der Bestellungen aus.
Wählen Sie die Summe (Zwischensumme) aus den Bestelldetails.

Wenn Sie die beiden Tabellen jetzt nicht zuerst sperren, können falsche Ergebnisse auftreten, da die Tabelle order_detail während der Ausführung der ersten Anweisung möglicherweise geändert wurde. Daher sollte der richtige Weg sein:

Sperrtabellen „Bestellungen“ lokal lesen, „order_detail“ lokal lesen;
Wählen Sie die Summe (Gesamtsumme) der Bestellungen aus.
Wählen Sie die Summe (Zwischensumme) aus den Bestelldetails.
Tabellen entsperren;

Auf die folgenden zwei Punkte soll besonders eingegangen werden.

• Im obigen Beispiel wird bei LOCK TABLES die Option „local“ hinzugefügt. Ihre Funktion besteht darin, anderen Benutzern das gleichzeitige Einfügen von Datensätzen am Ende der MyISAM-Tabelle zu ermöglichen, wenn die Bedingungen für gleichzeitiges Einfügen der MyISAM-Tabelle erfüllt sind. Das Problem des gleichzeitigen Einfügens von MyISAM-Tabellen wird in den folgenden Kapiteln näher erläutert.

• Wenn Sie LOCK TABLES verwenden, um eine Tabelle explizit zu sperren, müssen Sie Sperren für alle beteiligten Tabellen gleichzeitig erhalten und MySQL unterstützt keine Sperreskalation. Das heißt, nach der Ausführung von LOCK TABLES können Sie nur auf die explizit gesperrten Tabellen zugreifen und nicht auf die entsperrten Tabellen. Gleichzeitig können, wenn eine Lesesperre hinzugefügt wird, nur Abfragevorgänge ausgeführt werden, aber keine Aktualisierungsvorgänge. Tatsächlich ist dies grundsätzlich bei automatischer Sperrung der Fall. MyISAM erhält immer alle für eine SQL-Anweisung erforderlichen Sperren auf einmal. Aus diesem Grund treten in MyISAM-Tabellen keine Deadlocks auf.

Im Beispiel in der folgenden Tabelle verwendet eine Sitzung den Befehl LOCK TABLE, um eine Lesesperre für die Tabelle film_text hinzuzufügen. Diese Sitzung kann Datensätze in der gesperrten Tabelle abfragen, aber Aktualisierungen oder Zugriffe auf andere Tabellen führen zu Fehlern. Gleichzeitig kann eine andere Sitzung Datensätze in der Tabelle abfragen, aber Aktualisierungen führen zu Sperrwartezeiten.

Beispiel für Lese- und Schreibblockierung im MyISAM-Speichermodul

Beachten Sie, dass Sie bei der Verwendung von LOCK TABLES nicht nur alle verwendeten Tabellen gleichzeitig sperren müssen, sondern auch dieselbe Tabelle so oft sperren müssen, wie sie in der SQL-Anweisung mit demselben Alias ​​vorkommt. Andernfalls tritt ein Fehler auf! Unten finden Sie ein Beispiel.

(1) Erhalten Sie eine Lesesperre für die Akteurtabelle:

mysql> Sperrtabelle, Schauspieler lesen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

(2) Der Zugriff über einen Alias ​​führt allerdings zu einem Fehler:

mysql> wähle a.Vorname,a.Nachname,b.Vorname,b.Nachname von Schauspieler a,Schauspieler b, wobei a.Vorname = b.Vorname und a.Vorname = ‚Lisa‘ und a.Nachname = ‚Tom‘ und a.Nachname <> b.Nachname;
FEHLER 1100 (HY000): Tabelle 'a' wurde nicht mit LOCK TABLES gesperrt

(3) Aliase müssen separat gesperrt werden:

mysql> Sperrtabelle, Akteur als A-Leser, Akteur als B-Leser;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

(4) Damit die Abfrage per Alias ​​korrekt ausgeführt werden kann:

mysql> wähle a.Vorname,a.Nachname,b.Vorname,b.Nachname von Schauspieler a,Schauspieler b, wobei a.Vorname = b.Vorname und a.Vorname = ‚Lisa‘ und a.Nachname = ‚Tom‘ und a.Nachname <> b.Nachname;
+------------+--------------+------------+-----------+
| Vorname | Nachname | Vorname | Nachname |
+------------+--------------+------------+-----------+
| Lisa | Tom | LISA | MONROE |
+------------+--------------+------------+-----------+

1 Zeile im Satz (0,00 Sek.)

Gleichzeitige Einfügungen

Oben wurde erwähnt, dass das Lesen und Schreiben von MyISAM-Tabellen seriell erfolgt, aber das ist nur allgemein gehalten. Unter bestimmten Bedingungen unterstützen MyISAM-Tabellen auch gleichzeitige Abfrage- und Einfügevorgänge.

Die MyISAM-Speicher-Engine verfügt über eine Systemvariable concurrent_insert, die speziell zur Steuerung ihres gleichzeitigen Einfügeverhaltens verwendet wird. Ihr Wert kann 0, 1 oder 2 sein.

• Wenn concurrent_insert auf 0 gesetzt ist, sind gleichzeitige Einfügungen nicht zulässig.

• Wenn concurrent_insert auf 1 gesetzt ist und keine Lücken in der MyISAM-Tabelle vorhanden sind (das heißt, es befinden sich keine gelöschten Zeilen in der Mitte der Tabelle), ermöglicht MyISAM einem Prozess das Lesen der Tabelle, während ein anderer Prozess Datensätze vom Ende der Tabelle aus einfügt. Dies ist auch die Standardeinstellung für MySQL.

• Wenn concurrent_insert auf 2 gesetzt ist, ist das gleichzeitige Einfügen von Datensätzen am Ende der Tabelle zulässig, unabhängig davon, ob die MyISAM-Tabelle Lücken aufweist.

Im Beispiel in der folgenden Tabelle erhält session_1 die READ LOCAL-Sperre einer Tabelle. Der Thread kann die Tabelle abfragen, aber nicht aktualisieren. Der andere Thread (session_2) kann die Tabelle weder löschen noch aktualisieren, kann aber gleichzeitige Einfügevorgänge darin ausführen. Es wird davon ausgegangen, dass die Tabelle keine Lücke aufweist.

Beispiel für gleichzeitiges Lesen und Schreiben (INSERT) der MyISAM-Speicher-Engine

Sie können die Funktion für gleichzeitige Einfügungen der MyISAM-Speicher-Engine verwenden, um Sperrkonflikte bei Abfragen und Einfügungen in derselben Tabelle in Ihrer Anwendung aufzulösen. Wenn Sie beispielsweise die Systemvariable concurrent_insert auf 2 setzen, sind immer gleichzeitige Einfügungen möglich. Gleichzeitig werden durch die regelmäßige Ausführung der Anweisung OPTIMIZE TABLE während Leerlaufzeiten des Systems Speicherplatzfragmente defragmentiert und durch gelöschte Datensätze entstandene Lücken wiederhergestellt. Eine ausführliche Einführung in die OPTIMIZE TABLE-Anweisung finden Sie im Abschnitt „Zwei einfache und praktische Optimierungsmethoden“ in Kapitel 18.

MyISAM-Sperrplanung

Wie bereits erwähnt, schließen sich die Lesesperre und die Schreibsperre der MyISAM-Speicher-Engine gegenseitig aus und die Lese- und Schreibvorgänge sind seriell. Wenn also ein Prozess eine Lesesperre für eine MyISAM-Tabelle anfordert und ein anderer Prozess auch eine Schreibsperre für die gleiche Tabelle anfordert, wie geht MySQL damit um? Die Antwort ist, dass der Schreibvorgang zuerst die Sperre erhält. Nicht nur das, selbst wenn die Leseanforderung zuerst in der Sperrwarteschlange eintrifft und die Schreibanforderung später eintrifft, wird die Schreibsperre vor der Lesesperrenanforderung eingefügt! Dies liegt daran, dass MySQL Schreibanforderungen im Allgemeinen als wichtiger erachtet als Leseanforderungen. Dies ist auch der Grund, warum MyISAM-Tabellen nicht für Anwendungen mit einer großen Anzahl von Aktualisierungs- und Abfragevorgängen geeignet sind, da eine große Anzahl von Aktualisierungsvorgängen es für Abfragevorgänge schwierig macht, Lesesperren zu erhalten, und sie möglicherweise für immer blockiert sind. Diese Situation kann manchmal wirklich schlimm werden! Glücklicherweise können wir das Planungsverhalten von MyISAM durch einige Einstellungen anpassen.

• Durch Angabe des Startparameters „low-priority-updates“ priorisiert die MyISAM-Engine standardmäßig Lese-Anfragen.

• Durch Ausführen des Befehls SET LOW_PRIORITY_UPDATES=1 wird die Priorität der von der Verbindung gestellten Aktualisierungsanforderung verringert.

• Durch Angabe des Attributs LOW_PRIORITY der INSERT-, UPDATE- oder DELETE-Anweisung können Sie die Priorität der Anweisung herabsetzen.

Obwohl es sich bei den oben genannten drei Methoden entweder um Update-First- oder Query-First-Methoden handelt, können sie dennoch verwendet werden, um das schwerwiegende Problem des Wartens auf Lesesperren in Anwendungen zu lösen, bei denen Abfragen relativ wichtig sind (z. B. Benutzeranmeldesysteme).

Darüber hinaus bietet MySQL auch eine Kompromissmethode zum Anpassen von Lese-Schreib-Konflikten, nämlich das Setzen eines geeigneten Wertes für den Systemparameter max_write_lock_count. Wenn die Lesesperre einer Tabelle diesen Wert erreicht, senkt MySQL vorübergehend die Priorität der Schreibanforderung, um dem Lesevorgang eine gewisse Gelegenheit zu geben, die Sperre zu erhalten.

Die durch den Schreibprioritätsplanungsmechanismus verursachten Probleme und Lösungen wurden oben erläutert. Ein weiterer Punkt muss hier hervorgehoben werden: Einige Abfragevorgänge, deren Ausführung lange dauert, führen auch dazu, dass der Schreibvorgang „verhungert“! Daher sollten lang andauernde Abfragevorgänge in der Anwendung so weit wie möglich vermieden werden. Versuchen Sie nicht immer, das Problem mit einer SELECT-Anweisung zu lösen, da diese scheinbar clevere SQL-Anweisung häufig komplex ist und ihre Ausführung lange dauert. Wenn möglich, können Sie Zwischentabellen und andere Maßnahmen verwenden, um die SQL-Anweisung zu „zerlegen“, sodass jeder Abfrageschritt in kürzerer Zeit abgeschlossen werden kann und dadurch Sperrkonflikte reduziert werden. Wenn komplexe Abfragen unvermeidbar sind, sollten sie so geplant werden, dass sie während der Leerlaufzeit der Datenbank ausgeführt werden. Beispielsweise kann die Ausführung einiger regelmäßiger Statistiken so geplant werden, dass sie nachts ausgeführt werden.

InnoDB-Sperrproblem

Es gibt zwei große Unterschiede zwischen InnoDB und MyISAM: Einer besteht darin, dass Transaktionen unterstützt werden (TRANSACTION); der andere ist die Verwendung von Sperren auf Zeilenebene. Es gibt viele Unterschiede zwischen Sperren auf Zeilenebene und Sperren auf Tabellenebene. Darüber hinaus bringt die Einführung von Transaktionen auch einige neue Probleme mit sich. Nachfolgend stellen wir zunächst einige Hintergrundinformationen vor und besprechen dann das InnoDB-Sperrproblem im Detail.

Hintergrund

1. Transaktion und ihre ACID-Eigenschaften

Eine Transaktion ist eine logische Verarbeitungseinheit, die aus einer Gruppe von SQL-Anweisungen besteht. Eine Transaktion hat die folgenden vier Eigenschaften, die normalerweise als ACID-Eigenschaften der Transaktion bezeichnet werden.

• Atomarität: Eine Transaktion ist eine atomare Operationseinheit und ihre Datenänderungen werden entweder alle oder keine davon ausgeführt.

• Konsistenz: Die Daten müssen beim Start und Abschluss einer Transaktion in einem konsistenten Zustand bleiben. Dies bedeutet, dass alle relevanten Datenregeln auf die Transaktionsänderungen angewendet werden müssen, um die Datenintegrität zu wahren; am Ende der Transaktion müssen außerdem alle internen Datenstrukturen (wie B-Baum-Indizes oder doppelt verkettete Listen) korrekt sein.

• Isolierung: Das Datenbanksystem bietet bestimmte Isolierungsmechanismen, um sicherzustellen, dass Transaktionen in einer „unabhängigen“ Umgebung ausgeführt werden, die nicht von externen, gleichzeitig laufenden Vorgängen beeinflusst wird. Dies bedeutet, dass Zwischenzustände während der Transaktionsverarbeitung für die Außenwelt nicht sichtbar sind und umgekehrt.

• Dauerhaft: Nachdem eine Transaktion abgeschlossen ist, sind die Datenänderungen dauerhaft und können auch bei einem Systemausfall beibehalten werden.

Eine Banküberweisung ist ein klassisches Beispiel für eine Transaktion.

2. Probleme bei der gleichzeitigen Transaktionsverarbeitung

Im Vergleich zur seriellen Verarbeitung kann die gleichzeitige Transaktionsverarbeitung die Nutzung der Datenbankressourcen erheblich steigern und den Transaktionsdurchsatz des Datenbanksystems verbessern, wodurch mehr Benutzer unterstützt werden. Allerdings bringt die gleichzeitige Transaktionsverarbeitung auch einige Probleme mit sich, vor allem in den folgenden Situationen.

• Verlorenes Update: Wenn zwei oder mehr Transaktionen dieselbe Zeile auswählen und diese dann auf Grundlage der anfangs ausgewählten Werte aktualisieren, tritt das Problem des verlorenen Updates auf, da die einzelnen Transaktionen nichts von der Existenz der anderen Transaktionen wissen – das letzte Update überschreibt die von anderen Transaktionen vorgenommenen Aktualisierungen. Beispielsweise erstellen zwei Redakteure elektronische Kopien desselben Dokuments. Jeder Redakteur nimmt selbstständig Änderungen an seiner Kopie vor und speichert anschließend die geänderte Kopie, wobei das Originaldokument überschrieben wird. Der Bearbeiter, der seine Änderungskopie zuletzt speichert, überschreibt die Änderungen der anderen Bearbeiter. Dieses Problem kann vermieden werden, wenn ein Editor nicht auf dieselbe Datei zugreifen kann, bis ein anderer Editor die Transaktion abgeschlossen und festgeschrieben hat.

• Dirty Reads: Eine Transaktion ändert einen Datensatz. Bevor die Transaktion abgeschlossen und festgeschrieben ist, befinden sich die Daten des Datensatzes in einem inkonsistenten Zustand. Zu diesem Zeitpunkt liest eine andere Transaktion denselben Datensatz. Wenn dies nicht kontrolliert wird, liest die zweite Transaktion die „schmutzigen“ Daten und führt auf ihrer Grundlage weitere Verarbeitungen durch, wodurch nicht festgeschriebene Datenabhängigkeiten erzeugt werden. Dieses Phänomen wird anschaulich als „Dirty Read“ bezeichnet.

• Nicht wiederholbare Lesevorgänge: Eine Transaktion liest einige Daten zu einem bestimmten Zeitpunkt, nachdem sie diese gelesen hat, und liest dann die Daten, die sie zuvor gelesen hat, nur um festzustellen, dass sich die gelesenen Daten geändert haben oder einige Datensätze gelöscht wurden! Dieses Phänomen wird als „nicht wiederholbares Lesen“ bezeichnet.

• Phantomlesevorgänge: Wenn eine Transaktion zuvor abgerufene Daten unter Verwendung derselben Abfragekriterien erneut liest und dann feststellt, dass andere Transaktionen neue Daten eingefügt haben, die die Abfragekriterien erfüllen, wird dieses Phänomen als „Phantomlesevorgang“ bezeichnet.

3. Transaktionsisolationsebene

Unter den oben genannten Problemen, die durch die gleichzeitige Transaktionsverarbeitung verursacht werden , sollte der „Aktualisierungsverlust“ normalerweise vollständig vermieden werden. Das Verhindern von Aktualisierungsverlusten kann jedoch nicht allein durch den Datenbanktransaktionscontroller gelöst werden. Die Anwendung muss den zu aktualisierenden Daten die erforderlichen Sperren hinzufügen. Daher sollte das Verhindern von Aktualisierungsverlusten in der Verantwortung der Anwendung liegen.

Bei „Dirty Reads“, „Non-Repeatable Reads“ und „Phantom Reads“ handelt es sich eigentlich um Konsistenzprobleme beim Lesen von Datenbanken, die dadurch gelöst werden müssen, dass die Datenbank einen bestimmten Transaktionsisolierungsmechanismus bereitstellt. Die Arten und Weisen, wie Datenbanken die Transaktionsisolierung implementieren, können grundsätzlich in die folgenden zwei Typen unterteilt werden.

• Eine Möglichkeit besteht darin, die Daten vor dem Lesen zu sperren, um zu verhindern, dass andere Transaktionen die Daten ändern.

• Die andere Methode besteht darin, durch einen bestimmten Mechanismus ohne Hinzufügen von Sperren zum Zeitpunkt der Datenanforderung einen konsistenten Datenschnappschuss (Snapshot) zu generieren und diesen Schnappschuss zu verwenden, um konsistentes Lesen auf einer bestimmten Ebene (Anweisungsebene oder Transaktionsebene) bereitzustellen. Aus Sicht des Benutzers scheint die Datenbank mehrere Versionen derselben Daten bereitstellen zu können. Daher wird diese Technologie MultiVersion Concurrency Control (MVCC oder MCC) genannt und häufig als Multiversion-Datenbank bezeichnet.

Je strenger die Transaktionsisolierung der Datenbank ist, desto geringer sind die Nebenwirkungen der Parallelität, aber desto höher ist auch der Preis, den man dafür zahlt, denn die Transaktionsisolierung führt im Wesentlichen dazu, dass Transaktionen bis zu einem gewissen Grad „serialisiert“ werden, was offensichtlich im Widerspruch zur „Parallelität“ steht. Gleichzeitig haben unterschiedliche Anwendungen unterschiedliche Anforderungen an die Lesekonsistenz und Transaktionsisolierung. Viele Anwendungen reagieren beispielsweise nicht empfindlich auf „nicht wiederholbare Lesevorgänge“ und „Phantomlesevorgänge“ und legen möglicherweise mehr Wert auf die Möglichkeit, gleichzeitig auf Daten zuzugreifen.

Um den Widerspruch zwischen „Isolation“ und „Parallelität“ aufzulösen, definiert ISO/ANSI SQL92 vier Transaktionsisolationsebenen. Jede Ebene hat einen anderen Isolationsgrad und lässt unterschiedliche Nebeneffekte zu. Anwendungen können den Widerspruch zwischen „Isolation“ und „Parallelität“ ausgleichen, indem sie je nach ihren Geschäftslogikanforderungen unterschiedliche Isolationsebenen auswählen. Die folgende Tabelle bietet einen guten Überblick über die Eigenschaften dieser vier Isolationsebenen.

Vergleich von 4 Isolationsstufen

Abschließend ist zu beachten, dass nicht jede einzelne Datenbank die oben genannten vier Isolationsebenen unbedingt vollständig implementiert. Oracle bietet beispielsweise nur zwei Standardisolationsebenen, Read Committed und Serializable, und stellt auch eine eigene definierte Read-Only-Isolationsebene bereit. Neben der Unterstützung der oben genannten vier von ISO/ANSI SQL92 definierten Isolationsebenen unterstützt SQL Server auch eine Isolationsebene namens „Snapshot“, aber streng genommen handelt es sich dabei um eine mit MVCC implementierte serialisierbare Isolationsebene. MySQL unterstützt alle vier Isolationsebenen, es gibt jedoch einige Besonderheiten bei der Implementierung. Beispielsweise werden MVCC-konsistente Lesevorgänge in einigen Isolationsebenen verwendet, in anderen jedoch nicht. Diese Inhalte werden in den folgenden Kapiteln weiter erläutert.

Holen Sie sich den InnoDB-Zeilensperrenkonfliktstatus

Sie können den Zeilensperrenkonflikt auf Ihrem System analysieren, indem Sie die Statusvariable InnoDB_row_lock prüfen:

mysql> Status wie „innodb_row_lock%“ anzeigen;
+-------------------------------+----------+
| Variablenname | Wert |
+-------------------------------+----------+
| InnoDB_row_lock_current_waits | 0 |
| InnoDB_row_lock_time | 0 |
| InnoDB_row_lock_time_avg | 0 |
| InnoDB_row_lock_time_max | 0 |
| InnoDB_row_lock_waits | 0 |
+-------------------------------+----------+
5 Zeilen im Satz (0,01 Sek.)

Bei schwerwiegenden Sperrkonflikten, z. B. bei hohen Werten für InnoDB_row_lock_waits und InnoDB_row_lock_time_avg , können Sie InnoDB-Monitore so einrichten, dass sie die Tabellen und Datenzeilen, in denen Sperrkonflikte auftreten, genauer beobachten und die Ursachen der Sperrkonflikte analysieren.

Die konkrete Methode ist wie folgt:

mysql> TABELLE ERSTELLEN innodb_monitor(ein INT) ENGINE=INNODB;
Abfrage OK, 0 Zeilen betroffen (0,14 Sek.)

Anschließend können Sie es mit der folgenden Anweisung anzeigen:

mysql> InnoDB-Status anzeigen\G;
*************************** 1. Reihe ***************************
Typ: InnoDB
Name:
Status:
…
…
------------
TRANSAKTIONEN
------------
TRX-ID-Zähler 0 117472192
Bereinigung für TRXs Nr. < 0 117472190 durchgeführt Rückgängig machen Nr. < 0 0
Länge der Verlaufsliste 17
Gesamtzahl der Sperrstrukturen in der Zeilensperr-Hashtabelle 0
LISTE DER TRANSAKTIONEN FÜR JEDE SITZUNG:
---TRANSACTION 0 117472185, nicht gestartet, Prozessnummer 11052, OS-Thread-ID 1158191456
MySQL-Thread-ID 200610, Abfrage-ID 291197, Localhost-Stamm
---TRANSACTION 0 117472183, nicht gestartet, Prozessnummer 11052, OS-Thread-ID 1158723936
MySQL-Thread-ID 199285, Abfrage-ID 291199, Localhost-Root
InnoDB-Status anzeigen
…

Der Monitor kann durch die Ausgabe der folgenden Anweisung gestoppt werden:

mysql> DROP TABLE innodb_monitor;
Abfrage OK, 0 Zeilen betroffen (0,05 Sek.)

Nach dem Einrichten des Monitors enthält der Anzeigeinhalt von SHOW INNODB STATUS detaillierte Informationen zur aktuellen Sperrwartezeit, einschließlich des Tabellennamens, des Sperrtyps und des Status des gesperrten Datensatzes, was für die weitere Analyse und Problembestimmung praktisch ist. Nach dem Einschalten des Monitors wird der Überwachungsinhalt standardmäßig alle 15 Sekunden im Protokoll aufgezeichnet. Wenn er für längere Zeit eingeschaltet bleibt, wird die .err-Datei sehr groß. Daher muss der Benutzer nach der Bestätigung der Problemursache daran denken, die Überwachungstabelle zu löschen, um den Monitor auszuschalten, oder den Server mit der Option „--console“ starten, um das Schreiben von Protokolldateien zu deaktivieren.

InnoDB-Zeilensperrmodus und Sperrmethode

InnoDB implementiert die folgenden zwei Arten von Zeilensperren.

• Gemeinsam genutzte Sperre (S): Ermöglicht einer Transaktion das Lesen einer Zeile und verhindert, dass andere Transaktionen exklusive Sperren für denselben Datensatz erhalten.

• Exklusive Sperre (X): Ermöglicht Transaktionen, die exklusive Sperren erhalten, das Aktualisieren von Daten und verhindert, dass andere Transaktionen gemeinsame Lesesperren und exklusive Schreibsperren für denselben Datensatz erhalten. Um außerdem die Koexistenz von Zeilensperren und Tabellensperren zu ermöglichen und einen Sperrmechanismus mit mehreren Granularitäten zu implementieren, verfügt InnoDB auch über zwei intern verwendete Absichtssperren (Intention Locks), bei denen es sich beide um Tabellensperren handelt.

• Absichtliche gemeinsame Sperre (IS): Die Transaktion beabsichtigt, einer Datenzeile eine gemeinsame Zeilensperre hinzuzufügen. Die Transaktion muss zuerst die IS-Sperre der Tabelle erhalten, bevor sie einer Datenzeile eine gemeinsame Sperre hinzufügt.

• Absichtliche exklusive Sperre (IX): Die Transaktion beabsichtigt, einer Datenzeile eine exklusive Sperre hinzuzufügen. Die Transaktion muss zuerst die IX-Sperre der Tabelle erhalten, bevor sie einer Datenzeile eine exklusive Sperre hinzufügt.

Die Kompatibilität der oben genannten Sperrmodi wird in der folgenden Tabelle dargestellt.

Kompatibilitätsliste für den InnoDB-Zeilensperrmodus

Wenn der von einer Transaktion angeforderte Sperrmodus mit der aktuellen Sperre kompatibel ist, gewährt InnoDB der Transaktion die angeforderte Sperre. Andernfalls, wenn die beiden nicht kompatibel sind, wartet die Transaktion auf die Freigabe der Sperre.

Absichtssperren werden von InnoDB automatisch und ohne Benutzereingriff hinzugefügt. Bei UPDATE-, DELETE- und INSERT-Anweisungen fügt InnoDB dem betreffenden Datensatz automatisch eine exklusive Sperre (X) hinzu. Bei normalen SELECT-Anweisungen fügt InnoDB keine Sperren hinzu. Transaktionen können durch die folgenden Anweisungen dem Datensatz explizit gemeinsame Sperren oder exklusive Sperren hinzufügen.

• Gemeinsam genutzte Sperren: SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE.

• Exklusive Sperre (X): SELECT * FROM table_name WHERE ... FOR UPDATE.

Verwenden Sie SELECT ... IN SHARE MODE, um eine gemeinsame Sperre zu erhalten. Diese wird hauptsächlich verwendet, um zu bestätigen, ob eine Datensatzzeile vorhanden ist, wenn Datenabhängigkeiten erforderlich sind, und um sicherzustellen, dass niemand UPDATE- oder DELETE-Vorgänge für diesen Datensatz ausführt. Wenn die aktuelle Transaktion jedoch auch den Datensatz aktualisieren muss, führt dies wahrscheinlich zu einem Deadlock. Für Anwendungen, die den Zeilendatensatz nach dem Sperren aktualisieren müssen, sollte die Methode SELECT... FOR UPDATE verwendet werden, um eine exklusive Sperre zu erhalten.

Im Beispiel in der folgenden Tabelle wird SELECT ... IN SHARE MODE verwendet, um den Datensatz zu sperren und dann zu aktualisieren, um zu sehen, was passiert. Das Feld actor_id der Actor-Tabelle ist der Primärschlüssel.

Beispiel für eine gemeinsame Sperre der InnoDB-Speicher-Engine

Wenn Sie SELECT...FOR UPDATE verwenden, um Datensätze zu sperren und dann zu aktualisieren, tritt die folgende Situation ein.

Beispiel für eine exklusive Sperre der InnoDB-Speicher-Engine

Implementierung einer InnoDB-Zeilensperre

InnoDB Row -Sperren werden durch Verriegelung der Indexelemente im Index implementiert . InnoDBs ROW-Lock-Implementierungsfunktion bedeutet, dass InnoDB nur dann Zeilenspiegel verwendet, wenn Daten über Indexbedingungen abgerufen werden.

In den tatsächlichen Anwendungen sollte auf diese Funktion von InnoDB -Reihenschlössern besondere Aufmerksamkeit geschenkt werden, da ansonsten eine große Anzahl von Sperrkonflikten auftreten kann, wodurch die Leistung der Parallelität beeinträchtigt wird. Das Folgende sind einige praktische Beispiele, um dies zu veranschaulichen.

(1) Bei Abfragen ohne Indexbedingungen verwendet InnoDB Tabellenverriegelungen anstelle von Zeilensperrungen.

Im folgenden Beispiel hat die Tabelle tab_no_index am Anfang keinen Index:

MySQL> Tabelle tab_no_index (id int, name varchar (10)) motor = innoDB;
Abfrage OK, 0 Zeilen betroffen (0,15 Sek.)
MySQL> In tab_no_index -Werte (1, '1'), (2, '2'), (3, '3'), (4, '4') einfügen;
Abfrage OK, 4 Zeilen betroffen (0,00 Sek.)
Datensätze: 4 Duplikate: 0 Warnungen: 0

Beispiel für die Verwendung von Tabellenresseln, wenn die InnoDB Storage Engine -Tabelle keine Indizes verwendet

In dem in der obigen Tabelle gezeigten Beispiel scheint Session_1 nur eine exklusive Sperre zu einer Zeile hinzugefügt, aber wenn Session_2 exklusive Sperren für andere Zeilen verlangt, tritt ein Sperre auf ein! Der Grund dafür ist, dass InnoDB ohne Index nur Tabellenschlösser verwenden kann. Wenn wir einen Index hinzufügen, sperrt InnoDB nur die Zeilen, die den Bedingungen erfüllen, wie in der folgenden Tabelle gezeigt.

Erstellen Sie die Tabelle tab_with_index mit einem normalen Index im Feld ID:

MySQL> Tabelle tab_with_index (id int, name varchar (10)) motor = innoDB;
Abfrage OK, 0 Zeilen betroffen (0,15 Sek.)
MySQL> Alter Table tab_with_index index id (id);
Abfrage OK, 4 betroffene Zeilen (0,24 Sekunden)
Datensätze: 4 Duplikate: 0 Warnungen: 0

Beispiel für die Verwendung von Zeilensperrungen bei Verwendung von Indizes für Tabellen mit der InnoDB Storage Engine

(2) Da MySQL -Zeile -Sperren gegen Indizes und keine Datensätze gesperrt sind, werden Sperrkonflikte auftreten, wenn auf Datensätze in verschiedenen Zeilen mit demselben Indexschlüssel zugreifen. Beachten Sie dies beim Entwerfen Ihrer Bewerbung.

In dem in der folgenden Tabelle gezeigten Beispiel hat das Feld ID der Tabelle tab_with_index einen Index, das Feld Namen hat jedoch keinen Index:

MySQL> Alter Table tab_with_index Drop -Index Name;
Abfrage OK, 4 betroffene Zeilen (0,22 Sekunden)
Datensätze: 4 Duplikate: 0 Warnungen: 0
MySQL> In tab_with_index -Werte einfügen (1, '4');
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
MySQL> Wählen Sie * aus tab_with_index wobei id = 1;
+------+------+
| Ich würde | Name |
+------+------+
| 1 | 1 |
|
+------+------+
2 Zeilen im Satz (0,00 Sek.)

InnoDB Storage Engine Blocking Beispiel mit demselben Indexschlüssel

(3) Wenn eine Tabelle mehrere Indizes aufweist, können verschiedene Transaktionen verschiedene Indizes verwenden, um verschiedene Zeilen zu sperren.

In dem in der folgenden Tabelle gezeigten Beispiel enthält das Feld ID der Tabelle tab_with_index einen primären Schlüsselindex und das Feld Name einen normalen Index:

MySQL> Alter Table tab_with_index Index Name (Name);
Abfrage OK, 5 Zeilen betroffen (0,23 Sekunden)
Datensätze: 5 Duplikate: 0 Warnungen: 0

Blockierungsbeispiel für die Verwendung verschiedener Indizes für InnoDB -Speicher -Motortabellen

(4) Wenn ein Indexfeld in der Bedingung verwendet wird, wird die Verwendung des Index zum Abrufen von Daten durch MySQL bestimmt, indem die Kosten verschiedener Ausführungspläne beurteilt werden. Vergessen Sie daher nicht, den SQL -Ausführungsplan zu überprüfen, um zu bestätigen, ob der Index tatsächlich verwendet wird.

Im folgenden Beispiel unterscheidet sich der Datentyp des abgerufenen Werts vom Indexfeld. Durch die Verwendung von Erklärung zur Überprüfung der Ausführungspläne der beiden SQL -Anweisungen können wir dies deutlich sehen.

Im Beispiel des Namens der Tabelle tab_with_index enthält ein Index, aber das Feld Name ist VARCHAR -Typ.

MySQL> Alter Table tab_no_index Indexname (Name) hinzufügen;
Abfrage OK, 4 betroffene Zeilen (8,06 Sek.)
Datensätze: 4 Duplikate: 0 Warnungen: 0
MySQL> Erklären Sie ausgewählt * aus tab_with_index wobei Name = 1 \ g
*************************** 1. Reihe ***************************
ID: 1
select_type: EINFACH
Tabelle: tab_with_index
Typ: ALLE
SOLY_KEYS: Name
Schlüssel: NULL
key_len: NULL
Ref: NULL
Reihen: 4
Extra: Verwenden von „where“
1 Zeile im Satz (0,00 Sek.)
MySQL> Erklären Sie ausgewählt * aus tab_with_index wobei name = '1' \ g
*************************** 1. Reihe ***************************
ID: 1
select_type: EINFACH
Tabelle: tab_with_index
Typ: ref
SOLY_KEYS: Name
Schlüssel: Name
Key_len: 23
Verweis: const
Reihen: 1
Extra: Verwenden von „where“
1 Zeile im Satz (0,00 Sek.)

Gap Lock (Nächstschleuder)

Wenn wir Daten unter Verwendung von Bereichsbedingungen anstelle von Gleichstellungsbedingungen abrufen und gemeinsam genutzte oder exklusive Schlösser anfordern, sperrt InnoDB die Indexelemente vorhandener Datensätze, die die Bedingungen erfüllen.

Wenn beispielsweise die EMP-Tabelle nur 101 Datensätze enthält und die EMPID-Werte jeweils 1, 2, ..., 100 und 101 sind, lautet das folgende SQL:

Wählen Sie * aus EMP, wo Empid> 100 für das Update;

Dies ist eine Bereichsbedingungssuche. InnoDB sperrt nicht nur die Datensätze mit einem Empid-Wert von 101, die die Bedingungen erfüllen, sondern sperrt auch die „Lücken“, bei denen der Empid-Wert größer als 101 ist (diese Datensätze existieren nicht).

Der Zweck von InnoDB unter Verwendung von Gap -Sperren ist zum einen zu verhindern, dass Phantom die Anforderungen der verwandten Isolationsstufen erfüllt. Der Einfluss der Erholung und Replikation auf den Verriegelungsmechanismus sowie die Verwendung von Lückensperrungen durch InnoDB in unterschiedlichen Isolationsniveaus wird in nachfolgenden Kapiteln weiter eingeführt.

Wenn zum Abrufen und Sperren von Datensätzen Bereichsbedingungen verwendet werden, blockiert der Sperrmechanismus von InnoDB offensichtlich das gleichzeitige Einfügen von Schlüsselwerten, die die Bedingungen erfüllen, was häufig zu erheblichen Wartezeiten bei der Sperre führt. Daher sollten wir in der tatsächlichen Anwendungsentwicklung, insbesondere Anwendungen mit vielen gleichzeitigen Einfügungen, unser Bestes versuchen, um die Geschäftslogik zu optimieren, die Gleichstellungsbedingungen zu verwenden, um auf Daten zuzugreifen und zu aktualisieren und die Verwendung von Bereichsbedingungen zu vermeiden.

Es sollte auch beachtet werden, dass InnoDB zusätzlich zur Verwendung von Gap-Sperren beim Durchsperren der Reichweite auch Gap-Sperren verwendet, wenn eine Gleichstellungsbedingung verwendet wird, um eine Sperre für einen nicht existierenden Datensatz anzufordern!

In dem in der folgenden Tabelle gezeigten Beispiel sind die Werte von Empid 1, 2, ..., 100, 101, wenn nur 101 Datensätze in der EMP -Tabelle vorhanden sind.

InnoDB Storage Motor Gap Lock Blocking Beispiel

Die Notwendigkeit einer Wiederherstellung und Replikation beeinflusst den InnoDB -Verriegelungsmechanismus

MySQL verwendet Binlog, um erfolgreiche Einfügen, Aktualisierung, Löschen und andere SQL-Anweisungen aufzuzeichnen, die Daten aktualisieren und so die Wiederherstellung und Master-Slave-Replikation der MySQL-Datenbank realisieren (siehe Einführung im Abschnitt "Management" dieses Buches). Der MySQL-Wiederherstellungsmechanismus (Replikation ist tatsächlich eine kontinuierliche Binlog-basierte Wiederherstellung auf der Slave MySQL) hat die folgenden Eigenschaften.

l Erstens ist die MySQL-Wiederherstellung auf der SQL-Anweisungsebene, dh die Wiedereinführung der SQL-Anweisungen in Binlog. Dies unterscheidet sich von der Oracle -Datenbank, die auf Datenbankdateiblöcken basiert.

In Zweitens zeichnet Binloks Binlog -Transaktionen von MySQL in der Reihenfolge auf, in der sie eingereicht wurden, und in dieser Reihenfolge wird auch die Wiederherstellung durchgeführt. Dies unterscheidet sich auch von Oracle.

Aus den beiden oben genannten Punkten können wir feststellen, dass der Wiederherstellungsmechanismus von MySQL vor einer Transaktion keine Aufzeichnungen einfügen, die ihren Verriegelungsbedingungen erfüllen, dh Phantom Reads sind nicht zulässig. Dies ist auch der Grund, warum InnoDB in vielen Fällen Gap -Sperren verwendet.

Für SQL -Anweisungen wie "In target_tab select * aus Source_Tab wobei ..." und "Tabelle neuer_tab erstellen ... auswählen ... von Source_Tab Where ... (ctas) erstellen" führt der Benutzer keine Aktualisierungsvorgänge auf Source_TAB durch, aber MySQL macht eine spezielle Verarbeitung für solche SQL -Aussagen. Schauen wir uns zunächst das Beispiel in der folgenden Tabelle an.

Beispiel für den CTA -Betrieb, um die ursprüngliche Tabelle zu sperren

Im obigen Beispiel lesen wir einfach die Daten in der Tabelle Source_Tab, die der Ausführung einer normalen Auswahlanweisung entspricht und mit Konsistenz gelesen werden kann. Genau das macht Oracle. Wir wissen, dass InnoDB auch Multi-Versions-Daten implementiert und keine Sperren für die gewöhnliche Auswahlkonsequenz benötigt.

Warum macht MySQL das? Der Grund ist, die Richtigkeit der Erholung und Replikation zu gewährleisten. Wenn die Sperre nicht hinzugefügt wird, kann dies bei anderen Transaktionen während der Ausführung der obigen Anweisung zu Fehlern in den Ergebnissen der Datenwiederherstellung führen. Um dies zu demonstrieren, wiederholen wir das vorherige Beispiel.

Ein Beispiel für Sicherheitsprobleme, die durch den CTA -Betrieb verursacht werden, ohne die ursprüngliche Tabelle zu sperren

Wie aus den oben genannten Erkenntnissen ersichtlich ist, sperrt der Wert des Systems der Systemvariablen innoDB_LOCKS_UNSAFE_FOR_BINLOG auf "On" nicht mehr, und das Ergebnis stimmt jedoch mit der Anwendungslogik überein, wenn der Inhalt von Binlog analysiert wird:

......
Setzen Sie den Zeitstempel = 1169175130;
BEGINNEN;
# bei 274
#070119 10:51:57 Server -ID 1 end_log_pos 105 Abfrage thread_id = 1 exec_time = 0 error_code = 0
Setzen Sie den Zeitstempel = 1169175117;
Aktualisieren Sie Source_Tab set name = '8' wobei Name = '1';
# bei 379
#070119 10:52:10 Server -ID 1 end_log_pos 406 xid = 5
BEGEHEN;
# bei 406
#070119 10:52:14 Server -ID 1 end_log_pos 474 Abfrage thread_id = 2 exec_time = 0 error_code = 0
Setzen Sie den Zeitstempel = 1169175134;
BEGINNEN;
# bei 474
#070119 10:51:29 Server -ID 1 end_log_pos 119 Abfrage thread_id = 2 exec_time = 0 error_code = 0
Setzen Sie den Zeitstempel = 1169175089;
In target_tab einfügen D1, Name von Source_Tab Where name = '1';
# bei 593
#070119 10:52:14 Server -ID 1 end_log_pos 620 xid = 7
BEGEHEN;
......

In Binlog befindet sich die Aktualisierungsoperation vor dem Einfügen ... SELECT.

Aus dem obigen Beispiel fällt es uns nicht schwer zu verstehen, warum MySQL scress_tab sperrt, wenn sie verarbeiten "In target_tab select * aus Source_Tab Where ..." und "Tabelle neue_tab erstellen ... Wählen Sie ... aus Source_Tab Where ...", anstatt Daten mit mehreren Versionen mit minimaler Auswirkungen auf die Parallelität zu verwenden, um eine konsistente Lesen zu erreichen. Es sollte auch beachtet werden, dass InnoDB auch eine Lückensperrung (nächstes Lock) in die Quellentabelle hinzufügt, wenn die Auswahl in der obigen Anweisung eine Bereichsbedingung ist.

Daher einfügen ... Wählen Sie ... und erstellen Sie die Tabelle ... SELECT ... Anweisungen können gleichzeitige Aktualisierungen der Quellentabelle verhindern, wodurch die Wartezeit für die Quellentabellensperre führt. Wenn die Abfrage komplex ist, verursacht sie schwerwiegende Leistungsprobleme und wir sollten versuchen, sie in der Anwendung zu vermeiden. Tatsächlich nennt MySQL diese Art von SQL nicht deterministischem SQL und empfiehlt ihre Verwendung nicht.

Wenn Sie diese SQL verwenden müssen, um die Geschäftslogik in Ihrer Anwendung zu implementieren und nicht die gleichzeitigen Aktualisierungen der Quellentabelle zu beeinflussen, können Sie die folgenden zwei Maßnahmen ergreifen:

• Einer besteht darin, den Ansatz im obigen Beispiel zu übernehmen und den Wert von innoDB_LOCKS_UNSAFE_FOR_BINLOG auf "on" auf "On" zu zwingen, MySQL dazu zu zwingen, eine Multi-Version-Datenkonsistenzlesung zu verwenden. Der Preis, den Sie zahlen, ist jedoch, dass Sie Daten möglicherweise nicht korrekt wiederherstellen oder mit Binlog kopieren können, sodass diese Methode nicht empfohlen wird.

• Die zweite Methode besteht darin, die Kombination von "Select * aus Source_Tab ... in Outfile" und "Daten infile ..." zu verwenden, um sie indirekt zu erreichen.

Unterschiede in konsistenten Lesevorgängen und Schlössern zwischen verschiedenen Isolationsniveaus von InnoDB

Wie bereits erwähnt, sind Sperr- und Mehrversionsdaten das Mittel, mit dem InnoDB konsistente Lesevorgänge und ISO/ANSI-SQL92-Isolationsniveaus implementiert. Gleichzeitig haben die Merkmale der Datenwiederherstellung und Replikationsmechanismen auch einen großen Einfluss auf die konsistenten Lesestrategien und Verriegelungsstrategien einiger SQLs. Diese Funktionen sind in der folgenden Tabelle zusammengefasst, um die Leser zu bequem zu machen.

Vergleich von Schlössern in verschiedenen SQL -Anweisungen bei verschiedenen Isolationsniveaus in der InnoDB -Speichermotor

Aus der obigen Tabelle können wir erkennen, dass für viele SQL -Aussagen, je höher die Isolationsstufe, desto strengerer Schloss, das InnoDB zu dem Datensatzmengen hinzufügt (insbesondere bei der Verwendung von Bereichsbedingungen), desto höher die Möglichkeit von Lock -Konflikten und desto größer die Auswirkungen auf die gleichzeitige Leistung der Transaktionsverarbeitung. Daher sollten wir versuchen, in unserer Anwendung ein niedrigeres Isolationsniveau zu verwenden, um die Wahrscheinlichkeit von Verriegelungskonflikten zu verringern. Durch die Optimierung der Transaktionslogik reicht es für die meisten Anwendungen aus, die abgelesene Isolationsstufe zu verwenden. Für einige Transaktionen, die eine höhere Isolationsstufe erfordern, können Sie die Isolationsstufe dynamisch ändern, indem Sie die Set -Session -Transaktions -Isolationsstufe wiederholbares Lese- oder Set -Session -Transaktions -Isolationsebene im Programm ausführen, um die Anforderungen zu erfüllen.

Wann verwendet Tischschlösser

Für InnoDB-Tabellen sollten in den meisten Fällen Zeilensperren verwendet werden, da Transaktionen und Zeilensperren häufig die Gründe sind, warum wir uns für InnoDB-Tabellen entscheiden. Bei einigen speziellen Transaktionen können Sie jedoch auch die Verwendung von Sperren auf Tabellenebene in Betracht ziehen.

• Die erste Situation ist: Die Transaktion muss die meisten oder alle Daten aktualisieren und die Tabelle ist relativ groß. Wenn die standardmäßige Zeilensperre verwendet wird, ist nicht nur die Effizienz der Transaktionsausführung gering, sondern es kann auch zu langen Sperrwartezeiten und Sperrkonflikten für andere Transaktionen kommen. In diesem Fall können Sie die Verwendung von Tabellensperren in Betracht ziehen, um die Ausführung der Transaktion zu beschleunigen.

• Die zweite Situation besteht darin, dass die Transaktion mehrere Tabellen umfasst, was relativ komplex ist und zu Deadlocks und Rollbacks einer großen Anzahl von Transaktionen führen kann. In diesem Fall können Sie auch in Erwägung ziehen, alle an der Transaktion beteiligten Tabellen gleichzeitig zu sperren, um Deadlocks zu vermeiden und den durch das Zurücksetzen der Transaktion verursachten Datenbank-Overhead zu verringern.

Natürlich sollten nicht zu viele dieser beiden Transaktionen in der Anwendung vorhanden sein, andernfalls sollten Sie in Betracht ziehen, MyISAM -Tabellen zu verwenden.

Beachten Sie in InnoDB bei der Verwendung von Tabellensperren die folgenden zwei Punkte.

(1) Obwohl in InnoDB Sperren mit Tischniveau hinzugefügt werden können, muss festgestellt werden, dass die Tabellenschlösser nicht von der InnoDB-Speicher-Engine-Schicht verwaltet werden, aber der MySQL-Server ist jedoch nur dann, wenn autokommit = 0 und innodb_table_locks = 1 (Standardeinstellungen). B. In diesem Fall kann InnoDB automatisch Deadlocks mit Schlösser auf Tabellenebene identifizieren. Wir werden weiterhin Deadlock im nächsten Abschnitt diskutieren.

(2) Wenn Sie eine InnoDB-Tabelle sperren, sollten Sie die Tabelle nicht auf die Tischschloss freisetzen, da die Tischschloss die Tischschloss nicht freigeben können, um die Tischschloss zu veröffentlichen, um die Tischschloss zu veröffentlichen. Der richtige Weg ist wie folgt:

Wenn Sie beispielsweise in Tabelle t1 schreiben und aus Tabelle t lesen müssen, können Sie dies wie folgt tun:

AUTOCOMMIT SETZEN=0;
Sperrtabellen T1 Write, T2 Read, ...;
[Machen Sie hier etwas mit Tabellen T1 und T2];
BEGEHEN;
TABELLEN ENTSPERREN;

Über Deadlock

Wie oben erwähnt, sind MyISAM -Tischschlösser tadellos frei. In InnoDB werden jedoch mit Ausnahme von Transaktionen, die aus einer einzelnen SQL -Anweisung bestehen, Schritt für Schritt erworben, was Deadlock in InnoDB ermöglicht. Das Folgende ist ein Beispiel für einen Deadlock.

Deadlock -Beispiel in InnoDB Storage Engine

Im obigen Beispiel müssen beide Transaktionen die von der andere Partei gehaltene exklusive Schloss erhalten, um die Transaktion weiter abzuschließen.

Wenn ein Deadlock auftritt, kann InnoDB ihn im Allgemeinen automatisch erkennen und eine Transaktion dazu veranlassen, die Sperre freizugeben und ein Rollback durchzuführen, während die andere Transaktion die Sperre erwirbt und mit der Ausführung der Transaktion fortfährt. Wenn jedoch externe Sperren oder Tabellenverriegelungen beteiligt sind, kann InnoDB nicht automatisch Deadlocks erkennen. Es ist zu beachten, dass dieser Parameter nicht nur zur Lösung des Deadlock -Problems verwendet wird. Wir können diese Situation vermeiden, indem wir einen entsprechenden Schwellenwert für das Timeout der Sperre festlegen.

Im Allgemeinen sind Deadlocks ein Problem des Anwendungsdesigns. Im Folgenden sind einige häufige Methoden aufgeführt, um Deadlock durch Beispiele zu vermeiden.

(1) Wenn in einer Anwendung verschiedene Programme gleichzeitig auf mehrere Tabellen zugreifen, sollten Sie versuchen, den Zugriff auf die Tabellen in derselben Reihenfolge zu vereinbaren. Dadurch kann die Wahrscheinlichkeit eines Deadlocks erheblich verringert werden. Im folgenden Beispiel ist die Chance auf Deadlock sehr hoch, da die beiden Sitzungen in verschiedenen Bestellungen auf die beiden Tabellen zugreifen! Wenn die Zugriffe jedoch in der gleichen Reihenfolge erfolgen, können Deadlocks vermieden werden.

Deadlock -Beispiel, das durch Tischreihenfolge in InnoDB Storage Engine verursacht wurde

(2) Wenn das Programm Daten stapelweise verarbeitet und die Daten im Voraus sortiert werden, um sicherzustellen, dass jeder Thread die Datensätze in einer festen Reihenfolge verarbeitet, kann die Möglichkeit eines Deadlocks erheblich verringert werden.

Ein Beispiel für Deadlock, die durch inkonsistente Tischdatenbetriebsauftrag in der InnoDB Storage Engine verursacht werden

(3) Wenn Sie einen Datensatz aktualisieren möchten, sollten Sie sich direkt für eine ausreichende Sperre beantragen, dh eine exklusive Sperre, anstatt zuerst eine gemeinsame Sperre zu beantragen und dann eine exklusive Schloss bei der Aktualisierung zu beantragen, wenn ein Benutzer eine exklusive Schloss beantragt.

(4) Wie bereits erwähnt, verwenden bei der Wiederholungslese-Isolationsebene, wenn zwei Threads gleichzeitig ausgewählte ... für die Aktualisierung exklusive Sperren auf Datensätze mit derselben Bedingung angewendet werden. Beide Threads gelten bei der Anwendung der Sperren, wenn keine Datensätze die Bedingung erfüllen. Das Programm stellt fest, dass der Datensatz noch nicht vorhanden ist, und versucht daher, einen neuen Datensatz einzufügen. Wenn zwei Threads dies tun, kommt es zu einem Deadlock. In diesem Fall kann das Ändern der Isolationsstufe zum Lesen des festgelegten Lesens das Problem vermeiden, wie unten gezeigt.

Deadlock -Beispiel 1 verursacht durch Isolationsniveau in der InnoDB -Speichermotor

(5) Wenn die Isolationsstufe festgelegt wird, bestimmen beide Threads für die Aktualisierung, ob es Datensätze gibt, die die Bedingungen erfüllen. Zu diesem Zeitpunkt kann nur ein Thread erfolgreich eingefügt werden, und der andere Thread wartet auf die Sperre. Wenn zu diesem Zeitpunkt ein dritter Thread erneut eine exklusive Sperre beantragt, tritt ein Deadlock auf.

In diesem Fall können Sie den Einfügenvorgang direkt ausführen und dann die doppelte Ausnahme von Primärschlüssel fangen. Führen Sie bei der Begegnung eines Primärschlüssel -Duplikatfehlers Rollback immer durch, um die erfasste exklusive Sperre zu veröffentlichen, wie unten gezeigt.

Deadlock Beispiel 2 verursacht durch Isolationsniveau in der InnoDB -Speichermotor

Obwohl Deadlock durch die oben eingeführten Design- und SQL -Optimierungsmaßnahmen stark reduziert werden kann, ist es schwierig, Deadlock vollständig zu vermeiden. Daher ist es eine gute Programmiergewohnheit, bei der Programmdesign immer Deadlock -Ausnahmen zu fangen und umzugehen.

Wenn ein Deadlock auftritt, können Sie den Befehl show InnoDB Status verwenden, um die Ursache des letzten Deadlocks zu bestimmen. Die zurückgegebenen Ergebnisse enthalten detaillierte Informationen zu den Deadlock-bezogenen Transaktionen, wie die SQL-Anweisung, die die Deadlock verursacht hat, die Schlösser, die die Transaktion erworben hat, auf die Warten und die zurückgerollten Transaktionen. Basierend darauf können wir die Ursachen von Deadlock- und Verbesserungsmaßnahmen analysieren. Das Folgende ist eine Stichprobe von Show InnoDB Statusausgabe:

MySQL> InnoDB Status \ g anzeigen
…….
------------------------
Zuletzt erkannter Deadlock
------------------------
070710 14:05:16
*** (1) TRANSAKTION:
Transaktion 0 117470078, aktiv 117 Sek., Prozess Nr. 1468, OS -Thread -ID 1197328736 Einfügen
MySQL-Tabellen in Verwendung 1, gesperrt 1
Lock Wait 5 Lock Struct (en), Haufengröße 1216
MySQL Thread ID 7521657, Abfrage -ID 673468054 Localhost Root Update
In das Land einfügen (Country_id, Country) Werte (110, 'Test')
………
*** (2) TRANSAKTION:
Transaktion 0 117470079, Active 39 Sek., Prozess Nr. 1468, OS -Thread -ID 1164048736 Startindex Lesen, Thread in InnoDB 500 deklariert
MySQL-Tabellen in Verwendung 1, gesperrt 1
4 Sperrstrukturen (n), Haufengröße 1216, Log -Einträge rückgängig machen 1
MySQL Thread ID 7521664, Abfrage -ID 673468058 Localhost Root Statistics
Wählen Sie First_Name, last_name vom Schauspieler, wobei Actor_id = 1 für die Aktualisierung
*** (2) HÄLT DAS SCHLOSS:
………
*** (2) WARTEN AUF DIE GEWÄHRUNG DIESER SPERRE:
………
*** WIR MACHEN DIE TRANSAKTION ZURÜCK (1)
…

Dieser Artikel erklärt umfassend die detaillierte Verwendung von MySQL -Tabellenschlössern, Zeilensperrungen, gemeinsamen Schlössern, exklusiven Schlössern und Gap -Sperren.