Beispielanalyse der Verwendung gemeinsam genutzter und exklusiver Sperren in MySQL

Dieser Artikel veranschaulicht anhand von Beispielen die Verwendung gemeinsam genutzter und exklusiver Sperren in MySQL. Teilen Sie uns die Einzelheiten zu Ihrer Information mit:

Der MySQL-Sperrmechanismus ist in Sperren auf Tabellenebene und Sperren auf Zeilenebene unterteilt. In diesem Artikel werde ich Ihnen meine Erfahrungen und Informationen zu gemeinsamen Sperren und exklusiven Sperren in Sperren auf Zeilenebene in MySQL mitteilen.

Eine gemeinsame Sperre wird auch Lesesperre oder kurz S-Sperre genannt. Wie der Name schon sagt, bedeutet eine gemeinsame Sperre, dass mehrere Transaktionen eine Sperre für dieselben Daten gemeinsam nutzen und auf die Daten zugreifen können, sie aber nur lesen und nicht ändern können.

Eine exklusive Sperre wird auch als Schreibsperre oder kurz X-Sperre bezeichnet. Wie der Name schon sagt, kann eine exklusive Sperre nicht mit anderen Sperren koexistieren. Wenn beispielsweise eine Transaktion eine exklusive Sperre für eine Datenzeile erwirbt, können andere Transaktionen keine anderen Sperren für die Zeile erwirken, einschließlich gemeinsam genutzter Sperren und exklusiver Sperren. Die Transaktion, die die exklusive Sperre erwirbt, kann die Daten jedoch lesen und ändern.

Jeder hat wahrscheinlich ein gutes Verständnis von gemeinsam genutzten Sperren, was bedeutet, dass mehrere Transaktionen Daten nur lesen, aber nicht ändern können. Jeder hat jedoch möglicherweise ein anderes Verständnis von exklusiven Sperren. Ich habe am Anfang einen Fehler gemacht und gedacht, dass, nachdem eine Datenzeile durch eine exklusive Sperre gesperrt wurde, andere Transaktionen die Datenzeile nicht lesen oder ändern können. Tatsächlich ist dies nicht der Fall. Eine exklusive Sperre bedeutet, dass, nachdem eine Transaktion einer Datenzeile eine exklusive Sperre hinzugefügt hat, andere Transaktionen keine weiteren Sperren darauf anwenden können. Die standardmäßigen Datenänderungsanweisungen der MySQL InnoDB-Engine (Update, Delete und Insert) fügen den betreffenden Daten automatisch exklusive Sperren hinzu. Die Select-Anweisung fügt standardmäßig keinen Sperrtyp hinzu. Wenn Sie eine exklusive Sperre hinzufügen möchten, können Sie die Select ... for Update-Anweisung verwenden. Wenn Sie eine gemeinsame Sperre hinzufügen möchten, können Sie die Select ... Lock in Share Mode-Anweisung verwenden. Daher kann die Datenzeile mit einer exklusiven Sperre weder in anderen Transaktionen geändert noch im Freigabemodus zum Aktualisieren und Sperren abgefragt werden. Die Daten können jedoch direkt über select ...from... abgefragt werden, da normale Abfragen keinen Sperrmechanismus haben.

Nachdem wir nun so viel gesagt haben, schauen wir uns das folgende einfache Beispiel an:

Wir haben folgende Testdaten

Jetzt fragen wir ausschließlich die Datenzeile mit der ID=1 ab. Hier verwenden wir begin, um die Transaktion zu öffnen, und Sie werden nicht sehen, wie ich die Transaktion schließe. Dies wird zu Testzwecken durchgeführt, da das Festschreiben oder Zurücksetzen der Transaktion die Sperre aufhebt.

Öffnen eines Abfragefensters

Ein Datenelement wird gefunden. Öffnen Sie nun ein weiteres Abfragefenster und fragen Sie dieselben Daten mithilfe einer exklusiven Abfrage und einer gemeinsamen Sperrabfrage ab.

Exklusiver Check

Gemeinsame Abfrage

Wir können sehen, dass sich sowohl die Abfrage mit exklusiver Sperre als auch die Abfrage mit gemeinsamer Sperre in einem blockierten Zustand befinden, da die Daten mit der ID = 1 mit einer exklusiven Sperre gesperrt wurden und die Blockierung hier darauf wartet, dass die exklusive Sperre freigegeben wird.

Was wäre, wenn wir die folgende Abfrage direkt verwenden würden?

Wir sehen, dass die Daten abgefragt werden können.

Schauen wir uns eine Transaktion an, die eine gemeinsame Sperre erhält. In anderen Abfragen können nur gemeinsame Sperren oder keine Sperren hinzugefügt werden.

Wir können sehen, dass die Daten abgefragt werden können, aber nicht mit einer exklusiven Sperre, da exklusive Sperren und gemeinsame Sperren nicht für dieselben Daten existieren können.

Lassen Sie uns abschließend das Problem der automatischen exklusiven Sperren für Update-, Delete- und Insert-Anweisungen in der oben erwähnten MySQL InnoDb-Engine überprüfen.

Zu diesem Zeitpunkt ist die gemeinsame Abfrage blockiert und wartet auf die Freigabe der exklusiven Sperre. Die Daten können jedoch mithilfe normaler Abfragen gefunden werden, da es keinen Sperrmechanismus gibt, der sich mit der exklusiven Sperre gegenseitig ausschließt. Die gefundenen Daten sind jedoch die alten Daten vor der Änderung.

Dann übernehmen wir die Daten, geben die exklusive Sperre frei und sehen uns die geänderten Daten an. Zu diesem Zeitpunkt können exklusive Abfragen, gemeinsame Abfragen und normale Abfragen verwendet werden. Da die exklusive Sperre der Datenzeile nach der Übernahme der Transaktion freigegeben wird, wird unten nur die normale Abfrage angezeigt. Andere Schüler können dies selbst überprüfen.

Sie können sehen, dass die Ergebnisse wie erwartet sind.

Das Obige ist mein persönliches Verständnis von gemeinsam genutzten Sperren und exklusiven Sperren in MySQL. Wenn es Ungenauigkeiten gibt, hoffe ich, dass Sie mich korrigieren können.

Leser, die an weiteren MySQL-bezogenen Inhalten interessiert sind, können sich die folgenden Themen ansehen: „Zusammenfassung der Kenntnisse im Zusammenhang mit MySQL-Datenbanksperren“, „Zusammenfassung der Kenntnisse im Zusammenhang mit MySQL-gespeicherten Prozeduren“, „Zusammenfassung der allgemeinen MySQL-Funktionen“, „Zusammenfassung der Kenntnisse im Zusammenhang mit MySQL-Protokollvorgängen“ und „Zusammenfassung der Kenntnisse im Zusammenhang mit MySQL-Transaktionsvorgängen“.

Ich hoffe, dass dieser Artikel für jedermann beim Entwurf einer MySQL-Datenbank hilfreich ist.