Wie man Replikationsunterbrechung durch Deadlocks bei INSERT INTO … SELECT verhindert

Der My-Hammer Auftragsradar, der unsere Auftragnehmer auf Wunsch regelmässig per E-Mail über neu eingestellte Auktionen anhand einstellbarer Filterkriterien informiert, baut bei jedem Durchlauf eine eigene Suchtabelle auf. Diese wird gefüllt mit einer Untermenge der Daten unserer Haupt-Auktionstabelle, nämlich nur den derzeit laufenden Auktionen.

Die Verwendung von INSERT INTO … SELECT ist hier naheliegend, zum Beispiel so:

INSERT INTO Suchtabelle
 SELECT a, b, c FROM Auktionstabelle WHERE x = y

Es ergab sich folgendes Problem: Der Query wird wie jeder andere auch auf die Datenbankslaves repliziert. Dort wurde er auch korrekt ausgeführt. Jedoch nicht immer auf dem Master: hier kam es regelmäßig zu Deadlocks auf der Auktionstabelle, da dies eine InnoDB Tabelle ist (bei MyISAM Tabellen können Deadlocks nicht auftreten).

Wenn ein MySQL Slave jedoch feststellt, dass beim gleichen Query auf dem Master und auf dem Slave unterschiedliche Fehler auftreten (Slave: no error; Master: deadlock), unterbricht dieser die Replikation. Es hilft dann nur ein SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;.

Ich habe mich daraufhin nach Lösungen umgeschaut. Erste Anlaufstelle ist das Kapitel Vom Umgang mit Deadlocks im MySQL Handbuch.

Mein erster Versuch war, den 4. Tipp dieses Kapitels zu befolgen: Das Einstellen eines niedrigeren Isolationslevels. Da perfekte Datenkonsistenz für die benötigte Suchtabelle nicht nötig ist (dirty reads also akzeptabel sind), verwendete ich gleich den niedrigsten Level READ UNCOMMITED. Das Ergebnis war gelinde gesagt verheerend, es traten noch mehr Deadlocks auf als zuvor.

Deshalb bin ich dazu übergegangen, die beteiligten Tabellen explizit mit einem READ LOCK zu sperren – viele Artikel zu diesem Thema haken diese Vorgehensweise sofort als nicht gangbar ab, da die Performance darunter leide. Da es sich beim Auftragsradar jedoch um einen Cronjob handelt, der nur alle paar Minuten einmal läuft, und der INSERT INTO … SELECT Query sehr schnell durchläuft, erschien mir das Risiko, eine unserer wichtigsten Tabellen für diesen Query zu sperren, als gering.

Wie sich zeigte, brachte dies den gewünschten Erfolg: Seitdem sind an dieser Stelle keinerlei Deadlocks mehr aufgetreten, und der Rest der Plattform zeigt sich von den seltenen und kurzen Locks völlig unbeeindruckt.