Reihenfolge der Updates kontrollieren

Hallo,

ich habe in etwa folgende funktion:

BEGIN;
UPDATE tabletest SET refresh = 1 WHERE id IN(3,2,1);
END;

In welcher Reichenfolge werden meine Datensätze upgedated? Gibt es da eine Regel dafür? Oder kann ich irgendwie beeinflussen in welcher Reihenfolge die Datensätze behandelt werden?

Danke schon mal im Voraus,

kicmad

Wozu? Du siehst nach außen hin eh nur eine TX.

Andreas

Ich habe 2 Funktionen.
Eben die besagte:

BEGIN;
UPDATE tabletest SET refresh = 1 WHERE id IN(3,2,1);
END;

und eine 2.

BEGIN;
UPDATE tabletest SET refresh = = WHERE id = 1;
UPDATE tabletest SET refresh = 0 WHERE id = 2;
END;

Beide Funktionen sind hier in vereinfachter Form dargestellt.
Beide Funktionen können auch gleichzeitig aufgerufen werden. Dabei entstehen Deadlocks. Wenn ich jetzt aber dafür sorgen könnte das in der oberen Funktion die Datensätze der Reihe nach von id 1 bis id 3 upgedated werden würden, könnte ich die Deadlocks verhindern.

Lg, Kicmad

Ähm, nein. Es wird gewartet, die die andere TX beendet ist.

Wenn ich jetzt aber dafür sorgen könnte das in der oberen Funktion die Datensätze der Reihe nach von id 1 bis id 3 upgedated werden würden, könnte ich die Deadlocks verhindern.

2 mal nein.

Andreas

Du könntest LOCKs einsetzen, funktioniert prima, ist aber nicht SQL-Standard konform.
Oder du könntest den Transaktion-Level hochsetzen.

Dann müsstest du deine Anwendung darauf vorbereiten, ggf. eine Aktion noch mal komplett von vorn zu beginnen.

Hallo,

also hier einmal die beiden Funktionen in vollem Ausmaß. Beim gleichzeitigen Aufruf passieren immer wieder Deadlocks. Vielleicht gibt es auch eine einfachere Lösung diese zu verhindern. Bin für jeden Ratschlag dankbar.

Lg, Kicmad

Funktion 1:

CREATE OR REPLACE FUNCTION getbsddakeys(smallint, smallint, smallint)
  RETURNS SETOF record AS
$BODY$
/**
 * This function returns all DA Keys of registered DA Tabs which have to be refreshed and resets their refresh flag.
 *
 * @usage: SELECT * FROM getbsddakeys(1::int2, 0::int2, 3::int2) AS 
 * DEPT(busystatus int2,tabnumber int2, rownumber int2, columnnumber int2, terminalrolenumber int4, refresh int2, 
 * idcommunicationtype int8, localmodecode int4, trainmodecode int4, nrisdn char(20), idregistereddatabs int8);
 * @param thislocation number of the calling location
 * @param norefresh value of the refresh flag for tabs which need no update
 * @param newtab value of the refresh flag for tabs after page switches
 * @return: resultset of all selected DA Keys
 */
DECLARE
    thislocation ALIAS FOR $1;
    norefresh ALIAS FOR $2;
    newtab ALIAS FOR $3;
        regtabids int8[] := '{}'::int8[];
    lastregtabid int8 := -1;
    strSQLwithKeys text;
    strSQLnoKeys text;
    recAffected record;
BEGIN
    strSQLwithKeys := 'SELECT bs.busystatus,tabs.tabnumber, keys.rownumber, keys.columnnumber, aur.terminalrolenumber, 
           tabs.refresh, keys.idcommunicationtype, aur.localmodecode, aur.trainmodecode, aur.nrisdn, tabs.idregistereddatabs 
           FROM 
           tableregistereddatabs tabs, tableactivedakeys keys, tablebusystatus bs, tableactiveuserroles aur 
           WHERE 
           tabs.refresh <> '|| norefresh ||' AND 
             tabs.locationnumber = '|| thislocation ||' AND 
           keys.tabnumber = tabs.tabnumber AND 
           keys.idactiveuserrole = tabs.idactiveuserrole AND 
           aur.idactiveuserrole = tabs.idactiveuserrole AND 
           keys.idbusystatus = bs.idbusystatus
           ORDER BY tabs.idregistereddatabs
           FOR UPDATE OF tabs;';
       FOR recAffected IN EXECUTE strSQLwithKeys LOOP
        IF lastregtabid <> recAffected.idregistereddatabs THEN
            regtabids := regtabids || recAffected.idregistereddatabs;
            lastregtabid := recAffected.idregistereddatabs;
        END IF;
               RETURN NEXT recAffected;
       END LOOP;

    strSQLnoKeys := 'SELECT -1::int2, tabs.tabnumber, -1::int2, -1::int2, aur.terminalrolenumber, tabs.refresh, -1::int8, -1::int4, -1::int4, 
             aur.nrisdn, tabs.idregistereddatabs FROM tableregistereddatabs tabs, tableactiveuserroles aur 
             WHERE
             tabs.refresh = '|| newtab ||' AND aur.idactiveuserrole = tabs.idactiveuserrole AND tabs.locationnumber = '|| thislocation || '';
    IF array_upper(regtabids, 1) IS NOT NULL THEN
         strSQLnoKeys := strSQLnoKeys || ' AND tabs.idregistereddatabs NOT IN ('|| array_to_string(regtabids, ', ') ||')';
    END IF;
    strSQLnoKeys := strSQLnoKeys || ' FOR UPDATE OF tabs;';
       FOR recAffected IN EXECUTE strSQLnoKeys LOOP
            regtabids := regtabids || recAffected.idregistereddatabs;
               RETURN NEXT recAffected;
       END LOOP;

    IF array_upper(regtabids, 1) IS NOT NULL THEN
        EXECUTE 'UPDATE tableregistereddatabs SET refresh = '|| norefresh ||' WHERE idregistereddatabs IN ('|| array_to_string(regtabids, ', ') ||')';
    END IF;
       RETURN;

end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION getbsddakeys(smallint, smallint, smallint) OWNER TO postgres;

Funktion 2:

CREATE OR REPLACE FUNCTION storeinternalbusystatus(character varying, smallint, smallint, smallint)
  RETURNS void AS
$BODY$
/**
 * This function
 * - sets busy status of physicalnumbers
 * - sets refresh flag of registered DA tabs which are affected by busy status change
 * - sets refresh flag of busy status interface registrations affected by busy status change
 *
 * @usage SELECT storeinternalbusystaus('2123385',1,1,1);
 * @param physicalnumber varchar of physicalnumber where busystatus is to be set
 * @param busystatus int2 value to be set - 0...free, 1...busy, 2...undefined
 * @param refresh int2 value to be set at refresh values
 * @param locationnumber int2 location which initiates this update
 * @return void
 */
BEGIN
-- update refresh flag of registered DA tabs
UPDATE tableregistereddatabs SET refresh = $3 
WHERE idregistereddatabs IN (
    SELECT dat.idregistereddatabs 
    FROM (
        -- get neighbours by physicalnumber of parent
        SELECT bs.idbusystatus, bsn.physicalnumber, false as isparent
        FROM tablebusystatus bs
            INNER JOIN (
                SELECT idbusystatus,idbusystatusneighbour 
                FROM tableactiveneighbourhoods
                UNION
                SELECT idbusystatusneighbour,idbusystatus
                FROM tableactiveneighbourhoods
            ) nb USING (idbusystatus)
            INNER JOIN tablebusystatus bsn ON bsn.idbusystatus = nb.idbusystatusneighbour
        UNION
        -- get parent by its own physicalnumber
        SELECT idbusystatus, physicalnumber, true as isparent
        FROM tablebusystatus
        ) bs 
        INNER JOIN tableactivedakeys da USING (idbusystatus) 
        INNER JOIN tableregistereddatabs dat USING(idactiveuserrole,tabnumber)
    WHERE bs.physicalnumber = $1
    )
    AND refresh < 2;
-- update refresh flag of busy status interface registrations
UPDATE tablebsifregistrations SET refresh = $3 
FROM (
    -- get neighbours by physicalnumber of parent
    SELECT bs.idbusystatus, bsn.physicalnumber, false as isparent
    FROM tablebusystatus bs
        INNER JOIN (
            SELECT idbusystatus,idbusystatusneighbour 
            FROM tableactiveneighbourhoods
            UNION
            SELECT idbusystatusneighbour,idbusystatus
            FROM tableactiveneighbourhoods
        ) nb USING (idbusystatus)
        INNER JOIN tablebusystatus bsn ON bsn.idbusystatus = nb.idbusystatusneighbour
    UNION
    -- get parent by its own physicalnumber
    SELECT idbusystatus, physicalnumber, true as isparent
    FROM tablebusystatus
    ) bs 
WHERE tablebsifregistrations.idbusystatus=bs.idbusystatus
    AND bs.physicalnumber = $1;
-- update busy status of physicalnumber
UPDATE tablebusystatus 
SET busystatus = $2, locationnumber = $4 
FROM (
    -- get neighbours by physicalnumber of parent
    SELECT bs.idbusystatus, bsn.physicalnumber, false as isparent
    FROM tablebusystatus bs
        INNER JOIN (
            SELECT idbusystatus,idbusystatusneighbour 
            FROM tableactiveneighbourhoods
            UNION
            SELECT idbusystatusneighbour,idbusystatus
            FROM tableactiveneighbourhoods
        ) nb USING (idbusystatus)
        INNER JOIN tablebusystatus bsn ON bsn.idbusystatus = nb.idbusystatusneighbour
    UNION
    -- get parent by its own physicalnumber
    SELECT idbusystatus, physicalnumber, true as isparent
    FROM tablebusystatus
    ) bs 
WHERE bs.physicalnumber = $1 AND tablebusystatus.idbusystatus = bs.idbusystatus;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION storeinternalbusystatus(character varying, smallint, smallint, smallint) OWNER TO postgres;

selber nein …

es wird blockiert - warten ist was anderes … dumm, wenn z.B. beide abfragen / updates auf connections ein und des selben clients in mehreren threads laufen die clientseitig ungünstig synchronisiert sind … d.h. der clent wartet dann u.U. quasi auf sich selbst … -> indirekte deadlock ueber die DB-Verbindung

man muss dabei immer das gesamtsystem im auge haben - nicht nur einen teil …

Die frage bleibt hier - in welchem kontext die funktionen aufgerufen werden … der code der funktionen hilft da nicht so recht weiter

Es gibt das CODE-Tag für den Editor, das erhöht die Leserlichkeit wesentlich.

Hast du eigentlich überhaupt meinen letzten Vorschlag in Betracht gezogen?

kannst du bitte kurz erlaeutern wann die funktionen warum aufgerufen werden ? potential fuer deadlocks ist da - das glauben wir dir ja :slight_smile:

aber es ist auch wichtig zu wissen wie es ueberhaupt dazu kommen kann, dass diese transaktionen gleichzeitig ausgefuehrt werden koennen (was ja legitim ist) und PG synchronisieren (blockieren) muss

d.h. naemlich dass du irgendwas multithreaded haben koenntest … was wiederum den schluss zulaesst, dass du die threads irgendwie synchroniserst … da wuerde ich mal schauen …

ich schaetze naemlich, dass du (wie mir auch schon passiert) uebereifrig semaphoren einsetzt wo die server-connection (TX) selbst doch schon eine semaphore darstellt - das macht das eine oder das andere nicht ueberfluessig - aber du solltest die synchronisierung nochmal genau anschauen - da sind sicher ein oder zwei wait() an der falschen stelle … also das problem scheint eher auf dem client zu finden

denn trigger - funktionen scheinen es ja nicht zu sein - da waere auch potential …

Im Programm bzw. in den Programmen läuft das so ab.

Programm 1 bekommt von einer Außenstelle Events und ruft bei jedem Event die Funktion storeinternalbusystatus auf. Die Frequenz dieser Aufrufe kann sehr hoch sein: bis zu > 100 Events pro Sekunde.

Programm 2 ruft periodisch (alle 500 ms) die Funktion getbsddakeys auf.

Zwischen diesen beiden Programmen findet keine synchronisation statt!

@ads
Habe deinen Lösungsvorschlag tatsächlich überlesen. Das Problem mit LOCKs zu lösen ist mein letzter Ausweg den ich realisieren will. Habe da schon schlechte Erfahrungen gemacht.

Lg, Kicmad

Welche schlechten Erfahrungen?

Klar, es ist langsamer. Aber der andere Ausweg für dich wäre, die Exceptions abzufangen und die Aktion von vorne zu starten. Das ist noch teurer als ein selektiver LOCK.

du hast in dem obigen selekt der ersten funktion ein SELECT - FOR UPDATE drin … da wird ja bereits gelockt … was ja dein problem ist - ich kann geraden nicht ganz nachvollziehen wie du ein locking-problem (deadlock) mit mehr lockings lösen willst :slight_smile: flucht nach vorn ?

ich wuerde das entweder einfach ohne FOR UPDATE fahren - oder mal ein NOWAIT dazubasteln um zu schauen wo ein Fehler gemeldet wird.

Der Fehler kann an unterschiedlichen Stellen auftreten, nicht nur an einer spezifischen.

Der Deadlock entsteht, weil zwei Prozesse die Tuples in umgekehrter Reihenfolge locken und sich dann gegenseitig blockieren. Also hilft kein SELECT FOR UPDATE weil zwischen dem ersten und zweiten SELECT trotzdem eine Deadlock Situation entstehen kann. Da hilft es nur, alle Tabellen mit einem einzigen LOCk Befehl zu sperren.

Alternativ, ich wiederhole es noch mal: die Transaktionen mit einem höheren Transaktionslevel fahren und die Anwendung darauf vorzubereiten, die Aktion ggf. mehrfach wieder auszuführen. Das erspart LOCKs und ist die Methode, die der Standard eigentlich vorsieht.

ich hab das SELECT FOR UPDATE nicht empfohlen … es ist bei ihm im code … in der ersten funktion … wie du schon sagst - 2 prozesse warten aufeinander … ich denke halt, dass es (damit man nicht gleich alles ueber board wirft) interessant waere zu sehen, was passiert, wenn er einfach mal ein NOWAIT einbaut … da sollen ja lt. doku fehler generiert werden … ich wuerde dabei hoffen, hinweise ueber den deadlock zu ergattern

und wieso eigentlich “in umgekehrter Reihenfolge gelockt” … ist ein lock auf eine range nicht auch wie eine transaktion zu sehen ? entweder alles oder nix ? hab ich da wieder ein probelm in PG entdeckt ? :wink: