delete von 100 Mill. Datensätze

[tt0ny]

Hallo Datenbank Spezialist,

ein bsp: delete from users where member_id in (select member_id ...)
Es sind 100 Million members zu löschen. Meine Frage ist werden die 100 Million member_ids direkt alle gelockt ?
oder nur blockweise gesperrt (mehrere commit)? spielt hier die Locksize eine Rolle ? Kann es zur Phantom Read kommen wenn das delete Statement sehr lange braucht ?

DB ist IBM i

Vielen Dank
tt

[BenderD]

... wenn der delete unter commit läuft, werden sukzessive alle Sätze gesperrt und beim commit dann freigegeben; locksize ist da wurscht. Wenn ein anderer Prozess dabei mit uncommited read liest, kann er auch phantom reads bekommen. Je nach commit level kann es zu länger andauernden Sperren kommen, bzw. Transaktionsabbruch. Maximale Leistung bekäme man bei paralleler Ausführung von Segmenten, das muss aber dann korrekt designed werden, damit keine Sätze stehen bleiben, was auch passieren kann, wenn das Sperrlevel des löschenden Jobs kleiner als read stability ist.

D*B

[tt0ny]

Vielen Dank für Antwort. Was bedeutet genau sukzessiv sperren(in Zeit) wenn das delete Statement Tage braucht ?
Kann in dieser Zeit die Auswahlkriterien durch update manipulieren werden ? und der delete job davon nichts mitbekommen? Wenn ich richtig informiert bin ist DB2 für IBM i Commit(*all) oder RS als Standard Isolation Level eingestellt.

[B.Hauser]

Sukzessive gesperrt heißt, dass der Datensatz erst in dem Moment gesprerrt wird, wenn er gelesen/gelöscht wird. So könnte der 100 Millionste Datensatz noch gelesen und geändert werden, wenn die Delete-Orgie bereits gestartet ist.
Wenn Du Commit(*ALL) einstellst geht bei Deiner Aktion überhaupt nichts mehr, da selbst die Datensätze, die nur gelesen und nicht gelöscht werden gelockt werden.
Je nach dem mit welchem Tool Du arbeitest ist entweder kein Commit oder Commit *CHG per Default eingestellt. Change heißt, solange die Änderung noch nicht mit COMMIT bestätigt wurde, kann der geänderte Datensatz durch keine andere Aktion erneut geändert werden. Wird der geänderte Datensatz nur gelesen, so ist die Änderung sichtbar.
Höhere Commit Level sind schwierig zu handeln und benötigen eine exakte Planung.

Birgitta

Birgitta

[TheDevil]

Hallo.
Man möge mich hier verbessern wenn ich falsch liege. Aber wenn z. B. die Datei
im Parameter FRCRATIO = 1 und dazu noch eine nicht geringe Anzahl von LF´s vorhanden
ist kann das mit dem löschen ewig dauern. Und evtl. kann es dann ja auch zu Zugriffen
auf Datensätze kommen die eigentlich schon längst nicht mehr vorhanden sein sollten oder ?
Gruß
Ralf

[BenderD]

... also erst einmal sollte das delete statement nicht Tage dauern, eher Stunden. Standard isolation ind DB2/400 ist *CHG (uncommitted read), was hier erst mal bedeutet, dass alle bereits gelöschten Sätze eine update verhindernde Sperre haben. Alles was gelesen ist, hat keine Sperre, das subselect kann sich also während der delete Operation in alle Richtungen ändern, was dazu führen kann, dass Sätze nicht gelöscht werden oder Sätze zum löschen nicht mehr gefunden werden. Wenn man im Sperrlevel auf Cursor stability (nennt sich sinnigerweise *ALL) hochgeht muss man im konkurrierenden Betrieb mit reichlich Kollisionen rechnen, je nachdem woher der Subselect gezogen wird auch in einer nicht tangierten Tabelle. Im übrigen kann man davon ausgehen, dass eine Sicherungsoperation Probleme bekommt, wobei Save while active hier am schlechtesten wäre, der geht garnicht.
Je nach Anforderung wäre hier Segmentierung des subselects in jedem Fall anzuraten, oder man zieht ein Substract und arbeitet dieses per Programm in Einzeltransaktionen (lesen substract, löschen Satz, löschen Satz in Substract, commit oder rolback, je nachdem). Bei dieser Logik kann man das Programm bei richtiger Transaktionslogik (keine Sperreskalation auf das substract!) mehrfach parallel submitten - relativ einfach und schnell.

D*B

[BenderD]

Je nach dem mit welchem Tool Du arbeitest ist entweder kein Commit
Birgitta

commit *none bei Bulk Statements mit Fortschreibung sind ein ernsthafter Kunstfehler, beim Abbruch bleiben dann undefinierte Zustände übrig!!!

D*B

[tt0ny]

In einem konkretes Problem. Alle Gast-User sollen gelöscht werden die noch nie bestellt haben. Jeder Gast-user hat eine member_id. Wie gesagt dauert das delete mehrere Tagen. Der Gast-user kommt in dieser Zeit wieder und bekommt die gleicher member_id zugewiesen und bestellt. Ergebniss der Gast-user wird gelöscht obwohl er nicht mehr zur Auswahlkriterien gehört (schon einmal bestellt). Kannt es so vorkommen ?

[BenderD]

Unter Commit level *all ist das Transaktionssicher, vorausgesetzt der Bestellprozess macht nicht nur einen dirty read, benutzt also ebenfalls Transaktions Steuerung mit minimalem Sperrlevel read committed. Der Preis wäre dann, dass die Transaktion mit der Bestellung mit timeout abbricht.
Verwendet der Löschprozess ein niedrigeres Sperrlevel, ist das nicht transaktionssicher, das selbe gilt, wenn der Bestellprozess mit commit *none arbeitet, letzteres selbst dann wenn man den Löschprozess transaktionssicher macht.
Wie macht man das transaktions sicher:
- Der Bestellprozess arbeitet mit read committed.
- Der Löschprozess (hier braucht es ein Programm!!!) arbeitet am einfachsten ebenfalls unter commit und liest über einen dynamic scroll cursor (der wird automatisch aktualisiert, wenn das resultset von updates tangiert ist), prüft und löscht einen Satz und beendet die Transaktion mit commit, wenn das erfolgreich war, ansonsten rollback. Das ganze in einer Schleife, bis der Cursor durch ist.

Zur Dauer ist noch zu sagen, dass die Tage mir zu lange vorkommen. Ich habe für die Löschung von 30 Millionen von 900 Millionen Sätzen etliche Stunden in Erinnerung (die wir dann letztlich in den Bereich unter einer Stunde gedampft haben, dafür musste man allerdings schon in die Trickkiste greifen, was im concurrent mode nicht alles möglich gewesen wäre).

D*B

[holgerscherer]

mit einem SQL-Zweizeiler wird es kritisch, wie schon beschrieben. Kannst Du nicht ein Statusfeld verwenden, das erst mal programmatisch gesetzt wird, z.B. von 1=Active auf 0=Inactive - das sollte in der Auftragserfassung natürlich berücksichtigt werden. Dann Löschen nach Auswahl auf diesem Statusfeld.

-h

[BenderD]

mit einem SQL-Zweizeiler wird es kritisch, wie schon beschrieben. Kannst Du nicht ein Statusfeld verwenden, das erst mal programmatisch gesetzt wird, z.B. von 1=Active auf 0=Inactive - das sollte in der Auftragserfassung natürlich berücksichtigt werden. Dann Löschen nach Auswahl auf diesem Statusfeld.

-h

... das ändert die Transaktionsproblematik in keiner Weise, sondern verschiebt sie bestenfalls in das setzen des Statusfeldes.

D*B

[Fuerchau]

FRCRATIO(1) ist der absolute Tod von schnellen SQL-Lösungen.
Mit *NONE erreicht man durchaus Faktor 100 oder mehr.
Durch RAID, Cachebatterie, Notstromversorgung und sonstige Maßnahmen kann man auf FRCRATIO(1) verzichten.
Bei mir dauerte ein CHGPF mit Quelle (ein neues Feld wurde benötigt) bei FRCRATIO(1) schon 30 Minuten und hatte mal gerade 7 Mio-Sätze kopiert. Für 100Mio war also 1 Tag zu erwarten.
Das habe ich abgebrochen, einen CHGPF FRCRATIO(*NONE) und anschließend die Feldänderung. Das dauerte dann noch knapp 15 Minuten.