Detaillierte Einführung in den Sperrmechanismus in der MySQL-Datenbank

Dieser Artikel bietet Ihnen eine detaillierte Einführung in den Sperrmechanismus in der MySQL-Datenbank. Ich hoffe, dass er für Freunde hilfreich ist.

Pessimistische Sperre und optimistische Sperre:
Pessimistische Sperre: Wie der Name schon sagt, ist sie sehr pessimistisch. Jedes Mal, wenn Sie die Daten erhalten, denken Sie, dass andere sie ändern werden, also werden Sie sie jedes Mal sperren Sie erhalten die Daten auf diese Weise. Wenn andere diese Daten erhalten möchten, werden sie blockiert, bis sie die Sperre erhalten. In herkömmlichen relationalen Datenbanken werden viele solcher Sperrmechanismen verwendet, z. B. Zeilensperren, Tabellensperren, Lesesperren, Schreibsperren usw., die alle vor Operationen gesperrt werden.

Optimistische Sperre: Wie der Name schon sagt, ist es sehr optimistisch. Jedes Mal, wenn Sie die Daten abrufen, denken Sie, dass andere sie nicht ändern werden, also werden Sie sie nicht sperren, aber wenn Sie Wenn Sie es aktualisieren, werden Sie davon ausgehen, dass andere es in diesem Zeitraum nicht ändern. Haben Sie diese Daten aktualisiert? Sie können Mechanismen wie Versionsnummern verwenden. Optimistisches Sperren eignet sich für Multi-Read-Anwendungstypen, die den Durchsatz verbessern können. Wenn eine Datenbank einen ähnlichen Mechanismus wie write_condition bereitstellt, bietet sie tatsächlich optimistisches Sperren.

Tabellenebene: Die Engine MyISAM sperrt direkt die gesamte Tabelle. Während Ihres Sperrzeitraums können andere Prozesse nicht in die Tabelle schreiben. Wenn Sie eine Schreibsperre haben, dürfen andere Prozesse nicht lesen

Seitenebene: Engine BDB, Sperren auf Tabellenebene sind schnell, weisen aber viele Konflikte auf, Sperren auf Zeilenebene weisen wenige Konflikte auf, sind aber langsam. Daher wurde auf Seitenebene ein Kompromiss geschlossen, bei dem jeweils eine Gruppe benachbarter Datensätze gesperrt wird.

Zeilenebene: Engine INNODB sperrt nur bestimmte Datensätze, sodass andere Prozesse weiterhin dieselbe Tabelle sperren können andere Datensätze bearbeiten.

Die Eigenschaften der oben genannten drei Sperren lassen sich grob wie folgt zusammenfassen:
1) Sperre auf Tabellenebene: geringer Overhead, schnelle Sperre; es tritt kein Deadlock auf höchste Wahrscheinlichkeit und niedrigste Parallelität.
2) 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.
3) Sperren auf Zeilenebene: Hoher Overhead, langsame Sperren; die Sperrgranularität ist am geringsten, die Wahrscheinlichkeit von Sperrenkonflikten ist auch am höchsten.

Die drei Arten von Sperren haben jeweils ihre eigenen Eigenschaften. Aus Sperrsicht eignen sich Sperren auf Tabellenebene besser für Anwendungen, die sich auf Abfragen konzentrieren und Daten nur basierend auf Indexbedingungen aktualisieren, wie z. B. WEB Anwendung: Sperren auf Zeilenebene eignen sich besser für Anwendungen, bei denen eine große Anzahl unterschiedlicher Daten entsprechend den Indexbedingungen gleichzeitig aktualisiert und gleichzeitige Abfragen durchgeführt werden, wie z. B. einige OLTP-Systeme (Online Transaction Processing).

MySQL-Sperren auf Tabellenebene haben zwei Modi:
1. Gemeinsame Lesesperre für Tabellen (Table Read Lock). Beim Durchführen eines Lesevorgangs für eine MyISAM-Tabelle werden die Leseanforderungen anderer Benutzer für dieselbe Tabelle nicht blockiert, es werden jedoch Schreibvorgänge für dieselbe Tabelle blockiert.
2. Schreibvorgänge in der MyISAM-Tabelle blockieren die Lese- und Schreibvorgänge anderer Benutzer in derselben Tabelle.

Das Lesen und Schreiben von MyISAM-Tabellen erfolgt seriell, das heißt, Schreibvorgänge können nicht ausgeführt werden, während Lesevorgänge ausgeführt werden, und umgekehrt. Unter bestimmten Bedingungen unterstützt die MyISAM-Tabelle jedoch auch gleichzeitige Abfrage- und Einfügevorgänge, indem der Wert einer Systemvariablen (concurrent_insert) gesteuert wird er ist 1, wenn es keine Lücken in der MyISAM-Tabelle gibt (d. h. es gibt keine gelöschten Zeilen in der Tabelle), erlaubt MyISAM einem Prozess, die Tabelle zu lesen, während ein anderer Prozess Datensätze vom Ende der Tabelle einfügt, wenn sein Wert erreicht ist ist auf 2 gesetzt, unabhängig davon, ob MyISAM Wenn in der Tabelle eine Lücke vorhanden ist, ist das gleichzeitige Einfügen von Datensätzen am Ende der Tabelle zulässig.

Wie die MyISAM-Sperrplanung implementiert wird, ist ebenfalls ein sehr kritisches Thema. Wenn beispielsweise ein Prozess eine Lesesperre für eine MyISAM-Tabelle anfordert und gleichzeitig ein anderer Prozess auch eine Schreibsperre für dieselbe Tabelle anfordert, wie priorisiert MySQL den Prozess? Untersuchungen haben gezeigt, dass der Schreibprozess zuerst die Sperre erhält (d. h. die Leseanforderung kommt zuerst in der Warteschlange für die Sperre an). Dies führt jedoch auch zu einem großen Fehler: Eine große Anzahl von Schreibvorgängen macht es für Abfragevorgänge schwierig, Lesesperren zu erhalten, was zu dauerhaften Blockierungen führen kann. Glücklicherweise können wir das Planungsverhalten von MyISAM durch einige Einstellungen anpassen. Wir können dafür sorgen, dass die MyISAM-Standard-Engine Leseanforderungen Priorität einräumt, indem wir den Parameter low-priority-updates angeben und seinen Wert auf 1 setzen (set low_priority_updates=1), um die Priorität zu senken.

Der größte Unterschied zwischen der InnoDB-Sperre und der MyISAM-Sperre ist:
1. Sie unterstützt Transaktionen (TRANCSACTION).
2. Es wird eine Sperre auf Zeilenebene verwendet.

Wir wissen, dass eine Transaktion eine logische Verarbeitungseinheit ist, die aus einer Reihe von SQL-Anweisungen besteht. Sie verfügt über vier Attribute (kurz ACID-Attribute):
Atomizität: Eine Transaktion ist eine Atomare Operationseinheit, deren Änderungen an den Daten entweder alle ausgeführt werden oder überhaupt nicht ausgeführt werden;
Konsistent: Die Daten müssen zu Beginn und am Ende der Transaktion konsistent bleiben.
Isolation (Isolation): Das Datenbanksystem stellt sicher ein bestimmter Isolationsmechanismus, um sicherzustellen, dass Transaktionen in einer „unabhängigen“ Umgebung ausgeführt werden, die nicht durch externe gleichzeitige Vorgänge beeinträchtigt wird: Nach Abschluss einer Transaktion ist die Änderung der Daten dauerhaft, auch wenn es zu Systemausfällen kommt werden ebenfalls gepflegt.

Probleme, die durch die gleichzeitige Transaktionsverarbeitung verursacht werden
Im Vergleich zur seriellen Verarbeitung kann die gleichzeitige Transaktionsverarbeitung die Nutzung von Datenbankressourcen erheblich erhöhen und den Transaktionsdurchsatz des Datenbanksystems verbessern, wodurch mehr Benutzer unterstützt werden. Die gleichzeitige Transaktionsverarbeitung bringt jedoch auch einige Probleme mit sich, darunter hauptsächlich die folgenden Situationen.
1. Verlorene Aktualisierung: Tritt auf, wenn zwei oder mehr Transaktionen dieselbe Zeile auswählen und die Zeile dann basierend auf dem ursprünglich ausgewählten Wert aktualisieren, da jede Transaktion nicht weiß, dass die anderen Transaktionen vorhanden sind – die letzte Aktualisierung überschreibt Aktualisierungen, die durch andere Transaktionen vorgenommen wurden. Beispielsweise erstellen zwei Redakteure elektronische Kopien desselben Dokuments. Jeder Redakteur ändert seine Kopie selbstständig und speichert dann die geänderte Kopie, wobei das Originaldokument überschrieben wird. Der Bearbeiter, der zuletzt eine Kopie seiner Änderungen gespeichert hat, überschreibt die von einem anderen Bearbeiter vorgenommenen Änderungen. Dieses Problem kann vermieden werden, wenn ein Editor nicht auf dieselbe Datei zugreifen kann, bis ein anderer Editor die Transaktion abschließt und festschreibt.
2. Dirty Reads: Eine Transaktion ändert einen Datensatz. Zu diesem Zeitpunkt befinden sich die Daten dieses Datensatzes in einem inkonsistenten Zustand nicht kontrolliert wird und eine zweite Transaktion die „schmutzigen“ Daten liest und entsprechend weiterverarbeitet, kommt es zu nicht festgeschriebenen Datenabhängigkeiten. Dieses Phänomen wird treffend als „Dirty Reading“ bezeichnet.
3. Nicht wiederholbare Lesevorgänge: Eine Transaktion liest die zuvor gelesenen Daten zu einem bestimmten Zeitpunkt erneut und stellt dann fest, dass sich die gelesenen Daten geändert haben. Oder einige Datensätze wurden gelöscht! Dieses Phänomen wird als „nicht wiederholbares Lesen“ bezeichnet.
4. Phantom-Lesevorgänge: Eine Transaktion liest zuvor abgerufene Daten gemäß denselben Abfragebedingungen erneut, stellt jedoch fest, dass andere Transaktionen neue Daten eingefügt haben, die ihre Abfragebedingungen erfüllen.

Transaktionsisolationsstufe
Unter den oben genannten Problemen, die durch die gleichzeitige Transaktionsverarbeitung verursacht werden, sollte „Update-Verlust“ normalerweise vollständig vermieden werden. Das Verhindern von Aktualisierungsverlusten kann jedoch nicht allein durch den Datenbanktransaktionscontroller gelöst werden. Die Anwendung muss die erforderlichen Sperren für die zu aktualisierenden Daten hinzufügen. Daher sollte die Verhinderung von Aktualisierungsverlusten in der Verantwortung der Anwendung liegen.
„Dirty Read“, „Non-Repeatable Read“ und „Phantom Read“ sind eigentlich Datenbank-Lesekonsistenzprobleme, die durch die Bereitstellung eines bestimmten Transaktionsisolationsmechanismus durch die Datenbank gelöst werden müssen. Die Art und Weise, wie Datenbanken die Transaktionsisolation implementieren, kann grundsätzlich in die folgenden zwei Typen unterteilt werden.
1. Eine besteht darin, die Daten vor dem Lesen zu sperren, um zu verhindern, dass andere Transaktionen die Daten ändern.
2. Die andere besteht darin, über einen bestimmten Mechanismus einen konsistenten Daten-Snapshot (Snapshot) des Datenanforderungszeitpunkts zu generieren, ohne Sperren hinzuzufügen, und diesen Snapshot zu verwenden, um eine bestimmte Konsistenzebene (Anweisungsebene oder Transaktionsebene) bereitzustellen Lesen. Aus Benutzersicht scheint es, dass die Datenbank mehrere Versionen derselben Daten bereitstellen kann. Daher wird diese Technologie als Data Multi-Version Concurrency Control (kurz MVCC oder MCC) bezeichnet, die häufig auch als Multiversionsdatenbank bezeichnet wird.

Je strenger die Transaktionsisolation der Datenbank ist, desto geringer sind die gleichzeitigen Nebenwirkungen, aber desto höher ist der gezahlte Preis, da die Transaktionsisolation im Wesentlichen dazu führt, dass Transaktionen bis zu einem gewissen Grad „serialisiert“ werden. Dies ist offensichtlich widersprüchlich auf „Parallelität“. Gleichzeitig stellen verschiedene Anwendungen unterschiedliche Anforderungen an die Lesekonsistenz und die Transaktionsisolation. Beispielsweise reagieren viele Anwendungen nicht empfindlich auf „nicht wiederholbare Lesevorgänge“ und „Phantomlesevorgänge“ und legen möglicherweise mehr Wert auf die Fähigkeit, gleichzeitig auf Daten zuzugreifen.
Um den Widerspruch zwischen „Isolation“ und „Parallelität“ zu lösen, definiert ISO/ANSI SQL92 4 Transaktionsisolationsstufen. Jede Stufe hat einen unterschiedlichen Isolationsgrad und ermöglicht unterschiedliche Nebenwirkungen. Anwendungen können ihre eigenen Geschäftslogikanforderungen erfüllen , gleichen Sie den Widerspruch zwischen „Isolation“ und „Parallelität“ aus, indem Sie unterschiedliche Isolationsstufen wählen. Tabelle 20-5 bietet eine gute Zusammenfassung der Merkmale dieser vier Isolationsstufen.

Konsistenz der Lesedaten und zulässige Nebenwirkungen der Nebenläufigkeit
Isolationsebene Konsistenz der Lesedaten Dirty Read Nicht wiederholbarer Lesevorgang Phantom Read
Lesen ohne Festschreibung Die niedrigste Ebene, nur garantiert Lesen Sie keine physisch beschädigten Daten Ja Ja Ja
Read commited Statement level Nein Ja Ja
Wiederholbares Lesen (Repeatable read) Transaktionsebene Nein Nein Ja
Serialisierbar Höchste Ebene, Transaktionsebene Nein Nein Nein

Abschließend ist zu beachten, dass nicht jede spezifische Datenbank die oben genannten vier Isolationsstufen vollständig implementiert. Beispielsweise bietet Oracle nur zwei Standard-Isolationsstufen: „Read commit“ und „Serializable“ sowie eine eigene definierte „Read only“-Isolationsstufe ; Zusätzlich zur Unterstützung der vier oben von ISO/ANSI SQL92 definierten Isolationsstufen unterstützt SQL Server auch eine Isolationsstufe namens „Snapshot“, aber streng genommen handelt es sich um eine mit MVCC implementierte serialisierbare Isolationsstufe. MySQL unterstützt alle 4 Isolationsstufen, aber in bestimmten Implementierungen gibt es einige Merkmale. Beispielsweise wird in einigen Isolationsstufen das MVCC-Konsistenzlesen verwendet, in einigen Fällen jedoch nicht.
InnoDB verfügt über zwei Modi für Zeilensperren:
1) Gemeinsame Sperre (S): Ermöglicht einer Transaktion das Lesen einer Zeile und verhindert, dass andere Transaktionen eine exklusive Sperre für denselben Datensatz erhalten.
(Wählen Sie * aus Tabellenname aus, wobei ……im Freigabemodus sperren)
2) Exklusive Sperre (X): Ermöglichen Sie Transaktionen, die exklusive Sperren erhalten, um Daten zu aktualisieren, und verhindern Sie, dass andere Transaktionen gemeinsame Lesesperren und exklusive Schreibsperren erhalten gleichen Datensatz sperren. (wählen Sie * aus Tabellenname aus, wobei ... für die Aktualisierung)
Um die Koexistenz von Zeilensperren und Tabellensperren zu ermöglichen, ist ein Multi-Granularitäts-Sperrmechanismus implementiert. Es gibt auch zwei intern verwendete Absichtssperren (beide Tabellensperren). nämlich Intention-Shared-Sperren und Intent-Exclusive-Sperren.
1) Absichtliche gemeinsame Sperre (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.
2) Absichtliche 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.
Kompatibilitätsliste für den InnoDB-Zeilensperrmodus
Angeforderter Sperrmodus
Ist er kompatibel?
Aktueller Sperrmodus Wenn der von einer Transaktion angeforderte Sperrmodus mit der aktuellen Sperre kompatibel ist, gewährt InnoDB der Transaktion die angeforderte Sperre. Wenn die beiden nicht kompatibel sind, wartet die Transaktion auf die Freigabe der Sperre.
Absichtssperren werden von InnoDB automatisch hinzugefügt und erfordern keinen Benutzereingriff. Bei UPDATE-, DELETE- und INSERT-Anweisungen fügt InnoDB automatisch exklusive Sperren (X) zu den beteiligten Datensätzen hinzu. Bei normalen SELECT-Anweisungen fügt InnoDB keine Sperren hinzu Aussagen.
1. Gemeinsame Sperre (S): SELECT * FROM table_name WHERE … LOCK IN SHARE MODE.
2. Exklusive Sperre (X): SELECT * FROM table_name WHERE ... FOR UPDATE.
InnoDB-Zeilensperre wird durch Sperren der Indexeinträge im Index erreicht. Dies unterscheidet sich von MySQL und Oracle, die durch Sperren der entsprechenden Datenzeilen im Datenblock erreicht werden. Diese 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.


Sperrenkonflikt auf Tabellenebene abfragen
Tabellensperrenkonflikt:
Der Tabellensperrenkonflikt auf dem System kann durch Überprüfen der Statusvariablen table_locks_waited und table_locks_immediate analysiert werden:

mysql> show status like ‘table%’; 
+———————–+——-+ 
| Variable_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, weist dies darauf hin, dass ein schwerwiegender Sperrkonflikt auf Tabellenebene vorliegt.
InnoDB-Zeilensperrenkonflikt:
Sie können den Zeilensperrenkonflikt auf dem System analysieren, indem Sie die Statusvariable InnoDB_row_lock überprüfen:

mysql> show status like ‘innodb_row_lock%’; 
+——————————-+——-+ 
| Variable_name                 | Value | 
+——————————-+——-+ 
| 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 rows in set (0.01 sec)

MyISAM-Schreibsperrenexperiment:
Für MyISAM-Tabelle lesen Vorgänge blockieren nicht die Leseanforderungen anderer Benutzer für dieselbe Tabelle, aber Schreibanforderungen für dieselbe Tabelle. Schreibvorgänge für die MyISAM-Tabelle blockieren die Lese- und Schreibvorgänge anderer Benutzer für dieselbe Tabelle Operationen und Schreiboperationen sowie Schreiboperationen 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.
BENUTZER1:

mysql> lock table film_text write;

Die aktuelle Sitzung kann Abfrage-, Aktualisierungs- und Einfügevorgänge für die gesperrte Tabelle ausführen:

mysql> select film_id,title from film_text where film_id = 1001;

BENUTZER2:

mysql> select film_id,title from film_text where film_id = 1001;

Warten

BENUTZER1:

Sperre freigeben:

mysql> unlock tables;

BENUTZER2:
Sperre erhalten, die Abfrage gibt zurück:
Experiment mit gemeinsamer Sperre der InnoDB-Speicher-Engine

USER1： 
mysql> set autocommit = 0; 
USER2： 
mysql> set autocommit = 0;

BENUTZER1:
Die aktuelle Sitzung fügt eine gemeinsame Sperre im Freigabemodus zum Datensatz von „actor_id=178“ hinzu:

mysql> select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode;

BENUTZER2:

Andere Sitzungen können noch Fragen Sie die Datensätze ab und Sie können diesem Datensatz auch eine gemeinsame Sperre im Freigabemodus hinzufügen:

mysql> select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode;

BENUTZER1:

Die aktuelle Sitzung aktualisiert den gesperrten Datensatz und wartet auf die Sperre:

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178;

Warten

BENUTZER2:
Andere Sitzungen aktualisieren den Datensatz ebenfalls, was zu einem Deadlock-Exit führt:

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178;

FEHLER 1213 (40001): Beim Versuch wurde ein Deadlock gefunden Um die Sperre zu erhalten, versuchen Sie, die Transaktion neu zu starten
USER1:
Nach Erhalt der Sperre kann sie erfolgreich aktualisiert werden:

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178; 
Query OK, 1 row affected (17.67 sec) 
Rows matched: 1  Changed: 1  Warnings: 0

Exklusives Sperrbeispiel der InnoDB-Speicher-Engine

USER1： 
mysql> set autocommit = 0; 
USER2： 
mysql> set autocommit = 0;

USER1：
当前session对actor_id=178的记录加for update的排它锁：

mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update;

USER2：
其他session可以查询该记录，但是不能对该记录加共享锁，会等待获得锁：

mysql> select actor_id,first_name,last_name from actor where actor_id = 178;

USER1：
当前session可以对锁定的记录进行更新操作，更新后释放锁：

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178;

USER2：
其他session获得锁，得到其他session提交的记录：

mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update;

更新性能优化的几个重要参数
bulk_insert_buffer_size
批量插入缓存大小,这个参数是针对MyISAM存储引擎来说的.适用于在一次性插入100-1000+条记录时,提高效率.默认值是8M.可以针对数据量的大小,翻倍增加.
concurrent_insert
并发插入,当表没有空洞(删除过记录),在某进程获取读锁的情况下,其他进程可以在表尾部进行插入.
值可以设0不允许并发插入, 1当表没有空洞时,执行并发插入, 2不管是否有空洞都执行并发插入.
默认是1针对表的删除频率来设置.
delay_key_write
针对MyISAM存储引擎,延迟更新索引.意思是说,update记录时,先将数据up到磁盘,但不up索引,将索引存在内存里,当表关闭时,将内存索引,写到磁盘.值为 0不开启, 1开启.默认开启.
delayed_insert_limit, delayed_insert_timeout, delayed_queue_size
延迟插入,将数据先交给内存队列,然后慢慢地插入.但是这些配置,不是所有的存储引擎都支持,目前来看,常用的InnoDB不支持, MyISAM支持.根据实际情况调大,一般默认够用了。

Das obige ist der detaillierte Inhalt vonDetaillierte Einführung in den Sperrmechanismus in der MySQL-Datenbank. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!