Detaillierte Erläuterung des Prinzips des MySQL-Sperrmechanismus (1)

Sperre ist ein Mechanismus für Computer, um mehrere Prozesse oder Threads zu koordinieren, um gleichzeitig auf eine Ressource zuzugreifen. In einer Datenbank sind Daten neben dem traditionellen Wettbewerb um Rechenressourcen (wie CPU, RAM, I/O usw.) auch eine Ressource, die von vielen Benutzern gemeinsam genutzt wird. Die Gewährleistung der Konsistenz und Wirksamkeit des gleichzeitigen Zugriffs auf Daten ist ein Problem, das alle Datenbanken lösen müssen. Sperrkonflikte sind ebenfalls ein wichtiger Faktor, der sich auf die Leistung des gleichzeitigen Zugriffs auf Datenbanken auswirkt. Aus dieser Sicht sind Sperren für Datenbanken besonders wichtig und komplex. In diesem Kapitel konzentrieren wir uns auf die Eigenschaften des MySQL-Sperrmechanismus, häufige Sperrprobleme und einige Methoden oder Vorschläge zur Lösung von MySQL-Sperrproblemen.
Mysql verwendet viele solcher Sperrmechanismen, wie z. B. Zeilensperren, Tabellensperren, Lesesperren, Schreibsperren usw., die alle vor Operationen gesperrt werden. Diese Sperren werden zusammenfassend als pessimistische Sperren bezeichnet.

MySQL-Sperrübersicht

Im Vergleich zu anderen Datenbanken ist der Sperrmechanismus von MySQL relativ einfach und sein wichtigstes Merkmal ist der unterschiedliche Speicher, den die Engine unterstützt verschiedene Schließmechanismen. Beispielsweise verwenden die Speicher-Engines MyISAM und MEMORY Sperren auf Tabellenebene (table-level locking); die Speicher-Engine BDB verwendet Seitensperren (page-level locking), unterstützt aber auch Sperren auf Tabellenebene Speicher-Engine Sowohl Sperren auf Zeilenebene (；InnoDB) als auch Sperren auf Tabellenebene werden unterstützt, standardmäßig werden jedoch Sperren auf Zeilenebene verwendet. row-level locking
Sperre auf Tabellenebene: geringer Overhead, keine Deadlocks; große Sperrgranularität, höchste Wahrscheinlichkeit von Sperrenkonflikten und niedrigste Parallelität.
Sperre auf Zeilenebene: Es kommt zu einem hohen Overhead, es kommt zu einem langsamen Deadlock; die Sperrgranularität ist am geringsten und die Parallelität ist am höchsten.
Seitensperre: Der Overhead und die Sperrzeit liegen zwischen Tabellensperren und Zeilensperren; die Sperrgranularität liegt zwischen Tabellensperren und Zeilensperren, und die Parallelität ist durchschnittlich Es kann sein Anhand der oben genannten Eigenschaften ist ersichtlich, dass es schwierig ist, allgemein zu sagen, welches Schloss besser ist. Wir können nur anhand der Eigenschaften bestimmter Anwendungen sagen, welches Schloss besser geeignet ist! Nur aus Sicht der Sperre: Sperren auf Tabellenebene eignen sich besser für Anwendungen, die hauptsächlich auf Abfragen basieren und nur eine kleine Datenmenge gemäß Indexbedingungen aktualisieren, z. B. Webanwendungen, während Sperren auf Zeilenebene besser für Anwendungen geeignet sind die über eine große Anzahl gleichzeitiger Aktualisierungen basierend auf Indexbedingungen und eine kleine Anzahl unterschiedlicher Daten sowie gleichzeitige Abfrageanwendungen verfügen, wie z. B. einige Online-Transaktionsverarbeitungssysteme (OLTP).

MyISAM-Tabellensperre

MySQL-Sperren auf Tabellenebene haben zwei Modi: Tabellen-gemeinsame Lesesperre (Table Read Lock) und Tabellen-exklusive Schreibsperre (Tabellenschreibsperre).

Lesevorgänge für die MyISAM-Tabelle blockieren nicht die Leseanforderungen anderer Benutzer für dieselbe Tabelle, Schreibvorgänge für die MyISAM-Tabelle blockieren jedoch die Lese- und Schreibanforderungen anderer Benutzer für dieselbe Tabelle Schreiboperationen; die Leseoperationen und Schreiboperationen der MyISAM-Tabelle sind seriell! Anhand des in Tabelle 20-2 gezeigten Beispiels 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 von anderen Threads warten, bis die Sperre aufgehoben wird.

Die Schreibsperre der MyISAM-Speicher-Engine blockiert Lesebeispiele:

Wenn ein Thread eine Schreibsperre für eine Tabelle erhält, kann nur der Thread, der die Sperre hält, die Tabelle aktualisieren. Lese- und Schreibvorgänge von anderen Threads warten, bis die Sperre aufgehoben wird.

Beispiel für die Lesesperre der MyISAM-Speicher-Engine:

Eine Sitzung verwendet den Befehl LOCK TABLE, um der Tabelle film_text eine Lesesperre hinzuzufügen Tabelle. Datensätze, aber das Aktualisieren oder Zugreifen auf andere Tabellen führt gleichzeitig zu Fehlern. Eine andere Sitzung kann die Datensätze in der Tabelle abfragen, aber Aktualisierungen verursachen Sperrwartezeiten.

So fügen Sie eine Tabellensperre hinzu

MyISAM fügt vor der Ausführung der Abfrageanweisung (SELECT) automatisch Lesesperren für alle beteiligten Tabellen hinzu Ein Benutzereingriff ist nicht erforderlich, sodass Benutzer im Allgemeinen nicht direkt den Befehl LOCK TABLE verwenden müssen, um die MyISAM-Tabelle explizit zu sperren. In den Beispielen dient die explizite Sperrung hauptsächlich zu Demonstrationszwecken und ist nicht erforderlich.
Die Anzeigesperre von MyISAM-Tabellen wird im Allgemeinen durchgeführt, um 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 eine Bestelldetailtabelle „order_detail“, die die Zwischensumme jedes Produkts jeder Bestellung aufzeichnet. Angenommen, wir müssen die beiden Tabellen überprüfen. Um zu überprüfen, ob die Gesamtbeträge übereinstimmen, müssen Sie möglicherweise die folgenden beiden SQLs ausführen:

Select sum(total) from orders;
Select sum(subtotal) from order_detail;

Wenn zu diesem Zeitpunkt die beiden Tabellen nicht zuerst gesperrt werden, können falsche Ergebnisse auftreten, da während der Ausführung der ersten Tabelle Anweisung: Die Tabelle order_detail hat sich möglicherweise geändert. Daher sollte die richtige Methode sein:

Lock tables orders read local, order_detail read local;
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
Unlock tables;

Erklären Sie insbesondere die folgenden zwei Punkte:
1. Das obige Beispiel fügt die Option „local“ zu LOCK TABLES hinzu, die verwendet wird, um die Parallelität von MyISAM-Tabellen zu erfüllen Im Falle von Einfügebedingungen ist es anderen Benutzern gestattet, gleichzeitig Datensätze am Ende der Tabelle einzufügen. Das Problem des gleichzeitigen Einfügens in die MyISAM-Tabelle wird später näher erläutert.
2. Wenn Sie LOCK TABLES zum expliziten Hinzufügen von Tabellensperren zu einer Tabelle verwenden, müssen alle an der Tabelle beteiligten Sperren gleichzeitig erhalten werden, und MySQL unterstützt keine Sperraktualisierungen. Das heißt, nach der Ausführung von LOCK TABLES können Sie nur auf die explizit gesperrten Tabellen zugreifen, nicht jedoch gleichzeitig auf die entsperrten Tabellen. Wenn Sie eine Lesesperre hinzufügen, können Sie nur Abfragevorgänge ausführen, jedoch keine Aktualisierungsvorgänge. Tatsächlich ist dies bei der automatischen Sperrung grundsätzlich der Fall und erhält immer alle von der SQL-Anweisung benötigten Sperren auf einmal. Aus diesem Grund werden MyISAM-Tabellen nicht blockiert (Deadlock Free).

Bei der Verwendung von LOCK TABLES müssen Sie nicht nur alle verwendeten Tabellen auf einmal sperren, sondern auch, wie oft dieselbe Tabelle in der SQL-Anweisung vorkommt, Sie müssen sie über denselben Alias ​​wie in der sperren SQL-Anweisung. Andernfalls kann etwas schief gehen! Nachfolgend finden Sie ein Beispiel.

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

mysql> lock table actor read; 
Query OK, 0 rows affected (0.00 sec)

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

mysql> select a.first_name,a.last_name,b.first_name,b.last_name 
from actor a,actor b 
where a.first_name = b.first_name and a.first_name = &#39;Lisa&#39; and a.last_name = &#39;Tom&#39; and a.last_name <> b.last_name;
ERROR 1100 (HY000): Table ‘a’ was not locked with LOCK TABLES

(3) Die Alias ​​muss separat gesperrt werden:

mysql> lock table actor as a read,actor as b read;
Query OK, 0 rows affected (0.00 sec)

(4) Die Abfrage gemäß dem Alias ​​kann korrekt ausgeführt werden:

mysql> select a.first_name,a.last_name,b.first_name,b.last_name 
from actor a,actor b where a.first_name = b.first_name 
and a.first_name = &#39;Lisa&#39; and a.last_name = &#39;Tom&#39; and a.last_name <> b.last_name;
+————+———–+————+———–+ 
| first_name | last_name | first_name | last_name | 
+————+———–+————+———–+ 
| Lisa | Tom | LISA | MONROE | 
+————+———–+————+———–+ 
1 row in set (0.00 sec)

Sperrkonflikt auf Tabellenebene abfragen

Konflikte mit Tabellensperren auf dem System können durch Überprüfen der Statusvariablen table_locks_waited und table_locks_immediate analysiert werden:

mysql> show status like 'table%';
1Variable_name | Value 
Table_locks_immediate | 2979 
Table_locks_waited | 0 
2 rows in set (0.00 sec))

Wenn der Wert von Table_locks_waited relativ hoch ist, deutet dies auf einen schwerwiegenden Fehler hin Sperrkonflikt auf Tabellenebene.

Gleichzeitige Einfügungen

Wie oben erwähnt, erfolgt das Lesen und Schreiben von MyISAM-Tabellen seriell, aber das ist insgesamt. 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 des gleichzeitigen Einfügeverhaltens verwendet wird. Ihr Wert kann jeweils 0, 1 oder 2 sein.

1. Wenn concurrent_insert auf 0 gesetzt ist, ist gleichzeitiges Einfügen nicht zulässig.

2. Wenn concurrent_insert auf 1 gesetzt ist und keine Lücken in der MyISAM-Tabelle vorhanden sind (d. h. es gibt keine gelöschten Zeilen in der Mitte der Tabelle), erlaubt MyISAM einem Prozess, die Tabelle zu lesen Ein anderer Prozess fügt am Ende des Tabellendatensatzes ein. Dies ist auch die Standardeinstellung für MySQL.

3. Wenn concurrent_insert auf 2 gesetzt ist, dürfen Datensätze gleichzeitig am Ende der Tabelle eingefügt werden, unabhängig davon, ob Lücken in der MyISAM-Tabelle vorhanden sind.

Im folgenden Beispiel erhält session_1 die READ LOCAL-Sperre einer Tabelle, kann die Tabelle jedoch nicht aktualisieren (session_2), obwohl sie die Tabelle nicht abfragen, löschen und aktualisieren können Es werden Operationen ausgeführt, es können jedoch gleichzeitig Einfügeoperationen für die Tabelle ausgeführt werden. Es wird davon ausgegangen, dass die Tabelle keine Lücken aufweist.

Wie oben erwähnt, erfolgt das Lesen und Schreiben von MyISAM-Tabellen seriell, dies gilt jedoch allgemein. 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 des gleichzeitigen Einfügeverhaltens verwendet wird. Ihr Wert kann jeweils 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 (d. h. keine gelöschten Zeilen in der Mitte der Tabelle vorhanden sind), erlaubt MyISAM einem Prozess, die Tabelle zu lesen, während ein anderer Prozess Datensätze vom Ende der Tabelle einfügt der Tisch. Dies ist auch die Standardeinstellung für MySQL. Wenn concurrent_insert auf 2 gesetzt ist, können Datensätze gleichzeitig am Ende der Tabelle eingefügt werden, unabhängig davon, ob Lücken in der MyISAM-Tabelle vorhanden sind.

Im folgenden Beispiel erhält session_1 die READ LOCAL-Sperre einer Tabelle, kann die Tabelle jedoch nicht aktualisieren (session_2), obwohl sie die Tabelle nicht abfragen, löschen und aktualisieren können Es werden Operationen ausgeführt, es können jedoch gleichzeitig Einfügeoperationen für die Tabelle ausgeführt werden. Es wird davon ausgegangen, dass die Tabelle keine Lücken aufweist.

Beispiel für die Lese- und Schreib-Parallelität (INSERT) der MyISAM-Speicher-Engine:

Sie können die Funktion zum gleichzeitigen Einfügen der MyISAM-Speicher-Engine verwenden, um das Problem zu lösen Einfügen derselben Tabelle in die Anwendung. Wenn Sie beispielsweise die Systemvariable concurrent_insert auf 2 setzen, ist das gleichzeitige Einfügen immer möglich. Gleichzeitig wird die OPTIMIZE TABLE-Anweisung regelmäßig während der Leerlaufzeit des Systems ausgeführt, um den Speicherplatz zu defragmentieren und die durch das Löschen von Datensätzen verursachten Zwischenlücken zu schließen.

MyISAM-Sperrplanung

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

1. Durch die Angabe des Startparameters low-priority-updates gibt die MyISAM-Engine Leseanfragen standardmäßig Priorität.

2. Durch die Ausführung des Befehls SET LOW_PRIORITY_UPDATES=1 wird die Priorität der von der Verbindung ausgegebenen Update-Anfrage reduziert.

3. Reduzieren Sie die Priorität der Anweisung, indem Sie das LOW_PRIORITY-Attribut der INSERT-, UPDATE- und DELETE-Anweisung angeben.

Obwohl die oben genannten drei Methoden entweder Update-First oder Query-First sind, 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- und Schreibkonflikten, d. h. das Festlegen eines geeigneten Werts für den Systemparameter max_write_lock_count. Wenn die Lesesperre einer Tabelle diesen Wert erreicht, blockiert MySQL die Schreibanforderung vorübergehend . Die Priorität wird gesenkt, wodurch der Lesevorgang eine gewisse Chance erhält, die Sperre zu erhalten.

Die durch den Schreibprioritätsplanungsmechanismus verursachten Probleme und Lösungen wurden oben besprochen. Auch hier gilt es zu betonen: Manche Abfrageoperationen, die lange Laufzeiten erfordern, „verhungern“ auch den Schreibvorgang! Daher sollten Sie versuchen, langwierige Abfragevorgänge in Ihrer Anwendung zu vermeiden. Versuchen Sie nicht immer, das Problem mit einer SELECT-Anweisung zu lösen, da diese scheinbar clevere SQL-Anweisung oft komplexer ist und wenn möglich mehr Zeit in Anspruch nimmt. SQL-Anweisungen können durch den Einsatz von Zwischentabellen und anderen Maßnahmen bis zu einem gewissen Grad „zerlegt“ werden, sodass jeder Schritt der Abfrage 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 Leerlaufzeiten der Datenbank ausgeführt werden. Beispielsweise können einige reguläre Statistiken so geplant werden, dass sie nachts ausgeführt werden.

Ich werde Ihnen später die InnoDB-Sperre erklären.

Weitere verwandte Fragen finden Sie auf der chinesischen PHP-Website: Mysql-Video-Tutorial

Das obige ist der detaillierte Inhalt vonDetaillierte Erläuterung des Prinzips des MySQL-Sperrmechanismus (1). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!