Grundlegendes zu MySQL-Sperren basierend auf Update-SQL-Anweisungen

Grundlegendes zu MySQL-Sperren basierend auf Update-SQL-Anweisungen

Vorwort

Die MySQL-Datenbanksperre ist ein wichtiges Mittel, um Datenkonsistenz zu erreichen und Parallelitätsprobleme zu lösen. Die Datenbank ist eine Ressource, die von mehreren Benutzern gemeinsam genutzt wird. Bei gleichzeitiger Verwendung treten alle möglichen seltsamen Probleme auf. Genau wie bei Programmcode treten bei gleichzeitiger Verwendung mehrerer Threads unerwartete Dinge auf, wenn keine spezielle Kontrolle durchgeführt wird, z. B. „schmutzige“ Daten, Änderungsverlust und andere Probleme. Daher muss die Datenbank-Parallelität mithilfe von Transaktionen gesteuert werden, und Probleme mit der Transaktions-Parallelität müssen mithilfe von Datenbanksperren gesteuert werden. Datenbanksperren stehen also mit der Parallelitätssteuerung und Transaktionen in Zusammenhang.

In diesem Artikel wird hauptsächlich das Verständnis von MySQL-Sperren basierend auf SQL-Update-Anweisungen beschrieben. Werfen wir einen Blick auf die ausführliche Einführung.

1. Tektonische Umgebung

(root@localhost) [Benutzer]> Variablen wie „Version“ anzeigen;
+---------------+------------+
| Variablenname | Wert |
+---------------+------------+
| Version | 5.7.23-log |
+---------------+------------+

(root@localhost) [Benutzer]> desc t1;
+-------------+--------------+------+-----+---------+----------------+
| Feld | Typ | Null | Schlüssel | Standard | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NEIN | PRI | NULL | auto_increment |
| n | int(11) | JA | | NULL | |
| Tabellenname | varchar(64) | JA | | NULL | |
| Spaltenname | varchar(64) | JA | | NULL | |
| pad | varchar(100) | JA | | NULL | |
+-------------+--------------+------+-----+---------+----------------+

(root@localhost) [Benutzer]> select count(*) from t1;
+----------+
| Anzahl(*) |
+----------+
| 3406 |
+----------+

(root@localhost) [Benutzer]> eindeutigen Index idx_t1_pad auf t1(pad) erstellen;
Abfrage OK, 0 Zeilen betroffen (0,35 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

(root@localhost) [Benutzer]> Index idx_t1_n auf t1(n) erstellen;
Abfrage OK, 0 Zeilen betroffen (0,03 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0
(root@localhost) [Benutzer]> Index von t1 anzeigen;
+-------+------------+------------+-----------+---------------+---------------+------------+------+------------+
| Tabelle | Nicht_eindeutig | Schlüsselname | Sequenz_im_Index | Spaltenname | Sortierung | Kardinalität | Null | Indextyp |
+-------+------------+------------+-----------+---------------+---------------+------------+------+------------+
| t1 | 0 | PRIMARY | 1 | Ich d | A | 3462 | | BTREE |
| t1 | 0 | idx_t1_pad | 1 | pad | A | 3406 | JA | BTREE |
| t1 | 1 | idx_t1_n | 1 | n | A | 12 | JA | BTREE |
+-------+------------+------------+-----------+---------------+---------------+------------+------+------------+
Wählen Sie den Autor „Leshami“, „http://blog.csdn.net/leshami“ Blog;
+---------+-------------------+
| Autor | Blog |
+---------+-------------------+
| Leshami | http://blog.csdn.net/leshami |
+---------+------------------+

2. Update basierend auf dem Primärschlüssel

(root@localhost) [Benutzer]> Transaktion starten;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

(root@localhost) [Benutzer]> update t1 set table_name='t1' where id=1299;
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
Übereinstimmende Zeilen: 1 Geändert: 1 Warnungen: 0

Wählen Sie trx_id aus,
 trx_state,
 trx_started,
 trx_mysql_thread_id,
 trx_tables_locked,
 trx_rows_locked,
 trx_rows_modifiziert,
 TRX-Isolationsstufe
VON INFORMATION_SCHEMA.INNODB_TRX \G

-- Aus dem folgenden Ergebnis können wir ersehen, dass trx_rows_locked, eine Zeile gesperrt ist ***************************** 1. Zeile ***************************
 trx_id: 6349647
 trx_state: LÄUFT
 trx_started: 2018-11-06 16:54:12
trx_mysql_thread_id: 2
 trx_tables_locked: 1
 trx_rows_locked: 1
 trx_rows_modified: 1
trx_isolation_level: WIEDERHOLBARES LESEN 

(root@localhost) [Benutzer]> Rollback;
Abfrage OK, 0 Zeilen betroffen (0,01 Sek.)

3. Basierend auf dem sekundären eindeutigen Index

(root@localhost) [Benutzer]> Transaktion starten;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

(root@localhost) [Benutzer]> update t1 set table_name='t2' where pad='4f39e2a03df3ab94b9f6a48c4aecdc0b';
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
Übereinstimmende Zeilen: 1 Geändert: 1 Warnungen: 0

Wählen Sie trx_id aus,
 trx_state,
 trx_started,
 trx_mysql_thread_id,
 trx_tables_locked,
 trx_rows_locked,
 trx_rows_modifiziert,
 TRX-Isolationsstufe
VON INFORMATION_SCHEMA.INNODB_TRX \G

-- Aus den Abfrageergebnissen unten können wir ersehen, dass trx_rows_locked, 2 Zeilen gesperrt sind ***************************** 1. Zeile ***************************
 trx_id: 6349649
 trx_state: LÄUFT
 trx_started: 2018-11-06 16:55:22
trx_mysql_thread_id: 2
 trx_tables_locked: 1
 trx_rows_locked: 2
 trx_rows_modified: 1
trx_isolation_level: WIEDERHOLBARES LESEN 

(root@localhost) [Benutzer]> Rollback;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

3. Basierend auf einem sekundären nicht eindeutigen Index

(root@localhost) [Benutzer]> Transaktion starten;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

(root@localhost) [Benutzer]> update t1 set table_name='t3' where n=8;
Abfrage OK, 350 Zeilen betroffen (0,01 Sek.)
Übereinstimmende Zeilen: 351 Geändert: 351 Warnungen: 0

Wählen Sie trx_id aus,
 trx_state,
 trx_started,
 trx_mysql_thread_id,
 trx_tables_locked,
 trx_rows_locked,
 trx_rows_modifiziert,
 TRX-Isolationsstufe
VON INFORMATION_SCHEMA.INNODB_TRX \G
 
--Aus den folgenden Abfrageergebnissen können wir ersehen, dass 703 Zeilen gesperrt sind. ****************************** 1. Zeile ***************************
  trx_id: 6349672
  trx_state: LÄUFT
 trx_started: 2018-11-06 17:06:53
trx_mysql_thread_id: 2
 trx_tables_locked: 1
 trx_rows_locked: 703
 trx_rows_modified: 351
trx_isolation_level: WIEDERHOLBARES LESEN

(root@localhost) [Benutzer]> Rollback;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

4. Update ohne Index

(root@localhost) [Benutzer]> Transaktion starten;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

(root@localhost) [Benutzer]> aktualisiere t1, setze Tabellenname='t4', wobei Spaltenname='id' ist;
Abfrage OK, 26 Zeilen betroffen (0,00 Sek.)
Übereinstimmende Zeilen: 26 Geändert: 26 Warnungen: 0

Wählen Sie trx_id aus,
 trx_state,
 trx_started,
 trx_mysql_thread_id,
 trx_tables_locked,
 trx_rows_locked,
 trx_rows_modifiziert,
 TRX-Isolationsstufe
VON INFORMATION_SCHEMA.INNODB_TRX \G

-- Aus den Abfrageergebnissen unten können wir ersehen, dass trx_rows_locked, 3429 Zeilen gesperrt sind und nur 26 Zeilen aktualisiert werden -- und dieses Ergebnis übersteigt die Gesamtzahl der Zeilen in der Tabelle 3406
*************************** 1. Reihe ***************************
  trx_id: 6349674
  trx_state: LÄUFT
 trx_started: 2018-11-06 17:09:41
trx_mysql_thread_id: 2
 trx_tables_locked: 1
 trx_rows_locked: 3429
 trx_rows_modified: 26
trx_isolation_level: WIEDERHOLBARES LESEN

(root@localhost) [Benutzer]> Rollback;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

-- Sie können es auch über „show engine innodb status show engine innodb status\G“ beobachten.

------------
TRANSAKTIONEN
------------
Trx-ID-Zähler 6349584
Bereinigung für TRXs durchgeführt, Nr. < 0, Rückgängigmachen, Nr. < 0, Status: läuft, aber im Leerlauf
Länge der Verlaufsliste: 0
LISTE DER TRANSAKTIONEN FÜR JEDE SITZUNG:
---TRANSAKTION 421943222819552, nicht gestartet
0 Sperrstruktur(en), Heap-Größe 1136, 0 Zeilensperre(n)
---TRANSAKTION 6349583, AKTIV 2 Sek.
2 Sperrstruktur(en), Heapgröße 1136, 1 Zeilensperre(n), Undo-Logeinträge 1


------------
TRANSAKTIONEN
------------
Trx-ID-Zähler 6349586
Bereinigung für TRXs Nr. 0 < 6349585 durchgeführt, Rückgängigmachen Nr. 0 < 0 Status: läuft, aber im Leerlauf
Länge der Verlaufsliste: 1
LISTE DER TRANSAKTIONEN FÜR JEDE SITZUNG:
---TRANSAKTION 421943222819552, nicht gestartet
0 Sperrstruktur(en), Heap-Größe 1136, 0 Zeilensperre(n)
---TRANSAKTION 6349585, AKTIV 8 Sek.
3 Sperrstruktur(en), Heapgröße 1136, 2 Zeilensperre(n), Undo-Logeinträge 1
MySQL-Thread-ID 2, OS-Thread-Handle 140467640694528, Abfrage-ID 29, Localhost-Root

5. Sperrbezogene Abfrage-SQL

1: Aktuelle Transaktionen anzeigen

Wählen Sie * aus Information_Schema.InnoDB_TRX;

2: Aktuell gesperrte Transaktionen anzeigen

Wählen Sie * aus Information_Schema.InnoDB_Locks;

3: Zeigen Sie die aktuelle Transaktion an, die auf eine Sperre wartet

Wählen Sie * aus Information_Schema.InnoDB_Lock_Waits;

Wählen Sie trx_id aus,
 trx_state,
 trx_started,
 trx_mysql_thread_id thr_id,
 trx_tables_locked tb_lck,
 trx_rows_locked rows_lck,
 trx_rows_modified row_mfy,
 trx_isolation_level ist_lvl
VON INFORMATION_SCHEMA.INNODB_TRX;

Wählen Sie r. `trx_id` wartende_trx_id,
 r.`trx_mysql_thread_id` Wartethread,
 r.`trx_query` wartende_Abfrage,
 b.`trx_id` bolcking_trx_id,
 b.`trx_mysql_thread_id` blockierender Thread,
 b.`trx_query` block_query
VON information_schema.`INNODB_LOCK_WAITS` w
 INNER JOIN information_schema.`INNODB_TRX` b
 EIN b. `trx_id` = w. `blocking_trx_id`
 INNER JOIN information_schema.`INNODB_TRX` r
 EIN r. „trx_id“ = w. „anfordernde_trx_id“;

VI. Zusammenfassung

1. Wenn die MySQL-Tabelle aktualisiert wird, wird die Datensatzsperre während der Aktualisierung gemäß der Where-Prädikatbedingung bestimmt.

2. Da beim Filtern von gruppierten Indizes der Index die Daten sind, wird nur die Aktualisierungszeile gesperrt, die durch die Art des gruppierten Indexes bestimmt wird.

3. Da bei der nicht gruppierten eindeutigen Indexfilterung eine Rückkehr zur Tabelle erforderlich ist, wird die Anzahl der durch den eindeutigen Index gefilterten Zeilen plus die Anzahl der an die Tabelle zurückgegebenen Zeilen gesperrt.

4. Bei der nicht gruppierten, nicht eindeutigen Indexfilterung sind Lückensperren beteiligt, sodass mehr Datensätze gesperrt werden

5. Wenn die Filterbedingung keinen Index hat oder den Index nicht verwenden kann, werden alle Datenzeilen in der gesamten Tabelle gesperrt.

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.

Das könnte Sie auch interessieren:
  • Zusammenfassung der MySQL-Cross-Table-Update-SQL-Anweisung für Multi-Table-Update
  • Tutorial zum Anzeigen von Ausführungsanweisungen und Aktualisierungsprotokollen in Echtzeit über MySQL-Protokolle
  • Verwendung der SQL UPDATE-Aktualisierungsanweisung (einzelne und mehrere Spalten)
  • Java Reflection JavaBean-Objekte generieren automatisch Einfüge-, Aktualisierungs-, Lösch- und Abfrage-SQL-Anweisungsvorgänge
  • Notieren Sie eine Falle bei der Aktualisierung der MySQL-Update-Anweisung
  • Implementierung des Update-Sets aus der Verwendung in der SQL-Update-Anweisung
  • Analyse des Ausführungsprozesses einer SQL-Update-Anweisung

<<:  Detaillierte Erläuterung des Prozesses, mit dem Nginx die echte Quell-IP erhält, nachdem mehrere Proxy-Ebenen durchlaufen wurden

>>:  jQuery implementiert die Drop-Down-Box zur Auswahl des Wohnortes

Artikel empfehlen

Verwenden von js zum Implementieren eines Zahlenratespiels

Letzte Woche gab mir der Lehrer eine kleine Hausa...

80 Zeilen Code zum Schreiben eines Webpack-Plugins und Veröffentlichen auf npm

1. Einleitung Ich habe vor Kurzem die Prinzipien ...

Nach dem Absenden des HTML-Dropdown-Menüs bleibt der ausgewählte Wert erhalten, anstatt auf den Standardwert zurückzusetzen

Code kopieren Der Code lautet wie folgt: <html...

So zeigen Sie im Hintergrund laufende Programme in Linux an und schließen sie

1. Führen Sie die .sh-Datei aus Sie können es dir...

Die Komponente vue-cropper realisiert das Zuschneiden und Hochladen von Bildern

In diesem Artikel wird der spezifische Code der V...

Detailliertes Installationstutorial zur dekomprimierten Version von mysql5.7.19 (mit rein geknackter chinesischer Version von SQLYog)

Die Version Mysql5.7.19 ist eine neue Version, di...

Design: Ein eigenwilliger Designer

<br />In meiner jahrelangen professionellen ...

So migrieren Sie den MySQL-Speicherort auf eine neue Festplatte

1. Bereiten Sie eine neue Festplatte vor und form...

Beispielcode für die Verwendung von CSS zum Schreiben eines strukturierten Hintergrundbilds mit Farbverlauf

Die Seitenlänge im Projekt beträgt etwa 2000 Pixe...

Eine detaillierte Einführung in die Linux-Systemkonfiguration (Dienststeuerung)

Inhaltsverzeichnis Vorwort 1. Systemdienststeueru...

Miniprogramm zur Implementierung der Rechnerfunktion

In diesem Artikelbeispiel wird der spezifische Co...

Zwei Implementierungscodes der programmgesteuerten Navigation mit Vue-Router

Zwei Möglichkeiten zum Navigieren auf der Seite D...

Schritte zum Übertragen von Dateien und Ordnern zwischen zwei Linux-Servern

Heute habe ich mich mit der Migration eines Proje...

CSS3 realisiert eine springende Ballanimation

Normalerweise besuche ich gerne die Sonderseiten ...

Beispiel für die Anzeige von Bildjalousien mit reinem CSS

Lassen Sie mich Ihnen zunächst den fertigen Effek...