DEADLOCK Problem

[mike700]

Hallo,

wir haben ein Problem mit Deadlocks auf unserer MySQL 5.5 Datenbank.

Ausgangssituation:
Problem war, dass wenn mehrere Anwendungen auf die Datenbank editierend (UPDATE, INSERT) zugegriffen haben, dies gleichzeitig passierte und es zu DEADLOCKS kam. Daraufhin haben wir gedacht, dass wir LOCK-Statements implementieren und dadurch alles besser wird. Pustekuchen!
Gefühlt wurde es schlimmer.
Das LOCKEN von Tabellen sollte eigtl. auch nur eine vorrübergehende Lösung sein.

Nun haben wir aber das Problem, dass wenn Select-Statements zwischen das LOCKen von Tabellen kommt, diese beiden Threads sich gegenseitig sperren.

Beispiel:
Thread1:
Select * From tabelle1 where id=54785;
// sagen wir, die Abfrage dauert ca. 1min und wurde um 12:45:00 angestoßen

dann kommt ein 2ter Thread:
Thread2:
Lock Table tabelle1 WRITE;
update tabelle1 set sum=15 where id=5;
UNLOCK TABLES;
// sagen wir diese Transaktion wird um 14:45:10 angestoßen

Wenn genau dieser Fall eintrifft, dann Sperren sich die beiden Threads gegeneinander und es kommt nie zu einem Ergebnis!

Hat jemand vielleicht eine Ahnung wie man dieser Problematik aus dem Weg gehen kann ?

Beste Grüße,
Mike

[Falk Prüfer]

Hallo,

...Hat jemand vielleicht eine Ahnung wie man dieser Problematik aus dem Weg gehen kann ?

Also Deadlocks sind immer ein Zeichen für schlechtes Anwendungsdesigns und nicht grundsätzlich ein Locking-Problem!
Dazu sollte man sich erstmal klarmachen, wann genau ein Deadlock entsteht. Ein Deadlock entsteht erst dann, wenn Sperren auf mehrere Objekte gesetzt werden und zwar in umgekehrten (oder ungünstigen) Reihenfolgen.

Mögliches Szenario: Eine Anwendung verwaltet eine globale ID-Tabelle. Diese wird bei jedem Insert in irgendeine Tabelle benutzt und dafür gelockt. Die Anwendung hat z.B. eine Funktion für das Anlegen eines Datensatzes in Tabelle A und eine für das Update. Beim Update wird zusätzlich in eine Loggingtabelle B ein Eintrag geschrieben (und dafür die ID-Tabelle gelockt).

[Pseudocode]

Insert:
LOCK ID_TABLE
INSERT A
UNLOCK TABLES

Update:
UPDATE A
Verarbeite_Daten()
LOCK ID_TABLE
INSERT B
UNLOCK TABLES

[/Pseduocode]

Das sieht erstmal nicht schlimm aus. Wenn jetzt jedoch in der Zeit in der in einem Thread1 das Update in der Subroutine Verarbeite_Daten() feststeckt, ein Thread2 ein Insert ausführt, dann kommt es zum Deadlock!
Thread1 hat durch das UPDATE A die Tabelle A gesperrt. Das LOCK ID_TABLE in Thread2 kann jedoch ausgeführt werden, da Thread1 noch Daten verarbeitet. Allerdings wartet das anschliessende INSERT A aus Thread2 auf das Aufheben der Sperre aus Thread1. Wenn jetzt Thread1 die Datenverarbeitung abgeschlossen hat und den Log-Eintrag schreiben will, dann kommt es beim Versuch LOCK ID_TABLE zum Deadlock, da dieser Lock bereits durch Thread2 gesetzt ist der aber auf Thread1 wartet, der jetzt auf Thread2 warten würde...

Lösung:
[Pseudocode]

Update:
LOCK ID_TABLE
UPDATE A
Verarbeite_Daten()
INSERT B
UNLOCK TABLES

[/Pseduocode]

Durch das Vorziehen der Sperre der Logtabelle wird der Deadlock in jedem Fall vermieden.

Fazit: Ihr solltet eure Anwendung also mal nach Szenarien durchkämmen, in denen mehrere Tabellen gesperrt werden, egal ob explizit oder implizit, und ob in jedem Fall die Reihenfolge der Sperren sinnvoll eingehalten ist. Mit willkürlich verteilten expliziten Locks macht ihr die Sache - wie du schon gemerkt hast - nur noch schlimmer.

Gruß Falk

[Michael Bieri]

Kurze Frage;
Welche Storage Engine wird bei dieser DB verwendet? Anhand der problemen klingt das für mich spontan nach myISAM. Falls das so ist - unbedingt auf InnoDB welchseln.
Grund ist, das du bei myISAM immer (!) Tablelocks hast, bei InnoDB geht das auf Rowlevel.

Die Ganze Lockingsache in eine Applikation einzubauen betrachte ich als äusserst heikel. Hier braucht es ein sauberes Errorhandling. Wenn ein Query fehlschlägt - muss danach in jedem Fall durch den Errorhandler der Lock wieder aufgehoben werden! Locking ist eher für Bulkupdates oder backups sinnvoll - keinesfalls für den Einsatz in einer normalen Applikation.