Parallele Updates Locken sich gegenseitig

[Biggreuda]

Hallo zusammen,

ich bin neu sowohl hier im Forum wie auch im Beruf des Anwendungsentwicklers.
Jetzt habe ich gleich zu Beginn meiner Ausbildung die Chance bekommen, ein interessantes Projekt umzusetzen.
Dabei geht es um die Überprüfung einer Replikation von diversen Tabellen.
Im Zuge dieses Projektes habe ich mir jetzt ein wenig PL/SQL beigebracht und mein Script ermittelt auch die entprechenden Tabellen um sie zu überprüfen.

Ein großes Problem waren dann die größeren Tabellen, welche bis zu 20 Gigabyte groß sind. Dafür habe ich eine Prozedur entwickelt, die zuerst anhand diverser Kriterien einzelne Segmente ermittelt, in die diese größeren Tabellen sozusagen zerlegt werden können.

Dann werden zugunsten der Geschwindigkeit mehere Jobs leicht versetzt, aber sich überschneidend gestartet, welche die einzelnen Segmente jeweils parallel überprüfen.

Mein Problem ist nun, dass sich diese einzelnen Jobs gegenseitig keine Daten zuschicken können, und ich auch keine globalen Variablen benutzen kann/darf/soll. Also lasse ich einfach eine temporäre Tabelle erzeugen, welche immer nur eine Zeile enthält, in der jeweils die entsprechenden Daten stehen, wie z.B. welches Segent als nächstes geprüft werden muss. Die einzelnen Jobs nehmen sich beim Start dann diesen Wert und setzen ihn im Anschluss sofort höher, damit der nächste Job dann nicht das selbe Segment scannt.

Das Problem ist jetzt nur, dass sich die Jobs gegenseitig zu locken scheinen. Jedenfalls sehe ich nach kurzer Zeit immer, dass die Jobs alle mit einem Lock versehen sind und auch gleichzeitig versuchen die temporäre Tabelle zu "updaten" (mir fällt doch tatsächlich kein vernünftiger Begriff dafür ein).

Ich habe auch google bemüht, aber entweder mein Problem ist hier ein ganz besonderes, oder aber ich suche nach den falschen Wörtern.

Es wäre wirklich klasse wenn mir jemand dazu einen Tipp geben könnte, in der Firma ist gerade sowohl Urlaubsphase, als auch einiger Stress wegen diversen anderen Problemen, so dass ich momentan einfach nicht die Möglichkeit habe jemand von den Kollegen zu fragen.

[dimitri]

Committest Du denn auch die Änderung in dieser temporären Tabelle? Ansonsten warten die anderen natürlich darauf, dass die Transaktion beendet wird.

Ganz glücklich finde ich die Mthode allerdings nicht. Hast Du die Möglichkeit die Segmente schon vorher festzulegen? Wir verwenden dazu z.B. immer eine Ziffer in einer Nummer die die Menge in etwa gleich große Teile aufteilt.
Z.B. SELECT * from tab where einenummer like '%1_' wären alle Daten, die an der Vorletzten Stelle eine 1 haben.
Das ganze durchnummeriert von 0-9 ergibt wieder die Komplette Menge. Damit hast Du vor dem Start der Jobs schon festgelegt, wer was machen soll.

Dim

[Biggreuda]

Wow, die Antwort kam schnell!

Ja, ein Commit hängt immer hinten dran.

Die von dir erwähnte Methode hatte ich zuerst auch ausprobiert. Das Problem dabei ist aber, dass ich damit das ganze in höchstens 10 Segmente zerlegen könnte. Damit dauert die Überprüfung bei den sehr großen Tabellen auch noch sehr lange. Außerdem würden sie die Tabelle auch komplett überprüfen, wenn irgendwo ein Fehler/Unterschied gefunden werden sollte. Bei mir habe ich halt in der Temporären Tabelle auch ein Feld für die gefundenen Fehler. Findet ein Job Fehler, so werden diese gleich in die Spalte eingetragen und alle anderen Jobs starten sich nicht mehr neu, falls sie dort einen Wert finden, sondern schicken gleich eine Mail an den Verantwortlichen (bzw. der erste davon).

Hmm, es ist schon schwierig zu erklären was mein Package alles macht und Datenbanken kommen in der Schule auch erst im nächsten Jahr... :/

[dimitri]

Das Problem dabei ist aber, dass ich damit das ganze in höchstens 10 Segmente zerlegen könnte

Nein Du kannst das in beliebig viele Teile zerlegen, wenn Du pro Blockmehr Ziffern in die Abfrage nimmst. Also ...LIKE '%00', ...LIKE '%10' etc.

Da ich selbst hier seit vielen Jahren Daten verteile und das ganze auch parallel ablaufen muss, kann ich Dir nur raten das Aufteilen der Blöcke und das Abarbeiten zu trennen. Fehler können ebenfalls über diese Tabelle mitgeteilt werden.

Zu Deinem aktuellen Problem: Wenn danach ein Commit kommt, muss anschließend eine andere Session dran kommen bis alle durch sind. Warten denn alle Sessions oder läuft eine weiter? Falls alle warten, dann hat jemand ganz anderes noch eine offene Transaktion und blockiert das Update.

Dim

[Biggreuda]

Okay, am besten fasse ich noch mal kurz zusammen.
Die zu überprüfenden Tabellen werden erst mal zusammen mit allen benötigten Daten ermittelt.

Dann werden diese einzeln nacheinander abgearbeitet.
Sollten sie unter einer bestimmten Größe liegen, dann wird ein einfaches MINUS ausgeführt. Bei zu großen Tabellen wird normalerweise der höchstfavourisierte Primary Key der auf einer Spalte mit NUMBER-Werten liegt ermittelt und anhand dessen werden die erwähnten Segmente sowie auch gleich deren Größe erstellt.

Danach wird eine Procedure aufgerufen, welche zuerst die temporäre Tabelle erstellt und mit Werten füllt, bevor sie per dbms_job.submit leicht verzögert die einzelnen Jobs startet. Diese einzelnen Jobs gucken zuerst in die temporäre Tabelle, was sie zu tun haben und ob evtl. schon Differenzen gefunden werden. Wenn keine Differenzen in der Tabelle vorhanden sind, dann überprüfen sie ihren Teil.
Wenn sie Differenzen finden, dann schreiben sie dies entsprechend in die Tabelle und schicken dann eine Mail raus. Finden sie keine, so schauen sie in der temp-Tabelle nach, an welcher Stelle die Überprüfung gerade ist und ob noch was über ist. Falls ja, so starten sie sich praktisch selber wieder neu.

Damit ist halt hauptsächlich geplant Performance zu sparen, besonders wenn es Fehler gab.
Man kann es sich so vorstellen, dass 8 Prozesse unabhängig voneinander sich die ganze Tabelle stück für Stück vornehmen. Sollte einer was finden, so schreit er und alle anderen machen ihr Segment fertig, hören danach aber auf.
So schlecht finde ich meinen Plan gar nicht, das einzige Problem ist halt nur der erwähnte Lock. Der kommt übrigens auch nicht immer, aber sicher reproduzieren konnte ich den Fehler leider noch nicht.

[dimitri]

Hast Du dir schon mal überlegt, Trigger auf die Tabellen zu setzen, die in einer Logtabelle den Namen der Tabelle, den PK und die Art der Änderung protokolieren?
Damit brauchst Du nicht zig GB abzugleichen, sondern hast direkt eine Änderungshistorie und weißt welche Sätze betroffen sind.

Wegen den Locks müsste man sich wohl den Code ansehen, das scheint mir irgendein Problem im Ablauf zu sein.

Dim

[Biggreuda]

Sorry, aber mit Triggern kenne ich mich nocht gar nicht aus.
AUßerdem gehörte es zur Auflage des Projektes, dass keine zusätzliche Tabelle mehr angelegt werden sollte. Mit der temporären Tabelle bin ich da schon an der Grenze.
Das Script soll auch nur sehr selten und unter verschiedenen Bedingungen ausgeführt werden.

Das Package habe ich auch geschrieben während ich PL/SQL gelernt habe, daher ist es evtl. etwas chaotisch, auch wenn ich daran arbeite es aufzuräumen und zu optimieren. Bin trotzdem noch bei über 1600 Zeilen...
Aber ich schaue mal, dass ich die entsprechenden Stellen aus dem Code kopiere.

Vielen Dank auf jeden Fall schon mal für die Hilfe!

PROCEDURE SEGMENT_CHECK (p_temp_schema   IN VARCHAR2,
                            p_temp_table    IN VARCHAR2,
                            p_run_schema    IN VARCHAR2)
   IS
   
   BEGIN
      DECLARE
         v_differences     NUMBER := 0;
         v_already_found   NUMBER := 0;
         v_where           VARCHAR2 (512);
         v_max             NUMBER := 0;
         num_job           NUMBER := 0;
         v_job             VARCHAR2 (1024);
         send              EXCEPTION;
         no_table          EXCEPTION;
         v_subject         VARCHAR2 (70) := NULL;
         v_message         VARCHAR2 (4098) := NULL;
         new_pk            NUMBER := 0;
         v_table_there     NUMBER := 0;
         v_schema          VARCHAR2 (32);
         v_name            VARCHAR2 (40);
         v_min             NUMBER;
         v_recipient       VARCHAR2 (128);
         v_segment         NUMBER;
         v_pk_complete     NUMBER;
         v_column          VARCHAR2 (64);
         v_columns         VARCHAR2 (2048);
         v_size            VARCHAR2 (32);
         v_rows            VARCHAR2 (32);
         a_protocol        VARCHAR2 (4096) := NULL;
         v_percent         VARCHAR2 (32) := NULL;
      BEGIN
         -- The procedures are tending to synchronize, so that they sooner or later all scan the same segments at a time. To avoid that, I included a random delay
         DBMS_LOCK.SLEEP (DBMS_RANDOM.VALUE);

         a_protocol :=
            a_protocol
            || '<ul><li><div border="3">SEGMENT_CHECK started</li>';

         EXECUTE IMMEDIATE 'SELECT NVL(COUNT(*),0) FROM all_tables WHERE owner = '''
                          || p_temp_schema
                          || ''' AND table_name = '''
                          || p_temp_table
                          || ''''
            INTO v_table_there;

         IF v_table_there > 0
         THEN
            EXECUTE IMMEDIATE 'SELECT NVL(SUM(differences),0) FROM '
                             || p_temp_table
               INTO v_already_found;
         ELSE
            RAISE no_table;
         END IF;

         EXECUTE IMMEDIATE   'SELECT T_SCHEMA'
                          || ',T_NAME'
                          || ',PK_MIN'
                          || ',E_MAIL'
                          || ',SEGMENT_SIZE'
                          || ',MAX_PK'
                          || ',CONDITION'
                          || ',PK_COLUMN'
                          || ',T_COLUMNS'
                          || ',T_SIZE'
                          || ',T_ROWS'
                          || ' FROM '
                          || p_temp_schema
                          || '.'
                          || p_temp_table
            INTO v_schema,
                 v_name,
                 v_min,
                 v_recipient,
                 v_segment,
                 v_pk_complete,
                 v_where,
                 v_column,
                 v_columns,
                 v_size,
                 v_rows;

         new_pk := v_min + v_segment + 1;
         v_percent := ROUND (v_min / (v_pk_complete / 100), 2);

         IF v_percent > 100
         THEN
            v_percent := 'Nearly 100%, scanning last block.';
         ELSE
            v_percent := 'ca. ' || TO_CHAR (v_percent) || ' Percent done';
         END IF;

         a_protocol := a_protocol || '<br />Prozent erreicht= ' || v_percent;

         EXECUTE IMMEDIATE   'BEGIN UPDATE '
                          || p_temp_schema
                          || '.'
                          || p_temp_table
                          || ' set pk_min = '
                          || new_pk
                          || ', last_checked = sysdate, COMNT = '''
                          || v_percent
                          || '''; COMMIT; END;';

         v_where := REPLACE (v_where, '''''', '''');

         v_max := v_min + v_segment;

         EXECUTE IMMEDIATE   '    SELECT  case  '
                          || '    FROM ('
                          || ' SELECT  CASE WHEN COUNT(*) < 0 THEN '
                          || '              0 '
                          || '          ELSE '
                          || '           count(1) '
                          || '         END CASE'
                          || '          FROM( '
                          || '        SELECT '
                          || v_columns
                          || '        FROM '
                          || v_schema
                          || '.'
                          || v_name
                          || '         '
                          || v_where
                          || v_column
                          || ' BETWEEN '
                          || v_min
                          || ' AND '
                          || v_max
                          || '        MINUS'
                          || '        SELECT '
                          || v_columns
                          || '        FROM '
                          || '**Hier Steht das Schema der Repliaktion **.'
                          || v_name
                          || '         '
                          || v_where
                          || v_column
                          || ' BETWEEN '
                          || v_min
                          || ' AND '
                          || v_max
                          || '        )) '
            INTO v_differences;

         IF     v_differences = 0
            AND v_min < v_pk_complete
            AND v_max < v_pk_complete
            AND v_already_found = 0
         THEN
            EXECUTE IMMEDIATE   'SELECT PK_MIN FROM '
                             || p_temp_schema
                             || '.'
                             || p_temp_table
               INTO v_max;

            IF v_max < v_pk_complete
            THEN
               v_job :=
                     'BEGIN '
                  || p_run_schema
                  || '.**PACKAGE_NAME**.SEGMENT_CHECK('''
                  || p_temp_schema
                  || ''', '''
                  || p_temp_table
                  || ''', '''
                  || p_run_schema
                  || '''); END;';
               a_protocol := a_protocol || '<br />' || v_job || '<hr />';
               DBMS_JOB.SUBMIT (num_job, v_job, SYSDATE);
            END IF;
         ELSIF v_max >= v_pk_complete
         THEN
            DBMS_LOCK.SLEEP (90);

            EXECUTE IMMEDIATE   'SELECT NVL(SUM(differences),0) FROM '
                             || p_temp_schema
                             || '.'
                             || p_temp_table
               INTO v_already_found;

            v_differences := v_differences + v_already_found;

            RAISE send;
         ELSIF v_differences > 0
         THEN
            EXECUTE IMMEDIATE   'SELECT NVL(SUM(differences),0) FROM '
                             || p_temp_schema
                             || '.'
                             || p_temp_table
               INTO v_already_found;

            IF v_already_found = 0
            THEN
               EXECUTE IMMEDIATE   'BEGIN UPDATE '
                                || p_temp_schema
                                || '.'
                                || p_temp_table
                                || ' SET differences = '
                                || v_differences
                                || ', last_checked = sysdate; END;';

               DBMS_LOCK.SLEEP (90);

               RAISE send;
            ELSE NULL;
            END IF;
         ELSIF v_already_found > 0
         THEN
         NULL;
         END IF;

         a_protocol := a_protocol || '<li>Segment_Check succesfull</li></ul>';

      END;
   END SEGMENT_CHECK;

P.S.: Ich hatte noch ne Menge Exceptions drin, aber die passten nicht mehr rein...

[Biggreuda]

Hallo nochmal,

sorry für den Doppelpost, aber ich denke mal, nach 2 Monaten darf ich
Das ganze Projekt hat sich weiter entwickelt und läuft nun mittlerweile recht gut.
Das Problem mit den Locks habe ich jetzt so lösen können, dass in der temporären Tabelle eine Zeile für jeden parallelen Prozess angelegt wird.

Die einzelnen Jobs haben jetzt eine ID, welche als Parameter mitgegeben wird.
Zusätzlich habe ich noch einen Debugging-Modus mit eingebaut, in welchem die einzelnen Jobs jeweils eintragen, was sie getan haben.
Eigentlich war dies eher dazu da, um zu sehen, ob auch wirklich alle Segmente der Tabelle geprüft wurden.
Nun mein neues Problem, aus dem eventuell auch mein letztes Problem entstanden ist.

In der Debugging-Tabelle konnte ich sehen, dass trotz genau hundert zu überprüfenden Segmenten, 119 Zeilen eingefügt wurden. Obwohl die ersten Jobs mit jeweils meheren Sekunden Abstand gestartet werden, sind sie sehr oft genau gleichzeitig fertig. Dabei scheint die Reihenfolge egal zu sein, Job 1 ist oft gleichzeitig mit Job 3 fertig, Job 16 und 17 treffen auch auf die gleiche Sekunde, etc.
Jetzt gucken diese natürlich gleichzeitig in die temporäre Tabelle, welches Segment als nächstes dran ist, und schnappen sich deshalb das gleiche. Teilweise sind es sogar 3 oder 4 auf einmal, welche das gleiche tun.

Es wäre ja nicht schlimm, wenn diese Jobs nicht teilweise 1/2h und länger brauchen würden und ihnen nur ein begrenztes Zeitfenster zur Verfügung steht.
Gibt es da irgend etwas, was die Jobs der Datenbank irgendwie synchronisiert?

Ein kleines Beispiel aus der Debugging-Tabelle:
JOB_ID | TIME_STARTED | TIME_READY
4 15:52:02 16:03:29
8 15:51:27 16:03:29

Zuerst habe ich es für Zufall gehalten, aber bei einer Trefferquote von 20% behaupte ich einfach mal, dass es selbst für einen Pechvogel wie mich zu viel ist^^

Edit:
Hier mal eine Grafik, wie der aktuelle Job läuft: