[verständnisfrage] tabelle blokieren bei update Statement

nehmen wir an ich habe ein update-statement das sehr lange braucht.

UPDATE table set feld = x where id in (select ...)

sagen wir die where clause liefert mir 1000 ids, was bedeuete das ich 1000 einträge in meiner tabelle aktualisieren muss. jedes update dauert 10 sekunden, dh 1000*10 = 10.000 sekunden.

wenn ich das richtig verstehe wäre in diesem fall die tabelle "table" 10.000 sekunden lang blokiert?

sollte die datenbank da nicht irgendwelche zugriffszeiten (maximal blokierzeiten) für tabellen haben, damit andere auch zwischendurch auf die tabelle können? so ne art zwischenspeichern für das update statment

natürlich könnte man das ganze auch selbst machen indem man das update statement aufsplittet. also zb erst das was in der where clause steht und dann über das ergebnis iterieren und dann eben 1000 update statements. somit hat man "lücken" in seinem update die andere nutzen können

kann mir jemand erklären wie genau das ist bzw funktioniert? also muss ich mich selbst darum kümmern oder kann das auch die datenbank für mich machen?

besten dank im vorraus

nman

Du gehst davon aus, dass bei einem Update die ganze Tabelle gelockt wird. Das passiert heutzutage aber nichtmal mehr bei MySQL.

Typischerweise wird MVCC oder row-level locking verwendet.

erstmal danke für die antwort.

ehrlich gesagt hab ich keine ahnung. ich bin schon davon ausgegangen, dass die datenbank nicht 10.000 sekunden lang blockiert wird, wusste aber eben nicht wie das funktioniert

wenn ich das mit dem MVCC jetzt aber richtig verstehe, dann kostet das performance.
ist es also nicht besser statt dieses einen statements besser 1000 statements zu machen, wenn jedes update-statement in einer eigenen transaktion läuft? (das mit der eigenen transaktion muss halt irgendwie gewährleistet sein)
kommt vermutlich auch stark darauf an, wieviele andere updates es in diesen 10.000 sekunden noch gibt. lesende zugriffe sollten egal sein (wenn ich das richtig verstanden habe)

nman

sqlDAU schrieb:

wenn ich das mit dem MVCC jetzt aber richtig verstehe, dann kostet das performance.

Ja, es braucht natürlich ein wenig Platz und bringt einen gewissen Overhead mit sich. Aber du brauchst es um einige Garantien geben zu können, die du anders nicht geben könntest. Und die Performance wird im Normalfall auch um Größenordnungen besser sein, als sie es mit Locks wäre.

ist es also nicht besser statt dieses einen statements besser 1000 statements zu machen, wenn jedes update-statement in einer eigenen transaktion läuft? (das mit der eigenen transaktion muss halt irgendwie gewährleistet sein)

Aber du willst doch gar nicht jedes Update in einer eigenen Transaktion durchführen. Alle Updates gemeinsam sollen entweder durchgeführt oder nicht durchgeführt werden und von außen soll man das als ein atomares Update sehen.

Ich glaube du stellst dir auch Updates viel teurer vor, als sie sind. Ich habe noch nie ein Update gesehen, das zehn Sekunden(!) gebraucht hätte. Im Normalfall wird ein Update von 1000 Datensätzen eher irgendwas in der Größenordnung von deutlich unter einer Sekunde brauchen.

Ich bin mir nicht sicher, wo die Verständnisschwierigkeiten gerade liegen, aber mit Transaktionen und MVCC stellt man (vereinfacht gesagt) sicher, dass es relativ egal ist, wie lange ein Update dauert. Von außen sieht es so aus, als wäre das Update der 1000 Datensätze eine Operation, die ohne jegliche Verzögerung auf einmal durchgeführt wird.

ja, das ganze ist natürlich erstmal nur theoretischer natur. ein update-statement wird in der regel nie 10 sekunden dauern. wenn das ganze aber kein einfaches set spalte = 1234 ist sondern das "1234" nochmal ein select sum mit joins, subselects etc pp welches selbst schon 1-2 sekunden dauert und man das ganze über ein paar tausend rows macht, dann wird das update-statemnt insgesamt eben auch ein paar minuten dauern.

ich glaube auch das ich das mit dem rowlock und mvcc falsch verstanden habe. wenn in einer transaktion die row 4711 upgedated wird, dann ist diese row für die dauer der transaktion gelockt. man kann zwar selects auf die row machen, aber eben kein weiters update. innerhalb der gleichen transaktion könnte man ein update auf row 4711 machen.

was ist aber dann in einem solchen theoretischen fall best practise? ich bin eigentlich davon ausgegangen, dass die datenbank das schon irgendwie für mich macht. tut sie aber dann scheinbar doch nicht, weshalb man wohl kompromisse eingehen muss wie zb auf mehrere transaktionen aufteilen oder die datenstruktur ändern etc pp

nman

Tut sie doch. Selbst wenn gerade irgendwas gelockt sein sollte, braucht doch einfach nur dein nächstes Update entsprechend länger, weil die DB damit wartet, bis der Lock wieder weg ist.

Vielleicht verstehe ich dein Problem auch einfach nur deswegen nicht, weil ich im Alltag nie Schwierigkeiten mit einem vergleichbaren Szenario hatte.

hustbaer

Ich glaub er will wissen was man macht wenn man Statements hat die wirklich stundenlang dauern.
Und dann teilt man die - wenn möglich - natürlich auf in mehrere kleinere. Auch deswegen weil es gar nicht wünschenswert wäre wenn eine Transaktion stundenlang läuft und dann fehlschlägt - und wiederholt werden muss.

Ist aber nicht weiter tragisch, da sowas selten genug vorkommt. Und meist sind es dann keine Statements wo man sämtliche ACID Garantien über das gesamte Statement braucht, und es daher leicht in kleinere Statements aufsplitten kann.

Shade Of Mine

vorallem weil wenn das select ewig dauert, ist solange ja die update table dennoch benutzbar.

denke das passt jetzt schon irgendwie zum verständnis. es läuft letztendlich auf das hinaus was hustbaer schreibt. das sind alles sehr spezielle theoretische fälle die so wohl eher selten bis nie in der realität vorkommen werden. und wenn doch dann wird es (normalerweise) immer wege geben es anders zu machen.

mir ist/war das problem der locks halt nicht so wirklich bewusst, weil ich eigentlich immer dachte das funktioniert schon irgendwie, die datenbank macht das schon irgendwie für mich, dauert halt länger oder was auch immer.

wenn es aber nur lange genug dauert, dh "genügend warten müssen", dann gehen mir irgendwann die verbindungen zur datenbank aus und somit wird es eben doch zum problem.
wie gesagt, alles sehr spezielle theoretische probleme.

Shade Of Mine

Ja, die DB macht das schon irgendwie für dich.
Probleme gibt es hier in der Praxis keine - zumindest sind die Lösungansätze wenn es in der Praxis zu so einem Problem kommen soll idR mit kaputtem Design zu Begründen.

Weiters lässt sich bei Datenbanken sehr vieles auch durch Hardware lösen. Einfach einen stärkeren Cluster einsetzen und das Problem ist mal wieder 5-6 Jahre in die Zukunft verschoben.

PS:
Das "Problem" bei Datenbanken sind die Selects. Nicht die Updates. Natürlich können Updates auch performance ziehen wenn viele Schlüssel und Indices angepasst werden müssen, aber das ist idR eher selten der Fall - vor allem da man auch sehr selten massig updates macht.

Das "Problem" sind eher die Select abfragen. Die oft Joins über mehrere Tabellen sind, uU full table scans verlangen, etc. und dadurch recht langsam werden.

Ich habe zB eine DB wo das Insert ein Problem ist, da es sehr viele Indices sind die sich updaten müssen. Dieses insert findet aber nur einmal in der Woche statt - und da ist das System halt für 30 Minuten offline. Wenn ich es nicht offline nehmen könnte, würde ich einfach mehr RAM in die Kiste packen, das würde das Problem auch lösen. Hardware löst die meisten DB Probleme.

sqlDAU schrieb:

denke das passt jetzt schon irgendwie zum verständnis.... (...GANZ VIELE KLEINBUCHSTABEN)

Noch ein wichtiger Hinweis: Du hast auf der Tastatur gleich 2 Tasten, mit denen Du Großbuchstaben erzeugen kannst. Geeignet in Deine Frage eingestreut, erhöht die Lesbarkeit und damit die Lust, Dir zu helfen.