Die Sicherstellung der Konsistenz und Wirksamkeit des gleichzeitigen Datenzugriffs ist ein Problem, das alle Datenbanken lösen müssen. Sperrkonflikte sind ebenfalls ein wichtiger Faktor, der sich auf die Leistung des gleichzeitigen Datenbankzugriffs auswirkt. Aus dieser Perspektive sind Sperren für Datenbanken besonders wichtig und komplex.
MySQL-Sperrübersicht
Im Vergleich zu anderen Datenbanken ist der Sperrmechanismus von MySQL relativ einfach. Sein wichtigstes Merkmal ist, dass verschiedene Speicher-Engines unterschiedliche Sperrmechanismen unterstützen.
Beispielsweise verwenden
MyISAM- und MEMORY-Speicher-Engines Sperren auf Tabellenebene.
Die InnoDB-Speicher-Engine unterstützt sowohl das Sperren auf Zeilenebene als auch das Sperren auf Tabellenebene, standardmäßig wird jedoch das Sperren auf Zeilenebene verwendet.
Die Eigenschaften dieser drei Sperren in MySQL lassen sich grob wie folgt zusammenfassen: Sperren auf Tabellenebene: geringer Overhead, keine Deadlocks, höchste Wahrscheinlichkeit von Sperrenkonflikten und niedrigste Parallelität.
Sperren auf Zeilenebene: Hoher Overhead, langsame Sperren; die Sperrgranularität ist am geringsten, die Wahrscheinlichkeit von Sperrenkonflikten ist am höchsten.
Seitensperre: Die Kosten und die Sperrzeit liegen zwischen Tabellensperren und Zeilensperren. Die Sperrgranularität liegt zwischen Tabellensperren und Zeilensperren, und die Parallelität ist durchschnittlich.
Nur aus Sicht der Sperren: Sperren auf Tabellenebene eignen sich besser für Anwendungen, die hauptsächlich auf Abfragen basieren und bei denen nur eine kleine Datenmenge entsprechend den Indexbedingungen aktualisiert wird, z. B. Sperren auf Zeilenebene Geeignet für Anwendungen mit einer großen Anzahl gleichzeitiger Aktualisierungen basierend auf Indexbedingungen. Eine kleine Menge unterschiedlicher Daten und gleichzeitige Abfrageanwendungen, wie z. B. einige Online-Transaktionsverarbeitungssysteme (OLTP). In den folgenden Abschnitten konzentrieren wir uns auf die Probleme von MySQL-Tabellensperren und InnoDB-Zeilensperren.
MyISAM-Tabellensperre
Die MyISAM-Speicher-Engine unterstützt nur Tabellensperren, was auch der einzige Sperrtyp ist, der in den ersten Versionen von MySQL unterstützt wurde. Mit der kontinuierlichen Verbesserung der Anwendungsanforderungen für Transaktionsintegrität und Parallelität begann MySQL mit der Entwicklung transaktionsbasierter Speicher-Engines. Später erschienen nach und nach die BDB-Speicher-Engine, die Seitensperren unterstützt, und die InnoDB-Speicher-Engine, die Zeilensperren unterstützt (eigentlich ist InnoDB eine separate). Ein Unternehmen, das jetzt von Oracle übernommen wurde. Allerdings ist die Tabellensperre von MyISAM immer noch der am weitesten verbreitete Sperrtyp. In diesem Abschnitt wird die Verwendung von MyISAM-Tabellensperren ausführlich vorgestellt.
Sperrenkonflikt auf Tabellenebene abfragen
Sie können den Tabellensperrenkonflikt auf dem System analysieren, indem Sie die Statusvariablen table_locks_waited und table_locks_immediate überprüfen:
mysql>show status like 'table%';
Die MySQL-Sperre auf Tabellenebene verfügt über zwei Modi: gemeinsame Lesesperre für Tabellen (Table Read Lock) und exklusive Tabellenschreibsperre (Table Write Lock).
Der Lesevorgang der MyISAM-Tabelle blockiert nicht die Leseanforderungen anderer Benutzer für dieselbe Tabelle, aber er blockiert die Schreibanforderungen für dieselbe Tabelle.
Der Schreibvorgang der MyISAM-Tabelle blockiert das Lesen und Schreiben anderer Benutzer ; ; Die Lese- und Schreibvorgänge der MyISAM-Tabelle sind seriell.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.
Schreibsperre der Tabelle film_text erhalten
mysql> Tabelle film_text write sperren;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
|. 1001 |
Session2 erhält die Sperre, die Abfrage gibt zurück: mysql>
|. 1001 |. Test |
1 Zeile im Satz (57,59 Sek.)
Wie füge ich eine Tabellensperre hinzu?
MyISAM fügt vor der Ausführung der Abfrageanweisung (SELECT) automatisch Lesesperren für alle beteiligten Tabellen hinzu. Dieser Vorgang führt automatisch Schreibsperren für die beteiligten Tabellen aus Es ist kein Benutzereingriff erforderlich. Daher müssen Benutzer im Allgemeinen den Befehl LOCK TABLE nicht direkt verwenden, um die MyISAM-Tabelle explizit zu sperren. Im Beispiel erfolgt die explizite Sperrung grundsätzlich aus Bequemlichkeitsgründen 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 zwei SQLs ausführen:
Summe (Gesamtsumme) aus Bestellungen auswählen;
Summe (Zwischensumme) aus Bestelldetail auswählen; Wenn Sie zuerst zwei Tabellen ausführen, können falsche Ergebnisse erzeugt werden, da sich die Tabelle „order_detail“ möglicherweise während der Ausführung der ersten Anweisung geändert hat. Daher sollte die richtige Methode sein:
Tabellen sperren, Aufträge lokal lesen, order_detail lokal lesen;
Summe (Gesamtsumme) aus Aufträgen auswählen;
Summe (Zwischensumme) aus Auftragsdetails auswählen;
Tabellen entsperren;
Spezielle Anweisungen Die folgenden zwei Punkten.
Das obige Beispiel fügt die Option „lokal“ bei LOCK TABLES hinzu. Ihre Funktion besteht darin, anderen Benutzern das gleichzeitige Einfügen von Datensätzen am Ende der Tabelle zu ermöglichen Die MyISAM-Tabelle wird später ebenfalls erläutert.
Wenn Sie LOCK TABLES verwenden, um Tabellensperren explizit zu einer Tabelle hinzuzufügen, 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).
Eine Sitzung verwendet den Befehl LOCK TABLE, um der Tabelle film_text eine Lesesperre hinzuzufügen. Bei der Aktualisierung oder beim gleichzeitigen Zugriff auf andere Tabellen wird jedoch ein Fehler angezeigt kann die Datensätze in der Tabelle abfragen, aber beim Aktualisieren kommt es zu einer Sperrwartezeit.
Bei der Verwendung von LOCK TABLES müssen Sie nicht nur alle verwendeten Tabellen auf einmal sperren, sondern Sie müssen sie auch, unabhängig davon, wie oft dieselbe Tabelle in der SQL-Anweisung erscheint, über denselben Alias wie in der SQL-Anweisung sperren , sonst kommt es zu einem Fehler!
Beispiele sind wie folgt.
(1) Lesesperre für Akteurtabelle erhalten:
mysql> Tabelle Akteur lesen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
(2) Der Zugriff über Alias führt jedoch zu einem Fehler:
mysql> select a.first_name,a.last_name,b.first_name,b.last_name fromactor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a .last_name b.last_name;
ERROR 1100 (HY000): Tabelle „a“ wurde nicht mit LOCK TABLES gesperrt.
(3) Aliase müssen separat gesperrt werden:
MySQL> ,actor as b read;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
(4) Die Abfrage entsprechend dem Alias kann korrekt ausgeführt werden:
mysql> select a.first_name,a.last_name,b.first_name ,b.last_name von Schauspieler a,actor b wobei a.first_name = b.first_name und a.first_name = 'Lisa' und a.last_name = 'Tom' und a.last_name
|. 0 |
5 Zeilen im Satz (0,01 Sek.)
Wenn Sie feststellen, dass der Sperrenkonflikt schwerwiegend ist, beispielsweise die Werte von InnoDB_row_lock_waits und InnoDB_row_lock_time_avg relativ hoch sind, können Sie InnoDB-Monitore auch so einstellen, dass die Tabellen und Daten weiter beobachtet werden Zeilen usw., in denen Sperrenkonflikte auftreten, und analysieren Sie die Sperren. Ursache des Konflikts.
Die spezifische Methode lautet wie folgt:
mysql> CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
Abfrage OK, 0 Zeilen betroffen (0,14 Sek.)
Dann können Sie die folgende Anweisung zum Anzeigen verwenden:
mysql> Innodb-Status anzeigen Im Inhalt werden detaillierte Informationen über die aktuelle Sperre angezeigt, einschließlich Tabellenname, Sperrtyp, Sperrdatensatzstatus usw., um die weitere Analyse und Problembestimmung zu erleichtern. Nach dem Öffnen des Monitors wird der Überwachungsinhalt standardmäßig alle 15 Sekunden im Protokoll aufgezeichnet. Wenn er über einen längeren Zeitraum geöffnet wird, wird die .err-Datei daher sehr groß Der Benutzer muss daran denken, die Überwachungstabelle zu löschen, um sie zu schließen, oder indem er den Server mit der Option „--console“ startet, um das Schreiben der Protokolldatei zu deaktivieren.
5. Der Zeilensperrmodus und die Sperrmethode von InnoDB
InnoDB implementiert die folgenden zwei Arten von Zeilensperren.
Gemeinsame Sperre (S): Ermöglicht einer Transaktion das Lesen einer Zeile und verhindert, dass andere Transaktionen eine exklusive Sperre für denselben Datensatz erhalten.
Exklusive Sperre (X): Ermöglicht einer Transaktion, die eine exklusive Sperre erhält, das Aktualisieren von Daten und verhindert, dass andere Transaktionen gemeinsame Lesesperren und exklusive Schreibsperren für denselben Datensatz erhalten.
Um die Koexistenz von Zeilensperren und Tabellensperren zu ermöglichen und einen Sperrmechanismus mit mehreren Granularitäten zu implementieren, verfügt InnoDB außerdem über zwei intern verwendete Absichtssperren (Intention Locks).
Beide Absichtssperren sind Tabellensperren.
Intention Shared Lock (IS): Die Transaktion beabsichtigt, der Datenzeile eine gemeinsame Sperre hinzuzufügen. Die Transaktion muss zuerst die IS-Sperre der Tabelle erhalten, bevor sie einer Datenzeile eine gemeinsame Sperre hinzufügt.
Intention exklusive Sperre (IX): Die Transaktion beabsichtigt, der Datenzeile eine exklusive Zeilensperre hinzuzufügen. Die Transaktion muss zuerst die IX-Sperre der Tabelle erhalten, bevor sie einer Datenzeile eine exklusive Sperre hinzufügt.
Wenn der von einer Transaktion angeforderte Sperrmodus mit der aktuellen Sperre kompatibel ist, gewährt InnoDB der Transaktion die angeforderte Sperre. Andernfalls wartet die Transaktion auf die Freigabe der Sperre, wenn beide nicht kompatibel sind . Absichtssperren werden von InnoDB automatisch hinzugefügt und erfordern keinen Benutzereingriff.Die Zusammenfassung lautet wie folgt: 
2. Für gewöhnliche SELECT-Anweisungen fügt InnoDB keine Sperren hinzu
3 Eine Transaktion kann dem Recordset durch die folgende Anweisung gemeinsame Sperren oder exklusive Sperren hinzufügen.
Gemeinsame Sperre(n): SELECT * FROM table_name WHERE ... SPERRE IM SHARE-MODUS.
Exklusive Sperre (X): SELECT * FROM table_name WHERE ... FOR UPDATE.
Verwenden Sie SELECT ... IN SHARE MODE, um eine gemeinsame Sperre zu erhalten. Sie wird hauptsächlich verwendet, um zu bestätigen, ob eine bestimmte Zeile von Datensätzen 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. Bei Anwendungen, die den Zeilendatensatz nach dem Sperren aktualisieren müssen, sollte die Methode SELECT... FOR UPDATE verwendet werden, um einen zu erhalten Exklusives Schloss.
6. Die InnoDB-Zeilensperre wird durch das Sperren der Indexelemente im Index implementiert. Letzteres wird durch das Sperren der entsprechenden Datenzeilen im Datenblock erreicht.
Die Zeilensperren-Implementierungsfunktion von InnoDB bedeutet, dass InnoDB Sperren auf Zeilenebene nur verwendet, wenn Daten über Indexbedingungen abgerufen werden. Andernfalls verwendet InnoDB Tabellensperren!
In praktischen Anwendungen sollte dieser Funktion der InnoDB-Zeilensperre besondere Aufmerksamkeit gewidmet werden, da sie sonst zu einer großen Anzahl von Sperrkonflikten führen und somit die Parallelitätsleistung beeinträchtigen kann.
(1) Bei Abfragen ohne Indexbedingungen verwendet InnoDB Tabellensperren anstelle von Zeilensperren.
(2) Da die Zeilensperre von MySQL eine Sperre für den Index und nicht für den Datensatz ist, wird zwar auf Datensätze verschiedener Zeilen zugegriffen,
aber wenn derselbe Indexschlüssel verwendet wird, kommt es zu einem Sperrkonflikt. Bitte beachten Sie dies bei der Gestaltung Ihrer Bewerbung.
(3) Wenn eine Tabelle über mehrere Indizes verfügt, können verschiedene Transaktionen unterschiedliche Indizes verwenden, um verschiedene Zeilen zu sperren.
Darüber hinaus verwendet InnoDB Zeilensperren, unabhängig davon, ob Primärschlüsselindizes oder gewöhnliche Indizes verwendet werden.
(4) Selbst wenn in der Bedingung ein Indexfeld verwendet wird, bestimmt MySQL anhand der Kosten verschiedener Ausführungspläne, ob ein vollständiger Tabellenscan effizienter ist, z Bei einigen kleinen Tabellen werden keine Indizes verwendet. In diesem Fall verwendet InnoDB Tabellensperren anstelle von Zeilensperren. Vergessen Sie daher bei der Analyse von Sperrkonflikten nicht, den SQL-Ausführungsplan zu überprüfen, um sicherzustellen, dass der Index tatsächlich verwendet wird. Eine ausführliche Diskussion der Umstände, unter denen MySQL keine Indizes verwendet, finden Sie in der Einleitung im Abschnitt „Indexprobleme“ dieses Kapitels.
7. Lückensperre (Next-Key-Sperre)
Wenn wir Bereichsbedingungen anstelle von Gleichheitsbedingungen verwenden, um Daten abzurufen und gemeinsame oder exklusive Sperren anzufordern, sperrt InnoDB die Indexeinträge vorhandener Datensätze, die die Bedingungen für Datensätze erfüllen, deren Schlüsselwerte jedoch innerhalb des Bedingungsbereichs liegen existieren, genannt „GAP“, InnoDB wird auch diese „Lücke“ sperren, dieser Sperrmechanismus ist die sogenannte Lückensperre (Next-Key-Sperre). Wenn die empid-Tabelle nur 101 Datensätze enthält, sind die empid-Werte 1, 2,...,100,101. Das folgende SQL:
Wählen Sie * aus empid > für update; Bedingungsabruf: InnoDB sperrt nicht nur die Datensätze mit einem empid-Wert von 101, die die Bedingungen erfüllen, sondern sperrt auch die „Lücken“ mit empid größer als 101 (diese Datensätze existieren nicht). Der Zweck der Verwendung von Lückensperren durch InnoDB besteht einerseits darin, Phantom-Lesevorgänge zu verhindern und die Anforderungen relevanter Isolationsstufen zu erfüllen. Wenn im obigen Beispiel keine Lückensperren verwendet werden, fügen andere Transaktionen Datensätze mit einem Emid-Wert größer als 100 ein Wenn diese Transaktion die obige Anweisung erneut ausführt, erfolgt andererseits ein Phantomlesen, um ihre Wiederherstellungs- und Replikationsanforderungen zu erfüllen. Die Auswirkungen seiner Wiederherstellung und Replikation auf den Sperrmechanismus sowie die Verwendung von Lückensperren durch InnoDB unter verschiedenen Isolationsstufen werden in den folgenden Kapiteln näher erläutert.
Wenn Bereichsbedingungen zum Abrufen und Sperren von Datensätzen verwendet werden, blockiert der Sperrmechanismus von InnoDB offensichtlich das gleichzeitige Einfügen von Schlüsselwerten innerhalb des qualifizierten Bereichs, was häufig zu erheblichen Sperrwartezeiten führt. Daher müssen wir bei der tatsächlichen Anwendungsentwicklung, insbesondere bei Anwendungen mit vielen gleichzeitigen Einfügungen, versuchen, die Geschäftslogik zu optimieren, gleiche Bedingungen für den Zugriff auf Aktualisierungsdaten zu verwenden und die Verwendung von Bereichsbedingungen zu vermeiden.
Besonderer Hinweis ist, dass InnoDB zusätzlich zur Verwendung von Lückensperren beim Sperren durch Bereichsbedingungen auch Lückensperren verwendet, wenn gleiche Bedingungen verwendet werden, um eine Sperre für einen nicht vorhandenen Datensatz anzufordern!
Der Bedarf an Wiederherstellung und Replikation, die Auswirkungen auf den InnoDB-Sperrmechanismus
MySQL verwendet BINLOG, um die erfolgreiche Ausführung von INSERT, UPDATE, DELETE und anderen SQL-Anweisungen aufzuzeichnen, die Daten aktualisieren, und realisiert so die Wiederherstellung und Master-Slave-Replikation der MySQL-Datenbank. Der Wiederherstellungsmechanismus von MySQL (die Replikation ist eigentlich eine kontinuierliche BINLOG-basierte Wiederherstellung auf Slave-MySQL) weist die folgenden Merkmale auf.
Erstens erfolgt die MySQL-Wiederherstellung auf der Ebene der SQL-Anweisung, d. h. die erneute Ausführung der SQL-Anweisung in BINLOG. Dies unterscheidet sich von der Oracle-Datenbank, die auf Datenbankdateiblöcken basiert.
Zweitens wird MySQLs BINLOG in der Reihenfolge aufgezeichnet, in der Transaktionen übermittelt werden, und die Wiederherstellung erfolgt ebenfalls in dieser Reihenfolge. Dies unterscheidet sich auch von Oracle. Oracle stellt Daten gemäß der Systemänderungsnummer (SCN) wieder her. Wenn jede Transaktion beginnt, weist Oracle eine weltweit eindeutige SCN zu. Die Reihenfolge der Transaktionsstarts ist konsistent.
Aus den beiden oben genannten Punkten geht hervor, dass der Wiederherstellungsmechanismus von MySQL Folgendes erfordert: Bevor eine Transaktion übermittelt wird, können andere gleichzeitige Transaktionen keine Datensätze einfügen, die ihre Sperrbedingungen erfüllen, d. ANSI SQL92 Die Anforderung für die Isolationsstufe „Wiederholbares Lesen“ erfordert tatsächlich, dass Transaktionen serialisiert werden.
Darüber hinaus ist dies für SQL-Anweisungen wie „insert into target_tab select * from source_tab where ...“ und „create table new_tab ...select ... From source_tab where ...(CTAS)“ nicht der Fall verfügen über source_tab Führen Sie beliebige Aktualisierungsvorgänge aus, MySQL führt jedoch eine spezielle Verarbeitung für diese Art von SQL-Anweisung durch.
(Hier fügt InnoDB eine gemeinsame Sperre zu source_tab hinzu und verwendet keine Technologie zum Lesen der Datenkonsistenz mehrerer Versionen!)
Im obigen Beispiel entspricht das einfache Lesen der Daten in der Tabelle „source_tab“ dem Ausführen einer gewöhnlichen For SELECT-Anweisung , verwenden Sie einfach eine konsistente Lesart. ORACLE tut genau dies, indem es durch die MVCC-Technologie implementierte Multiversionsdaten verwendet, um ein konsistentes Lesen zu erreichen, ohne Sperren für source_tab hinzuzufügen. Wir wissen, dass InnoDB auch Daten mit mehreren Versionen implementiert und keine Sperren für das normale SELECT-konsistente Lesen erfordert. Hier fügt InnoDB jedoch eine gemeinsame Sperre zu source_tab hinzu und verwendet keine Technologie zum konsistenten Lesen von Daten mit mehreren Versionen.
Warum macht MySQL das? Der Grund besteht darin, die Korrektheit der Wiederherstellung und Replikation sicherzustellen. Denn ohne Sperre kann es zu falschen Datenwiederherstellungsergebnissen kommen, wenn andere Transaktionen während der Ausführung der obigen Anweisung source_tab aktualisieren. Um dies zu demonstrieren, wiederholen wir das vorherige Beispiel. Der Unterschied besteht darin, dass vor der Ausführung der Transaktion durch session_1 der Wert der Systemvariablen innodb_locks_unsafe_for_binlog auf „on“ gesetzt wird (der Standardwert ist „off“)
Wie aus dem Obigen ersichtlich ist, sperrt InnoDB nach dem Setzen des Werts der Systemvariablen innodb_locks_unsafe_for_binlog den source_tab nicht mehr, und das Ergebnis stimmt mit der Anwendungslogik überein :
SET TIMESTAMP=1169175130;
BEGIN;
# bei 274
#070119 10:51:57 Server-ID 1 end_log_pos 105 Abfrage thread_id=1 exec_time=0 error_code=0
SET. TIME STEMPEL=1169175117;
update source_tab set name = '8 ' where name = '1 ';
# at 379
#070119 10:52:10 server id 1 end_log_pos 406 474 Query thread_id= 2 exec_time=0 error_code=0
SET TIMESTAMP= 1169175134;
BEGIN;
# bei 474
#070119 10:51:29 Server-ID 1 end_log_pos 119 Abfrage thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1169175089;
in target_tab einfügen, d1 auswählen, name from source_tab where name = '1';
# at 593
#070119 10:52:14 server id 1 end_log_pos 620 Der Aktualisierungsvorgang erfolgt vor INSERT...SELECT. Wenn dieses BINLOG für die Datenbankwiederherstellung verwendet wird, stimmt das Wiederherstellungsergebnis nicht mit der tatsächlichen Anwendungslogik überein, und die Master-Slave-Datenbank ist inkonsistent.
Daher können die Anweisungen INSERT...SELECT... und CREATE TABLE...SELECT... gleichzeitige Aktualisierungen der Quelltabelle verhindern und dazu führen, dass auf die Sperre der Quelltabelle gewartet wird. Wenn die Abfrage komplex ist, führt dies zu ernsthaften Leistungsproblemen, und wir sollten versuchen, sie in unseren Anwendungen nicht zu verwenden. Tatsächlich nennt MySQL diese Art von SQL nicht deterministisches SQL und wird nicht empfohlen.
Wenn diese Art von SQL zum Implementieren der Geschäftslogik in der Anwendung verwendet werden muss und Sie die gleichzeitigen Aktualisierungen der Quelltabelle nicht beeinträchtigen möchten, können Sie die folgenden zwei Maßnahmen ergreifen:
Verfolgen Sie zunächst den Ansatz im Beispiel oben und ändern Sie den Wert von innodb_locks_unsafe_for_binlog. Setzen Sie ihn auf „on“, um MySQL zu zwingen, das Lesen der Datenkonsistenz mehrerer Versionen zu verwenden. Der dafür gezahlte Preis besteht jedoch darin, dass die Daten mit Binlog möglicherweise nicht korrekt wiederhergestellt oder kopiert werden. Daher wird diese Methode nicht empfohlen.
Die zweite Möglichkeit besteht darin, dies indirekt durch die Verwendung einer Kombination aus den Anweisungen „select * from source_tab ... Into outfile“ und „load data infile ...“ zu erreichen. Auf diese Weise sperrt MySQL source_tab nicht.
8. Unterschiede beim konsistenten Lesen und Sperren von InnoDB unter verschiedenen Isolationsstufen. Wie bereits erwähnt, sind Sperren und Multiversionsdaten für InnoDB die Mittel, um konsistente Lese- und ISO/ANSI-SQL92-Isolationsstufen zu erreichen Die Isolationsstufe, die konsistente Lesestrategie und die erforderlichen Sperren, die InnoDB bei der Verarbeitung von SQL verwendet, sind unterschiedlich. Gleichzeitig haben die Merkmale der Datenwiederherstellungs- und Replikationsmechanismen auch einen großen Einfluss auf einige konsistente SQL-Lesestrategien und Sperrstrategien. Diese Merkmale sind zur Vereinfachung für den Leser wie in Tabelle 20-16 dargestellt zusammengefasst.
1: Wenn die Isolationsstufe RC ist, werden Lückensperren nicht verwendet:
Jeder konsistente Lesevorgang, auch innerhalb derselben Transaktion, legt einen eigenen neuen Snapshot fest und liest ihn. siehe Abschnitt 14.8.2.3, „Konsistente nicht sperrende Lesevorgänge“.
Beim Sperren von Lesevorgängen (SELECT mit FOR UPDATE oder LOCK IN SHARE MODE), UPDATE-Anweisungen und DELETE-Anweisungen sperrt InnoDB nur Indexdatensätze, nicht die Lücken davor und somit ermöglicht das freie Einfügen neuer Datensätze neben gesperrten Datensätzen. Die Lückensperre wird nur zur Prüfung von Fremdschlüsseleinschränkungen und zur Prüfung doppelter Schlüssel verwendet.
Die offizielle Dokumentationsadresse lautet: https://dev.mysql.com/doc/refman /5.5/en/innodb-transaction-isolation-levels.html
2: Unter der wiederholbaren Leseisolationsstufe werden Lückensperren nicht verwendet, wenn der Index eindeutig ist und die Suche ebenfalls eindeutig ist. Andernfalls werden Lückensperren verwendet Die offizielle Beschreibung lautet wie folgt:
REPEATABLE READ
Dies ist die Standardisolationsstufe für konsistente Lesevorgänge innerhalb derselben Transaktion. Dies bedeutet, dass Sie mehrere einfache (nicht sperrende) SELECT-Anweisungen innerhalb derselben Transaktion lesen In derselben Transaktion sind diese SELECT-Anweisungen auch untereinander konsistent. Siehe Abschnitt 14.8.2.3, „Konsistente nicht sperrende Lesevorgänge“. Das Sperren hängt davon ab, ob die Anweisung einen eindeutigen Index mit einer eindeutigen Suchbedingung oder eine Suchbedingung vom Bereichstyp verwendet.
Bei einem eindeutigen Index mit einer eindeutigen Suchbedingung sperrt InnoDB nur den gefundenen Indexdatensatz, nicht die Lücke davor.
Für andere Suchbedingungen sperrt InnoDB den gescannten Indexbereich mithilfe von Lückensperren oder Next-Key-Sperren, um Einfügungen anderer Sitzungen in die vom Bereich abgedeckten Lücken zu blockieren. Informationen zu Lückensperren und Next-Key-Sperren finden Sie in Abschnitt 14.8 . 1, „InnoDB-Sperre“.
Die offizielle Adresse der Dokumentbeschreibung lautet: https://dev.mysql.com/doc/refman/5.5/en/innodb-transaction-isolation-levels.html
9. Wann werden Tabellensperren verwendet?
Für InnoDB-Tabellen In den meisten Fällen sollten Sperren auf Zeilenebene verwendet werden, da Transaktionen und Zeilensperren häufig der Grund sind, warum wir uns für InnoDB-Tabellen entscheiden. In einzelnen Sondertransaktionen können jedoch auch Sperren auf Tabellenebene in Betracht gezogen werden.
Die erste Situation ist: Die Transaktion muss die meisten oder alle Daten aktualisieren und die Tabelle ist relativ groß. Wenn die Standardzeilensperre verwendet wird, ist nicht nur die Effizienz der Transaktionsausführung gering, sondern es kann auch andere Ursachen haben Transaktionen warten lange und es kommt zu Sperrkonflikten. In diesem Fall können Sie die Verwendung von Tabellensperren in Betracht ziehen, um die Ausführungsgeschwindigkeit der Transaktion zu verbessern.
Die zweite Situation ist: Die Transaktion umfasst mehrere Tabellen, was relativ komplex ist und wahrscheinlich zu einem Deadlock und einer großen Anzahl von Transaktions-Rollbacks führt. In diesem Fall können Sie auch erwägen, die an der Transaktion beteiligten Tabellen einmal zu sperren, um Deadlocks zu vermeiden und den durch das Transaktions-Rollback verursachten Datenbank-Overhead zu reduzieren.
Natürlich sollten nicht zu viele dieser beiden Arten von Transaktionen in der Anwendung vorhanden sein, andernfalls sollten Sie über die Verwendung von MyISAM-Tabellen nachdenken.
Beachten Sie unter InnoDB die folgenden zwei Punkte, wenn Sie Tabellensperren verwenden.
(1) Obwohl Sie InnoDB mithilfe von LOCK TABLES Sperren auf Tabellenebene hinzufügen können, ist zu beachten, dass Tabellensperren nicht von der InnoDB-Speicher-Engine-Schicht verwaltet werden, sondern von der oberen Schicht: MySQL Server Autocommit = 0, innodb_table_locks = 1 (Standardeinstellung), die InnoDB-Schicht kann die von MySQL hinzugefügten Tabellensperren kennen, und MySQL Server kann auch die von InnoDB hinzugefügten Zeilensperren erkennen. In diesem Fall kann InnoDB automatisch Deadlocks auf Tabellenebene identifizieren Sperren; Andernfalls kann InnoDB solche Deadlocks nicht automatisch erkennen und behandeln. Was den Deadlock betrifft, werden wir ihn im nächsten Abschnitt weiter besprechen.
(2) Wenn Sie LOCK TABLES zum Sperren der InnoDB-Tabelle verwenden, achten Sie darauf, AUTOCOMMIT auf 0 zu setzen, da MySQL die Tabelle sonst nicht sperren wird. Verwenden Sie UNLOCK TABLES nicht, um die Tabellensperre vor Ende der Transaktion aufzuheben, da UNLOCK TABLES dies tut Die Transaktion wird implizit festgeschrieben; COMMIT oder ROLLBACK können keine Sperren auf Tabellenebene aufheben, die mit LOCK TABLES hinzugefügt wurden, und Tabellensperren müssen mit UNLOCK TABLES aufgehoben werden. Der richtige Weg ist wie folgt.
Wenn Sie beispielsweise in Tabelle t1 schreiben und aus Tabelle t lesen müssen, können Sie wie folgt vorgehen:
SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, . ..;
[hier etwas mit den Tabellen t1 und t2 machen];
COMMIT;
UNLOCK TABLES;
10. Bezüglich Deadlock
Wie oben erwähnt, sind MyISAM-Tabellensperren Deadlock-frei, da MyISAM sie immer erhält einmal Alle erforderlichen Sperren sind entweder erfüllt oder warten, sodass kein Deadlock auftritt. Aber in InnoDB werden Sperren, mit Ausnahme von Transaktionen, die aus einem einzelnen SQL bestehen, schrittweise erworben, was bestimmt, dass in InnoDB ein Deadlock möglich ist. Tabelle 20-17 zeigt ein Beispiel für einen Deadlock.
Im obigen Beispiel müssen beide Transaktionen die exklusive Sperre der anderen Partei erhalten, um mit dem Abschluss der Transaktion fortfahren zu können. Diese Art des zyklischen Wartens auf eine Sperre ist ein typischer Deadlock.
Nachdem ein Deadlock auftritt, kann InnoDB ihn normalerweise automatisch erkennen und veranlassen, dass eine Transaktion die Sperre aufhebt und zurücksetzt, während eine andere Transaktion die Sperre erhält und die Transaktion weiter abschließt. Wenn jedoch externe Sperren oder Tabellensperren beteiligt sind, kann InnoDB Deadlocks nicht vollständig automatisch erkennen. Dies muss durch Festlegen des Sperrwartezeit-Timeout-Parameters innodb_lock_wait_timeout behoben werden. Es ist zu beachten, dass dieser Parameter nicht nur zur Lösung des Deadlock-Problems verwendet wird. Wenn der gleichzeitige Zugriff relativ hoch ist und eine große Anzahl von Transaktionen ausgesetzt wird, weil die erforderlichen Sperren nicht sofort erhalten werden können, wird eine große Menge an Computerressourcen beansprucht und schwerwiegende Leistungsprobleme verursachen. Das Problem zieht sich sogar über Datenbanken hinweg. Wir können diese Situation vermeiden, indem wir einen geeigneten Schwellenwert für die Sperrwartezeit festlegen.
Im Allgemeinen sind Deadlocks ein Problem des Anwendungsdesigns, indem Geschäftsprozesse, Datenbankobjektdesign, Transaktionsgröße und SQL-Anweisungen angepasst werden, die auf die Datenbank zugreifen. Die meisten Deadlocks können vermieden werden.
Im Folgenden werden anhand von Beispielen einige gängige Methoden zur Vermeidung von Deadlocks vorgestellt.
(1) Wenn in einer Anwendung verschiedene Programme gleichzeitig auf mehrere Tabellen zugreifen, versuchen Sie, den Zugriff auf die Tabellen in derselben Reihenfolge zu vereinbaren, was die Wahrscheinlichkeit eines Deadlocks erheblich verringern kann. Da im folgenden Beispiel die beiden Sitzungen in unterschiedlicher Reihenfolge auf die beiden Tabellen zugreifen, ist die Wahrscheinlichkeit eines Deadlocks sehr hoch! Wenn die Zugriffe jedoch in der gleichen Reihenfolge erfolgen, kann ein Deadlock vermieden werden.
(2) Wenn das Programm Daten stapelweise verarbeitet und die Daten im Voraus sortiert werden, um sicherzustellen, dass jeder Thread Datensätze in einer festen Reihenfolge verarbeitet, kann die Möglichkeit eines Deadlocks ebenfalls erheblich verringert werden.
(3) Wenn Sie in einer Transaktion einen Datensatz aktualisieren möchten, sollten Sie direkt eine Sperre mit ausreichender Stufe beantragen, d. h. eine exklusive Sperre. Sie sollten nicht zuerst eine gemeinsame Sperre beantragen Beantragen Sie beim Aktualisieren eine exklusive Sperre, denn wenn der Benutzer eine exklusive Sperre beantragt Zu diesem Zeitpunkt haben möglicherweise andere Transaktionen die gemeinsame Sperre desselben Datensatzes erhalten, was zu Sperrkonflikten oder sogar Deadlocks führt. Eine konkrete Demonstration finden Sie im Beispiel in Abschnitt 20.3.3.
(4) Wie bereits erwähnt, unter der Isolationsstufe REPEATABLE-READ, wenn zwei Threads SELECT...FOR UPDATE verwenden, um gleichzeitig exklusive Sperren für Datensätze mit derselben Bedingung hinzuzufügen, wenn es keinen Datensatz gibt, der die Bedingung erfüllt , beide Threads fügen Lock erfolgreich hinzu. Das Programm erkennt, dass der Datensatz noch nicht existiert und versucht, einen neuen Datensatz einzufügen. Wenn beide Threads dies tun, kommt es zu einem Deadlock. In diesem Fall kann das Problem durch Ändern der Isolationsstufe auf READ COMMITTED vermieden werden.
(5) Wenn die Isolationsstufe READ COMMITTED ist und beide Threads zuerst SELECT...FOR UPDATE ausführen, stellen Sie fest, ob Datensätze vorhanden sind, die die Bedingungen erfüllen, und fügen Sie die Datensätze ein, wenn nicht. Zu diesem Zeitpunkt kann nur ein Thread erfolgreich eingefügt werden, und der andere Thread wartet auf eine Sperre. Wenn der erste Thread sendet, macht der zweite Thread jedoch einen Fehler erhält ein exklusives Schloss! Wenn zu diesem Zeitpunkt ein dritter Thread eine exklusive Sperre beantragt, kommt es ebenfalls zu einem Deadlock.
In diesem Fall können Sie den Einfügevorgang direkt ausführen und dann die Ausnahme beim Duplikat des Primärschlüssels abfangen oder immer ROLLBACK ausführen, um die erworbene exklusive Sperre aufzuheben, wenn ein Fehler beim Duplikat des Primärschlüssels auftritt.
Obwohl Deadlocks durch das Design und die SQL-Optimierung sowie andere oben eingeführte Maßnahmen erheblich reduziert werden können, ist es schwierig, Deadlocks vollständig zu vermeiden. Daher ist es eine gute Programmiergewohnheit, Deadlock-Ausnahmen beim Programmieren immer abzufangen und zu behandeln.
Wenn ein Deadlock auftritt, können Sie den Befehl SHOW INNODB STATUS verwenden, um die Ursache des letzten Deadlocks zu ermitteln. Die zurückgegebenen Ergebnisse umfassen detaillierte Informationen zu Transaktionen im Zusammenhang mit Deadlocks, z. B. die SQL-Anweisung, die den Deadlock verursacht hat, die Sperren, die die Transaktion erworben hat, auf welche Sperren sie wartet und die Transaktionen, die zurückgesetzt wurden. Darauf aufbauend können die Ursachen für Stillstände analysiert und Verbesserungsmaßnahmen ergriffen werden.
Das Folgende ist eine Beispielausgabe von SHOW INNODB STATUS:
mysql> show innodb status G
InnoDB-Zusammenfassung
Dieses Kapitel konzentriert sich auf die Implementierungsfunktionen von MyISAM-Sperren auf Tabellenebene und InnoDB-Sperren auf Zeilenebene in MySQL diskutiert die beiden Sperrprobleme und Lösungen, auf die Speicher-Engines häufig stoßen.
Für MyISAM-Tabellensperren werden hauptsächlich die folgenden Punkte diskutiert:
(1) Gemeinsam genutzte Lesesperren (S) sind kompatibel, aber zwischen gemeinsam genutzten Lesesperren (S) und exklusiven Schreibsperren (X) und exklusiven Schreibsperren (X ) schließen sich gegenseitig aus, was bedeutet, dass Lesen und Schreiben seriell sind.
(2) Unter bestimmten Bedingungen ermöglicht MyISAM die gleichzeitige Ausführung von Abfragen und Einfügungen. Damit können wir das Sperrkonfliktproblem für Abfragen und Einfügungen in derselben Tabelle in Anwendungen lösen.
(3) Der Standard-Sperrplanungsmechanismus von MyISAM ist die Schreibpriorität, die nicht unbedingt für alle Anwendungen geeignet ist. Benutzer können den Lese-/Schreibsperrenkonflikt anpassen, indem sie den Parameter LOW_PRIORITY_UPDATES festlegen oder die Option LOW_PRIORITY in INSERT, UPDATE und DELETE angeben Aussagen.
(4) Aufgrund der großen Sperrgranularität von Tabellensperren und der seriellen Lese- und Schreibvorgänge kann es bei der MyISAM-Tabelle zu erheblichen Sperrwartezeiten kommen. Sie können die Verwendung von InnoDB-Tabellen in Betracht ziehen, um Sperrkonflikte zu reduzieren.
Für InnoDB-Tabellen werden in diesem Kapitel hauptsächlich die folgenden Inhalte behandelt.
Die Zeilensperre von InnoDB basiert auf dem Sperrindex. Wenn auf die Daten nicht über den Index zugegriffen wird, verwendet InnoDB eine Tabellensperre.
Einführung des InnoDB-Gap-Lock-Mechanismus (Next-Key) und der Gründe, warum InnoDB Gap-Locks verwendet.
Unter verschiedenen Isolationsstufen sind der Sperrmechanismus und die konsistente Lesestrategie von InnoDB unterschiedlich.
MySQL-Wiederherstellung und -Replikation haben auch einen großen Einfluss auf den InnoDB-Sperrmechanismus und die konsistente Lesestrategie.
Sperrkonflikte und sogar Deadlocks lassen sich nur schwer vollständig vermeiden.
Nachdem Benutzer die Sperreigenschaften von InnoDB verstanden haben, können sie Sperrkonflikte und Deadlocks durch Design- und SQL-Anpassungen und andere Maßnahmen reduzieren, darunter:
Verwenden Sie so viel wie möglich eine niedrigere Isolationsstufe;
Entwerfen Sie den Index sorgfältig und versuchen Sie es Verwenden Sie den Index, um auf Daten zuzugreifen. Machen Sie das Sperren präziser und verringern Sie so die Wahrscheinlichkeit von Sperrkonflikten.
Wählen Sie eine angemessene Transaktionsgröße, und die Wahrscheinlichkeit von Sperrkonflikten bei kleinen Transaktionen ist geringer.
Wenn Sie den Datensatz sperren, ist es am besten, auf einmal eine ausreichende Sperrstufe anzufordern. Wenn Sie beispielsweise Daten ändern möchten, ist es am besten, direkt eine exklusive Sperre zu beantragen, anstatt zuerst eine gemeinsame Sperre zu beantragen und dann beim Ändern eine exklusive Sperre anzufordern, was leicht zu einem Deadlock führen kann.
Wenn verschiedene Programme auf eine Gruppe von Tabellen zugreifen, sollten sie versuchen, auf jede Tabelle in der gleichen Reihenfolge zuzugreifen. Versuchen Sie bei einer Tabelle, auf die Zeilen in der Tabelle in einer festen Reihenfolge zuzugreifen. Dadurch wird die Wahrscheinlichkeit eines Deadlocks erheblich verringert.
Versuchen Sie, Gleichheitsbedingungen für den Zugriff auf Daten zu verwenden, um die Auswirkungen von Lückensperren auf das gleichzeitige Einfügen zu vermeiden.
Bewerben Sie sich nicht für eine Sperrstufe, die über den tatsächlichen Bedarf hinausgeht, und zeigen Sie bei der Abfrage keine Sperre an.
Für einige bestimmte Transaktionen können Tabellensperren verwendet werden, um die Verarbeitungsgeschwindigkeit zu erhöhen oder die Möglichkeit eines Deadlocks zu verringern.
