webentwicklung-frage-antwort-db.com.de

Wie man mysql vermeidet 'Deadlock beim Versuch, eine Sperre zu erhalten; Versuchen Sie, die Transaktion erneut zu starten. '

Ich habe eine innoDB-Tabelle, die Online-Benutzer aufzeichnet. Sie wird bei jeder Seitenaktualisierung von einem Benutzer aktualisiert, um zu verfolgen, auf welchen Seiten er sich gerade befindet, und sein letztes Zugriffsdatum auf die Website. Ich habe dann einen Cron, der alle 15 Minuten läuft, um alte Datensätze zu löschen.

Ich habe ein Deadlock gefunden, als ich versuchte, das Schloss zu bekommen; Starten Sie die Transaktion gestern für etwa 5 Minuten erneut. Dies scheint, wenn Sie INSERTs in diese Tabelle ausführen. Kann jemand vorschlagen, wie dieser Fehler vermieden werden kann?

=== BEARBEITEN ===

Hier sind die Abfragen, die ausgeführt werden:

Erster Besuch der Website:

INSERT INTO onlineusers SET
ip = 123.456.789.123,
datetime = now(),
userid = 321,
page = '/thispage',
area = 'thisarea',
type = 3

Bei jeder Seitenaktualisierung:

UPDATE onlineusers SET
ips = 123.456.789.123,
datetime = now(),
userid = 321,
page = '/thispage',
area = 'thisarea',
type = 3
WHERE id = 888

Cron alle 15 Minuten:

DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND

Dann werden einige Statistiken gezählt (einige Mitglieder online, Besucher online).

228
David

Ein einfacher Trick, der bei den meisten Deadlocks helfen kann, ist das Sortieren der Vorgänge in einer bestimmten Reihenfolge.

Sie erhalten einen Deadlock, wenn zwei Transaktionen versuchen, zwei Sperren bei entgegengesetzten Aufträgen zu sperren, dh:

  • verbindung 1: Sperrt Schlüssel (1), Sperrt Schlüssel (2);
  • verbindung 2: Sperrt Schlüssel (2), Sperrt Schlüssel (1);

Wenn beide gleichzeitig ausgeführt werden, sperrt die Verbindung 1 den Schlüssel (1), die Verbindung 2 den Schlüssel (2) und jede Verbindung wartet, bis die andere den Schlüssel freigibt -> Deadlock.

Wenn Sie nun Ihre Abfragen so geändert haben, dass die Verbindungen die Schlüssel in derselben Reihenfolge sperren würden, z.

  • verbindung 1: Sperrt Schlüssel (1), Sperrt Schlüssel (2);
  • verbindung 2: Sperrtaste ( 1 ), Sperrtaste ( 2 );

es wird unmöglich, einen Stillstand zu erreichen.

Das ist, was ich vorschlage:

  1. Stellen Sie sicher, dass keine anderen Abfragen vorhanden sind, die den Zugriff auf mehr als einen Schlüssel gleichzeitig mit Ausnahme der Anweisung delete verhindern. Wenn Sie dies tun (und ich vermute, dass Sie es tun), ordnen Sie WHERE in (k1, k2, .. kn) in aufsteigender Reihenfolge an.

  2. Korrigieren Sie Ihre Löschanweisung in aufsteigender Reihenfolge:

Veränderung 

DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND

Zu

DELETE FROM onlineusers WHERE id IN (SELECT id FROM onlineusers
    WHERE datetime <= now() - INTERVAL 900 SECOND order by id) u;

Beachten Sie auch, dass die Dokumentation von mysql darauf hinweist, dass der Client im Falle eines Deadlocks automatisch einen erneuten Versuch starten sollte. Sie können diese Logik zu Ihrem Client-Code hinzufügen. (Sagen Sie 3 Wiederholungen zu diesem bestimmten Fehler, bevor Sie aufgeben).

244
Omry Yadan

Deadlock tritt auf, wenn zwei Transaktionen aufeinander warten, um eine Sperre zu erhalten. Beispiel:

  • Tx 1: A und dann B sperren
  • Tx 2: verriegeln Sie B, dann A

Es gibt zahlreiche Fragen und Antworten zu Deadlocks. Jedes Mal, wenn Sie eine Zeile einfügen, aktualisieren oder löschen, wird eine Sperre aktiviert. Um Deadlocks zu vermeiden, müssen Sie sicherstellen, dass gleichzeitige Transaktionen keine Zeilen in einer Reihenfolge aktualisieren, die zu einem Deadlock führen kann. Im Allgemeinen gilt: versuchen Sie, die Sperre immer in derselben Reihenfolge abzurufen auch bei verschiedenen Transaktionen (z. B. immer zuerst Tabelle A, dann Tabelle B).

Ein weiterer Grund für ein Deadlock in der Datenbank kann fehlende Indizes sein. Wenn eine Zeile eingefügt/aktualisiert/gelöscht wird, muss die Datenbank die relationalen Einschränkungen überprüfen, dh sicherstellen, dass die Beziehungen konsistent sind. Dazu muss die Datenbank die Fremdschlüssel in den zugehörigen Tabellen überprüfen. Es kann dazu führen, dass eine andere Sperre als die geänderte Zeile abgerufen wird. Stellen Sie dann sicher, dass Sie immer einen Index für die Fremdschlüssel (und natürlich für die Primärschlüssel) haben, da dies sonst zu einer Tabellensperre anstelle einer Zeilensperre führen kann. Wenn eine Tabellensperre auftritt, ist der Sperrenkonflikt höher und die Wahrscheinlichkeit eines Deadlocks steigt.

68
ewernli

Es ist wahrscheinlich, dass die delete -Anweisung einen großen Teil der gesamten Zeilen in der Tabelle beeinflusst. Dies kann dazu führen, dass beim Löschen eine Tabellensperre angefordert wird. Das Festhalten an einer Sperre (in diesem Fall Zeilen- oder Seitensperren) und das Erhalten weiterer Sperren ist immer ein Deadlock-Risiko. Ich kann jedoch nicht erklären, warum die insert -Anweisung zu einer Eskalation der Sperre führt - dies kann mit dem Teilen/Hinzufügen von Seiten zu tun haben.

Zunächst kann es sich lohnen, explizit eine Tabellensperre für die delete-Anweisung zu erwerben. Siehe LOCK TABLES und Table-Sperrprobleme .

8
Anders Abel

Sie können versuchen, den delete-Job auszuführen, indem Sie zunächst den Schlüssel jeder zu löschenden Zeile in eine temporäre Tabelle wie diesen Pseudocode einfügen

create temporary table deletetemp (userid int);

insert into deletetemp (userid)
  select userid from onlineusers where datetime <= now - interval 900 second;

delete from onlineusers where userid in (select userid from deletetemp);

Ein derartiges Aufbrechen ist weniger effizient, aber es wird vermieden, dass während der delete eine Tastensperre gehalten werden muss. 

Ändern Sie auch Ihre select-Abfragen, um eine where-Klausel hinzuzufügen, die keine Zeilen enthält, die älter als 900 Sekunden sind. Dies vermeidet die Abhängigkeit von dem Cron-Job und ermöglicht es Ihnen, den Zeitplan so zu ändern, dass er weniger häufig ausgeführt wird. 

Theorie über die Deadlocks: Ich habe nicht viel Hintergrundwissen in MySQL, aber hier ist ... Die delete hält eine Schlüsselbereichssperre für datetime, um zu verhindern, dass Zeilen, die ihrer where-Klausel entsprechen, in der Mitte von hinzugefügt werden Wenn Sie die zu löschenden Zeilen finden, versucht sie, für jede zu ändernde Seite eine Sperre zu erlangen. Die Variable insert erhält eine Sperre für die Seite, in die sie eingefügt wird, und then versucht, die Tastensperre zu erhalten. Normalerweise wartet insert geduldig auf das Öffnen der Tastensperre, dies wird jedoch Deadlock, wenn delete versucht, dieselbe Seite zu sperren, die insert verwendet, da delete diese Seitensperre und insert diese Tastensperre benötigt. Dies scheint jedoch für Einfügungen nicht richtig zu sein. Die Variablen delete und insert verwenden Datumszeitbereiche, die sich nicht überschneiden, sodass möglicherweise etwas anderes passiert.

http://dev.mysql.com/doc/refman/5.1/de/innodb-next-key-locking.html

6
Brian Sandlin

Für Java-Programmierer, die Spring verwenden, habe ich dieses Problem mit einem AOP-Aspekt vermieden, bei dem Transaktionen, die vorübergehende Deadlocks ausführen, automatisch wiederholt werden.

Weitere Informationen finden Sie unter @RetryTransaction Javadoc.

1
Archie

Falls noch jemand mit diesem Problem zu kämpfen hat:

Ich hatte ein ähnliches Problem, bei dem zwei Anforderungen gleichzeitig auf den Server fielen. Es gab keine Situation wie unten:

T1:
    BEGIN TRANSACTION
    INSERT TABLE A
    INSERT TABLE B
    END TRANSACTION

T2:
    BEGIN TRANSACTION
    INSERT TABLE B
    INSERT TABLE A
    END TRANSACTION

Also war ich verwirrt, warum es zu Deadlocks kommt. 

Dann stellte ich fest, dass zwischen zwei Tabellen ein Eltern-Kind-Verhältnis aufgrund eines Fremdschlüssels bestand. Beim Einfügen eines Datensatzes in eine untergeordnete Tabelle erlangte die Transaktion eine Sperre für die Zeile der übergeordneten Tabelle. Unmittelbar danach versuchte ich, die übergeordnete Zeile zu aktualisieren, die die Erhöhung der Sperre auf EXCLUSIVE löste. Da die zweite gleichzeitige Transaktion bereits eine SHARED-Sperre hatte, führte dies zu einem Deadlock.

Siehe: https://blog.tekenlight.com/2019/02/21/database-deadlock-mysql.html

0
chatsap

Ich habe eine Methode, deren interne Elemente in eine MySqlTransaction eingeschlossen sind.

Die Deadlock-Frage zeigte sich für mich, als ich dieselbe Methode parallel mit sich selbst ausführte.

Es gab kein Problem bei der Ausführung einer einzelnen Instanz der Methode.

Als ich MySqlTransaction entfernte, konnte ich die Methode ohne Probleme parallel mit sich selbst ausführen.

Ich teile nur meine Erfahrungen mit, ich befürworte nichts.

0
CINCHAPPS