Deadlocks vermeiden, UPDATE mit geringtsmöglichen Locks

[Dave_Bowman]

Hallo,

ich habe hier das Problem, dass ich mehr oder weniger viele Deadlocks bekomme, wobei immer wieder ein bestimmtes Statement beteiligt ist. Dieses Statement steht in einer Procedure, die relativ häufig aufgerufen wird und ein paar Statusvariablen usw. ermittelt. Dabei ist es aber eigentlich relativ egal, ob die nun absolut exakt sind und konsistent, weil nach wenigen Augenblicken wird die ja wieder aufgerufen. Diese Werte ändern sich also ohnehin permanent, und es wäre sogar ok, wenn die Werte eben mal gar nicht ermittelt werden, wenn es Probleme gibt. Das andere beteiligte Statement ist da schon wichtiger (Ein UPDATE in table2). Blöderweise wird nur immer dieses dann zurückgesetzt.

Das weniger wichtige Statement ist eines der Art
UPDATE table1 LEFT JOIN (SELECT * FROM table2) tab2 ON table1.field = tab2.field SET ...
Es steht in keiner Transaktion bzw. in keiner, die mehrere Statements umfasst.

Der Lock ist immer auf dem PK von table2. Ich habe jetzt schon ein SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED vor dem Aufruf dieses UPDATEs gesetzt, aber da die Deadlocks nach wie vor auftreten, nehme ich an, dass das gar nicht wirkt bei einem SELECT, wenn das doch Teil eines UPDATEs ist.

Gibt es irgendeine Möglichkeit, diese Werte zu ermitteln und dem Server dabei zu sagen, dass er keine Locks oder Sperren setzen soll oder dass meinetwegen superaktuelle oder konsistente Zahlen an der Stelle gar nicht so wichtig sind?

Würde mich über ein paar Tips freuen, Dankeschön ...

Dave

[defo]

Ich bin kein MySQL Spezialist.
Mich würde in so einem Szenario als erstes interessieren, woher der Lock auf eine Table die nicht mal das Updateziel ist überhaupt kommt.
Mehr noch, wie sieht überhaupt dieser Prozess aus. Wer startet wann wo was und ist dieser Prozess serverseitig unter Kontrolle, sprich kann ich z.B. ganz klar ausschließen, dass "jemand" "sicherheitshalber" explizit Locks setzt.
Generell: Dein Statement ist ja offenbar exemplarisch und lässt viel Spielraum für Spekulation. Hier würde ich ganz allgemein die Frage stellen, ob das Updatestatement im Ergebnis direkte Auswirkungen auf die ursprüngliche Updatemenge hat. Sprich, verändert das Update Felder, die Teil der Joins oder where-Kriterien sind.

[Dave_Bowman]

Woher der Lock kommt, weiß ich nicht, aber ich kann ausschließen, dass jemand explizit Locks setzt. Diese Table2 ist im Grunde der Dreh- und Angelpunkt fast jeglicher Tätigkeit auf dem Server, die wird wirklich gut beansprucht. Das den Lock verursachende UPDATE (das weniger wichtige) verändert definitiv keine Daten in Table2, es ermittelt lediglich welche und schreibt diese dann in eine weitere Tabelle. Das andere UPDATE ändert schon Daten in Table2.

Ich gebe hier mal den entsprechenden Auszug aus dem INNODB-Status an, wobei Table2 in meinem Beispiel die Tabelle bigeye.apl_app_logs ist:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-03-17 17:44:54 8b0
*** (1) TRANSACTION:
TRANSACTION 1041750622, ACTIVE 1 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 29 lock struct(s), heap size 6960, 355 row lock(s)
MySQL thread id 4694, OS thread handle 0xf04, query id 26228244 localhost ::1 root updating
UPDATE bigeye.apl_app_logs SET apl_responsible_user = 1000157, apl_responsible_since = NOW() WHERE apl_id_server = 2 AND apl_remote_nsr_id = 26 AND apl_remote_nap_id = 28 AND apl_remote_nsc_id = 1513 AND apl_remote_type = 'ERROR' AND apl_remote_text_hash = 602801780 AND apl_id > 11819459
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1366563 page no 32472 n bits 160 index `PRIMARY` of table `bigeye`.`apl_app_logs` trx id 1041750622 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 1041750612, ACTIVE 1 sec fetching rows, thread declared inside InnoDB 495
mysql tables in use 9, locked 9
804 lock struct(s), heap size 80312, 12046 row lock(s)
MySQL thread id 4887, OS thread handle 0x8b0, query id 26228230 IT-NEPTUN 10.0.2.62 BigEye Sending data
UPDATE bigeye.usa_user_assignments ass
LEFT JOIN (SELECT usr_id,
SUM(IF(apl_remote_type = 'FATAL', 1, 0)) AS fat_groups,
SUM(IF(apl_remote_type = 'FATAL' AND new_entries > 0, 1, 0)) AS fat_groups_with_new_entries,
SUM(IF(apl_remote_type = 'FATAL' AND assigned > 0, 1, 0)) AS fat_groups_with_assigned_entries,
SUM(IF(apl_remote_type = 'FATAL', entries, 0)) AS fat_entries,
SUM(IF(apl_remote_type = 'FATAL', new_entries, 0)) AS fat_entries_new,
SUM(IF(apl_remote_type = 'FATAL', assigned, 0)) AS fat_entries_assigned,
SUM(IF(apl_remote_type = 'ERROR', 1, 0)) AS err_groups,
SUM(IF(apl_remote_type = 'ERROR' AND new_entries > 0, 1, 0)) AS err_groups_with_new_entries,
SUM(IF(apl_remote_type = 'ERROR' AND assigned > 0, 1, 0)) AS
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1366563 page no 32472 n bits 160 index `PRIMARY` of table `bigeye`.`apl_app_logs` trx id 1041750612 lock mode S locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1366563 page no 32362 n bits 80 index `PRIMARY` of table `bigeye`.`apl_app_logs` trx id 1041750612 lock mode S locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)

[Ralf Jansen]

Das andere beteiligte Statement ist da schon wichtiger (Ein UPDATE in table2).

Inwiefern ist das Beteiligt? Gleiche Transaktion? Wenn in der gleichen Transaktion vor oder nach dem gezeigten Update?
Wenn es eine andere Transaktion ist was passiert noch in der anderen Transaktion?

Mit einem Select und einen Update in verschiedenen Transaktionen sollte man eigentlich keinen Deadlock hinbekommen da muß eigentlich noch mehr passieren. Z.B. Das in der Transaktion in der du Table2 updatest danach nochmal aus Table2 liest.

[Dave_Bowman]

Beteiligt am Deadlock. Ansonsten haben die beiden nichts weiter miteinander zu tun. Das Update auf die apl_app_logs ist das erste Statement von zweien, die ich mit COMMIT abschließe. Da beim Deadlock immer nur dieses erste Statement genannt wird, nehme ich an, dass das zweite an der Stelle keine Rolle spielt, außer, dass es eben in einer nicht abgeschlossenen Transaktion sitzt (tatsächlich ist es keine explizite Transaktion, sondern ein auf einmal zum Server gesandtes Statement. Ist AUTO_COMMIT = 1, und das ist es an der Stelle, bin ich nicht mal sicher, ob es das COMMIT braucht. Oft stelle ich diese beiden SQLs aber in einer Schleife zusammen, wenn mehrere Konstellationen zu behandeln sind, daher das COMMIT, quasi sicherheitshalber. Also:

UPDATE apl_app_logs ...;
DELETE tab3 FROM table3 tab3 INNER JOIN apl_app_logs WHERE ...;
COMMIT

Und das zum Teil mehrmals hintereinander, obwohl ich auch Deadlocks bekomme, wenn es nur einmal abgearbeitet wird.

Das andere UPDATE, das lediglich lesend auf apl_app_logs zugreift, ist ein ziemlich gewaltiges, innerhalb dessen ich auch mehrmals auf apl_app_logs zugreife (SUB-SELECTS), aber dennoch immer nur lesend:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
UPDATE table1 LEFT JOIN (großes, komplexes SELECT * FROM table2) tab2 ON table1.field = tab2.field SET ...
COMMIT;

So sieht's aus. Ich weiß auch nicht, ob ich jedesmal einen Deadlock bekomme, wenn die beiden zufällig zusammen ausgeführt werden. Aber doch ziemlich regelmäßig, vielleicht auch immer, wenn sie eben zufällig zeitgleich ausgeführt werden sollen.

[defo]

Ob Autocommit oder nicht, auch ein Update Statement ist eine Transaktion.
Mir ist das was Du schreibst etwas zu durcheinander, mit "das eine .. das andere".
Ist es Dir schon mal gelungen, den Effekt zu reproduzieren?
Wie lange dauern die Transaktionen?

[Dave_Bowman]

Das komplexe, nur auf der app-Tabelle lesende Statement dauert zwischen 0,5 und 0,7 Sekunden. Das andere UPDATE, das schreibend auf die app-Tabelle zugreift, dürfte sich im selben Rahmen bewegen, wenn es nicht sogar schneller ist.
Und ja, 'das eine', 'das andere' ... ist natürlich doof. Also, einmal ein schnelles kleines UPDATE mit Schreibzugriff auf die entsprechende Tabelle, und ein einmal ein größeres, komplexes UPDATE auf eine andere Tabelle, aber mit Lesezugriff auf die enstprechende Tabelle. Tut mir leid wegen der Verwirrung ...
Was Du mit Reproduktion meinst, weiß ich an der Stelle nicht. Ist ein Live-System, und ich habe das gefühl, dass diese Deadlocks auch häufiger auftreten, wenn mehr auf dem Server los ist. Daher wüßte ich nicht, wie ich das auf einem Testsystem nachstellen, reproduzieren sollte. Um ehrlich zu sein, ich habe nicht mal eine Ahnung, wie diese Deadlocks überhaupt zustande kommen, weil das kurze Schreib-UPDATE im Grunde eine einzige Transaktion ist und das Lese-UPDATE auch, zudem mit dem, soweit ich weiß, geringsmöglichen Locking versehen. Fakt ist nur, ich kriege diese Deadlocks, siehe oben den Auszug aus dem INNODB-Status.

[defo]

Also ich kann mit dem Read Uncommited nichts anfangen. Für mich ist das Bastelei. Sowas habe ich noch nicht gebraucht. Ich arbeite allerdings nicht mit MySQL.
Im Gegenteil, ich würde hier ggF. mal mit stärkeren (als default) isolation levels experimentieren und schauen, ob ich dem deadlock damit auf die Schliche komme. Apropos: Reproduzierbarkeit ist dann gegeben, wenn Du mit einer festen Abfolge von Befehlen den Fehler herbeiführen kannst. Das hilft bei der Ursachenforschung.

Wie ist das mit den TransaktionsID aus dem Log, kann man davon ausgehen, dass die sequentiell aufsteigend entstehen und damit der 2. Block (Transaktion) älter ist, als der zuerst aufgeführte?

Zum Ausprobieren (auch wenn es nicht so richtig Sinn macht): Im Subselect "for update" einbauen um eine exclusive Sperre auf der Quelltabelle erzwingen (auch wenn es eigentlich nicht gebraucht wird)