Analyse der Implementierung der MySQL-Anweisungssperre

Analyse der Implementierung der MySQL-Anweisungssperre

Zusammenfassung: Analyse von zwei MySQL SQL-Anweisungssperren

Schauen Sie sich an, welche Sperren den folgenden SQL-Anweisungen hinzugefügt werden

SLQ1: Wählen Sie * aus t1, wobei ID = 10 ist;
SQL2: Löschen aus t1, wo ID = 10;

(1) Ist die ID ein Primärschlüssel?

(2) Wie hoch ist der Isolationsgrad des aktuellen Systems?

(3) Wenn die ID-Spalte nicht der Primärschlüssel ist, gibt es einen Index für die ID-Spalte?

(4) Wenn es einen Sekundärindex für die Spalte „ID“ gibt, handelt es sich bei diesem Index um einen Sekundärindex?

(5) Was sind die Ausführungspläne der beiden SQL-Anweisungen? Indexscan oder vollständiger Tabellenscan

Der eigentliche Ausführungsplan muss auf der Ausgabe von MySQL basieren

Kombination 1: ID-Spalte ist der Primärschlüssel, RC-Isolationsebene Kombination 2: ID-Spalte ist ein sekundärer eindeutiger Index, RC-Isolationsebene Kombination 3: ID-Spalte ist ein sekundärer nicht eindeutiger Index, RC-Isolationsebene Kombination 4: ID-Spalte hat keinen Index, RC-Isolationsebene Kombination 5: ID-Spalte ist der Primärschlüssel, RR-Isolationsebene Kombination 6: ID-Spalte ist ein sekundärer eindeutiger Index, RR-Isolationsebene Kombination 7: ID-Spalte ist ein sekundärer nicht eindeutiger Index, RR-Isolationsebene Kombination 8: Es gibt keinen Index für die ID-Spalte, RR-Isolationsebene

Serialisierbare Isolationsebene

Auf der Isolationsebene RR RC ist SQL1: select nicht gesperrt und es wird Snapshot Read verwendet. Im Folgenden wird nur die Sperrung von SQL2: delete besprochen.
Percona

Kombination 1: ID-Primärschlüssel + RC
Percona

---TRANSAKTION 1286310, AKTIV 9 Sek
2 Sperrstruktur(en), Heap-Größe 360, 1 Zeilensperre(n), Undo-Log-Einträge 1
MySQL-Thread-ID 341, OS-Thread-Handle 0x7f4d540d0700, Abfrage-ID 4510972, Localhost-Root wird bereinigt
TABELLENSPERRE Tabelle `test`.`t1` TRX-ID 1286310 Sperrmodus IX
Datensatzsperren, Speicherplatz-ID 29, Seitennummer 3, n Bits 80, Index „PRIMARY“ der Tabelle „test“. „t1“, TRX-ID 1286310, Sperrmodus X sperrt Datensatz, aber nicht Lücke

MySQL

---TRANSAKTION 5936, AKTIV 171 Sek.
2 Sperrstruktur(en), Heapgröße 360, 1 Zeilensperre(n), Undo-Logeinträge 1
MySQL-Thread-ID 2, OS-Thread-Handle 0x7f5677201700, Abfrage-ID 364, Localhost-Root
TABELLENSPERRE Tabelle `test`.`t1` trx id 5936 Sperrmodus IX
Datensatzsperren, Speicherplatz-ID 6, Seitennummer 3, n Bits 80, Index „PRIMARY“ der Tabelle „test“. „t1“, TRX-ID 5936, Sperrmodus X sperrt Datensatz, aber nicht Lücke
Datensatzsperre, Heap Nr. 5 PHYSIKALISCHER DATENSATZ: n_Felder 4; kompaktes Format; Infobits 32
 0: Länge 4; Hex 8000000a; aufsteigend ;;
 1: Länge 6; Hex 000000001730; aufsteigend 0;;
 2: Länge 7; Hex 26000001550110; aufsteigend & U ;;
 3: Länge 1; Hex 61; aufsteigend a;;

Kombination 2: ID eindeutiger Index + RC
Für die Aktualisierung eines eindeutigen Index sind zwei X-Sperren erforderlich: eine für den eindeutigen Index-ID=10-Datensatz und eine für den gruppierten Index-Namen='d'-Datensatz.
Percona

---TRANSAKTION 1286327, AKTIV 3 Sek.
3 Sperrstruktur(en), Heapgröße 360, 2 Zeilensperre(n), Undo-Logeinträge 1
MySQL-Thread-ID 344, OS-Thread-Handle 0x7f4d5404e700, Abfrage-ID 4510986, Localhost-Root wird bereinigt
TABELLENSPERRE Tabelle `test`.`t2` TRX-ID 1286327 Sperrmodus IX
Datensatzsperren Speicherplatz-ID 30 Seitennummer 4 n Bits 80 Index `id` der Tabelle `test`.`t2` TRX-ID 1286327 Sperrmodus X sperrt Datensatz, aber nicht Lücke
Datensatzsperren, Speicherplatz-ID 30, Seitennummer 3, n Bits 80, Index „PRIMARY“ der Tabelle „test“. „t2“, TRX-ID 1286327, Sperrmodus X sperrt Datensatz, aber nicht Lücke

MySQL

---TRANSAKTION 5938, AKTIV 3 Sek.
3 Sperrstruktur(en), Heap-Größe 360, 2 Zeilensperre(n), Undo-Log-Einträge 1
MySQL-Thread-ID 2, OS-Thread-Handle 0x7f5677201700, Abfrage-ID 374, Localhost-Root
TABELLENSPERRE Tabelle `test`.`t2` trx id 5938 Sperrmodus IX
Datensatzsperren, Speicherplatz-ID 7, Seitennummer 4, n Bits 80, Index „ID“ der Tabelle „test“. „t2“, TRX-ID 5938, Sperrmodus X sperrt Datensatz, aber nicht Lücke
Datensatzsperre, Heap Nr. 7 PHYSIKALISCHER DATENSATZ: n_Felder 2; kompaktes Format; Infobits 32
 0: Länge 4; Hex 8000000a; aufsteigend ;;
 1: Länge 1; Hex 64; aufsteigend d;;

Datensatzsperren, Speicherplatz-ID 7, Seitennummer 3, n Bits 80, Index „PRIMARY“ der Tabelle „test“. „t2“ TRX-ID 5938, Sperrmodus X sperrt Datensatz, aber nicht Lücke
Datensatzsperre, Heap Nr. 7 PHYSIKALISCHER DATENSATZ: n_Felder 4; kompaktes Format; Infobits 32
 0: Länge 1; Hex 64; aufsteigend d;;
 1: Länge 6; Hex 000000001732; aufsteigend 2;;
 2: Länge 7; Hex 27000001560110; aufsteigend ' V ;;
 3: Länge 4; Hex 8000000a; aufsteigend ;;

Kombination 3: ID nicht eindeutiger Index + RC
Wenn die ID-Spalte ein normaler Index ist, werden alle entsprechenden Datensätze, die die SQL-Abfragebedingungen erfüllen, gesperrt. Gleichzeitig werden auch die Datensätze im Primärschlüsselindex gesperrt.
Percona

---TRANSAKTION 1286339, AKTIV 9 Sek
3 Sperrstruktur(en), Heapgröße 360, 4 Zeilensperre(n), Undo-Logeinträge 2
MySQL-Thread-ID 347, OS-Thread-Handle 0x7f4b67fff700, Abfrage-ID 4511015, Localhost-Root wird bereinigt
TABELLENSPERRE Tabelle `test`.`t3` TRX-ID 1286339 Sperrmodus IX
Datensatzsperren, Speicherplatz-ID 31, Seitennummer 4, n Bits 80, Index „idx_key“ der Tabelle „test“. „t3“, TRX-ID 1286339, Sperrmodus X sperrt Datensatz, aber nicht Lücke
Datensatzsperren, Speicherplatz-ID 31, Seitennummer 3, n Bits 80, Index „PRIMARY“ der Tabelle „test“. „t3“, TRX-ID 1286339, Sperrmodus X sperrt Datensatz, aber nicht Lücke

MySQL

---TRANSAKTION 5940, AKTIV 3 Sek.
3 Sperrstruktur(en), Heapgröße 360, 4 Zeilensperre(n), Undo-Logeinträge 2
MySQL-Thread-ID 2, OS-Thread-Handle 0x7f5677201700, Abfrage-ID 378, Localhost-Root
TABELLENSPERRE Tabelle `test`.`t3` TRX-ID 5940 Sperrmodus IX
Datensatzsperren, Speicherplatz-ID 8, Seitennummer 4, n Bits 80, Index „idx_key“ der Tabelle „test“. „t3“, TRX-ID 5940, Sperrmodus X sperrt Datensatz, aber nicht Lücke
Datensatzsperre, Heap Nr. 4 PHYSIKALISCHER DATENSATZ: n_Felder 2; kompaktes Format; Infobits 32
 0: Länge 4; Hex 8000000a; aufsteigend ;;
 1: Länge 1; Hex 62; aufsteigend b;;

Datensatzsperre, Heap Nr. 5 PHYSIKALISCHER DATENSATZ: n_Felder 2; kompaktes Format; Infobits 32
 0: Länge 4; Hex 8000000a; aufsteigend ;;
 1: Länge 1; Hex 64; aufsteigend d;;

Datensatzsperren, Speicherplatz-ID 8, Seitennummer 3, n Bits 80, Index „PRIMARY“ der Tabelle „test“. „t3“, TRX-ID 5940, Sperrmodus X sperrt Datensatz, aber nicht Lücke
Datensatzsperre, Heap Nr. 4 PHYSIKALISCHER DATENSATZ: n_Felder 4; kompaktes Format; Infobits 32
 0: Länge 1; Hex 62; aufsteigend b;;
 1: Länge 6; Hex 000000001734; aufsteigend 4;;
 2: Länge 7; Hex 28000001570110; aufsteigend ( W ;;
 3: Länge 4; Hex 8000000a; aufsteigend ;;

Datensatzsperre, Heap Nr. 5 PHYSIKALISCHER DATENSATZ: n_Felder 4; kompaktes Format; Infobits 32
 0: Länge 1; Hex 64; aufsteigend d;;
 1: Länge 6; Hex 000000001734; aufsteigend 4;;
 2: Länge 7; Hex 28000001570132; aufsteigend ( W 2;;
 3: Länge 4; Hex 8000000a; aufsteigend ;;

Kombination 4: Kein ID-Index + RC
Percona

---TRANSAKTION 1286373, AKTIV 5 Sek.
2 Sperrstruktur(en), Heapgröße 360, 2 Zeilensperre(n), Undo-Logeinträge 2
MySQL-Thread-ID 348, OS-Thread-Handle 0x7f4d54193700, Abfrage-ID 4511037, Localhost-Root wird bereinigt
TABELLENSPERRE Tabelle `test`.`t4` TRX-ID 1286373 Sperrmodus IX
Datensatzsperren, Speicherplatz-ID 33, Seitennummer 3, n Bits 80, Index „PRIMARY“ der Tabelle „test“. „t4“, TRX-ID 1286373, Sperrmodus X sperrt Datensatz, aber nicht Lücke

MySQL

---TRANSAKTION 5946, AKTIV 2 Sek.
2 Sperrstruktur(en), Heapgröße 360, 2 Zeilensperre(n), Undo-Logeinträge 2
MySQL-Thread-ID 2, OS-Thread-Handle 0x7f5677201700, Abfrage-ID 382, ​​Localhost-Root
TABELLENSPERRE Tabelle `test`.`t4` trx id 5946 Sperrmodus IX
Datensatzsperren, Speicherplatz-ID 9, Seitennummer 3, n Bits 80, Index „PRIMARY“ der Tabelle „test“. „t4“ TRX-ID 5946, Sperrmodus X sperrt Datensatz, aber nicht Lücke
Datensatzsperre, Heap Nr. 3 PHYSIKALISCHER DATENSATZ: n_Felder 4; kompaktes Format; Infobits 32
 0: Länge 1; Hex 62; aufsteigend b;;
 1: Länge 6; Hex 00000000173a; aufsteigend :;;
 2: Länge 7; Hex 2b0000015a0110; asc + Z ;;
 3: Länge 4; Hex 8000000a; aufsteigend ;;

Datensatzsperre, Heap Nr. 5 PHYSIKALISCHER DATENSATZ: n_Felder 4; kompaktes Format; Infobits 32
 0: Länge 1; Hex 64; aufsteigend d;;
 1: Länge 6; Hex 00000000173a; aufsteigend :;;
 2: Länge 7; Hex 2b0000015a012c; Asc + Z,;;
 3: Länge 4; Hex 8000000a; aufsteigend ;;

Kombination 5: ID-Primärschlüssel + RR
Referenzkombination 1

Kombination 6: ID eindeutiger Index + RR
Referenzkombination 2

Kombination 7: ID nicht eindeutiger Index + RR
Percona

---TRANSAKTION 1592633, AKTIV 24 Sek.
4 Sperrstruktur(en), Heap-Größe 1184, 5 Zeilensperre(n), Undo-Log-Einträge 2
MySQL-Thread-ID 794, OS-Thread-Handle 0x7f4d5404e700, Abfrage-ID 7801799, Localhost-Root wird bereinigt
Trx-Lese-Ansicht sieht keine TRX mit ID >= 1592634, sieht < 1592634
TABELLENSPERRE Tabelle `test`.`t3` TRX-ID 1592633 Sperrmodus IX
Datensatzsperren, Bereichs-ID 31, Seitennummer 4, n Bits 80, Index „idx_key“ der Tabelle „test“. „t3“, TRX-ID 1592633, Sperrmodus X
Datensatzsperren, Speicherplatz-ID 31, Seitennummer 3, n Bits 80, Index „PRIMARY“ der Tabelle „test“. „t3“, TRX-ID 1592633, Sperrmodus X sperrt Datensatz, aber nicht Lücke
RECORD LOCKS Bereichs-ID 31 Seitennummer 4 n Bits 80 Index `idx_key` der Tabelle `test`.`t3` trx ID 1592633 lock_mode X sperrt Lücke vor rec

MySQL

---TRANSAKTION 5985, AKTIV 7 Sek.
4 Sperrstruktur(en), Heap-Größe 1184, 5 Zeilensperre(n), Undo-Log-Einträge 2
MySQL-Thread-ID 12, OS-Thread-Handle 0x7f56770fd700, Abfrage-ID 500, Localhost-Root
TABELLENSPERRE Tabelle `test`.`t3` trx id 5985 Sperrmodus IX
Datensatzsperren, Bereichs-ID 8, Seitennummer 4, n Bits 80, Index „idx_key“ der Tabelle „test“. „t3“, TRX-ID 5985, Sperrmodus X
Datensatzsperre, Heap Nr. 4 PHYSIKALISCHER DATENSATZ: n_Felder 2; kompaktes Format; Infobits 32
 0: Länge 4; Hex 8000000a; aufsteigend ;;
 1: Länge 1; Hex 64; aufsteigend d;;

Datensatzsperre, Heap Nr. 5 PHYSIKALISCHER DATENSATZ: n_Felder 2; kompaktes Format; Infobits 32
 0: Länge 4; Hex 8000000a; aufsteigend ;;
 1: Länge 1; Hex 62; aufsteigend b;;

Datensatzsperren, Speicherplatz-ID 8, Seitennummer 3, n Bits 80, Index „PRIMARY“ der Tabelle „test“. „t3“, TRX-ID 5985, Sperrmodus X sperrt Datensatz, aber nicht Lücke
Datensatzsperre, Heap Nr. 4 PHYSIKALISCHER DATENSATZ: n_Felder 4; kompaktes Format; Infobits 32
 0: Länge 1; Hex 64; aufsteigend d;;
 1: Länge 6; Hex 000000001761; aufsteigend a;;
 2: Länge 7; Hex 3f0000016d0132; aufsteigend ? m 2;;
 3: Länge 4; Hex 8000000a; aufsteigend ;;

Datensatzsperre, Heap Nr. 5 PHYSIKALISCHER DATENSATZ: n_Felder 4; kompaktes Format; Infobits 32
 0: Länge 1; Hex 62; aufsteigend b;;
 1: Länge 6; Hex 000000001761; aufsteigend a;;
 2: Länge 7; Hex 3f0000016d0110; aufsteigend? m ;;
 3: Länge 4; Hex 8000000a; aufsteigend ;;

RECORD LOCKS Bereichs-ID 8 Seitennummer 4 n Bits 80 Index `idx_key` der Tabelle `test`.`t3` trx ID 5985 lock_mode X sperrt Lücke vor rec
Datensatzsperre, Heap Nr. 8 PHYSIKALISCHER DATENSATZ: n_Felder 2; kompaktes Format; Infobits 0
 0: Länge 4; Hex 8000000b; aufsteigend ;;
 1: Länge 1; Hex 66; aufsteigend f;;

Kombination 8: ID ohne Index + RR
Percona

---TRANSAKTION 1592639, AKTIV 4 Sek.
2 Sperrstruktur(en), Heapgröße 360, 7 Zeilensperre(n), Undo-Logeinträge 2
MySQL-Thread-ID 794, OS-Thread-Handle 0x7f4d5404e700, Abfrage-ID 7801804, Localhost-Root wird bereinigt
TABELLENSPERRE Tabelle `test`.`t4` TRX-ID 1592639 Sperrmodus IX
Datensatzsperren, Bereichs-ID 33, Seitennummer 3, n Bits 80, Index „PRIMARY“ der Tabelle „test“. „t4“, TRX-ID 1592639, Sperrmodus X

MySQL

---TRANSAKTION 6000, AKTIV 3 Sek.
2 Sperrstruktur(en), Heapgröße 360, 7 Zeilensperre(n), Undo-Logeinträge 2
MySQL-Thread-ID 12, OS-Thread-Handle 0x7f56770fd700, Abfrage-ID 546, Localhost-Root
TABELLENSPERRE Tabelle `test`.`t4` trx id 6000 Sperrmodus IX
Datensatzsperren, Bereichs-ID 9, Seitennummer 3, n Bits 80, Index „PRIMARY“ der Tabelle „test“. „t4“, TRX-ID 6000, Sperrmodus X
Datensatzsperre, Heap Nr. 1 PHYSIKALISCHER DATENSATZ: n_Felder 1; kompaktes Format; Infobits 0
 0: Länge 8; Hex 73757072656d756d; Asc Supremum;;

Datensatzsperre, Heap Nr. 2 PHYSIKALISCHER DATENSATZ: n_Felder 4; kompaktes Format; Infobits 0
 0: Länge 1; Hex 61; aufsteigend a;;
 1: Länge 6; Hex 000000001722; aufsteigend ";;
 2: Länge 7; Hex 9e0000014e0110; aufsteigend N ;;
 3: Länge 4; Hex 8000000f; aufsteigend ;;

Datensatzsperre, Heap Nr. 3 PHYSIKALISCHER DATENSATZ: n_Felder 4; kompaktes Format; Infobits 32
 0: Länge 1; Hex 62; aufsteigend b;;
 1: Länge 6; Hex 000000001770; aufsteigend p;;
 2: Länge 7; Hex 47000001730110; aufsteigend G s ;;
 3: Länge 4; Hex 8000000a; aufsteigend ;;

Datensatzsperre, Heap Nr. 4 PHYSIKALISCHER DATENSATZ: n_Felder 4; kompaktes Format; Infobits 0
 0: Länge 1; Hex 63; aufsteigend c;;
 1: Länge 6; Hex 000000001722; aufsteigend ";;
 2: Länge 7; Hex 9e0000014e0122; aufsteigend N ";;
 3: Länge 4; Hex 80000006; aufsteigend ;;

Datensatzsperre, Heap Nr. 5 PHYSIKALISCHER DATENSATZ: n_Felder 4; kompaktes Format; Infobits 32
 0: Länge 1; Hex 64; aufsteigend d;;
 1: Länge 6; Hex 000000001770; aufsteigend p;;
 2: Länge 7; Hex 4700000173012c; aufsteigend G s ,;;
 3: Länge 4; Hex 8000000a; aufsteigend ;;

Datensatzsperre, Heap Nr. 6 PHYSIKALISCHER DATENSATZ: n_Felder 4; kompaktes Format; Infobits 0
 0: Länge 1; Hex 66; aufsteigend f;;
 1: Länge 6; Hex 000000001722; aufsteigend ";;
 2: Länge 7; Hex 9e0000014e0134; aufsteigend N 4;;
 3: Länge 4; Hex 8000000b; aufsteigend ;;

Datensatzsperre, Heap Nr. 7 PHYSIKALISCHER DATENSATZ: n_Felder 4; kompaktes Format; Infobits 0
 0: Länge 2; hex 7a7a; asc zz;;
 1: Länge 6; Hex 000000001722; aufsteigend ";;
 2: Länge 7; Hex 9e0000014e013d; aufsteigend N =;;
 3: Länge 4; Hex 80000002; aufsteigend ;;

Kombination 9: Serialisierbar

Für das oben erwähnte einfache SQL, der letzte Fall: Serialisierbare Isolationsebene. Für SQL2: „delete from t1 where id = 10;“ ist die serialisierbare Isolationsebene genau die gleiche wie die wiederholbare Lese-Isolationsebene und wird deshalb hier nicht eingeführt.

Die serialisierbare Isolationsebene betrifft SQL1: select * from t1 where id = 10; Diese SQL-Anweisung wird als Snapshot gelesen und ist auf den Isolationsebenen RC und RR nicht gesperrt. Auf der serialisierbaren Isolationsebene fügt SQL1 jedoch eine Lesesperre hinzu, was bedeutet, dass keine Snapshot-Lesevorgänge mehr möglich sind und die MVCC-Parallelitätssteuerung auf Lock-Based CC herabgestuft wird.

Fazit: In MySQL/InnoDB ist das sogenannte Lesen ohne Sperren nicht auf alle Situationen anwendbar, sondern hängt mit der Isolationsebene zusammen. Auf der Isolationsebene „Serialisierbar“ ist das Lesen ohne Sperren nicht mehr gültig und alle Lesevorgänge sind aktuelle Lesevorgänge.

Das könnte Sie auch interessieren:
  • Ein Artikel zum Verständnis des MySQL-Sperrmechanismus

<<:  So konfigurieren Sie den NAT-Modus für virtuelle VMware-Maschinen

>>:  js implementiert dynamisch Operationen zum Hinzufügen und Löschen von Tabellen

Artikel empfehlen

Allgemeine Betriebsbefehle von MySQL im Linux-System

Aufschlag: # chkconfig --list Alle Systemdienste ...

Einfacher CSS-Textanimationseffekt

Ergebnisse erzielen Implementierungscode html <...

Vue implementiert rekursiv ein dreistufiges Menü

In diesem Artikelbeispiel wird der spezifische Co...

18 Web-Usability-Prinzipien, die Sie kennen müssen

Sie können über die besten visuellen Designfähigk...

HTML + CSS realisiert das Scrollen zur Elementposition, um den Ladeanimationseffekt anzuzeigen

Wie füge ich jedes Mal eine Ladeanimation hinzu, ...

Können Sie alle Testfragen zum Erstellen der Webseite beantworten?

Fragen zum Webdesign. Können Sie alle beantworten...

Detaillierte Analyse und Verwendung des Befehls tcpdump unter Linux

Einführung Einfach ausgedrückt ist tcpdump ein Pa...

Tiefgreifendes Verständnis des Linux-Lastausgleichs LVS

Inhaltsverzeichnis 1. LVS-Lastausgleich 2. Grundl...

CSS-Techniken zum Erreichen eines mehrspaltigen Layouts gleicher Höhe, das das Frontend beherrschen sollte

1. Einleitung Beim Schreiben einer Seite stoßen w...

Eine kurze Erläuterung der Rolle des Parameters docker --privileged=true

Mit etwa Version 0.6 wurde Privileged in Docker e...

JavaScript-Funktion kapselt zufälligen Farbüberprüfungscode (vollständiger Code)

Ein n-stelliger Bestätigungscode, der aus Zahlen,...

Detaillierte Erklärung zur Verwendung der Vue-Komponente zur Datums- und Uhrzeitauswahl

In diesem Artikelbeispiel wird der spezifische Co...

Liste häufig verwendeter Escape-Codes für HTML: Größer als, Kleiner als, Leerzeichen, Anführungszeichen usw.

Die Tabelle sieht wie folgt aus: HTML-Quellcode E...

Verwendung der MySQL-Anweisung „truncate table“

Mit der Anweisung „Truncate table“ werden alle Da...

So ermitteln Sie die Ausführungszeit eines Befehls oder Prozesses in Linux

Auf Unix-ähnlichen Systemen wissen Sie möglicherw...