Fehler in der SQL-Abfrage (1213): Deadlock found when trying to get lock; try restarting transaction

[Nikolaus]

Vorbemerkung: Ich habe nicht viel Erfahrung mit SQL.

Ich habe unter Debian und MariaDB (neuseste Versionen) folgendes Problem in einem Bash-Script, das Statistiken meiner PV-Anlage erzeugt.
Für die Monatsstatistik werden zunächst einige Views erzeugt und dann eine Tabelle mit monatlichen Werten füllt (momenmtan ca.50 Zeilen).

Das Script läuft nächtlich einmal oder kann aus aus einer Grafana-Statistikseite (ohne Auto-Refresh) gestartet werden, einziger User bin ich
Es gibt kein weiteres Script, das auf diese Tabelle zugreift
Sehr oft (geschätzt 30%) steigt es aus mit o.g. Fehler.

Struktur: Monat varchar(5) NULL mit Werten z.B.: 26 01 ... 26 06

folgender Code:
...
mysql -D Test -B -e "DELETE FROM Monat;"

.... View(s) generieren....

#echo "########### Fill table Monat"
mysql -D Test -B -e "INSERT INTO Monat (DateTime, Date, Monat, Zaehlerstand, Einspeisung, BezugMonat) SELECT DateTime, Date, Monat, Zaehlerstand, Einspeisung
, BezugMonat FROM V_BezugMonat ORDER BY Monat ASC;"

#echo "############ Update EinspeisungMonat BEI DIESEM KOMMANDO TRITT DER FEHLERAUF "
mysql -D Test -B -e "UPDATE Monat SET EinspeisungMonat = (SELECT EinspeisungMonat FROM V_EinspeisungMonat WHERE V_EinspeisungMonat.Monat = Monat.Monat);"
# wegen Zählerwechsel
mysql -D Test -B -e "UPDATE Monat SET EinspeisungMonat = EinspeisungMonat+18870.298000 WHERE Monat > '26 05' ;"
.... usw....

Kann mir jemand sagen, woran es liegt und wie ich es verhindern kann?

 

[ukulele]

Ich weiß noch nicht genau, was du da eigentlich versuchst zu bauen, also was der Sinn dieser Struktur ist ;-)

#echo "########### Fill table Monat"
mysql -D Test -B -e "INSERT INTO Monat (DateTime, Date, Monat, Zaehlerstand, Einspeisung, BezugMonat) SELECT DateTime, Date, Monat, Zaehlerstand, Einspeisung
, BezugMonat FROM V_BezugMonat ORDER BY Monat ASC;"

Du hast eine Tabelle Monat und schreibst da den Inhalt aus der View V_BezugMonat rein - das für sich genommen macht schonmal nur bedingt Sinn. Du duplizierst nur Informationen, die du ja schon in der View hast. Es ist nicht ganz klar, ob es z.B. nur einen Datensatz pro Monat gibt, ein Aggregat wäre an dieser Stelle logisch. Aber wofür dann eigentlich noch ein Zeitstempel mit DateTime und ein Date da drin stehen, erschließt sich mir nicht.

Grundsätzlich kann es sinnvoll sein, die Statistik irgendwo "zwischen zu speichern", z.B. für die Performance. Aber warum gibt es dann gleichzeitig eine Live-View? Wie gesagt, ich versuche es nur zu verstehen

Sortieren musst du hier auch nicht, das macht man immer nur bei der Ausgabe.

#echo "############ Update EinspeisungMonat BEI DIESEM KOMMANDO TRITT DER FEHLERAUF "
mysql -D Test -B -e "UPDATE Monat SET EinspeisungMonat = (SELECT EinspeisungMonat FROM V_EinspeisungMonat WHERE V_EinspeisungMonat.Monat = Monat.Monat);"

Du nimmst die Spalte EinspeisungMonat eines jeden Datensatzes der Tabelle. Von oben wissen wir, das es eventuell zu jedem Monat auch mehrere Datensätze in Monat geben kann. Enthält das Attribut Monat auch das Jahr oder nur einen Monat? Das wäre schon wichtig.

Das mit dem Subselect wird immer dann zu einem Problem wenn der Subselect mehr als einen Wert zurück liefert. Du versuchst jeden Wert in der Spalte = das Ergebnis des Subselects zu setzen. Das klappt hier offenbar, ist aber potentiell ein Problem. Es stellt sich dann auch die Frage, ob es nicht einfacher wäre, jedes Mal die gesamte Statistik / das gesamte Aggregat komplett neu zu erzeugen. Also statt UPDATE ein DELETE und einen INSERT machen.

Kann mir jemand sagen, woran es liegt und wie ich es verhindern kann?

Deadlocks hast du ja vermutlich schon gegoogelt. Du schreibst, es gibt sonst keine Scripte etc. Wahrscheinlich ist, das eine betroffene View auch Informationen aus einer Tabelle nutzt, in die du Daten der View schreiben möchtest. Wir kennen aber den Code der View nicht, da würde ich zuerst mal schauen.

Vermutlich fasst das UPDATE Datensätze an, auf denen die View basiert.

 

[Nikolaus]

Danke für die schnelle Antwort und die konkreten Fragen!
Ich hatte ja schon gesagt, dass ich wenig SQL-Erfahrung habe und daher dem Datenbankexperten evtl. die Haare zu Berge stehen.

Zu dieser Anwendung:
Ich protokolliere für meine PV-Installation folgendes in jeweils getrennten Tabellen (um jeweiliges in Grafana anzuzeigen) mit:
- Strom (Werte von Zählerstand und Einspeisung)
- PV-Anlagendaten
- Daten meines Balkonkraftwerkes

Zudem generiere ich Statistiken (Tages-, Monats- und Jahresstatistik) in jeweils einer getrennten Tabelle.

Hier geht es nun um die Monatsstatistik.
Da ich keine wirklichen Kenntnisse mit komplexen Queries habe erzeuge ich für die einzuelnen Werte
jeweils einen View, aus dem ich dann die Werte in die (Monats-)Tabelle übernehme und anschließend lösche ich die Views.

Prinzip: Ich fülle zunächst die Basis in Monat (erstesKommando) und dann weitere Werte aus den verschiedenen Views

Zu den Kommandos:

mysql -D Test -B -e "INSERT INTO Monat (DateTime, Date, Monat, Zaehlerstand, Einspeisung, BezugMonat) SELECT DateTime, Date, Monat, Zaehlerstand, Einspeisung
, BezugMonat FROM V_BezugMonat ORDER BY Monat ASC;"

Hier fülle ich die Monatstabelle mit einem View (V_BezugMonat), der u.a. den monatlichen Strombezug enthält.
Zu den Fragen:
- Es gibt nur EINEN Eintrag pro Monat (Monat='YY MM')
- Das Date und den Monat brauche ich für Statistikanzeigen in Grafana
- Das DateTime ist nur zur Kontrolle dabei, da ich für den aktuellen Monatseintrag das letzte DateTime nehme.

Bei dem folgenden Kommando, das das Problem macht nehme ich die monatlichen Werte für die Einspeisung ins Netz und
schreibe sie in die Tabelle Monat.

mysql -D Test -B -e "UPDATE Monat SET EinspeisungMonat = (SELECT EinspeisungMonat FROM V_EinspeisungMonat WHERE V_EinspeisungMonat.Monat = Monat.Monat);"

Alle Views haben nur eine Zeile pro Monat und daher die Monats-Tabelle nach dem ersten Kommando ebenfalls.

Der View V_Einspeisung Monat ist im Anhang, die Struktur der Monatstabelle ist unten angehängt.

Tabelle: Monat​

Spalte	Typ	Kommentar
DateTime	datetime
Date	date
Monat	varchar(5) NULL
Zaehlerstand	decimal(10,5)
BezugMonat	decimal(10,3) NULL
Einspeisung	decimal(10,4) NULL
EinspeisungMonat	decimal(12,3) NULL
ErzeugungPVMonat	decimal(10,3) NULL
ErzeugungBKWMonat	decimal(10,3) NULL
Erzeugung_Gesamt	decimal(10,3) NULL
VerbrauchMonat	decimal(10,3) NULL
EinnahmenMonat	decimal(5,2) NULL
AutarkieMonat	decimal(5,2) NULL