MySQL 5.1 unterstützt Sperren auf Tabellenebene für MyISAM- und MEMORY-Tabellen, Sperren auf Seitenebene für BDB-Tabellen und InnoDB Tabellen. Sperren auf Zeilenebene. In vielen Fällen lässt sich anhand des Trainings erraten, welcher Sperrtyp für eine Anwendung am besten geeignet ist. Im Allgemeinen ist es jedoch schwierig zu sagen, ob ein bestimmter Sperrtyp besser ist als ein anderer. Alles hängt von der Anwendung ab. Verschiedene Teile der Anwendung erfordern möglicherweise unterschiedliche Sperrtypen. Um festzustellen, ob Sie eine Speicher-Engine mit Sperrung auf Zeilenebene verwenden möchten, sollten Sie sich ansehen, was Ihre Anwendung tut und welche Mischung aus Select- und Update-Anweisungen sie verwendet. Beispielsweise führen die meisten Webanwendungen viele Auswahlvorgänge und wenige Löschvorgänge, nur Aktualisierungen von Schlüsselwerten und nur wenige spezifische Tabelleneinfügungen durch. Das grundlegende MySQL MyISAM-Setup ist gut abgestimmt.
In MySQL gibt es bei Speicher-Engines, die Sperren auf Tabellenebene verwenden, keinen Deadlock, wenn die Tabelle gesperrt ist. Dies wird dadurch erreicht, dass alle notwendigen Sperren immer sofort zu Beginn einer Abfrage angefordert werden und die Tabelle immer in der gleichen Reihenfolge gesperrt wird.
Für WRITE funktioniert die von MySQL verwendete Tabellensperrmethode wie folgt:
◆ Wenn keine Sperre für die Tabelle vorhanden ist, setzen Sie eine Schreibsperre darauf.
◆ Andernfalls stellen Sie die Sperranforderung in die Schreibsperrwarteschlange.
Für READ funktioniert die von MySQL verwendete Sperrmethode wie folgt:
◆Wenn keine Schreibsperre für die Tabelle vorhanden ist, setzen Sie eine Lesesperre darauf.
◆ Andernfalls stellen Sie die Sperranforderung in die Lesesperrenwarteschlange.
Wenn eine Sperre aufgehoben wird, kann die Sperre von Threads in der Schreibsperrenwarteschlange und dann von Threads in der Lesesperrenwarteschlange erworben werden.
Das bedeutet, dass die SELECT-Anweisung wartet, bis keine weiteren Aktualisierungen mehr vorhanden sind, wenn eine Tabelle viele Aktualisierungen aufweist.
Wenn INSERT-Anweisungen nicht in Konflikt geraten, können Sie parallele INSERT- und SELECT-Anweisungen für eine MyISAM-Tabelle ohne Sperren frei kombinieren.
InnoDB verwendet Zeilensperren und BDB verwendet Seitensperren. Bei beiden Speicher-Engines sind Deadlocks möglich. Dies liegt daran, dass InnoDB automatisch Zeilensperren und BDB Seitensperren während der SQL-Anweisungsverarbeitung erhält und nicht erst beim Start der Transaktion.
Vorteile der Sperrung auf Zeilenebene:
· Es gibt nur wenige Sperrkonflikte, wenn in vielen Threads auf verschiedene Zeilen zugegriffen wird.
· Rollback mit nur wenigen Änderungen.
· Eine einzelne Zeile kann für längere Zeit gesperrt werden.
Nachteile der Sperrung auf Zeilenebene:
· Beansprucht mehr Speicher als die Sperrung auf Seiten- oder Tabellenebene.
· Bei Verwendung für große Teile einer Tabelle langsamer als Sperren auf Seiten- oder Tabellenebene, da Sie mehr Sperren erwerben müssen.
· Wenn Sie häufig GROUP BY-Vorgänge für die meisten Ihrer Daten ausführen oder häufig die gesamte Tabelle scannen müssen, ist die Sperre erheblich langsamer als andere Sperren.
· Mit Sperren auf hoher Ebene können Sie Ihre Anwendung auch einfach skalieren, indem Sie verschiedene Arten von Sperren unterstützen, da die Sperrkosten geringer sind als bei Sperren auf Zeilenebene.
Tabellensperren haben Vorrang vor Sperren auf Seiten- oder Zeilenebene, wenn:
· Die meisten Anweisungen in der Tabelle für Lesevorgänge verwendet werden.
· Mit strikten Schlüsseln lesen und aktualisieren: Sie können eine Zeile aktualisieren oderlöschen
, die mit einem einzelnen Leseschlüssel extrahiert werden kann:
• UPDATE tbl_name SET columns = value WHERE unique_key_col = key_value;
• DELETE FROM tbl_name WHERE unique_key_col = key_value;
parallele INSERT-Anweisungen und sehr wenige UPDATE- oder DELETE-Anweisungen.
· Es gibt viele Scans oder GROUP BY-Vorgänge für die gesamte Tabelle ohne Schreibvorgänge.
Optionen, die sich von Sperren auf Zeilen- oder Seitenebene unterscheiden:
· Versionierung (z. B. die in MySQL verwendete Technik für parallele Einfügungen), bei der ein Schreibvorgang und viele Lesevorgänge gleichzeitig ausgeführt werden können. Das bedeutet, dass die Datenbank bzw. Tabelle je nach Beginn des Zugriffs unterschiedliche Sichten auf die Daten unterstützt. Weitere gebräuchliche Begriffe sind „Zeiterfassung“, „Copy on Write“ oder „Copy on Demand“.
· Die On-Demand-Replikation hat in vielen Fällen Vorrang vor Sperren auf Seiten- oder Zeilenebene. Im schlimmsten Fall kann es jedoch sein, dass mehr Speicher verbraucht wird als bei der Verwendung einer regulären Sperre.
· Zusätzlich zur Sperrung auf Zeilenebene können Sie Sperren auf Anwendungsebene verwenden, z. B. GET_LOCK() und RELEASE_LOCK() in MySQL. Dabei handelt es sich um Empfehlungssperren, die nur bei gut laufenden Anwendungen funktionieren.
Um eine maximale Sperrgeschwindigkeit zu erreichen, verwendet MySQL für alle Speicher-Engines außer InnoDB und BDB Tabellensperren (anstelle von Seiten-, Zeilen- oder Spaltensperren). Bei InnoDB- und BDB-Tabellen verwendet MySQL die Tabellensperre nur, wenn Sie die Tabelle explizit mit LOCK TABLES sperren. Wenn Sie LOCK TABLES nicht verwenden, verwendet MySQL eine automatische Sperre auf Zeilenebene und BDB eine Sperre auf Seitenebene, um die Transaktionsisolation sicherzustellen.
Bei großen Tabellen ist die Tabellensperre für die meisten Anwendungen jedoch besser als die Zeilensperre, hat jedoch einige Nachteile. Durch Tabellensperren können viele Threads gleichzeitig aus einer Tabelle lesen. Wenn ein Thread jedoch in die Tabelle schreiben möchte, muss er zunächst exklusiven Zugriff erhalten. Während der Aktualisierung müssen alle anderen Threads, die auf die Tabelle zugreifen möchten, warten, bis die Aktualisierung abgeschlossen ist.
Tabellenaktualisierungen gelten im Allgemeinen als wichtiger als Tabellenabrufe und erhalten daher eine höhere Priorität. Dies sollte sicherstellen, dass die Aktivität, die eine Tabelle aktualisiert, nicht verhungern kann, selbst wenn eine starke SELECT-Aktivität in der Tabelle vorliegt.
Das Sperren von Tabellen kann in Situationen zu Problemen führen, wenn beispielsweise ein Thread wartet, weil die Festplatte voll ist und freier Speicherplatz vorhanden sein muss, bevor der Thread verarbeiten kann. In diesem Fall werden auch alle Threads, die auf die entsprechende Tabelle zugreifen wollen, in einen Wartezustand versetzt, bis wieder mehr Festplattenspeicher frei wird.
Das Sperren von Tabellen ist auch in den folgenden Situationen problematisch:
· Ein Kunde gibt eine Abfrage mit langer Laufzeit aus.
· Dann aktualisiert ein anderer Client dieselbe Tabelle. Der Client muss warten, bis der SELECT-Vorgang abgeschlossen ist.
· Ein anderer Client gibt eine weitere SELECT-Anweisung für dieselbe Tabelle aus. Da UPDATE eine höhere Priorität als SELECT hat, wartet die SELECT-Anweisung auf den Abschluss von UPDATE und auf den Abschluss des ersten SELECT-Befehls.
Im Folgenden werden einige Methoden beschrieben, um durch Tabellensperren verursachte Konflikte zu vermeiden oder zu reduzieren:
· Versuchen Sie, die Ausführung der SELECT-Anweisung zu beschleunigen. Möglicherweise müssen Sie dazu einige Übersichtstabellen erstellen.
· Starten Sie mysqld mit --low-priority-updates. Dadurch erhalten alle Anweisungen, die eine Tabelle aktualisieren (ändern), eine niedrigere Priorität als SELECT-Anweisungen. In diesem Fall wird die zweite SELECT-Anweisung in der vorherigen Situation vor der UPDATE-Anweisung ausgeführt, ohne auf den Abschluss der ersten SELECT-Anweisung zu warten.
· Sie können die Anweisung SET LOW_PRIORITY_UPDATES=1 verwenden, um anzugeben, dass alle Aktualisierungen in einer bestimmten Verbindung eine niedrige Priorität verwenden sollen.
· Sie können das Attribut LOW_PRIORITY verwenden, um einer bestimmten INSERT-, UPDATE- oder DELETE-Anweisung eine niedrigere Priorität zu geben.
· Sie können das HIGH_PRIORITY-Attribut verwenden, um einer bestimmten SELECT-Anweisung eine höhere Priorität zu geben.
· Starten Sie mysqld, indem Sie einen niedrigen Wert für die Systemvariable max_write_lock_count angeben, um MySQL zu zwingen, vorübergehend die Priorität aller SELECT-Anweisungen zu erhöhen, die auf eine Tabelle warten, nachdem eine bestimmte Anzahl von Einfügungen abgeschlossen ist. Dies ermöglicht die Vergabe einer READ-Sperre nach einer bestimmten Anzahl von WRITE-Sperren.
· Wenn Sie Probleme mit INSERT in Kombination mit SELECT haben, wechseln Sie zur Verwendung der neuen MyISAM-Tabellen, da diese gleichzeitiges SELECT und INSERT unterstützen.
· Wenn Sie Einfügungen und Löschvorgänge in derselben Tabelle mischen, ist INSERT DELAYED eine große Hilfe.
· Wenn Sie Probleme beim Mischen von SELECT- und DELETE-Anweisungen in derselben Tabelle haben, kann die LIMIT-Option von DELETE hilfreich sein.
· Die Verwendung von SQL_BUFFER_RESULT für SELECT-Anweisungen kann dazu beitragen, die Sperrzeiten für Tabellen zu verkürzen.
· Der Sperrcode in mysys/thr_lock.c kann geändert werden, um eine einzelne Warteschlange zu verwenden. In diesem Fall haben Schreibsperren und Lesesperren die gleiche Priorität, was für einige Anwendungen hilfreich sein kann.
Hier sind einige Tipps zum Sperren von Tabellen in MySQL:
· Wenn Sie Aktualisierungen nicht mit Auswahlen kombinieren, die die Überprüfung vieler Zeilen in derselben Tabelle erfordern, können Sie parallele Vorgänge ausführen.
· Sie können LOCK TABLES verwenden, um die Geschwindigkeit zu verbessern, da viele Aktualisierungen in einer Sperre viel schneller sind als Aktualisierungen ohne Sperren. Auch die Aufteilung des Tabelleninhalts auf mehrere Tabellen kann hilfreich sein.
· Wenn beim Sperren von Tabellen in MySQL Geschwindigkeitsprobleme auftreten, können Sie die Tabelle in eine InnoDB- oder BDB-Tabelle konvertieren, um die Leistung zu verbessern.